Microsoft Office Tutorials and References
In Depth Information
• 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
• 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 oth-
er) 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:
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 instruc-
tion 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”