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
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.
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.. ^^Posted byMatt at 7:29 PM 0 comments
Labels: Engineering Economy Factor, Excel, Userform, VBA
Visual Basic(VB) Editor: It has been some while...
Wednesday, July 21, 2010
It has been some while since I wrote my last and my very first post.Today is 21th of July and today is the day where I'm about to start writing and share with you guys about what I've been learning so far on programming. I believe most programmer, they would probably start learning programming with VB which stand for Visual Basic. I'm myself learnt my very first code in VB and it is VB editor build in Microsoft Excel.So from today onward I will most likely share with you some basic application that can be build by using VB Excel Editor. Frankly speaking at first I'm totally zero about what is programming all about but with enthusiasm and interest of learning this computer language gradually I managed to understand some basic coding that would be beneficial to me as a naive beginner. So first, before I begin sharing what I've been learning so far about VB, it is good if I can briefly explain to you what is VB Excel editor and how you can get excess to it and hence get use to its environments.
<*Note - You must have Microsoft Excel 2003 in order to follow with my explanation and do some simple tutorial that I'm about to share>
Below are easy instructions on how you can get excess to VB editor build in Microsoft Excel:
> Open Microsoft Excel 2003 , Go to Tool/Macro/Visual Basic Editor Or simply hold on Alt and press F11. You will see a new screen pop up as shown below:
Here I'll briefly explain the functions of each of the toolbox and properties happen to have in this editor:
1) First you'll have Project Explorer and Project Window at default. Project Explorer simply visualize about the tree that you might encounter in your application such as Sheet,Workbook,UserForm, module and ClassModule while Project Window is where the properties of each elements lies in. First you will noticed that there's only a set of 3 sheets and also workbook represent once you open this editor. In order to add those UserForm and module all you have to do is simply Right Click your mouse in the Project Explorer and choose INSERT. There are 3 options all together which is UserForm, Module and ClassModule as you can see in the following diagram;-
Some Useful Definition and its Functions
- UserForm - It is some sort like user friendly interface where the user can enter datas, key in inputs and thus evaluate those datas with just one click on a button created on that UserForm. To make it more clearer once you insert your first UserForm in the Project Explorer you will have a Toolbox where there's a bunch of options and command buttons that you can easily insert and place it on your UserForm. On the other hand your application will depend on how creative you create your Userform.
- Module - Here is the place where your source code will be place. You can manage your module depending on how you construct your application.
- In the "ToolBox" box there's some useful buttons and control tools that you can use to create your interface such as ;- Text, Label, Frame, MultiPage, CommandButton, ListBox, OptionButton, Combo Box and so on. For the sake of learning it is good if you can try to play around and see how this button and control tools does work.
2)Once you get use to this VB editor environment now is the time to do some simple coding and see how interesting VB programming can be. Your very first code will be "MsgBox" command.
Insert a module and type in;-
Sub MyFirstCode ( )
MsgBox "Hello World! My Name is....."
End Sub ( )
Hit the Play button in the top bar of VB editor.The result will be as below;-
3)Some important tips while writing your codes;
- Bare in mind every time you start coding it must begin with Sub Module ( ) and end with End ( ).
- Between this two codes here is where your main coding will take place.
This is just a very simple brief explanation about VB editor and a simple coding that a beginner can have. This is also a very first lesson that I have while learning programming with VB. Hope this post can give a glimpse to a newbies about what is VB language all about and its environment in Microsoft Excel. Stay tune and hopefully I'll come out with some more interesting coding in VB Excel and again sorry for my English..
Posted byMatt at 12:20 AM 0 comments
Labels: Beginner, VB Editor, Visual Basic
This is my first attempt...
Thursday, May 27, 2010
Hi,first thing first, let me briefly introduced who am I and what am I be doing right now.Today is May 27Th and I just barely sit on my chair typing this entry. You can just call me Matt and from now I'm in the journey of learning more and more on programming.I'm not telling that I'm a superb at this field but I'm just about to announced that this blog will be my journey record on what I'm doing toward this goal.It sort of diary one can say. I also want to justify that this blog will be written in English though my English is not very good. Whatever seem nice to me I'll just wrote it and criticism is much more welcome and thankful.
I think that's it from now and I'll continue writing this blog as often as I can.Thanks!
Posted byMatt at 3:05 AM 0 comments