Showing posts with label Userform. Show all posts
Showing posts with label Userform. Show all posts

VBA Excel Spreadsheets: Developing Loan Calculation Generator using VBA Excel
Sunday, December 19, 2010

A week ago, there’s one request from my customer to develop an automated Loan Calculation using Excel VBA and Spreadsheets. As I mention before I’m actually a part time freelance that accepted any request from my customer to create or develop a custom VBA Excel or Excel Spreadsheets. As time passed by I got many request from my customer that asked me to teach them on how we can connect and integrate VBA interface with Excel Spreadsheets. Many of them would like to learn advanced VBA Excel, so that they can easily utilized it on their work later on. And my advice to them, advanced VBA Excel is actually quite easy to learn but one need a little bit patience and continuous effort in learning them.:)

Today I would like to share with my reader on how we can create and integrated VBA interface with Excel Spreadsheets and I will used one of my complete VBA Excel application here as an example. As you can see in the following figure, it is actually a complete Loan Calculation Generator that can be use to estimate what type of loan one can apply based on their salary, age and so on. Before I further explaining about the VBA coding used in this application, I would like first to briefly explain about how this application might work:






As you can see in the figure above, it is actually a simple user interface created for the user to key in the required inputs and the result/output will be automatically placed in the Excel Spreadsheets once you hit “SUBMIT DATA” Commond button. Now, I would like to show on how the interface can be easily created and how you can assigned each button/object to perform a particular action.

First, open your VB Editor, right click on the project explorer window and insert UserForm.


Click on your UserForm window and you’ll see Toolbox. You can add your object control here like Label, CommandButton, Listbox, Frame, TextBox, picture, OptionButton and so on. You can sort and arranged your object according to your creativity.

Now you want to write VBA code inside the object . I’ll give you one example here for the “SUBMIT DATA” CommandButton. In your UserForm window, doubleclick on any of your object you wish to give them instruction and you’ll be directed into the source code window for that particular object. You can immediately start writing your code here.


Here is the result of Loan Calculation Generator Spreadsheets:

*Click for large Image

I can’t write all the VBA code here but if you wish to get them you can leave your email here. I’ll send you want I noticed it. I think that’s all from now and if you have any question just asked here ok. Till then~

Posted byMatt at 3:33 PM 0 comments  

Labels: , , ,

VBA Excel Array: How to understand array in VBA
Friday, December 17, 2010

It has been a very hectic day for me recently. I’ve been involved in one of my company project that required me to program some intricate array of datas. As a new employed staff there my knowledge on programming in vba is just quite a few. To think and handle with a huge of data after data make my day so miserable. This project is actually required me to sort and to recognize what type of data that need to be used in the calculation within this project. But after completing this task my knowledge on sorting and the use of the function of array in VBA become very rigid that I think I can easily handle an easy array now. :)

And today I would like to share some of my experiences handling with quite a bunch of datas that are related with one another and how to understand the metric use in array function within VBA environment. Dealing with VBA Excel array is actually quite the same as VB language applied. Specifically, I would like to make some understanding on how to sort a datas and place them in the excel spreadsheet. If you don’t know about the power of spreadsheet function you can review the topic before I post this entry here.

I will start with an example that I think would ease the way you understand this. VBA Excel array is actually counted in metric and it would normally start with (0,0) metric. As you can see here the zero on the left bracket normally represent the column while the right zero represents the row of array. Say you have a simple table represent two types of data below:

If you would like your program to be able to read this table and place it in excel sheet all you have to do is to make an array metric of (1,0)->Robert, (1,1)->A114562, (2,0)->Adam, (2,1)->A123345, (3,0)->Julia, (3,1)->A567678. Noted that metric (0,0) and (0,1) represent the “Name:” and “id. Number” respectively. This is how you can give a metric on each of your data. After recognizing all the metrics required than you can easily program your code in VBA.

Here are the steps:

1) Open your VB editor within your Microsoft Excel. If you miss up on how to open VB editor you can back refer here.

2) Define your VBA Excel array. As in the example above you need to define your array to be say:

Dim MyArrayName As (i , 3)

‘Every time you loop your array, the value of i will increase from 1 to 3

3) Write your code for sorting your VBA Excel array:
Sub VbaExcelArray ()

i = 0

a = i + 1

ReDim MyArrayName(i, 3)

MyArrayName(i, 0) = a & "."

MyArrayName(i, 1) = UserForm2.TextBox1.Value ‘Read Name

MyArrayName(i, 2) = UserForm2.TextBox2.Value ‘Read Id. Number

End If

4) The codes above are specifically written if you want to read an array in userform table that you must create earlier. And from this userform table you can call the data and place it in the listbox option. For example you can wrote your code as shown below:

UserForm1.ListBox1.List = MyArrayName()

Example of Listbox in Userform

Example of Userform table

This quick example would just show on how VBA read and program the data that need to be sort in an array form. Next time I would like to share on how this VBA Excel array function can be so much interesting when dealing with a huge datas. Till then~

Posted byMatt at 1:42 AM 0 comments  

Labels: , , ,

VBA Excel: Develop Engineering Economy Factor Table
Sunday, October 10, 2010

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 0 comments  

Labels: , , ,

Subscribe to: Posts (Atom)