Everybody in this country should learn how to program a computer... because it teaches you how to think." -Steve Jobs
I wish to extend the wise words of Steve Jobs and say everyone in the world should learn how to program a computer. You may not necessary end up working as a programming or writing programs at all but it will teach you how to think.
In this tutorial, we are going to cover the following topics.
- What is VBA?
- Why VBA?
- Personal & business applications of VBA in excel
- Visual Basic For Applications VBA basics
- Step by step example of creating a simple EMI calculator in Excel
VBA stands for Visual Basic for Applications. Before we go into further details, let's look at what computer programming is in a layman's language. Assume you have a maid. If you want the maid to clean the house and do the laundry. You tell her what to do using let's say English and she does the work for you. As you work with a computer, you will want to perform certain tasks. Just like you told the maid to do the house chores, you can also tell the computer to do the tasks for you.
The process of telling the computer what you want it to do for you is what is known as computer programming. Just as you used English to tell the maid what to do, you can also use English like statements to tell the computer what to do. The English like statements fall in the category of high level languages. VBA is a high level language that you can use to bend excel to your all powerful will.
VBA is actually a sub set of Visual Basic 6.0 BASIC stands for Beginners All-Purpose Symbolic Instruction Code.
- It uses English like statements to write instructions
- Creating the user interface is like using a paint program. You just have to drag, drop and align the graphical user interface controls.
- Short learning curve. From day one that you start learning, you can immediately start writing simple programs.
- Enhances the functionality of excel by allowing you to make excel behave the way you want it to
For personal use, you can use it for simple macros that will automate most of your routine tasks. Read the article on Macros for more information on how you can achieve this.
For business use, you can create complete powerful programs powered by excel and VBA. The advantage of this approach is you can leverage the powerful features of excel in your own custom programs.
Before we can write any code, we need to know the basics first. The following basics will help you get started.
- Variable – in high school we learnt about algebra. Find (x + 2y) where x = 1 and y = 3. In this expression, x and y are variables. They can be assigned any numbers i.e. 1 and 3 respective as in this example. They can also be changed to say 4 and 2 respectively. Variables in short are memory locations. As you work with VBA, you will be required to declare variables too just like in algebra classes
- Rules for creating variables
- Don't use reserved words – if you work as a student, you cannot use the title lecturer or principal. These titles are reserved for the lecturers and the school authority. Reserved words are those words that have special meaning in VBA and as such, you cannot use them as variable names.
- Variable names cannot contain spaces – you cannot define a variable named first number. You can use firstNumber or first_number.
- Use descriptive names – it's very tempting to name a variable after yourself but avoid this. Use descriptive names i.e. quantity, price, subtotal etc. this will make your VBA code easy to read
- Arithmetic operators - The rules of Brackets of Division Multiplication Addition and Subtraction (BODMAS) apply so remember to apply them when working with expressions that use multiple different arithmetic operators. Just like in excel, you can use
- + for addition
- - for subtraction
- * for multiplication
- / for division.
- Logical operators - The concept of logical operators covered in the earlier tutorials also apply when working with VBA. These include
- If statements
VBA Hello world
We will create a basic VBA program that displays an input box to ask for the user's name then display a greeting message
This tutorial assumes you have completed the tutorial on Macros in excel and have enabled the DEVELOPER tab in excel.
- Create a new work book
- Save it in an excel macro enabled worksheet format *.xlsm
- Click on the DEVELOPER tab
- Click on INSERT drop down box under controls ribbon bar
- Select a command button as shown in the image below
Draw the command button anywhere on the worksheet
You will get the following dialogue window
- Rename the macro name to btnHelloWorld_Click
- Click on new button
- You will get the following code window
Enter the following instruction codes
Dim name As String
name = InputBox("Enter your name")
MsgBox "Hello " + name
- "Dim name as String" creates a variable called name. The variable will accept text, numeric and other characters because we defined it as a string
- "name = InputBox("Enter your name")" calls the built in function InputBox that displays a window with the caption Enter your name. The entered name is then stored in the name variable.
- "MsgBox "Hello " + name" calls the built in function MsgBox that display Hello and the entered name.
Your complete code window should now look as follows
- Close the code window
- Right click on button 1 and select edit text
- Enter Say hello
- Click on Say Hello
- You will get the following input box
- Enter your name i.e. Jordan
- You will get the following message box
Congratulations, you just created your first VBA program in excel
In this tutorial exercise, we are going to create a simple program that calculates the EMI. EMI is the acronym for Equated Monthly Instalment. It's the monthly amount that you repay when you get a loan. The following image shows the formula for calculating EMI.
The above formula is complex and can be written in excel. The good news is excel already took care of the above problem. You can use the PMT function to compute the above.
The PMT function works as follows
- "rate" this is the monthly rate. It's the interest rate divided by the number of payments per year
- "nper" it is the total number of payments. It's the loan term multiplied by number of payments per year
- "pv" present value. It's the actual loan amount
Create the GUI using excel cells as shown below
Add a command button between rows 7 and 8
Give the button macro name btnCalculateEMI_Click
Click on edit button
Enter the following code
Dim monthly_rate As Single, loan_amount As Double, number_of_periods As Single, emi As Double
monthly_rate = Range("B6").Value / Range("B5").Value
loan_amount = Range("B3").Value
number_of_periods = Range("B4").Value * Range("B5").Value year
emi = WorksheetFunction.Pmt(monthly_rate, number_of_periods, -loan_amount)
Range("B9").Value = emi
- "Dim monthly_rate As Single,…" Dim is the keyword that is used to define variables in VBA, monthly_rate is the variable name, Single is the data type that means the variable will accept number.
- "monthly_rate = Range("B6").Value / Range("B5").Value" Range is the function used to access excel cells from VBA, Range("B6").Value makes reference to the value in B6
- "WorksheetFunction.Pmt(…)" WorksheetFunction is the function used to access all the functions in excel
The following image shows the complete source code
- Click on save and close the code window
- Test your program as shown in the animated image below
VBA stands for Visual Basic for Application. It's a sub component of visual basic programming language that you can use to create applications in excel. With VBA, you can still take advantage of the powerful features of excel and use them in VBA.