Excel Visual Basic (VBA) Tutorial - Part 1

Introduction

This tutorial aims to introduce Visual Basic for Applications (VBA, but often referred to as just VB) in the context of Microsoft Excel. Like the other applications included as part of Microsoft Office, Excel uses VBA as a way of extending and customising functionality. This tutorial is pitched at non-programmers taking their first steps into VB code as a way of getting the most out of Excel, rather than at developers who've already mastered VB, C# or Java. However, any knowledge of another programming language is going to make things easier.

To get the most out of this tutorial you'll have to be prepared to roll your sleeves up and dive into some code. And, first things first, the way to do that is via Excel's Visual Basic editor. To get to this press Alt-F11 (press the Alt key and the F11 key at the same time), or select the Tools > Macro > Visual Basic Editor menu item (click on Tools, then Macro and then Visual Basic Editor). Once this is open pressing F7 gives a blank editor window, which can then be used for writing VB code.

An alternative way of getting into code is to record a macro and then use the VB editor to examine and change it. We'll do that now. From the menu select Tools > Macro > Record New Macro. The recording dialog shows on screen while the macro is being recorded. For now we'll record a simple sequence of events: first we'll add a new worksheet, then enter the value of Monday in the first cell of the new sheet and then select this cell and auto-fill to get the rest of the days of the week. When we're done click on the Stop Recording button.

If we now select Tools > Macros from the menu the dialog will list our new macro, (it has the default name of Macro1). Selecting it and pressing on the edit button will open the VB Editor with the macro ready to edit:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/04/2006 by TechBookReport
'
'
    Sheets("Sheet1").Select
    Sheets.Add
    ActiveCell.FormulaR1C1 = "Monday"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A7"), Type:=xlFillDefault
    Range("A1:A7").Select
End Sub

What all this code means we'll come back to later. For now just note that the macro we recorded is stored as just a block of VBA code. Close the VB Editor for now and go back to the workbook itself.

As an alternative to recording a macro, we can create one explicitly. Press F8 from inside the workbook, and when presented with the Macro dialog give the new macro a name of Example1 and press the Create button. Once inside the Visual Basic editor key in the following code:

Sub Example1()
    ActiveCell = "hello techbookreport"
End Sub

Switch back to the Excel workbook, select a cell on a worksheet and press Alt-F8. This time Example1 should be listed in the dialog box, select it and then click on the run button and you should find that "hello techbookreport" has been entered into the current cell. Move to another cell and run it again and the same thing should happen. Congrats - you've coded your first VBA program.

Visual Basic Procedures and Functions

There are two types of VBA code that you can write, procedures and functions. You are already used to using Excel functions in worksheets - sum, average, max, min, all of the standard worksheet formulae are functions. Typically they take in a set of numbers from one or more cells and compute a result and write out a result in a different cell (e.g. =SUM(A1:B10)). More formally we say that a function takes in a set of parameters (or arguments, as they are sometimes called), and it produces a value as a result.

VBA allows you to write your own functions which you can use in a workbook or as part of a larger program. In the VB editor functions start with 'Function FunctionName()' and ends with 'End Function'. To return a value your VB code must assign a value to the function name at some point, e.g. if we have a function called sqr that returns the square of a number, the code would look like this:

Function sqr(r)
    sqr = r * r
End Function

Note that r is the variable that we're squaring. To use this function to square the value in cell A1 we would enter =sqr(A1) in a cell and Excel will calculate the result and place it in the cell containing the formula.

However, when we recorded our first macro earlier it wasn't a function. Excel created a Procedure for us. A VB procedure is a block of program code that performs one or more tasks and which does not necessarily return a value. Blocks of procedure code start with 'Sub ProcName()' and finish with 'End Sub'. Although procedures do not return a value they can accept parameters, and these have to be listed in the procedure definition. More of this later.

In Excel VB code is stored in an object called a 'Module', which may contain one or more functions and/or procedures, each defined using the 'Sub … End Sub' and 'Function … End Function' constructs. A workbook may contain any number of modules, which lends itself to a more structured approach to coding than having huge blocks of code into a single unwieldy lump. Keeping things simple and well-structured makes it easier to track down errors, and easier to build complicated programs from smaller building blocks. This is a good thing.

Return to home page