Microsoft Office Tutorials and References
In Depth Information
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
REMOVESPACES = Temp
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 desig-
nates 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 state-
ment are executed a number of times; the value of CellLength determines the number of times. For ex-
ample, 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 oper-
ator). 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