Visual Basic for Applications

One of the coolest Excel features – in addition to the vast repositories of functions that are available in its spreadsheets – is VBA or Visual Basic for Applications. It allows a user to create a procedure – or a macro – to perform more advanced calculations or automate repetitive calculations.

Accessing VBA

1. Go to File > Options > Customize Ribbon
2. Under Customize Ribbon and under Main Tabs, select the Developer Checkbox

3. Once the Developer tab is enabled, press OK and go back to excel. The Ribbon should show the Developer Tab.

4. There are two ways to get to VBA. Either click on the Visual Basic and get to the window or one could click on Record Macro to record a set of actions which would allow the user to record a set of actions to be repeated over and over again.

5. Personally, I prefer the Visual Basic route, but I often use the Record Macro option, if I am unsure of something and I need to quickly verify what a section of the code would look like. For example, I don’t remember how to highlight a particular cell with a particular colour – the Record Macro function is a great help there.

6. Once you click the Visual Basic button, you will be greeted with a window that looks like this:

7. To start writing our first Visual Basic program, go to Insert > Module.

8. A white space opens up where the user can write VBA code or macros.

9. Notice how a Modules folder opens up on the left panel. For more advanced code, a user may some times end up with multiple modules.

Writing our First VBA Code

Let’s write a very simple code that calculates the hypotenuse of a right angled triangle, given two sides of a right angled triangle.

Sub hypotenuse()

Dim a As Single, b As Single, hypotenuse As Single

Range("A1") = "Side 1"
Range("B1") = "Side 2"
Range("C1") = "Hypotenuse"

Range("A2") = 10
Range("B2") = 12
a = Range("A2").Value
b = Range("B2").Value

hypotenuse = (a ^ 2 + b ^ 2) ^ 0.5
Range("C2") = hypotenuse

End Sub

To run this code, all we have to do is go to Run > Run Sub/Userform. Alternatively, we can use the shortcut F5.

Upon running this code, we should see the following on our Excel Sheet:

The user may close the VBA window at this point, and to access it at a later time, the user may click on Macros and click Edit.

To save this file, save it as an Excel Macro-Enabled Workbook(*.xlsm) file.

Let’s analyse the code a bit here:

We start off with Sub hypotenuse() line. This line defines a subroutine. As soon as you write this subroutine, an End Sub line appears below it. Any code you write between these two lines will be run.

The next line which we see is the Dim a As Double, b As Double, hypotenuse As Double line. This line defines the variable which we will be using. In general, VBA allows for the following Variable types:

Of all the data types available within VBA, we could have used either Integer, Long, Single, Double or Variant for this piece of code. However, given that we were likely to have non-integer values, Integer and Long were not the best options. Single was chosen over Double and Variant for memory efficiency.

We then have:

Range("A1") = "Side 1"
Range("B1") = "Side 2"
Range("C1") = "Hypotenuse"

In these lines of code, we’re merely asking the code to fill up the Excel Cells A1, B1 and C1 with Side 1, Side 2 and Hypotenuse.

We then have:

Range("A2") = 10
Range("B2") = 12
a = Range("A2").Value
b = Range("B2").Value

In these lines of code, we assign certain values to the cells A2 and B2 and subsequently store the values in those cells in variables a and b respectively.

We then calculate the value of the hypotenuse using the well-known Pythagorean theorem; and save the value in cell C2 in the following lines of code.

hypotenuse = (a ^ 2 + b ^ 2) ^ 0.5
Range("C2") = hypotenuse

While the above code gets the job done, it may be useful to store it as a formula, rather than as a Macro. Let’s explore how we accomplish that in the next post.