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

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

• 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

• 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

• 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

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

• 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

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

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

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

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

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

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