Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

 Download CD Content

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 will consult the Help system and make use of Internet resources or other books for additional information.

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 Integer Dim Temp As String Dim Characters As String Dim i As Integer 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 simply a comment (optional) that describes what the function does. The initial apostrophe designates this line as a comment.

• The next four lines use the Dim keyword to declare the four variables used in the procedure: CellLength, Temp, Character, and i. Declaring a variable 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 statement creates a variable named Temp and assigns it an empty string.

• The next four statements 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 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.) 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)

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