Hi all. A warm greeting from me to everyone..This morning I wake up at 7.00am washing my face and have a very quick breakfast and I'm at my room while writing this entry.It has been some time but today I'm about to share with you on how to perform and create some basic table on Excel worksheet by using visual editor built in Excel. If you are studying Engineering Economy, you must probably learning about engineering economy factor which is important in calculating the Present Worth value, Annual Worth, Future Worth and etc. To calculate or predict these value you must first know how to calculate the factor that used in those calculation. For example you are planning to invest some amount of bucks for your business. While knowing the interest involved and the time for your investment to gain profit you can predict the future worth by evaluating the engineering factor in your prediction.
And today, I would like to share with you step by step on how to developed your own engineering factor table by coding some simple visual basic code in VB editor. You can also make it more attractive by creating some simple interface in order user to key in the required input for the interest, i and time,n .
Here is how your interface may look like.You can also create your own interface that is more attractive in the VB editor.
You can insert the percent of interest,i and number of year,n. In the "Show Table" command button you can coding some visual basic code in order to execute those input in one table in Excel Worksheet. OK, here is how it work.First, you can start writing the code inside command button by double click on it as shown in the following figure;
Now I would like to briefly explained on how you can write your code inside any object that you'd inserted within your interface. Say you have already create your own interface as shown in the first figure. double click on the "Show Table" command button. You'll be directed to the source code environment where you can start your coding. Once you enter inside any object it will automatically define the way you can activate it that is _Click(Single Click). You may also change the way it will be execute as you like say _DbClick, _Initialize, _activate and .etc.
Private Sub CommandButton1_Click()
{Your code}
End Sub
{Your code} ---> For calculating Engineering Economy factor for F/P, P/F, A/F, F/A, A/P and P/A
Private Sub CommandButton1_Click()
With Worksheets("Engineering Economy Table") '' Define your sheets name in Excel Worksheet
Format_Table.Format_Table '' The Format of your table.You can just simply macro it
n = 480 '' The number of years, n .you can either fix it or flexible its value in user interface
a = 480
i = TextBox7.Value * 1 / 100 '' The interest rate read from Textbox7 in your userform
Dim MyArrayFactor(12, 12) '' Define your Array to matrix 12 x 12
For n = 1 To a '' Use For Function to iterate your calculation from n = 1 to say 480
''Inserting your input in Excel Cell and calculating for each of the factor
************************************************************************
.Cells(5 + n, 1) = n
.Cells(5 + n, 8) = n
.Cells(5 + n, 2) = Format((1 + i) ^ n, "0.0000") ' *p
.Cells(5 + n, 3) = Format((1 + i) ^ -n, "0.0000")
.Cells(5 + n, 7) = Format(((((1 + i) ^ n) - 1) / (i * (1 + i) ^ n)), "0.0000") ' *A
.Cells(5 + n, 6) = Format(((i * ((1 + i) ^ n)) / (((1 + i) ^ n) - 1)), "0.0000") '*P
.Cells(5 + n, 4) = Format((i / (((1 + i) ^ n) - 1)), "0.0000") '*F
.Cells(5 + n, 5) = Format(((((1 + i) ^ n) - 1) / i), "0.0000") '*A
************************************************************************
Next n "For every For Function it must end with Next n (iteration)
.Cells(1, 5) = TextBox7.Value * 1 & " % percent"
.Cells(1, 3) = TextBox7.Value * 1 / 100
End With " For every With function it must end with End With
" Format of your table
************************************************************************
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.Zoom = 100
Range("C1,A6:A485").Select
Range("A6").Activate
Range("C1").Select
Selection.Interior.ColorIndex = 6
Range("A6:A485").Select
Selection.Interior.ColorIndex = 6
Range("A1:H5").Select
Range("H5").Activate
Module1.Macro4
Module1.Macro11
Range("A1:H5").Select
************************************************************************
Application.WindowState = xlMaximized " Maximized your Excel Worksheet once the_ calculation completed.
End Sub
The result should be as in the following figure:
You can try to copy paste this code into your command button and try to evaluate it. If there's a debug error just let me know. I'll try to advice on how to solve it . Till Then.. ^^
Posted byMatt
at
7:29 PM
Labels:
Engineering Economy Factor,
Excel,
Userform,
VBA
Now I would like to briefly explained on how you can write your code inside any object that you'd inserted within your interface. Say you have already create your own interface as shown in the first figure. double click on the "Show Table" command button. You'll be directed to the source code environment where you can start your coding. Once you enter inside any object it will automatically define the way you can activate it that is _Click(Single Click). You may also change the way it will be execute as you like say _DbClick, _Initialize, _activate and .etc.
You can try to copy paste this code into your command button and try to evaluate it. If there's a debug error just let me know. I'll try to advice on how to solve it . Till Then.. ^^