Chapter 25: VBA Programming Concepts

In This Chapter

• Introducing an example Function procedure

• Using comments in your code

• Understanding VBA's language elements, including variables, data types, and constants

• Using assignment expressions in your code

• Declaring arrays and multidimensional arrays

• Using the built-in VBA functions

• Controlling the execution of your Function procedures

• Using ranges in your code

This chapter discusses some of the key language elements and programming concepts in VBA. If you've used other programming languages, much of this information may sound familiar. VBA has a few unique wrinkles, however, so even experienced programmers may find some new information.

This chapter does not even come close to being a comprehensive guide to VBA. Motivated readers can consult the Help system and make use of Internet resources or other books for additional information.

Many of the code examples in this chapter are available at this book's website. The file is named function examples.xlsm.

An Introductory Example Function Procedure

To get the ball rolling, I'll begin with an example Function procedure. This function, named REMOVESPACES, accepts a single argument and returns that argument without any spaces. For example, the following formula uses the REMOVESPACES function and returns ThisIsATest.

=REMOVESPACES(“This Is A Test”)

To create this function, insert a VBA module into a project and then enter the following Function procedure into the code window of the module:

Function REMOVESPACES(cell) As String

‘   Removes all spaces from cell

    Dim CellLength As Long

    Dim Temp As String

    Dim Characters As String

    Dim i As Long

    CellLength = Len(cell)

    Temp = “”

    For i = 1 To CellLength

        Character = Mid(cell, i, 1)

        If Character <> Chr(32) Then Temp = Temp & Character

    Next i

    REMOVESPACES = Temp

End Function

Look closely at this function's code line by line:

• The first line of the function is called the function's declaration line. Notice that the procedure starts with the keyword Function, followed by the name of the function (REMOVESPACES). This function uses only one argument (cell); the argument name is enclosed in parentheses. As String defines the data type of the function's return value. The As part of the function declaration is optional.

• The second line is a comment (optional) that describes what the function does. The initial apostrophe designates this line as a comment. Comments are ignored when the function is executed.

• The next four lines use the Dim keyword to declare the four variables used in the procedure: CellLength, Temp, Character, and i. Declaring variables is not necessary, but as you'll see later, it's an excellent practice.

• The procedure's next line assigns a value to a variable named CellLength. This statement uses the VBA Len function to determine the length of the contents of the argument (cell).

• The next line creates a variable named Temp and assigns it an empty string.

• The next four lines make up a For-Next loop. The statements between the For statement and the Next statement are executed a number of times; the value of CellLength determines the number of times. For example, assume that the cell passed as the argument contains the text Bob Smith. The statements within the loop would execute nine times, one time for each character in the string.

• Within the loop, the Character variable holds a single character that is extracted using the VBA Mid function (which works just like Excel's MID function). The If statement determines whether the character is not a space. (The VBA Chr function is equivalent to Excel's CHAR function, and an argument of 32 represents a space character.) The two angle brackets (<>) represent “not equal to.” If the character is not a space, the character is appended to the string stored in the Temp variable (using an ampersand, the concatenation operator). If the character is a space, the Temp variable is unchanged, and the next character is processed. If you prefer, you can replace this statement with the following:

If Character <> “ “ Then Temp = Temp & Character

• When the loop finishes, the Temp variable holds all the characters that were originally passed to the function in the cell argument, except for the spaces.

• The string contained in the Temp variable is assigned to the function's name. This string is the value that the function returns.

• The Function procedure ends with an End Function statement.

The REMOVESPACES procedure uses some common VBA language elements, including

• A Function declaration statement

• A comment (the line preceded by the apostrophe)

• Variable declarations

• Three assignment statements

• Three built-in VBA functions (Len, Mid, and Chr)

• A looping structure (For-Next)

• A comparison operator (<>)

• An If-Then structure

• String concatenation (using the & operator)

Not bad for a first effort, eh? The remainder of this chapter provides more information on these (and many other) programming concepts.

The REMOVESPACES function listed here is for instructional purposes only. You can accomplish the same effect by using the Excel SUBSTITUTE function, which is much more efficient than using a custom VBA function. The following formula, for example, removes all space characters from the text in cell A1:

=SUBSTITUTE(A1,” “,””)

Using Comments in Your Code

A comment is descriptive text embedded within your code. VBA completely ignores the text of a comment. It's a good idea to use comments liberally to describe what you do because the purpose of a particular VBA instruction is not always obvious.

You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe up until the end of the line. An exception occurs when an apostrophe is contained within quotation marks. For example, the following statement does not contain a comment, even though it has an apostrophe:

Result = “That doesn't compute”

The following example shows a VBA Function procedure with three comments:

Function LASTSPACE(txt)

‘   Returns the position of the last space character

    LASTSPACE = InStrRev(txt, Chr(32)) ‘character 32 is a space

‘   If no spaces, return #NA error

    If LASTSPACE = 0 Then LASTSPACE = CVErr(xlErrNA)

End Function

When developing a function, you may want to test it without including a particular statement or group of statements. Instead of deleting the statement, simply convert it to a comment by inserting an apostrophe at the beginning. VBA then ignores the statement(s) when the routine is executed. To convert the comment back to a statement, delete the apostrophe.

The VB Editor Edit toolbar contains two very useful buttons. Select a group of instructions and then use the Comment Block button to convert the instructions to comments. The Uncomment Block button converts a group of comments back to instructions. If the Edit toolbar is not visible, choose View⇒Toolbars⇒Edit.

Using Variables, Data Types, and Constants

A variable is a named storage location in your computer's memory. Variables can accommodate a wide variety of data types, from simple Boolean values (TRUE or FALSE) to large, double-precision values (see the “Defining data types” section). You assign a value to a variable by using the assignment operator, which is an equal sign.

The following are some examples of assignment statements that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.

x = 1

InterestRate = 0.0625

LoanPayoffAmount = 243089

DataEntered = False

x = x + 1

MyNum = YourNum * 1.25

BanjoStyle = “Clawhammer”

DateStarted = #3/14/2013#

VBA has many reserved words, which are words that you can't use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next (used in a For-Next loop) may make a very descriptive variable name, the following instruction generates a syntax error:

Next = 132

Defining data types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Data type refers to how data is stored in memory — as integers, logical values, strings, and so on.

Although VBA can take care of data typing automatically, it does so at a cost — namely, slower execution and less efficient use of memory. If you want optimal speed for your functions, you need to be familiar with data types. Generally, it's best to use the data type that uses the smallest number of bytes yet still is able to handle all of the data that will be assigned to it. When VBA works with data, execution speed is a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes used by data, the faster VBA can access and manipulate the data. Table 25-1 lists VBA's assortment of built-in data types.

Table 25-1: VBA Data Types

Data Type	Bytes Used	Range of Values
Byte	1	0 to 255
Boolean	2	True or False
Integer	2	–32,768 to 32,767
Long	4	–2,147,483,648 to 2,147,483,647
Single	4	–3.40E38 to –1.40E–45 for negative values; 1.40E–45 to 3.40E38 for positive values
Double	8	–1.79E308 to –4.94E-324 for negative values; 4.94E–324 to 1.79E308 for positive values
Currency	8	–922,337,203,685,477 to 922,337,203,685,477
Date	8	1/1/0100 to 12/31/9999
Object	4	Any object reference
String	1 per character	Varies
Variant	Varies	Varies

Declaring variables

Before you use a variable in a procedure, you may want to declare it. Declaring a variable tells VBA its name and data type, providing two main benefits:

• Your procedures run faster and use memory more efficiently. The default data type — Variant — causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows the data type for a variable, it does not have to investigate; it can reserve just enough memory to store the data.

• If you use an Option Explicit statement at the top of your module, you avoid problems resulting from misspelled variable names. Suppose that you use an undeclared variable named CurrentRate. At some point in your procedure, however, you insert the statement CurrentRate = .075. This misspelled variable name, which is very difficult to spot, will likely cause your function to return an incorrect result. See the nearby sidebar, “Forcing yourself to declare all variables.”

You declare a variable by using the Dim keyword. For example, the following statement declares a variable named Count to be a Long:

Dim Count As Long

You also can declare several variables with a single Dim statement. For example:

Dim x As Long, y As Long, z As Long

Dim First As Long, Last As Double

Unlike some languages, VBA does not permit you to declare a group of variables to be a particular data type by separating the variables with commas. For example, the following statement — although valid — does not declare all the variables as As Long:

Dim i, j, k As Long

In the preceding statement, only k is declared to be an integer. To declare all variables as As Long, use this statement:

Dim i As Long, j As Long, k As Long

If you don't declare the data type for a variable that you use, VBA uses the default data type — Variant. Data stored as a variant acts like a chameleon: It changes type depending on what you do with it. The following procedure demonstrates how a variable can assume different data types:

Function VARIANT_DEMO()

    MyVar = “123”

    MyVar = MyVar / 2

    MyVar = “Answer: “ & MyVar

    VARIANT_DEMO = MyVar

End Function

In the VARIANT_DEMO Function procedure, MyVar starts out as a three-character text string that looks like a number. Then this string is divided by 2, and MyVar becomes a numeric data type. Next, MyVar is appended to a string, converting MyVar back to a string. The function returns the final string: Answer: 61.5.

You'll notice that I don't follow my own advice in this chapter. In many of the subsequent function listings in this chapter, I don't declare the variables used. I omitted the variable declarations to keep the code simple so that you can focus on the concept being discussed. In the code examples at this book's website, I always declare the variables.

Using constants

A variable's value may — and often does — change while a procedure is executing. That's why it's called a variable. Sometimes, you need to refer to a named value or string that never changes: in other words, a constant.

You declare a constant by using the Const statement. Here are some examples:

Const NumQuarters As Long = 4

Const Rate = .0725, Period = 12

Const CompanyName as String = “Acme Snapholytes”

The second statement declares two constants with a single statement, but it does not declare a data type. Consequently, the two constants are variants. Because a constant never changes its value, you normally want to declare your constants as a specific data type. The scope of a constant depends on where it is declared within your module:

• To make a constant available within a single procedure only, declare it after the Sub or Function statement to make it a local constant.

• To make a constant available to all procedures in a module, declare it before the first procedure in the module.

• To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. The following statement creates a constant that is valid in all VBA modules in the workbook:

Public Const AppName As String = “Budget Tools”

If you attempt to change the value of a constant in a VBA procedure, you get an error — as you would expect. A constant is a constant, not a variable.

Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and use the constant's name rather than its value in your expressions. This technique makes your code more readable and makes it easier to change should the need arise — you have to change only one instruction rather than several.

VBA and Excel define many constants that you can use in your code without declaring them. For example, the following statement uses a constant named vbInformation:

MsgBox “Hello”, vbInformation

The vbInformation constant has a value of 64, but it's not important that you know that. If you use the Excel macro recorder to record your actions, you'll find many other constants in the recorded code.

Using strings

Like Excel, VBA can manipulate both numbers and text (strings). VBA supports two types of strings:

• Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.

• Variable-length strings theoretically can hold up to 2 billion characters.

Each character in a string takes 1 byte of storage. When you declare a string variable with a Dim statement, you can specify the maximum length if you know it (that is, a fixed-length string), or you can let VBA handle it dynamically (a variable-length string). In some cases, working with fixed-length strings may be slightly more efficient in terms of memory usage.

In the following example, the MyString variable is declared to be a string with a fixed length of 50 characters. YourString is also declared as a string but with an unspecified length.

Dim MyString As String * 50

Dim YourString As String

Using dates

You can use a string variable to store a date, of course, but then you can't perform date calculations using the variable. Using the Date data type is a better way to work with dates.

A variable defined as a Date uses 8 bytes of storage and can hold dates ranging from January 1, 0100, to December 31, 9999. That's a span of nearly 10,000 years — more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and times by enclosing them between two number signs (#).

The range of dates that VBA can handle is much larger than Excel's own date range, which begins with January 1, 1900. Therefore, be careful that you don't attempt to use a date in a worksheet that lies outside of Excel's acceptable date range.

Here are some examples of declaring variables and constants as Date data types:

Dim Today As Date

Dim StartTime As Date

Const FirstDay As Date = #1/15/2013#

Const Noon = #12:00:00#

Date variables display dates according to your system's short date format, and times appear according to your system's time format (either 12 or 24 hours). You can modify these system settings by using the Regional and Language Options dialog box in the Windows Control Panel. See Chapter 6 for more information on working with dates and times.

Using Assignment Statements

An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable or an object. An expression is a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data.

If you know how to create formulas in Excel, you'll have no trouble creating expressions in VBA. With a worksheet formula, Excel displays the result in a cell. Similarly, you can assign a VBA expression to a variable or use it as a property value.

VBA uses the equal sign (=) as its assignment operator. Note the following examples of assignment statements. (The expressions are to the right of the equal sign.)

x = 1

x = x + 1

x = (y * 2) / (z * 2)

MultiSheets = True

Expressions often use functions. These can be VBA's built-in functions, Excel's worksheet functions, or custom functions that you develop in VBA. I discuss VBA's built-in functions later in this chapter.

Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition (+), multiplication (*), division (/), subtraction (–), exponentiation (^), and string concatenation (&). Less familiar operators are the backslash () that's used in integer division and the Mod operator that's used in modulo arithmetic. The Mod operator returns the remainder of one integer divided by another. For example, the following expression returns 2:

17 Mod 3

You may be familiar with the Excel MOD function. Note that in VBA, Mod is an operator, not a function.

VBA also supports the same comparative operators used in Excel formulas: equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). Additionally, VBA provides a full set of logical operators, as shown in Table 25-2. Refer to the Help system for additional information and examples of these operators.

Table 25-2: VBA Logical Operators

Operator	What It Does
Not	Performs a logical negation on an expression
And	Performs a logical conjunction on two expressions
Or	Performs a logical disjunction on two expressions
Xor	Performs a logical exclusion on two expressions
Eqv	Performs a logical equivalence on two expressions
Imp	Performs a logical implication on two expressions

The order of precedence for operators in VBA exactly matches that in Excel. Of course, you can add parentheses to change the natural order of precedence.

The negation operator (a minus sign) is handled differently in VBA. In Excel, the following formula returns 25:

=–5^2

In VBA, x equals –25 after this statement is executed:

x = –5 ^ 2

VBA performs the exponentiation operation first, and then applies the negation operator. The following statement returns 25:

x = (–5) ^ 2

Using Arrays

An array is a group of elements of the same type that have a common name; you refer to a specific element in the array by using the array name and an index number. For example, you may define an array of 12 string variables so that each variable corresponds to the name of a different month. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(0), the second element as MonthNames(1), and so on, up to MonthNames(11).

Declaring an array

You declare an array with a Dim or Public statement just as you declare a regular variable. You also can specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index number — all inside parentheses. For example, here's how to declare an array comprising exactly 100 numbers (of data type Long):

Dim MyArray(1 To 100) As Long

When you declare an array, you need to specify only the upper index, in which case VBA (by default) assumes that 0 is the lower index. Therefore, the following two statements have the same effect:

Dim MyArray(0 to 100) As Long

Dim MyArray(100) As Long

In both cases, the array consists of 101 elements.

If you want VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

Option Base 1

If this statement is present, the following two statements have the same effect (both declare an array with 100 elements):

Dim MyArray(1 to 100) As Long

Dim MyArray(100) As Long

Declaring multidimensional arrays

The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimensions although it's rare to need more than three dimensions (a 3-D array). The following statement declares a 100-integer array with two dimensions (2-D):

Dim MyArray(1 To 10, 1 To 10) As Long

You can think of the preceding array as occupying a 10 x 10 matrix. To refer to a specific element in a 2-D array, you need to specify two index numbers. For example, here's how you can assign a value to an element in the preceding array:

MyArray(3, 4) = 125

A dynamic array does not have a preset number of elements. You declare a dynamic array with a blank set of parentheses:

Dim MyArray() As Long

Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array (or ReDim Preserve if you want to keep the existing values in the array). You can use the ReDim statement any number of times, changing the array's size as often as you like.

Arrays crop up later in this chapter in the sections that discuss looping.

Using Built-In VBA Functions

VBA has a variety of built-in functions that simplify calculations and operations. Many of VBA's functions are similar (or identical) to Excel's worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER.

To display a list of VBA functions while writing your code, type VBA followed by a period (.). The VB Editor displays a list of all functions and constants (see Figure 25-1). If this does not work for you, make sure that you select the Auto List Members option. Choose Tools⇒Options and click the Editor tab. In addition to functions, the displayed list also includes built-in constants. The VBA functions are all described in the online help. To view Excel Help, just move the cursor over a function name and press F1.

Figure 25-1: Displaying a list of VBA functions in the VB Editor.

Here's a statement that calculates the square root of a variable by using VBA's Sqr function and then assigns the result to a variable named x:

x = Sqr(MyValue)

Having knowledge of VBA functions can save you lots of work. For example, consider the REMOVESPACES Function procedure presented at the beginning of this chapter. That function uses a For-Next loop to examine each character in a string and builds a new string. A much simpler (and more efficient) version of that Function procedure uses the VBA Replace function. The following is a rewritten version of the Function procedure:

Function REMOVESPACES2(cell) As String

‘   Removes all spaces from cell

    REMOVESPACES2 = Replace(cell, “ “, “”)

End Function

You can use many (but not all) of Excel's worksheet functions in your VBA code. To use a worksheet function in a VBA statement, just precede the function name with WorksheetFunction and a period.

The following code demonstrates how to use an Excel worksheet function in a VBA statement. The code snippet uses the ENCODEURL function (which is new to Excel 2013) to encode a URL.

URL = “http://spreadsheetpage.com”

Encoded = WorksheetFunction.ENCODEURL(URL)

For some reason, you can't use worksheet functions that have an equivalent VBA function. For example, VBA can't access Excel's SQRT worksheet function because VBA has its own version of that function: Sqr. Therefore, the following statement generates an error:

x = WorksheetFunction.SQRT(123)   ‘error

Controlling Execution

Some VBA procedures start at the top and progress line by line to the bottom. Often, however, you need to control the flow of your routines by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the routine does next.

This section discusses several ways of controlling the execution of your VBA procedures:

• If-Then constructs

• Select Case constructs

• For-Next loops

• Do While loops

• Do Until loops

• On Error statements

The If-Then construct

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This instruction is one way to endow your applications with decision-making capability. The basic syntax of the If-Then construct is as follows:

If condition Then true_instructions [Else false_instructions]

The If-Then construct executes one or more statements conditionally. The Else clause is optional. If included, it enables you to execute one or more instructions when the condition that you test is not true.

The following Function procedure demonstrates an If-Then structure without an Else clause. The example deals with time. VBA uses the same date-and-time serial number system as Excel (but with a much wider range of dates). The time of day is expressed as a fractional value — for example, noon is represented as .5. The VBA Time function returns a value that represents the time of day, as reported by the system clock. In the following example, the function starts out by assigning an empty string to GREETME. The If-Then statement checks the time of day. If the time is before noon, the Then part of the statement executes, and the function returns Good Morning.

Function GREETME()

    GREETME = “”

    If Time < 0.5 Then GREETME= “Good Morning”

End Function

The following function uses two If-Then statements. It displays either Good Morning or Good Afternoon:

Function GREETME()

    If Time < 0.5 Then GREETME = “Good Morning”

    If Time >= 0.5 Then GREETME = “Good Afternoon”

End Function

Notice that the second If-Then statement uses >= (greater than or equal to). This covers the extremely remote chance that the time is precisely 12:00 noon when the function is executed.

Another approach is to use the Else clause of the If-Then construct:

Function GREETME()

    If Time < 0.5 Then GREETME = “Good Morning” Else _

      GREETME = “Good Afternoon”

End Function

Notice that the preceding example uses the line continuation sequence (a space followed by an underscore); If-Then-Else is actually a single statement.

The following is another example that uses the If-Then construct. This Function procedure calculates a discount based on a quantity (assumed to be an integer value). It accepts one argument (quantity) and returns the appropriate discount based on that value.

Function DISCOUNT(quantity)

    If quantity <= 5 Then DISCOUNT = 0

    If quantity >= 6 Then DISCOUNT = 0.1

    If quantity >= 25 Then DISCOUNT = 0.15

    If quantity >= 50 Then DISCOUNT = 0.2

    If quantity >= 75 Then DISCOUNT = 0.25

End Function

Notice that each If-Then statement in this procedure is always executed, and the value for DISCOUNT can change as the function executes. The final value, however, is the desired value.

The preceding examples all used a single statement for the Then clause of the If-Then construct. However, you often need to execute multiple statements if a condition is TRUE. You can still use the If-Then construct, but you need to use an End If statement to signal the end of the statements that make up the Then clause. Here's an example that executes two statements if the If clause is TRUE:

If x > 0 Then

    y = 2

    z = 3

End If

You can also use multiple statements for an If-Then-Else construct. Here's an example that executes two statements if the If clause is TRUE, and two other statements if the If clause is not TRUE:

If x > 0 Then

    y = 2

    z = 3

Else

   y = –2

   z = –3

End If

The Select Case construct

The Select Case construct is useful for choosing among three or more options. this construct also works with two options and is a good alternative to using If-Then-Else. The syntax for Select Case is as follows:

Select Case testexpression

    [Case expressionlist–n

        [instructions–n]]

    [Case Else

        [default_instructions]]

End Select

The following example of a Select Case construct shows another way to code the GREETME examples presented in the preceding section:

Function GREETME()

    Select Case Time

        Case Is < 0.5

            GREETME = “Good Morning”

        Case 0.5 To 0.75

            GREETME = “Good Afternoon”

        Case Else

            GREETME = “Good Evening”

    End Select

End Function

And here's a rewritten version of the DISCOUNT function from the previous section, this time using a Select Case construct:

Function DISCOUNT2(quantity)

    Select Case quantity

        Case Is <= 5

            DISCOUNT2 = 0

        Case 6 To 24

            DISCOUNT2 = 0.1

        Case 25 To 49

            DISCOUNT2 = 0.15

        Case 50 To 74

            DISCOUNT2 = 0.2

        Case Is >= 75

            DISCOUNT2 = 0.25

    End Select

End Function

Any number of instructions can be written below each Case statement; they all execute if that case evaluates to TRUE.

Looping blocks of instructions

Looping is repeating a block of VBA instructions within a procedure. You may know the number of times to loop, or it may be determined by the values of variables in your program. VBA offers a number of looping constructs:

• For-Next loops

• Do While loops

• Do Until loops

For-Next loops

The following is the syntax for a For-Next loop:

For counter = start To end [Step stepval]

    [instructions]

    [Exit For]

    [instructions]

Next [counter]

The following listing is an example of a For-Next loop that does not use the optional Step value or the optional Exit For statement. This function accepts two arguments and returns the sum of all integers between (and including) the arguments:

Function SUMINTEGERS(first, last)

    total = 0

    For num = first To last

        total = total + num

    Next num

    SUMINTEGERS = total

End Function

The following formula, for example, returns 55 — the sum of all integers from 1 to 10:

=SUMINTEGERS(1,10)

In this example, num (the loop counter variable) starts out with the same value as the first variable, and increases by 1 each time the loop repeats. The loop ends when num is equal to the last variable. The total variable simply accumulates the various values of num as it changes during the looping.

When you use For-Next loops, you should understand that the loop counter is a normal variable — it is not a special type of variable. As a result, you can change the value of the loop counter within the block of code executed between the For and Next statements. this is, however, a very bad practice and can cause problems. In fact, you should take special precautions to ensure that your code does not change the loop counter.

You also can use a Step value to skip some values in the loop. Here's the same function rewritten to sum every other integer between the first and last arguments:

Function SUMINTEGERS2(first, last)

    total = 0

    For num = first To last Step 2

    total = total + num

    Next num

    SUMINTEGERS2 = Total

End Function

The following formula returns 25, which is the sum of 1, 3, 5, 7, and 9:

=SUMINTEGERS2(1,10)

For-Next loops can also include one or more Exit For statements within the loop. When this statement is encountered, the loop terminates immediately, as the following example demonstrates:

Function ROWOFLARGEST(c)

    NumRows = Rows.Count

    MaxVal = WorksheetFunction.Max(Columns(c))

    For r = 1 To NumRows

        If Cells(r, c) = MaxVal Then

            ROWOFLARGEST = r

            Exit For

        End If

    Next r

End Function

The ROWOFLARGEST function accepts a column number (1–16,384) for its argument and returns the row number of the largest value in that column. It starts by getting a count of the number of rows in the worksheet. (This varies, depending on the version of Excel.) This number is assigned to the NumRows variable. The maximum value in the column is calculated by using the Excel MAX function, and this value is assigned to the MaxVal variable.

The For-Next loop checks each cell in the column. When the cell equal to MaxVal is found, the row number (variable r, the loop counter) is assigned to the function's name, and the Exit For statement ends the procedure. Without the Exit For statement, the loop continues to check all cells in the column — which can take quite a long time!

The previous examples use relatively simple loops. But you can have any number of statements in the loop, and you can even nest For-Next loops inside other For-Next loops. The following is VBA code that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value –1. When the three loops finish executing, each of the 1,000 elements in MyArray contains –1.

Dim MyArray(1 to 10, 1 to 10, 1 to 10)

For i = 1 To 10

    For j = 1 To 10

        For k = 1 To 10

            MyArray(i, j, k) = –1

        Next k

    Next j

Next i

Do While loops

A Do While loop is another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes while a specified condition is met. A Do While loop can have one of two syntaxes:

Do [While condition]

    [instructions]

    [Exit Do]

    [instructions]

Loop

or

Do

    [instructions]

    [Exit Do]

    [instructions]

Loop [While condition]

As you can see, VBA enables you to put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed: That is, if the condition is met as soon as the Do statement is executed. In the second syntax, the contents of the loop are always executed at least one time.

The following example is the ROWOFLARGEST function presented in the previous section, rewritten to use a Do While loop (using the first syntax):

Function ROWOFLARGEST2(c)

    NumRows = Rows.Count

    MaxVal = Application.Max(Columns(c))

    r = 1

    Do While Cells(r, c) <> MaxVal

        r = r + 1

    Loop

    ROWOFLARGEST2 = r

End Function

The variable r starts out with a value of 1 and increments within the Do While loop. The looping continues as long as the cell being evaluated is not equal to MaxVal. When the cell is equal to MaxVal, the loop ends, and the function is assigned the value of r. Notice that if the maximum value is in row 1, the looping does not occur.

The following procedure uses the second Do While loop syntax. The loop always executes at least once.

Function ROWOFLARGEST(c)

    MaxVal = Application.Max(Columns(c))

    r = 0

    Do

        r = r + 1

    Loop While Cells(r, c) <> MaxVal

    ROWOFLARGEST = r

End Function

Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately.

Do Until loops

The Do Until loop structure closely resembles the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is true. In a Do Until loop, the loop executes until the condition is true. Do Until also has two syntaxes:

Do [Until condition]

    [instructions]

    [Exit Do]

    [instructions]

Loop

or

Do

    [instructions]

    [Exit Do]

    [instructions]

Loop [Until condition]

The following example demonstrates the first syntax of the Do Until loop. This example makes the code a bit clearer because it avoids the negative comparison required in the Do While example:

Function ROWOFLARGEST4(c)

    NumRows = Rows.Count

    MaxVal = Application.Max(Columns(c))

    r = 1

    Do Until Cells(r, c) = MaxVal

r = r + 1

    Loop

    ROWOFLARGEST4 = r

End Function

Finally, the following function is the same procedure but is rewritten to use the second syntax of the Do Until loop:

Function ROWOFLARGEST5(c)

    NumRows = Rows.Count

    MaxVal = Application.Max(Columns(c))

    r = 0

    Do

        r = r + 1

    Loop Until Cells(r, c) = MaxVal

    ROWOFLARGEST5 = r

End Function

The On Error statement

Undoubtedly, you've used one of Excel's worksheet functions in a formula and discovered that the formula returns an error value (for example, #VALUE!). A formula can return an error value in a number of situations, including these:

• You omitted one or more required argument(s).

• An argument was not the correct data type (for example, text instead of a value).

• An argument is outside of a valid numeric range (division by zero, for example).

In many cases, you can ignore error handling within your functions. If the user does not provide the proper number of arguments, the function simply returns an error value. It's up to the user to figure out the problem. In fact, this is how Excel's worksheet functions handle errors.

In other cases, you want your code to know if errors occurred and then do something about them. Excel's On Error statement enables you to identify and handle errors.

To simply ignore an error, use the following statement:

On Error Resume Next

If you use this statement, you can determine whether an error occurs by checking the Number property of the Err object. If this property is equal to zero, an error did not occur. If Err.Number is equal to anything else, an error did occur.

The following example is a function that returns the name of a cell or range. If the cell or range does not have a name, an error occurs, and the formula that uses the function returns a #VALUE! error.

Function RANGENAME(rng)

   RANGENAME = rng.Name.Name

End Function

The following list shows an improved version of the function. The On Error Resume Next statement causes VBA to ignore the error. The If Err statement checks whether an error occurs. If so, the function returns an empty string.

Function RANGENAME(rng)

    On Error Resume Next

    RANGENAME = rng.Name.Name

    If Err.Number <> 0 Then RANGENAME = “”

End Function

The following statement instructs VBA to watch for errors; if an error occurs, it continues executing at a different named location — in this case, a statement labeled ErrHandler:

On Error GoTo ErrHandler

The following Function procedure demonstrates this statement. The DIVIDETWO function accepts two arguments (num1 and num2) and returns the result of num1 divided by num2.

Function DIVIDETWO(num1, num2)

    On Error GoTo ErrHandler

    DIVIDETWO = num1 / num2

    Exit Function

ErrHandler:

    DIVIDETWO = “ERROR”

End Function

The On Error GoTo statement instructs VBA to jump to the statement labeled ErrHandler if an error occurs. As a result, the function returns a string (ERROR) if any type of error occurs while the function is executing. Note the use of the Exit Function statement. Without this statement, the code continues executing, and the error handling code always executes. In other words, the function always returns ERROR.

It's important to understand that the DIVIDETWO function is nonstandard in its approach. Returning an error message string when an error occurs (ERROR) is not how Excel functions work. Excel functions return an actual error value.

Chapter 26 contains an example that demonstrates how to return an actual error value from a function.

Using Ranges

Most of the custom functions that you develop will work with the data contained in a cell or in a range of cells. Recognize that a range can be a single cell or a group of cells. This section describes some key concepts to make this task easier. The information in this section is intended to be practical, rather than comprehensive. If you want more details, consult Excel's online help.

Chapter 26 contains many practical examples of functions that use ranges. Studying those examples helps to clarify the information in this section.

The For Each-Next construct

Your Function procedures often need to loop through a range of cells. For example, you may write a function that accepts a range as an argument. Your code needs to examine each cell in the range and do something. The For Each-Next construct is very useful for this sort of thing. The syntax of the For Each-Next construct is

For Each element In group

    [instructions]

    [Exit For]

    [instructions]

Next [element]

The following Function procedure accepts a range argument and returns the sum of the squared values in the range:

Function SUMOFSQUARES(rng as Range)

    Dim total as Double

    Dim cell as Range

    total = 0

    For Each cell In rng

        total = total + cell ^ 2

    Next cell

    SUMOFSQUARES = total

End Function

The following is a worksheet formula that uses the SUMOFSQUARES function:

=SUMOFSQUARES(A1:C100)

In this case, the function's argument is a range that consists of 300 cells.

In the preceding example, cell and rng are both variable names. There's nothing special about either name; you can replace them with any valid variable name.

Referencing a range

VBA code can reference a range in a number of different ways:

• Using the Range property

• Using the Cells property

• Using the Offset property

The Range property

You can use the Range property to refer to a range directly by using a cell address or name. The following example assigns the value in cell A1 to a variable named Init. In this case, the statement accesses the range's Value property.

Init = Range(“A1”).Value

In addition to the Value property, VBA enables you to access a number of other properties of a range. For example, the following statement counts the number of cells in a range and assigns the value to the Cnt variable:

Cnt = Range(“A1:C300”).Count

The Range property is also useful for referencing a single cell in a multicell range. For example, you may create a function that is supposed to accept a single-cell argument. If the user specifies a multicell range as the argument, you can use the Range property to extract the upper-left cell in the range. The following example uses the Range property (with an argument of “A1”) to return the value in the upper-left cell of the range represented by the cell argument.

Function SQUARE(cell as Range)

    CellValue = cell.Range(“A1”).Value

    SQUARE = CellValue ^ 2

End Function

Assume that the user enters the following formula:

=SQUARE(C5:C12)

The SQUARE function works with the upper-left cell in C5:C12 (which is C5) and returns the value squared.

Many Excel worksheet functions work in this way. For example, if you specify a multicell range as the first argument for the LEFT function, Excel uses the upper-left cell in the range. However, Excel is not consistent. If you specify a multicell range as the argument for the SQRT function, Excel returns an error.

The Cells property

Another way to reference a range is to use the Cells property. The Cells property accepts two arguments (a row number and a column number) and returns a single cell. The following statement assigns the value in cell A1 to a variable named FirstCell:

FirstCell = Cells(1, 1).Value

The following statement returns the upper-left cell in the range C5:D12:

UpperLeft = Range(“C5:D12”).Cells(1,1).Value

If you use the Cells property without an argument, it returns a range that consists of all cells on the worksheet. In the following example, the TotalCells variable contains the total number of cells in the worksheet:

TotalCells = Cells.Count

The following statement uses the Excel COUNTA function to determine the number of nonempty cells in the worksheet:

NonEmpty =WorksheetFunction.COUNTA(Cells)

The Offset property

The Offset property (like the Range and Cells properties) also returns a Range object. The Offset property is used in conjunction with a range. It takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or right), negative (up or left), or zero. The following example returns the value one cell below cell A1 (that is, cell A2) and assigns it to a variable named NextCell:

NextCell = Range(“A1”).Offset(1,0).Value

The following Function procedure accepts a single-cell argument and returns the sum of the eight cells that surround it:

Function SUMSURROUNDINGCELLS(cell)

    Dim Total As Double

    Dim r As Long, c As Long

    Total = 0

    For r = –1 To 1

        For c = –1 To 1

            Total = Total + cell.Offset(r, c)

        Next c

    Next r

    SUMSURROUNDINGCELLS = Total – cell

End Function

This function uses a nested For-Next loop. So, when the r loop counter is –1, the c loop counter goes from –1 to 1. Nine cells are summed, including the argument cell, which is Offset(0, 0). The final statement subtracts the value of the argument cell from the total. The function returns an error if the argument does not have eight surrounding cells (for example, if it's in row 1 or column 1).

To better understand how the nested loop works, following are nine statements that perform exactly the same calculation:

Total = Total + cell.Offset(–1, –1) ‘ upper left

Total = Total + cell.Offset(–1, 0) ‘left

Total = Total + cell.Offset(–1, 1) ‘upper right

Total = Total + cell.Offset(0, –1) ‘above

Total = Total + cell.Offset(0, 0) ‘the cell itself

Total = Total + cell.Offset(0, 1) ‘right

Total = Total + cell.Offset(1, –1) ‘lower left

Total = Total + cell.Offset(1, 0) ‘below

Total = Total + cell.Offset(1, 1) ‘lower right

Some useful properties of ranges

Previous sections in this chapter give you examples that used the Value property for a range. VBA gives you access to many additional range properties. Some of the more useful properties for function writers are briefly described in the following sections. For complete information on a particular property, refer to the VBA help system.

The Formula property

The Formula property returns the formula (as a string) contained in a cell. If you try to access the Formula property for a range that consists of more than one cell, you get an error. If the cell does not have a formula, this property returns a string, which is the cell's value as it appears in the Formula bar. The following function simply displays the formula for the upper-left cell in a range:

Function CELLFORMULA(cell)

    CELLFORMULA = cell.Range(“A1”).Formula

End Function

You can use the HasFormula property to determine whether a cell has a formula.

The Address Property

The Address property returns the address of a range as a string. By default, it returns the address as an absolute reference (for example, $A$1:$C$12). The following function, which is not all that useful, returns the address of a range:

Function RANGEADDRESS(rng)

    RANGEADDRESS = rng.Address

End Function

For example, the following formula returns the string $A$1:$C$3:

=RANGEADDRESS(A1:C3)

The formula below returns the address of a range named MyRange:

=RANGEADDRESS(MyRange)

The Count property

The Count property returns the number of cells in a range. The following function uses the Count property:

Function CELLCOUNT(rng)

    CELLCOUNT = rng.Count

End Function

The following formula returns 9:

=CELLCOUNT(A1:C3)

The Count property of a Range object is not the same as the COUNT worksheet function. The Count property returns the number of cells in the range, including empty cells and cells with any kind of data. The COUNT worksheet function returns the number of cells in the range that contain numeric data.

Excel 2007 and later worksheets contain more than 17 billion cells compared with a mere 17 million in previous versions. Because of this dramatic increase, the Count property — which returns a Long — may return an error if there are more than 2,147,483,647 cells to be counted. You can use the CountLarge property instead of Count to be safe, but beware that CountLarge does not work in older versions of Excel. In the CELLCOUNT function, the following statement will handle any size range (including all cells on a worksheet):

CELLCOUNT = rng.CountLarge

The Parent property

The Parent property returns an object that corresponds to an object's container object. For a Range object, the Parent property returns a Worksheet object (the worksheet that contains the range).

The following function uses the Parent property and returns the name of the worksheet of the range passed as an argument:

Function SHEETNAME(rng)

    SHEETNAME = rng.Parent.Name

End Function

The following formula, for example, returns the string Sheet1:

=SHEETNAME(Sheet1!A16)

The Name property

The Name property returns a Name object for a cell or range. To get the actual cell or range name, you need to access the Name property of the Name object. If the cell or range does not have a name, the Name property returns an error.

The following Function procedure displays the name of a range or cell passed as its argument. If the range or cell does not have a name, the function returns an empty string. Note the use of On Error Resume Next. This handles situations in which the range does not have a name.

Function RANGENAME(rng)

    On Error Resume Next

    RANGENAME = rng.Name.Name

    If Err.Number <> 0 Then RANGENAME = “”

End Function

The NumberFormat property

The NumberFormat property returns the number format (as a string) assigned to a cell or range. The following function simply displays the number format for the upper-left cell in a range:

Function NUMBERFORMAT(cell)

    NUMBERFORMAT = cell.Range(“A1”).NumberFormat

End Function

The Font property

The Font property returns a Font object for a range or cell. To actually do anything with this Font object, you need to access its properties. For example, a Font object has properties such as Bold, Italic, Name, Color, and so on. The following function returns TRUE if the upper-left cell of its argument is formatted as bold:

Function ISBOLD(cell)

    ISBOLD = cell.Range(“A1”).Font.Bold

End Function

The EntireRow and EntireColumn properties

The EntireRow and EntireColumn properties enable you to work with an entire row or column for a particular cell. The following function accepts a single cell argument and then uses the EntireColumn property to get a range consisting of the cell's entire column. It then uses the Excel COUNTA function to return the number of nonempty cells in the column.

Function NONEMPTYCELLSINCOLUMN(cell)

    NONEMPTYCELLSINCOLUMN = WorksheetFunction.CountA(cell.EntireColumn)

End Function

You cannot use this function in a formula that's in the same column as the cell argument. Doing so will generate a circular reference.

The Hidden property

The Hidden property is used with rows or columns. It returns TRUE if the row or column is hidden. If you try to access this property for a range that does not consist of an entire row or column, you get an error. The following function accepts a single cell argument and returns TRUE if either the cell's row or the cell's column is hidden:

Function CELLISHIDDEN(cell)

    If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then

        CELLISHIDDEN = True

    Else

        CELLISHIDDEN = False

    End If

End Function

You can also write this function without using an If-Then-Else construct. In the following function, the expression to the right of the equal sign returns either TRUE or FALSE — and this value is returned by the function:

Function CELLISHIDDEN(cell)

    CELLISHIDDEN = cell.EntireRow.Hidden Or cell.EntireColumn.Hidden

End Function

The Set keyword

An important concept in VBA is the ability to create a new Range object and assign it to a variable — more specifically, an object variable. You do so by using the Set keyword. The following statement creates an object variable named MyRange:

Set MyRange = Range(“A1:A10”)

After the statement executes, you can use the MyRange variable in your code in place of the actual range reference. Examples in subsequent sections help to clarify this concept.

Creating a Range object is not the same as creating a named range. In other words, you can't use the name of a Range object in your worksheet formulas.

The Intersect function

The Intersect function returns a range that consists of the intersection of two other ranges. For example, consider the two ranges selected in Figure 25-2. These ranges, D3:D10 and B5:F5, contain one cell in common (D5). In other words, D5 is the intersection of D3:D10 and B5:F5.

Figure 25-2: Use the Intersect function to work with the intersection of two ranges.

The following Function procedure accepts two range arguments and returns the count of the number of cells that the ranges have in common:

Function CELLSINCOMMON(rng1, rng2)

    Dim CommonCells As Range

    On Error Resume Next

    Set CommonCells = Intersect(rng1, rng2)

    If Err.Number = 0 Then

        CELLSINCOMMON = CommonCells.CountLarge

    Else

        CELLSINCOMMON = 0

    End If

End Function

The CELLSINCOMMON function uses the Intersect function to create a range object named CommonCells. Note the use of On Error Resume Next. This statement is necessary because the Intersect function returns an error if the ranges have no cells in common. If the error occurs, it is ignored. The final statement checks the Number property of the Err object. If it is 0, no error occurs, and the function returns the value of the CountLarge property for the CommonCells object. If an error does occur, Err.Number has a value other than 0, and the function returns 0.

The Union function

The Union function combines two or more ranges into a single range. The following statement uses the Union function to create a range object that consists of the first and third columns of a worksheet:

Set TwoCols = Union(Range(“A:A”), Range(“C:C”))

The Union function takes between 2 and 30 arguments.

The UsedRange property

The UsedRange property returns a Range object that represents the used range of the worksheet. Press Ctrl+End to activate the lower-right cell of the used range. The UsedRange property can be very useful in making your functions more efficient.

Consider the following Function procedure. This function accepts a range argument and returns the number of formula cells in the range:

Function FORMULACOUNT(rng)

    cnt = 0

    For Each cell In rng

        If cell.HasFormula Then cnt = cnt + 1

    Next cell

    FORMULACOUNT = cnt

End Function

In many cases, the preceding function works just fine. But what if the user enters a formula like this one?

=FORMULACOUNT(A:C)

The three-column argument consists of 3,145,728 cells. With an argument that consists of one or more entire columns, the function does not work well because it loops through every cell in the range, even those that are well beyond the area of the sheet that's actually used. The following function is rewritten to make it more efficient:

Function FORMULACOUNT(rng)

    cnt = 0

    Set WorkRange = Intersect(rng, rng.Parent.UsedRange)

    If WorkRange Is Nothing Then

        FORMULACOUNT = 0

        Exit Function

End If

    For Each cell In WorkRange

        If cell.HasFormula Then cnt = cnt + 1

    Next cell

    FORMULACOUNT = cnt

End Function

This function creates a Range object variable named WorkRange that consists of the intersection of the range passed as an argument and the used range of the worksheet. In other words, WorkRange consists of a subset of the range argument that only includes cells in the used range of the worksheet. Note the If-Then construct that checks if the WorkRange is Nothing. That will be the case if the argument for the function is outside of the used range. In such a case, the function returns 0, and execution ends.