Spreadsheets are among the most popular and common business applications in use today. Despite their usefulness, spreadsheets have limitations. Applications such as Microsoft Excel have introduced macros to assist the user in customizing the functionality. And this helps, but it doesn't meet the needs of users with specialized requirements. For those cases, Excel supports Visual Basic for Applications (or VBA).
If you've been around the Microsoft developer space for any length of time, you've no doubt heard of Visual Basic. In the case of Excel (and other Office applications) Microsoft created a language inspired by Visual Basic and exposed an application programming interface (or API). The API, in cooperation with VBA, makes it possible for end users to extend the functionality of the application. It even includes a design surface to augment the user interface, an editor, debugger and much more! If you don't know why this is so exciting or useful, that's what this guide is here to explain. After covering some language and syntax basics, we'll look at how to use the Visual Basic editor to design a simple user interface and write some code against it.
VBA is based upon Visual Basic 6, not Visual Basic .NET. But there are enough similarities between VB 6 and VB.NET that those who know VB.NET will be able to get up and running quickly.
Variable declaration in VBA is simple:
1myVar = 42
This declares a variable named
myVar and initializes it to
42. There is also an alternative syntax:
1Dim myVar As Integer 2myVar = 42
This explicitly declares
myVar to be of type
Integer. Notice that the declaration and initialization of
myVar are separate statements. When declaring a variable with the
Dim keyword, it must be initialized separately. One reason for this will become clear later on.
VBA has a number of data types, most of which exist in other languages. It also includes
Date. Arrays are declared with the following syntax:
1Dim Products(1 To 4) as String
This declares an array of 4
String instances. As you saw before, use of the
Dim keyword requires the array to be initialized in a separate statement or with an array, a group of statements.
1Products(1) = "Apples" 2Products(2) = "Oranges" 3Products(3) = "Peas" 4Products(4) = "Carrots"
Notice that in VBA, the first index of an array is 1. It is common for other languages to have zero-based arrays.
VBA supports the usual suspects for operators on numeric types. In other languages the additon operator (
+) will concatenate two strings. In VBA use the ampersand (
1Dim Message As String 2Message = "Greetings" 3Message = Message & " John Johnson!"
The value of
Message will now be
Greetings John Johnson!. Notice that strings in VBA are double-quoted. The single quote is used to precede a comment.
1Message = "Hello VBA" ' this line will be executed 2' this line will be ignored
In VBA, loops are conceptually the same as other popular languages. The syntax is more verbose.
1For x = 1 To 10 2 ' do something 3Next x
You can iterate over an array, using a different kind of loop, the for-each-in loop:
1Dim Product 2Dim Products(1 To 4) As String 3Products(1) = "Apples" 4' initialize Products 5For Each Product In Products 6 ' do something with Product 7Next
The loop will retrieve, in order, the elements in the
Products array. The retrieved element will be stored in the
Product variable and is used to refer to the element inside the body of the loop. Notice that the
Product variable must be declared outside of the loop.
Conditionals are another concept shared with other languages but with a more verbose syntax:
1WordCount = 1250 2If WordCount < 500 Then 3 ' too short 4ElseIf WordCount > 1500 Then 5 ' too long 6Else 7 ' just right 8End If
ElseIf has no space even though the conditional is terminated with
End If which does.
There are two types of procedure in VBA
Subroutines and functions are sets of named statements with an optional input. For example, a tip calculator would take as inputs the total amount of the bill and the size of the party. Then it would calculate the amount of the tip based on the inputs. The inputs are called the arguments list and are declared inside parentheses, similar to variables but without the
Dim keyword. The first difference is that a subroutine does not return a value, and a function does. A subroutine is declared with the
1Private Sub TipCalcSub(Total As Double, PartySize As Integer) 2 If PartySize < 8 Then 3 MsgBox (Total * 0.15) 4 Else 5 MsgBox (Total * 0.2) 6 End If 7End Sub
MsgBox is a function that will display the value passed to it in a modal dialog box. To execute the subroutine, call it by name followed by the arguments separated by commas:
1TipCalcSub 100.0, 3
Unlike in other languages, invoking a subroutine in VBA does not require parentheses. This is the function version of the tip calculator:
1Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double 2 If PartySize < 8 Then 3 TipCalcFn = Total * 0.15 4 Else 5 TipCalcFn = Total * 0.2 6 End If 7End Function
Sub keyword has been replaced with the
Function keyword. Notice the
As Double after the argument list indicating that the function will return a
Double. Inside the function body, the tip amounts are returned by assigning them to the name of the function. Calling a function is similar to other languages:
Calling a function does expect the arguments inside of parentheses.
The for-each-in loop introduced the idea of scoping, or determining where a variable is accessible. In the case of the for-each-in loop, the variable that holds the element for the current iteration is only accessible, or in scope, for the statements in the body of the loop. Variables can also be scoped to procedures (subroutines and functions) or scoped globally. A variable scoped to a procedure will be accessible only inside the procedure that declared it.
1Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double 2 Dim TipAmount 3 If PartySize < 8 Then 4 TipAmount = Total * 0.15 5 Else 6 TipAmount = Total * 0.2 7 End If 8 TipCalcFn = TipAmount 9End Function
Here the variable
TipAmount has been used to store the computed tip and is then returned by the function. If you try to access
TipAmount outside of the function, you will be rewarded with an error as
TipAmount is scoped to
TipCalcFn. You could also scope
TipAmount globally by declaring it outside of any procedure:
1Dim TipAmount 2 3Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double 4 If PartySize < 8 Then 5 TipAmount = Total * 0.15 6 Else 7 TipAmount = Total * 0.2 8 End If 9 TipCalcFn = TipAmount 10End Function
Note that while variables can be declared outside of procedures, they cannot be initialized outside of procedures.
1Dim TipAmount 2TipAmount = 0.0 ' Error! 3 4Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double 5 ' calc tip 6End Function
One more note about declaring variables in any scope. You saw earlier how to declare a variable with or without the
Dim keyword. Using the
Dim keyword is an explicit variable declaration. It is possible to force all variables to be explicitly declared by adding
Option Explicit to the top of the code.
Let's create a tip calculator in Excel using VBA. First, you'll need access to the Developer tab on the ribbon bar. In Excel, right click in an empty space on the ribbon bar and select Customize the Ribbon.
In the right hand side of the Excel Options dialog, under Customize the Ribbon, check Developer, and click OK.
In the Developer tab, click the button to bring up the Visual Basic editor.
The editor will appear in a new window. In the menu bar, click Insert and then UserForm. A new UserForm will be shown with a design surface for creating a user interface. Notice also the Toolbox will appear.
In the Toolbox, select the Command Button and drag a control on the design surface.
With the Command Button selected, in the Properties pane, change the Caption to Calculate Tip.
Now drag two TextBoxes onto the design surface for the bill total and party size. You could also add labels to identify them.
Select the first TextBox and in the Properties pane, change the name to
txtAmount and the second TextBox name to
Right click on the Command Button and select View Code.
The editor will open a code file and add a subroutine that will be invoked when the Command Button is clicked. Start off by adding the
Option Explicit statement at the top, about the
Now add the function to calculate the tip amount that we saw previously.
CommandButton1_Click subroutine, add the code to get the values of the TextBoxes (which are Strings), convert them to a Double and Integer, and display the result of the
TipCalcFn in a message box.
1Dim Amount As Double 2Dim PartySize As Integer 3Amount = CDbl(txtAmount.Value) 4PartySize = CDbl(txtPartySize.Value) 5MsgBox (TipCalcFn(Amount, PartySize))
Click the run button to show the UserForm in Excel.
In Excel, enter an amount and party size in the TextBoxes and click the Command Button. A message box will pop up with the tip amount.
Of course, the tip amount should really be stored in the spreadsheet. The
Range function takes the coordinates of a cell or range of cells in R1C1 format. If the coordinate are for a cell, a value can be assigned to that cell. Close the message box and the UserForm and change the last line of the
1Range("A1") = TipCalcFn(Amount, PartySize)
Run the UserForm again and this time, the tip amount will be placed in the spreadsheet in cell A1.
The Visual Basic editor in Microsoft Excel is a very powerful tool. As you type code, the editor helps you out as much as it can. For example, it supports IntelliSense, which gives you code hints.
And it also provides help about the argument list and return values of procedures:
But there's more! Let's introduce an error into the code. I'll comment out the
Since we added
Option Explicit to the top of the code, assigning to
Amount will result in an error if the Command Button is clicked.
Not only does the editor notify us of the error, but it also highlights the error in the code.
In this guide, we were only able to scratch the surface of using Visual Basic for Applications with Microsoft Excel. As you interact more with the Excel API, you can automate many processes that would otherwise take a lot of time by hand. Also, automating processes makes them more repeatable and reliable. There are other features that we didn't get to look at, such as the debugger and object browser, as well as other features of VBA. But you now have a good foundation to use when consulting the documentation.
Thanks for reading this guide!