VBA and Macros for Excel 2007 [StartupCTO]

Last Updated: 21 Nov 2020
|
Author: (external edit)
Page locked

This page is currently locked for editing by another user. You have to wait until this user finishes editing or the lock expires.
Currently locked by: 84.247.186.99
Lock expires at: Apr 20, 2024 05:29 AM (267 min)
This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
= 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:
  - **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 [[vba-for-excel#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:<code visual 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. 
</code>
 * 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 <code>Application.Selection.End(xlDown).Select</code> ''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: <code vba>
Dim myVariable As Range
</code>
 * Then they can be set with <code vba>
myVariable = 2
Set myVariable = Range("A1":"C3")  ' The 'Set' syntax must be used for setting non-trivial values, such as this Range object
</code>
 * Dynamic typing is done via the ''Variant'' type. If you don't set a type when declaring a variable; e.g.:<code>Dim myVariantVariable</code> 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:<code vba>
Sub mySubroutine(firstName As String, lastName As String)
 ' do some stuff
End Sub
</code>
 * Function declaration:<code vba>
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
</code>
 * Functions can be called from a cell, and populate that cell with a value. For example, you could call:<code vba>
=myNameFunction(A1, A2)
</code>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: <code vba>
For i = 1 To 10
  ' do something with i
Next i
</code>
 * You can also make these a bit more complex, for example running backwards and incrementing by more than just one: <code vba>
For i = 300 To 200 Step 10
  ' i will go from 300 to 200, in increments of 10
Next i
</code>
 * ''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''. <code vba>
Dim testVar As Boolean
testVar = true
Do While testVar 
  ' do something, and eventually set testVar = false, which breaks out of the loop
Loop
</code>
 * ''For Each...Next''. Like many languages (but certainly not all) VBA supports the For Each construct to iterate over all elements of an object. <code vba>
For Each cell in myRange
  ' do something with each cell
Next cell
</code>
=== Flow Control ===
 * Standard flow control structures are also supported.
 * ''If... Then....Else...End If'': <code vba>
If X > 0 Or Y > 0 Then 
  ' do something
Else
  ' do something else
End If
</code>
 * ''Select...Case'': <code vba>
Select Case action
  Case 'loadData'
    ' do load proceedure
  Case 'saveData'
    ' do save proceedure
  Case Else
    ' print an error; nothing matched
End Select
</code>

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

=== 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: <code vba>
 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)
</code>

=== 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:<code vba>
    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
</code>

== 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:<code visual 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'
</code>
 * 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:<code>
ActiveWorkbook.Names.Add Name:="foobar", RefersTo:=ActiveSheet.Range("A4:F5")
ActiveSheet.Names.Add Name:="foobaz", RefersTo:=3
</code>
 * You access names via the names object. For example, to delete a name:<code>
Names("ABCDEF").Delete		' Note that names are NOT case sensitive, so this will delete the name created with 'abcdef'
</code>

=== 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.:<code>
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetDataSource Source:=Range("C3:E4")
ActiveChart.ChartType = xlColumnClustered
</code>
 * 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:<code>
ActiveChart.Parent.Left = Range("D6").Left
ActiveChart.Parent.Top = Range("D6").Top
</code>
 * 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:<code>
On Error Resume Next
Names("foobar").Delete
Range("A1").Value = Err.number	' Contains zero if the name was found, or an error code otherwise
</code>

=== 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.
Page locked

About Me

Recent Changes

Get In Touch

Follow
