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:
Posted byMatt at 3:33 PM
Labels: Loan Calculation Spreadsheets, Spreadsheets, Userform, VBA Excel