Chapter 24: VBA Programming Concepts

 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.

On the CD 

Many of the code examples in this chapter are on the companion CD-ROM. 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 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.

Note 

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," ","")