Microsoft Office Tutorials and References

In Depth Information

**Controlling Execution**

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, “ “, “”)

End Function

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

Controlling Execution

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