Visual Basic for Applications with Excel Fundamentals

Mar 23, 2020 • 13 Minute Read

Introduction

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.

Visual Basic for Applications Primer

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:

      myVar = 42

This declares a variable named myVar and initializes it to 42. There is also an alternative syntax:

          Dim myVar As Integer
myVar = 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 String, Boolean, Double and Date. Arrays are declared with the following syntax:

      Dim 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.

          Products(1) = "Apples"
Products(2) = "Oranges"
Products(3) = "Peas"
Products(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 (&).

          Dim Message As String
Message = "Greetings"
Message = 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.

          Message = "Hello VBA" ' this line will be executed
' this line will be ignored
    

In VBA, loops are conceptually the same as other popular languages. The syntax is more verbose.

          For x = 1 To 10
    ' do something
Next x
    

You can iterate over an array, using a different kind of loop, the for-each-in loop:

          Dim Product
Dim Products(1 To 4) As String
Products(1) = "Apples"
' initialize Products
For Each Product In Products
    ' do something with Product
Next
    

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:

          WordCount = 1250
If WordCount < 500 Then
    ' too short
ElseIf WordCount > 1500 Then
    ' too long
Else
    ' just right
End If
    

Notice that ElseIf has no space even though the conditional is terminated with End If which does.

Procedures

There are two types of procedure in VBA

Subroutine
Function

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 Sub keyword:

          Private Sub TipCalcSub(Total As Double, PartySize As Integer)
    If PartySize < 8 Then
        MsgBox (Total * 0.15)
    Else
        MsgBox (Total * 0.2)
    End If
End Sub
    

The 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:

      TipCalcSub 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:

          Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double
    If PartySize < 8 Then
        TipCalcFn = Total * 0.15
    Else
        TipCalcFn = Total * 0.2
    End If
End Function
    

Obviously, the 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:

      MsgBox(TipCalcFn(100.0, 3))

Calling a function does expect the arguments inside of parentheses.

Variable Scoping

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.

          Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double
    Dim TipAmount
    If PartySize < 8 Then
        TipAmount = Total * 0.15
    Else
        TipAmount = Total * 0.2
    End If
    TipCalcFn = TipAmount
End 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:

          Dim TipAmount

Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double
    If PartySize < 8 Then
        TipAmount = Total * 0.15
    Else
        TipAmount = Total * 0.2
    End If
    TipCalcFn = TipAmount
End Function
    

Note that while variables can be declared outside of procedures, they cannot be initialized outside of procedures.

          Dim TipAmount
TipAmount = 0.0 ' Error!

Private Function TipCalcFn(Total As Double, PartySize As Integer) As Double
    ' calc tip
End 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.

Putting it All Together

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 txtPartySize.

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 CommandButton1_Click subroutine.

Now add the function to calculate the tip amount that we saw previously.

In the 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.

          Dim Amount As Double
Dim PartySize As Integer
Amount = CDbl(txtAmount.Value)
PartySize = CDbl(txtPartySize.Value)
MsgBox (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 CommandButton1_Click subroutine.

      Range("A1") = TipCalcFn(Amount, PartySize)

Run the UserForm again and this time, the tip amount will be placed in the spreadsheet in cell A1.

Features of the Editor

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 Amount variable.

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.

Summary

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!