Microsoft Office Tutorials and References
In Depth Information
Having knowledge of VBA’s 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, “ “, “”)
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.
Excel’s infrequently used ROMAN function converts a decimal number into a Roman numeral.
DecValue = 2010
RomanValue = WorksheetFunction.Roman(DecValue)
The variable RomanValue contains the string MMX . Fans of old movies are often dismayed when
they learn that Excel does not have a function to convert a Roman numeral to its decimal
equivalent. You can, of course, create such a function using VBA. Are you up for a challenge?
It’s important to understand that 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
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:
h If-Then constructs
h Select Case constructs
h For-Next loops