Excel Tutorial Series – VBA Macros, Part Two Of Two

This is part six of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.

Let's continue the macro tutorial series with the following topics.

Scope
UDFs
Passing arguments
Decision trees and Loops

1. Procedure & Variable Scope

What is Scope?

Scope refers to the range or reach of a procedure or variable, and defines how it can be accessed and how it uses memory. There are several different kinds of scope which we cover here.

Types:

Module-level

available to any procedure in the same module

Procedure-level

only accessible from the procedure in which it is declared

Project

available to any procedure in any module in the same VB project (using Option Private Module at the top of the module in which it is declared)

Global

available to any procedure in any module in any VB Project, as well as any other project or module which references the original project (must not use Option Private Module)

Keywords:

Public

available to any procedure in any module in any VB Project (as well as any other project or module which references the original project, if Option Private Module is not printed at the top of the module).

Private

only accessible from the module in which it is declared

Procedure Scope

Procedures can have two different types of scope, Public or Private. By default, all procedures are Public and Global unless declared with the Private keyword. For example,

Sub AddRangeVer1()

and

Public Sub AddRangeVer1()

are the same. To declare a procedure as public, simply declare it.

Using "Private Sub AddRangeVer1()" prevents your procedure from being accessed outside of the module in which it is placed, and also prevents it from being listed in the "Run Macros" dialog box (Alt-F8 from the GUI). For example, if you write a series of macros and several of them are internal procedures that are used by other macros but shouldn't otherwise be accessed, declaring them with the Private keyword will do this.

Variable Scope

Variable scope depends on where it is declared. To declare a variable for use in only one procedure, use a "Dim" statement within a Sub procedure. This example

Sub MyMacro()
Dim rng As Excel.Range
End Sub

declares "rng" as a Range Object with module-level scope. To give it procedure-level scope and make it available to any procedure in the same module, put the "Dim" statement at the top of the module and outside the macro. This example

Dim rng As Excel.Range

Sub MyMacro()
  Set rng = Range("A1:A100")
End Sub

Sub MyOtherMacro()
  Set rng = Range("B15:B23")
End Sub

gives the variable "rng" procedure-level scope, so it can be used in multiple procedures in the same module. Now it can be used in MyMacro and MyOtherMacro, both located in the same module, without having to declare it in each procedure.

To make a variable publicly accessible to any procedure in any module, declare it using the "Public" keyword.

Public rng As Excel.Range

The value of rng may be read or set from any procedure in any module in the same project. Note that all references to objects must be prefaced with the Set keyword.

In general, variables should be declared with the smallest scope possible. Every declaration uses system memory and variables are only destroyed when they go out of scope. It can also be considered lazy programming to create numerous global scope variables.

For more information on scope, check out Excel VBA Variables Scope and Lifetime and How to use variables in Excel sub-procedures in Visual Basic for Applications.

2. User Defined Functions

Excel allows you to create custom functions (called User Defined Functions, or UDFs) which perform actions you might not otherwise accomplish without mega formulas. These functions can be used within VBA or as worksheet formulas, with the following conditions:

• UDFs cannot be recorded by the macro recorder.
• UDFs can only return values in the cell in which they are placed.
• A UDF is often much slower than a built-in Excel function. Don't re-invent the wheel.
• UDFs can only process input and return values; they cannot edit or format worksheet cells. Calling another macro from a UDF places it under the same restriction. Remember this is a function intended for small, single purpose uses.
• UDFs may trigger a macro security warning when opening a workbook that contains them.

Custom functions are written with the "Function" keyword (not "Sub").

Function IsStringFound(str1 As String, str2 As String) As Boolean
If Instr(str2, str1) > 0 Then
  IsStringFound = True
End If
End Function

Or we can combine everything into one line:

Function IsStringFound(str1 As String, str2 As String) As Boolean
IsStringFound = (Instr(str2, str1) > 0)
End Function

To insert a custom function, press Alt-F11 to access Excel's VB Editor. Go to Insert > Module and paste in the code you want to use.

To use the function, type =IsStringFound(cell1, cell2) in a worksheet cell, where cell1 contains the string you want to search for, and cell2 contains the string you want to search in. (I realize this is the reverse of the way Instr works.)

The built-in VBA function "Instr" returns a number showing the position of one string within another. If the result is zero, the string was not found. Therefore, the function "IsStringFound" returns "True" if one string is contained in another.

This function replaces the longer and less friendly

=LEN(B1)-LEN(SUBSTITUTE(B1,A1,""))

Here is another set of UDFs I wrote to return the header and footer information from Excel's Page Setup dialog.

Function GetLeftHeader()
GetLeftHeader = ActiveSheet.PageSetup.LeftHeader
End Function

Function GetCenterHeader()
GetCenterHeader = ActiveSheet.PageSetup.CenterHeader
End Function

Function GetRightHeader()
GetRightHeader = ActiveSheet.PageSetup.RightHeader
End Function

Function GetLeftFooter()
GetLeftFooter = ActiveSheet.PageSetup.LeftFooter
End Function

Function GetCenterFooter()
GetCenterFooter = ActiveSheet.PageSetup.CenterFooter
End Function

Function GetRightFooter()
GetRightFooter = ActiveSheet.PageSetup.RightFooter
End Function

If you wanted to print the contents of the left side header in a worksheet cell, simply type "=GetLeftHeader()".

A large list of sample UDFs may be found here: Custom Excel Formulas – User Defined Functions/Formulas

3. Passing Arguments to Procedures/Functions

Functions and procedures can call each other, passing needed values to them as arguments. An argument is an independent value needed by a function in order to complete a calculation.

In the example below, the MyMacro procedure calls the AddThis function, passing two values to it (in this case, the numbers 1 and 2). The AddThis procedure accepts two Integer values as arguments, and returns an Integer value back to the calling procedure. It simply sums the arguments and passes the value back to MyMacro, which displays the result in a message box.

Sub MyMacro()
    MsgBox "The result is " & AddThis(1,2)
End Sub

Function AddThis(intNum1 As Integer, intNum2 As Integer) As Integer
    AddThis = intNum1 + intNum2
End Function

Notice the format of the function. It has a name, AddThis, which is used to call the function. There are two parameters, passed inside parentheses, and both of them are declared as specific types. The return type of the function is declared after the parameters.

To return a value, we assign a value to the name of the function (AddThis = intNum1 + intNum2).

When writing a function or procedure that accepts arguments, always declare the variable type of each argument, and the type of output you expect; this allows VBA to use memory more efficiently by avoiding Variant types. It will also alert you to errors; for example, if someone tried AddThis("cat","dog"), the function would throw an error.

An important distinction is that parameters may be passed by value or by reference. "By value" means that a copy of the value of a variable is used inside the function. "By reference" means that the variable itself will be used inside the function, and its value in the calling procedure will be changed. The keyword ByVal is used to pass by value, and ByRef is used to pass by reference. The keyword is written before the parameter variable, like this:

Function AddThis(ByVal intNum1 As Integer, ByVal intNum2 As Integer) As Integer
    AddThis = intNum1 + intNum2
End Function

Remember that by default, all values are passed ByRef, so if you don't want the variable in a calling procedure changed by a function, make sure you use the ByVal keyword.

For more about ByRef and ByVal, read Understanding the importance of ByVal and ByRef keywords.

4. Decision Trees & Loops

The most common decision structures you will see in VBA are:

• If-Then
• Select Case
• Do Loop
• For Loop

Each of these has a different purpose in your code, however they all evaluate a condition and make a decision based on that evaluation.

If-Then

An If-Then statement works the same as the IF formula on the worksheet. It is used to evaluate statements and then make simple decisions based on the return value.

If [logical test] Then
    ' do this if true
Else[If]
    ' do this if false
End If

The logical test is evaluated and, if true, the code branches to the next line. If false, the code branches to the second line following the 'Else' clause. Multiple If statements can be nested using 'If – Then – ElseIf'. Once a true statement is found, the code executes that branch and then exits the If statement.

If MyNum = 1 Then
    MsgBox "MyNum equals 1"
ElseIf MyNum > 10 Then
    MsgBox "MyNum is too high"
ElseIf MyNum > 5 Then
    MsgBox "MyNum is close"
Else
    MsgBox "MyNum is just right"
End If

If you need to nest more than three If statements, consider using the Select Case statement.

Select Case

The Select Case statement is preferable to If-Then if you have a statement you want to evaluate, for which there are many different possible answers, and you want a different decision made for each one. It is roughly equivalent to the switch statement in Java or PHP.

Select Case [variable or logical test]
    Case value1
        ' do this
    Case value2
        ' do this
    Case value3
        ' do this
    Case value4
        ' do this
    Case Else
        ' catch-all
End Select

The Select Case statement can evaluate a logical test, or check the value of a variable, and branch to the appropriate code. Once a matching value is found, further Case statements are not evaluated; the code executes the statements inside the matching Case condition and then exits the statement. For example,

Select Case MyVariable
    Case Is > 10
        Call Macro1
    Case Is > 5
        Call Macro2
    Case Else
        MsgBox "Too low!"
End Select

If the value of MyVariable is greater than 10, Macro1 is called. If MyVariable is between 6 and 10, Macro2 is called. Otherwise a message box is displayed. 'Case Else' is an optional condition that can be used as a catch-all in case none of the other conditions are met. Note that the Case conditions are listed in reverse order, because a number greater than 10 will also be greater than 5, which could trigger the wrong condition.

Do Loop

There are four types of Do Loops. There are actually two different keywords (Until and While) that modify the loop, and their placement determines how the loop runs.

Do While … Loop

tests a condition at the beginning of the loop and repeats a block of code while the condition is true; exits when false. Does not necessarily run.

Sub CheckDoLoop1()
Dim MyInt As Long
Dim MySum As Long

MyInt = 1

Do While MyInt < 100
    MySum = MySum + MyInt
    MyInt = MyInt + 1
Loop

MsgBox MySum
End Sub

This code shows a message box with the sum of all numbers from 1 through 99. Note that the loop condition is tested at the start of the loop; if MyInt was equal to 100 or greater at the start of the loop, the code would have never entered the loop.
Also note that the loop condition is tested with every iteration of the loop. Therefore it's important that at some point, the loop condition be met, to avoid infinite loops. The way we accomplish this is inside the loop, by incrementing the variable in the loop condition. Eventually, MyInt will equal 100, and the loop will exit. In Professional Excel Development, the authors suggest using an arbitrary internal incrementing loop variable and calling "Exit Do" after, say, 10,000 iterations, to avoid infinite loops.

Do Until … Loop

tests a condition at the beginning of the loop and repeats a block of code until the condition is true (i.e. while the condition is false); exits when true. Does not necessarily run.

Like the Do While loop, this loop tests the condition before entering the loop, so the loop itself may never actually execute.

Here is the same macro as the one above, except we are using a Do Until Loop.

Sub CheckDoLoop2()
Dim MyInt As Long
Dim MySum As Long

MyInt = 1
Do Until MyInt = 100
    MySum = MySum + MyInt
    MyInt = MyInt + 1
Loop

MsgBox MySum
End Sub

Do … Loop While

same as Do While … Loop, except this loop runs at least once.

Sub CheckDoLoop3()
Dim MyInt As Long

MyInt = 1

Do
    Cells(MyInt, 1).Value = MyInt
    MyInt = MyInt + 1
Loop While MyInt <= 200

End Sub

Because the loop condition is at the end of the loop, this loop runs at least once before checking the exit condition. Even if MyInt = 200 at the start of the loop, it will run once before bailing out.

Do … Loop Until

same as Do Until … Loop, except this loop runs at least once.

Sub CheckDoLoop4()
Dim MyInt As Long

MyInt = 1

Do
    Cells(MyInt, 1).Value = MyInt
    MyInt = MyInt + 1
Loop Until MyInt = 100

End Sub

It is very important that your loops have an exit condition that can be met, to avoid the dreaded "infinite loop." In the samples above, MyInt is incremented inside the loop and tested every iteration, so eventually the exit condition will be met. If you didn't increment MyInt inside the loop, you would be stuck in an infinite loop because MyInt will never reach 100, so the exit condition would never be satisfied.

For Loop

There are two different types of For Loops.

• For … Next Loop
• For Each Loop

For … Next Loop

A For … Next Loop is used when the number of iterations is known, or can be counted. Here is the same code as the Do Loop above.

Sub ForLoop1()
Dim MyNum As Long
Dim MySum As Long

For MyNum = 1 To 99
    MySum = MySum + MyNum
Next MyNum

    MsgBox MySum
End Sub

Here is a sample For Loop that uses the number of columns as the loop counter to produce its result.

Sub ForLoop2()
Dim MySum As Long
Dim MyNum As Long

For MyNum = 1 To Columns.count
    MySum = MySum + MyNum
Next MyNum
    MsgBox Format(MySum, "###,###,###,###")
End Sub

And here is an Outlook macro that saves email attachments to the C:\ folder. It uses a For Loop nested in an If statement, as well as a Select Case statement.

Sub GoThroughAttachments()
Dim MyItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim i As Long
Dim Att As String

On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        Set MyItem = ActiveExplorer.Selection.Item(1)
    Case "Inspector"
        Set MyItem = ActiveInspector.CurrentItem
    Case Else
End Select
On Error GoTo 0

If MyItem Is Nothing Then
    Exit Sub
End If

Set myAttachments = MyItem.Attachments

If myAttachments.Count > 0 Then
    For i = 1 To myAttachments.Count
        Att = myAttachments.Item(i).DisplayName
        myAttachments.Item(i).SaveAsFile "C:\" & Att
    Next i
End If

Set myAttachments = Nothing
Set MyItem = Nothing
End Sub

For Each Loop

A For Each Loop is used when we don't know how many iterations there will be, but we know we want to perform some calculation or procedure on each matching object we find.
For example, if we wanted to change certain cells to UPPER case, we probably won't know how many cells to act on, but we want the code to be flexible enough to work on as many cells as we need. The following code simply accepts whatever cells you select and faithfully changes them to upper case.

Sub UpperCase()
Dim cell As Excel.Range

For Each cell In Selection.SpecialCells(xlCellTypeConstants, 2)
    cell.Value = Ucase$(cell.Value)
Next cell

End Sub

Read more about loops at Excel VBA For Do While and Do Until Loop.

There is one more post left in the Excel Tutorial Series! Stay tuned.

Related Articles:

• June 1, 2009 -- Excel Tutorial Series – Tips & Tricks
• April 3, 2009 -- Advanced Excel Conference 2009
• February 9, 2009 -- Calculate working days from start date