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 0 comments
Labels: Loan Calculation Spreadsheets, Spreadsheets, Userform, VBA Excel
Budgeting spreadsheets: Developing Spreadsheets for the Future Worth Value Calculation
Saturday, December 18, 2010
Today, one of my friends asked me whether I can develop automated budgeting spreadsheets in Excel to estimate what is the Future Worth Value of one machine. Actually his company would like to make a decision to purchase the lease cost of several machines the vendor offered to them. They would like to make a comparison between those machines and he asked me to include only 5 variables in determining the future worth value of one machine which is the initial investment, interest rate, annual operating cost and also the number of year before the machine down.
Initially, I rejected his request as this problem is quite unrelated to my field but after doing some research on the future worth calculation I’ve found out that it is actually quite easy to digest. Moreover all of the variables that my friend asked me to include are actually the basic fundamental variables in calculating one future worth. Back in the date, I remember I used to learn about the Engineering Economic courses in my school and one of the syllabuses did teach us about the uses of Engineering Economic Factor. You may refer here for the VBA program for Engineering Economic Table that I’ve developed before.
But today I would not like to share with you on the Engineering Economic table rather on how you can easily developed your own budgeting spreadsheets for future worth value. After doing some research on the future worth calculation then it only takes me about an hour to complete my budgeting spreadsheets. I’ve inserted the values for the required variables and generating 3 different results for 3 different machines as shown below:
It’s actually quite easy to setup and use. All you have to do is to fill in the * column in order to automate calculating the future worth value donated in the blue column as shown in the figure above. This budgeting spreadsheets can be customized according to what worth value do you want to find. If you want to get one or to modify it according to your need you can asked for it here all right. Till then~
Posted byMatt at 2:38 AM 0 comments
Labels: Budgeting Spreadsheets, CFD, Excel, Future Worth, Spreadsheets
VBA Excel Cell: How to connect custom VBA program with Excel Spreadsheet
Friday, December 17, 2010
A part from working with my company, I used to do some freelance work on the custom VBA Excel program. Usually I’ll do some advertised on the web offering people on the automated VBA program that I think would ease their daily basis work. There are a lot of my customers who want me to program some easy application that would give the result or output in the Excel Spreadsheet. This is actually a very basic fundamental knowledge for one VBA Excel programmer in dealing and connecting data between VBA and Excel Cell within Excel Spreadsheet.
I would not like to mess your mind up with all the VBA coding but with some easy practical example. I hope by going through this example you can easily understand and hence applying it on your work or task immediately.
As we all know the spreadsheet excel is sort in cell form indicating by alphabet (A-Z) for the column and digit (1-infinite) for the row. For example Column 1 and Row 1 are donated by A1 in Excel cell. Ya, I know everybody knew this but what I would like to stress here is actually the characteristic of Excel cell that you must know in order to establish the connection with the VBA program within Excel itself. For this example I would like to give some example on how you can extract data from the text file and put it within excel spreadsheet cell. Below are the data from the text file:
From these data I would like to build some easy coding on VBA in order to extract those data and placed it in Excel cell. For example I would like to read the value of OD (0.2191), TH (0.0191), SRHO (7850), E (207000000000) and POISS (0.3) and then placed it in the Excel Cell.
In order to read the text file, first you need to open the file by using this coding:
Open Pathname For Input As #1
‘Pathname here need to be defined first on where the file is located in your directory. For example you can set Pathname to be = “C:\Users \Documents\VBA Excel Cell \Input Data\LMEP0000”
After that you can program your VBA to be able to read the text file line after line. Below is the code:
Input #1, xx(i), OD(i)
Line Input #1, textline
Input #1, xx(i), TH(i)
Line Input #1, textline
Input #1, xx(i), SRHO(i)
Line Input #1, textline
Input #1, xx(i), E(i)
Line Input #1, textline
Input #1, xx(i), POISS(i)
After you have successfully read all the required values then you’ll just need to write a simple VBA coding to place them in the Excel cell. Below is how you can write it:
With Worksheets ("VBAExcelCell")
.Cells(1,1).Value = “OD Value”
OD(i) = .Cells(1,2).Value
.Cells(2,1) .Value = “OD Value”
TH(i) = .Cells(2,2).Value
.Cells(3,1) .Value = “OD Value”
SRHO(i) = .Cells(3,2).Value
.Cells(4,1) .Value = “OD Value”
E(i) = .Cells(4,2).Value
.Cells(5,1) .Value = “OD Value”
POISS(i) = .Cells(5,2).Value
End With
And the result is as below:
I hope you can try it by your own to make your understanding on the VBA Excel cell clearer. It is actually a very fundamental knowledge in connecting any input file with Excel Cell. Till then~
Posted byMatt at 9:42 PM 0 comments
Labels: Excel, Excel Cell, Spreadsheets, VBA, VBA Excel Cell
VBA Excel Array: How to understand array in VBA
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:
‘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:
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:
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: Excel, Userform, VBA Array, VBA Function