Last Updated: 09 Aug 2011

   |   

Author: dordal

VBA and Macros for Excel 2007

This is a quick overview/primer on how to macros using Visual Basic for Applications in Excel 2007.

Basic Setup

  • 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:
    1. Project: Excel objects, code, etc. for all open workbooks appear in this window
    2. Properties: Lets you view and modify various properties of whatever object you have selected
    3. Editor: The main editor, where you write code
    4. Object Browser: Press F2 to get the Object Browser, which lets you see a comprehensive view of all Excel classes
    5. 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:
    basic
    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, setting could have been the fifth argument to the function myFunction. All other arguments get their default values, but setting is set to 3.
  • 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
      Application.Selection.End(xlDown).Select

      Application is an object and Selection is a property that returns an object (e.g. a Range object that contains selected cells on the worksheet). End is a property of the Range object that returns another object (e.g. a Cell object with the cell at the end of the range). Select is finally the function, to select the cell indicated by the Cell object.

    • You'll note that we passed xlDown to the End property. This shows a slight difference between VBA and other languages, because End is 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

  • 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 Variant type. If you don't set a type when declaring a variable; e.g.:
    Dim myVariantVariable

    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:
    =myNameFunction(A1, A2)

    which would run that function and populate the value of the cell with the result.

Loops

  • 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 While…Loop and Do Until…Loop. These are both constructs that let you run code until something becomes true. They are the same loop; Until is basically syntactic shorthand for While Not.
    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

Flow Control

  • 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:
    Select Case action
      Case 'loadData'
        ' do load proceedure
      Case 'saveData'
        ' do save proceedure
      Case Else
        ' print an error; nothing matched
    End Select

Arrays

  • 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)
  • Examples:
    	Dim myArray(2)	  ' declares a three element, one-dimensional array
    	Dim myArray(2,5)  ' declares an eighteen (3 x 6) element, two-dimensional array

Collections

  • A 'collection' is a grouping of objects of some sort. A collection has four primary methods: Add, Remove, Count and 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)

Regular Expressions

  • 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

Ranges

  • 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:
    basic
    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. Range(“myRange”).Select
  • 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.
  • Offset is 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.
  • Resize lets you expand an existing range. For example, myRange.Resize(RowSize:=2, ColumnSize:=3) would expand the existing range by two rows and three columns.

Cells

  • The Cells object 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

  • 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'

Worksheet Functions

  • 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 object, e.g. 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: R[5]C[3]. 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.

Charts

  • 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 Shape that 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.

Events

  • 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 AppEvent_NewWorkbook or AppEvent_WindowResize
    • Workbook Events - Events at the workbook level, such as Workbook_Open and Workbook_SheetChange
    • Worksheet Events - Events at the worksheet level, such as Worksheet_Change. Note that Worksheet_Change only applies to a single worksheet, whereas 'Workbook_SheetChange' is fired if any sheet is changed.
    • Chart Events - Events specific to a chart, such as Chart_SeriesChange
  • Code for events is stored in the appropriate Excel module. For example, Workbook events are stored in the ThisWorkbook module
  • To add a subroutine to handle an event, use the Object drop-down and Procedure drop-down from the top of the code window.

Misc

Error Handling

  • 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

Debugging

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

Discussion

Enter your comment. Wiki syntax is allowed: