Last Updated: 21 Nov 2020
VBA and Macros for Excel 2007
This is a quick overview/primer on how to macros using Visual Basic for Applications in Excel 2007.
- Install Excel 2007 and enable the Developer Toolbar (Office Menu → Excel Options).
- Make sure you can get into the VBA Editor via the Developer Toolbar. Once there, you may want to set the 'Require Variable Declaration' option in Tools → Options. I find this makes programming easier.
- Note that there is only one VBA editor for ALL workbooks that you have open.
- The VBA Editor has a few key windows:
- Project: Excel objects, code, etc. for all open workbooks appear in this window
- Properties: Lets you view and modify various properties of whatever object you have selected
- Editor: The main editor, where you write code
- Object Browser: Press F2 to get the Object Browser, which lets you see a comprehensive view of all Excel classes
- Debugging Windows: There are several windows that you use for debugging, such as the Watches window which lets you watch variables. See Debugging for more information.
VBA Language Constructs
VBA: An Object-Oriented Language
- VBA is object oriented. So we have an object like a 'Row' or a 'Cell'. There are also 'collections' of these objects, e.g. 'Rows' and 'Cells'
- As with every other object oriented language, an object can have both properties and methods (functions). However, the syntax for calling functions is a little weird:
Object.myProperty = 3 ' Assign the value 3 to the property 'myProperty' Object.myFunction(3, 4) ' Call 'myFunction' with 3 being passed in as the first argument and 4 as the second argument Object.myFunction setting := 3 ' Call 'myFunction' with the specific argument 'setting' set to 3.
- You'll note there are two different types of function calls. The first is the one that is probably familiar to you; you pass a list of arguments in a specific order, as defined by the function that you're calling. The second allows you to specific which arguments get which values; in the example above,
settingcould have been the fifth argument to the function
myFunction. All other arguments get their default values, but
settingis set to
- Also as with any other object-oriented language, objects can have properties that are, in fact, other objects. Similarly, you can pass objects as arguments to functions.
- Example: If you look at the code
Applicationis an object and
Selectionis a property that returns an object (e.g. a
Rangeobject that contains selected cells on the worksheet).
Endis a property of the Range object that returns another object (e.g. a
Cellobject with the cell at the end of the range).
Selectis finally the function, to select the cell indicated by the
- You'll note that we passed
Endproperty. This shows a slight difference between VBA and other languages, because
Endis a (read-only) property, but we can pass an argument to it. In this way, the property is acting a bit like a function.
- Variables in VBA can either be statically or dynamically typed, but most developers prefer static typing (see below).
- Variables are declared with
Dim, and can be passed a type:
Dim myVariable As Range
- Then they can be set with
myVariable = 2 Set myVariable = Range("A1":"C3") ' The 'Set' syntax must be used for setting non-trivial values, such as this Range object
- Dynamic typing is done via the
Varianttype. If you don't set a type when declaring a variable; e.g.:
then VBA will create a
Variant, and assign whatever type it thinks is best, based on your usage. On the surface, this sounds great, but it comes with several problems, including increased memory and CPU overhead, and an increased likelihood for lazy-developer errors.
Functions and Subroutines
- VBA supports both functions and subroutines. The difference is that functions can return a value, and sub-routines can't.
- Subroutine declaration:
Sub mySubroutine(firstName As String, lastName As String) ' do some stuff End Sub
- Function declaration:
Function myNameFunction(firstName As String, lastName As String) As String ' do some stuff ' return a value from a function by assigning the return value to a variable with the same name myFunction = firstName & ' ' & lastName End Function
- Functions can be called from a cell, and populate that cell with a value. For example, you could call:
which would run that function and populate the value of the cell with the result.
- Surprise, surprise. VBA supports the all loops that you'd expect.
For…Next. These are very similar to other languages that support the for() construct, although the syntax is a bit different:
For i = 1 To 10 ' do something with i Next i
- You can also make these a bit more complex, for example running backwards and incrementing by more than just one:
For i = 300 To 200 Step 10 ' i will go from 300 to 200, in increments of 10 Next i
Do Until…Loop. These are both constructs that let you run code until something becomes true. They are the same loop;
Untilis basically syntactic shorthand for
Dim testVar As Boolean testVar = true Do While testVar ' do something, and eventually set testVar = false, which breaks out of the loop Loop
For Each…Next. Like many languages (but certainly not all) VBA supports the For Each construct to iterate over all elements of an object.
For Each cell in myRange ' do something with each cell Next cell
- Standard flow control structures are also supported.
If… Then….Else…End If:
If X > 0 Or Y > 0 Then ' do something Else ' do something else End If
Select Case action Case 'loadData' ' do load proceedure Case 'saveData' ' do save proceedure Case Else ' print an error; nothing matched End Select
- VBA supports both single and multi-dimensional arrays.
- VBA is a little weird; if you declare an array like this:
Dim myArray(2)it actually has three elements: myArray(0), myArray(1) and myArray(2). Nearly every other language I'd use would only have two (index 0 and index 1)
Dim myArray(2) ' declares a three element, one-dimensional array Dim myArray(2,5) ' declares an eighteen (3 x 6) element, two-dimensional array
- A 'collection' is a grouping of objects of some sort. A collection has four primary methods:
Item. To use a collection:
Dim myCollection As New Collection myCollection.Add 'foobar' 1 ' add the value 'foobar' with a unqiue key of '1' myCollection.Add 'goobar' 2 ' add the value 'goobar' with the unique key of '2' myCollection.Add 'roobar' 1 ' THROWS AN ERROR, because we already have an item with a unqiue key of 1 myCollection.Count ' value is 2 (foobar & goobar)
- VBA supports regular expressions via an add-in. To enable it, go to the Visual Basic Editor and select 'Tools → References'. Enable 'Microsoft VBScript Regular Expressions 5.5'
- Here's an example of a regular expression:
Dim regex As New RegExp ' Set regex properties; we're looking for a string that starts with 'foobar' With regex .MultiLine = False .Global = True .IgnoreCase = False .Pattern = "^foobar" End With regex.Test("foobarbaz") ' will evaluate to true regex.Test("barfoo") ' will evaluate to false
Excel Specific VBA
- The range object is probably the thing you'll interact with most in Excel, because it lets you select and act on bits of data.
- A Range may be a single cell, or a group of cells. Most common syntax for a range:
Range("A1").Select ' Selects only cell A1 Range("A:A").Select ' Selects the entire A column Range("1:1").Select ' Selects the entire 1 row Range("A1:C5").Select ' Select the box from A1 to C5 Range("A1","C5").Select ' Same thing as above; just a different syntax Worksheets("Sheet2").Range("A1").Select ' Selects A1 on the Worksheet 'Sheet2'
- You can also access a named range, e.g.
- You can get a specific item in a range by using
myRange.Cells.Item(5,2). This would get the cell that is five rows down and two columns over from the root cell of the range.
Offsetis similar, but it moves the entire range rather than selecting a cell within it:
myRange.Offset(5,2)will move the Range down five and over two.
myRange.Offset(RowOffset:=-1)moves the range one row up.
Resizelets you expand an existing range. For example,
myRange.Resize(RowSize:=2, ColumnSize:=3)would expand the existing range by two rows and three columns.
Cellsobject lets you get data from and take action on specific cells.
IsEmpty(myCell)lets you figure out whether a cell is empty or not.
- Names are a little bit like persistent variables for Excel. You can store data in them when you need it to persist across sessions, but you don't want to store it in a hidden part of your worksheet.
- Basically, a 'name' can refer to a range of cells, a string, a number, or an array.
- Names can either be local (specific to a single worksheet) or global (common across all worksheets).
- You create names like this:
ActiveWorkbook.Names.Add Name:="foobar", RefersTo:=ActiveSheet.Range("A4:F5") ActiveSheet.Names.Add Name:="foobaz", RefersTo:=3
- You access names via the names object. For example, to delete a name:
Names("ABCDEF").Delete ' Note that names are NOT case sensitive, so this will delete the name created with 'abcdef'
- VBA has quite a few built in functions, but there are quite a few more built into Excel and not VBA. You need to access these via the
WorksheetFunction.Max(1,2,3,4,5)will let you use the 'Max' function in Excel.
- Reportedly, switching between VBA and Excel causes significant overhead, so it should be avoided if possible.
R1C1 Style Formulas
- In addition to standard A1 style formulas, Excel supports R1C1 style formula entry. Every cell is assigned a numeric row and column index, so cell R2C2 would be B2 (second row, second column).
- R1C1 style formulas can make your code more elegant in many cases
- R1C1 syntax supports both absolute references: R5C3 as well as relative references: RC. The former would go to the fifth row, third column. The latter would go down five rows and over three columns from the current cell
- You can also do a mixed reference: R4C[-1] would go to the fourth row, back one column from the current cell.
- Create a Chart using the Shapes object; e.g.:
ActiveSheet.Shapes.AddChart.Select ActiveChart.SetDataSource Source:=Range("C3:E4") ActiveChart.ChartType = xlColumnClustered
- If you need to move a chart, you need to move the parent
Shapethat it's attached to, not the chart itself. For example, this positions a chart at the top left corner of cell D6:
ActiveChart.Parent.Left = Range("D6").Left ActiveChart.Parent.Top = Range("D6").Top
- Some arguments, such as type and size, can also be passed to AddChart, see the Excel docs for details.
- Like many other languages, VBA uses an event driven model for running code when certain things happen to a workbook
- There are four types of events:
- Application Events - Events at the application level, such as
- Workbook Events - Events at the workbook level, such as
- Worksheet Events - Events at the worksheet level, such as
Worksheet_Change. Note that
Worksheet_Changeonly applies to a single worksheet, whereas 'Workbook_SheetChange' is fired if any sheet is changed.
- Chart Events - Events specific to a chart, such as
- Code for events is stored in the appropriate Excel module. For example, Workbook events are stored in the
- To add a subroutine to handle an event, use the Object drop-down and Procedure drop-down from the top of the code window.
- Many functions in Excel return an error if something goes wrong, which stops the execution of the program.
- If you'd rather have execution continue, and handle the error, you can catch errors like this:
On Error Resume Next Names("foobar").Delete Range("A1").Value = Err.number ' Contains zero if the name was found, or an error code otherwise
- VBA sports all the usual suspects for debugging: Step Into, Step Over, Run to Cursor, adding breakpoints, watching variables, etc. Generally, it all works pretty much as you'd expect.
- All of the debugging functionality is accessed from the Debug menu of the Visual Basic for Applications IDE.