Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking s


MsOffice.US
Home
Excel
FrontPage
Access
Word
PowerPoint
Outlook
Visio
Project
Publisher
InfoPath
Exchange
SharePoint
Commerce
Expression
Silverlight
Management
Software
Windows

 

 

 

 

   

 
Home Excel Topics

Using Built-In VBA Functions

 


VBA has a variety of built-in functions that simplify calculations and operations. Many of VBA’s functions are similar (or identical) to Excel’s worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER.

 

To display a list of VBA functions while writing your code, type VBA followed by a

period (.). The VB Editor displays a list of all functions and constants (see Figure 24-1).

 

Text Box:  

 

If this does not work for you, make sure that you select the Auto List Members option. Choose ToolsOptions and click the Editor tab. In addition to functions, the displayed list also includes built-in constants. The VBA functions are all described in the online help. To view Excel Help, just move the cursor over a function name and press F1.

 

 

 

Figure 24-1: Displaying a list of VBA functions in the VB Editor.

 

Here’s a statement that calculates the square root of a variable by using VBA’s Sqr function and then assigns the result to a variable named x:

 

 

x  =  Sqr(MyValue)


 

 

 

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 func- tion. 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 work- sheet 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 equiva- lent. 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


 

 

 

 

 

h  Do  While loops

 

h  Do  Until loops

 

h  On  Error statements

 

 

The If-Then construct

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This instruction is one way to endow your applications with decision-making capability. The basic syntax of the If-Then construct is as follows:

 

 

If  condition  Then  true_instructions  [Else  false_instructions]

 

 

The If-Then construct executes one or more statements conditionally. The Else clause is optional. If included, it enables you to execute one or more instructions when the condition that you test is not true.

 

The following Function procedure demonstrates an If-Then structure without an Else

clause. The example deals with time. VBA uses the same date-and-time serial number system as Excel (but with a much wider range of dates). The time of day is expressed as a fractional value for example, noon is represented as .5. The VBA Time function returns a value that represents

the time of day, as reported by the system clock. In the following example, the function starts out

by assigning an empty string to GreetMe. The If-Then statement checks the time of day. If the time is before noon, the Then part of the statement executes, and the function returns Good Morning.

 

 

Function  GreetMe() GreetMe  =  “”

If  Time  <  0.5  Then  GreetMe=  “Good  Morning” End  Function

 

 

The following function uses two If-Then statements. It displays either Good  Morning or

Good  Afternoon:

 

 

Function  GreetMe()

If  Time  <  0.5  Then  GreetMe  =  “Good  Morning”

If  Time  >=  0.5  Then  GreetMe  =  “Good  Afternoon” End  Function

 

 

Notice that the second If-Then statement uses >= (greater than or equal to). This covers the extremely remote chance that the time is precisely 12:00 noon when the function is executed.


 

 

 

Another approach is to use the Else clause of the If-Then construct:

 

 

Function  GreetMe()

If  Time  <  0.5  Then  GreetMe  =  “Good  Morning”  Else  _ GreetMe  =  “Good  Afternoon”

End  Function

 

 

Notice that the preceding example uses the line continuation sequence (a space followed by an underscore); If-Then-Else is actually a single statement.

 

The following is another example that uses the If-Then construct. This Function procedure calculates a discount based on a quantity (assumed to be an integer value). It accepts one argu- ment (quantity) and returns the appropriate discount based on that value.

 

 

Function  Discount(quantity)

If  quantity  <=  5  Then  Discount  =  0

If  quantity  >=  6  Then  Discount  =  0.1

If  quantity  >=  25  Then  Discount  =  0.15

If  quantity  >=  50  Then  Discount  =  0.2

If  quantity  >=  75  Then  Discount  =  0.25

End  Function

 

 

Notice that each If-Then statement in this procedure is always executed, and the value for

Discount can change as the function executes. The final value, however, is the desired value.

 

The preceding examples all used a single statement for the Then clause of the If-Then con- struct. However, you often need to execute multiple statements if a condition is TRUE. You can still use the If-Then construct, but you need to use an End  If statement to signal the end of

the statements that make up the Then clause. Here’s an example that executes two statements if

the If clause is TRUE:

 

 

If  x  >  0  Then y  =  2

z  =  3

End  If

 

 

You can also use multiple statements for an If-Then-Else construct. Here’s an example that exe- cutes two statements if the If clause is TRUE, and two other statements if the If clause is not TRUE:

 

 

If  x  >  0  Then y  =  2

z  =  3

Else

y  =  –2

z  =  –3

End  If


 

 

The Select Case construct

The Select  Case construct is useful for choosing among three or more options. this construct also works with two options and is a good alternative to using If-Then-Else. The syntax for Select  Case is as follows:

 

 

Select  Case  testexpression

[Case  expressionlist–n

[instructions–n]]

[Case  Else

[default_instructions]] End  Select

 

 

The following example of a Select  Case construct shows another way to code the GreetMe

examples presented in the preceding section:

 

 

Function  GreetMe() Select  Case  Time

Case  Is  <  0.5

GreetMe  =  “Good  Morning” Case  0.5  To  0.75

GreetMe  =  “Good  Afternoon” Case  Else

GreetMe  =  “Good  Evening” End  Select

End  Function

 

 

And here’s a rewritten version of the Discount function from the previous section, this time using a Select  Case construct:

 

 

Function  Discount2(quantity) Select  Case  quantity

Case  Is  <=  5

Discount2  =  0

Case  6  To  24

Discount2  =  0.1

Case  25  To  49

Discount2  =  0.15

Case  50  To  74

Discount2  =  0.2

Case  Is  >=  75

Discount2  =  0.25

End  Select

End  Function

 

 

Any number of instructions can be written below each Case statement; they all execute if that case evaluates to TRUE.


 
 

 

 

 

 

Looping blocks of instructions

Looping is the process of repeating a block of VBA instructions within a procedure. You may know the number of times to loop, or it may be determined by the values of variables in your program. VBA offers a number of looping constructs:

 

h  For-Next loops h  Do  While loops h  Do  Until loops

 

 

For-Next loops

The following is the syntax for a For-Next loop:

 

 

For  counter  =  start  To  end  [Step  stepval]

[instructions]

[Exit  For]

[instructions] Next  [counter]

 

 

The following listing is an example of a For-Next loop that does not use the optional Step

value or the optional Exit  For statement. This function accepts two arguments and returns the sum of all integers between (and including) the arguments:

 

 

Function  SumIntegers(first,  last)

total  =  0

For  num  =  first  To  last total  =  total  +  num

Next  num

SumIntegers  =  total

End  Function

 

 

The following formula, for example, returns 55 the sum of all integers from 1 to 10:

 

 

=SumIntegers(1,10)

 

 

In this example, num (the loop counter variable) starts out with the same value as the first vari- able, and increases by 1 each time the loop repeats. The loop ends when num is equal to the last

variable. The total variable simply accumulates the various values of num as it changes during the looping.


 

 

 

When you use For-Next loops, you should understand that the loop counter is a nor-

mal variable it is not a special type of variable. As a result, you can change the value

 

Text Box:  

 

of the loop counter within the block of code executed between the For and Next statements. this is, however, a very bad practice and can cause problems. In fact, you should take special precautions to ensure that your code does not change the loop counter.

 

 

You also can use a Step value to skip some values in the loop. Here’s the same function rewrit-

ten to sum every other integer between the first and last arguments:

 

 

Function  SumIntegers2(first,  last)

total  =  0

For  num  =  first  To  last  Step  2

total  =  total  +  num

Next  num

SumIntegers2  =  Total

End  Function

 

 

The following formula returns 25, which is the sum of 1, 3, 5, 7, and 9:

 

 

=SumIntegers2(1,10)

 

 

For-Next loops can also include one or more Exit  For statements within the loop. When this statement is encountered, the loop terminates immediately, as the following example demonstrates:

 

 

Function  RowOfLargest(c) NumRows  =  Rows.Count

MaxVal  =  WorksheetFunction.Max(Columns(c)) For  r  =  1  To  NumRows

If  Cells(r,  c)  =  MaxVal  Then

RowOfLargest  =  r

Exit  For

End  If

Next  r

End  Function

 

 

The RowOfLargest function accepts a column number (1–16,384) for its argument and returns the row number of the largest value in that column. It starts by getting a count of the number of rows

in the worksheet. (This varies, depending on the version of Excel.) This number is assigned to the NumRows variable. The maximum value in the column is calculated by using the Excel MAX func- tion, and this value is assigned to the MaxVal variable.


 

 

 

The For-Next loop checks each cell in the column. When the cell equal to MaxVal is found, the

row number (variable r, the loop counter) is assigned to the function’s name, and the Exit  For statement ends the procedure. Without the Exit  For statement, the loop continues to check all cells in the column which can take quite a long time!

 

The previous examples use relatively simple loops. But you can have any number of statements

in the loop, and you can even nest For-Next loops inside other For-Next loops. The following

is VBA code that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value –1. When the three loops finish executing, each of the 1,000 elements in MyArray contains –1.

 

 

Dim  MyArray(1  to  10,  1  to  10,  1  to  10) For  i  =  1  To  10

For  j  =  1  To  10

For  k  =  1  To  10

MyArray(i,  j,  k)  =  –1

Next  k

Next  j

Next  i

 

 

Do While loops

A Do  While loop is another type of looping structure available in VBA. Unlike a For-Next

loop, a Do  While loop executes while a specified condition is met. A Do  While loop can have one of two syntaxes:

 

 

Do  [While  condition]

[instructions]

[Exit  Do]

[instructions]

Loop

 

 

or

 

 

Do

[instructions]

[Exit  Do]

[instructions]

Loop  [While  condition]

 

 

As you can see, VBA enables you to put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed: That is, if the con- dition is met as soon as the Do statement is executed. In the second syntax, the contents of the loop are always executed at least one time.


 

 

 

 

 

The following example is the RowOfLargest function presented in the previous section, rewrit-

ten to use a Do  While loop (using the first syntax):

 

 

Function  RowOfLargest2(c) NumRows  =  Rows.Count

MaxVal  =  Application.Max(Columns(c))

r  =  1

Do  While  Cells(r,  c)  <>  MaxVal r  =  r  +  1

Loop

RowOfLargest2  =  r

End  Function

 

 

The variable r starts out with a value of 1 and increments within the Do  While loop. The looping continues as long as the cell being evaluated is not equal to MaxVal. When the cell is equal to MaxVal, the loop ends, and the function is assigned the value of r. Notice that if the maximum value is in row 1, the looping does not occur.

 

The following procedure uses the second Do  While loop syntax. The loop always executes at least once.

 

 

Function  RowOfLargest(c)

MaxVal  =  Application.Max(Columns(c))

r  =  0

Do

r  =  r  +  1

Loop  While  Cells(r,  c)  <>  MaxVal

RowOfLargest  =  r

End  Function

 

 

Do  While loops can also contain one or more Exit  Do statements. When an Exit  Do state- ment is encountered, the loop ends immediately.

 

 

Do Until loops

The Do  Until loop structure closely resembles the Do  While structure. The difference is evi-

dent only when the condition is tested. In a Do  While loop, the loop executes while the condition

is true. In a Do  Until loop, the loop executes until the condition is true. Do  Until also has two syntaxes:

 

 

Do  [Until  condition]

[instructions]

[Exit  Do]

[instructions]

Loop


 

 

 


 

 

 

 

or

 

 

Do

[instructions]

[Exit  Do]

[instructions]

Loop  [Until  condition]

 

 

The following example demonstrates the first syntax of the Do  Until loop. This example makes the code a bit clearer because it avoids the negative comparison required in the Do  While example.

 

 

Function  RowOfLargest4(c) NumRows  =  Rows.Count

MaxVal  =  Application.Max(Columns(c))

r  =  1

Do  Until  Cells(r,  c)  =  MaxVal r  =  r  +  1

Loop

RowOfLargest4  =  r

End  Function

 

 

Finally, the following function is the same procedure but is rewritten to use the second syntax of the Do  Until loop:

 

 

Function  RowOfLargest5(c) NumRows  =  Rows.Count

MaxVal  =  Application.Max(Columns(c))

r  =  0

Do

r  =  r  +  1

Loop  Until  Cells(r,  c)  =  MaxVal

RowOfLargest5  =  r

End  Function

 

 

The On Error statement

Undoubtedly, you’ve used one of Excel’s worksheet functions in a formula and discovered that

the formula returns an error value (for example, #VALUE!). A formula can return an error value in

a number of situations, including these:

 

h  You omitted one or more required argument(s).

 

h  An argument was not the correct data type (for example, text instead of a value).

 

h  An argument is outside of a valid numeric range (division by zero, for example).


 

 

 

In many cases, you can ignore error handling within your functions. If the user does not provide

the proper number of arguments, the function simply returns an error value. It’s up to the user to figure out the problem. In fact, this is how Excel’s worksheet functions handle errors.

 

In other cases, you want your code to know if errors occurred and then do something about them. Excel’s On Error statement enables you to identify and handle errors.

 

To simply ignore an error, use the following statement:

 

 

On  Error  Resume  Next

 

 

If you use this statement, you can determine whether an error occurs by checking the Number property of the Err object. If this property is equal to zero, an error did not occur. If Err. Number is equal to anything else, an error did occur.

 

The following example is a function that returns the name of a cell or range. If the cell or range

does not have a name, an error occurs, and the formula that uses the function returns a #VALUE!

error.

 

 

Function  RANGENAME(rng) RANGENAME  =  rng.Name.Name

End  Function

 

 

The following list shows an improved version of the function. The On  Error  Resume  Next

statement causes VBA to ignore the error. The If  Err statement checks whether an error occurs.

If so, the function returns an empty string.

 

 

Function  RANGENAME(rng) On  Error  Resume  Next

RANGENAME  =  rng.Name.Name

If  Err.Number  <>  0  Then  RANGENAME  =  “” End  Function

 

 

The following statement instructs VBA to watch for errors; if an error occurs, it continues execut- ing at a different named location in this case, a statement labeled ErrHandler:

 

 

On  Error  GoTo  ErrHandler

 

 

The following Function procedure demonstrates this statement. The DIVIDETWO function accepts two arguments (num1 and num2) and returns the result of num1 divided by num2.


 

 


 

 

 

 

Function  DIVIDETWO(num1,  num2)

On  Error  GoTo  ErrHandler

DIVIDETWO  =  num1  /  num2

Exit  Function

ErrHandler:

DIVIDETWO  =  “ERROR” End  Function

 

 

The On  Error  GoTo statement instructs VBA to jump to the statement labeled ErrHandler

if an error occurs. As a result, the function returns a string (ERROR) if any type of error occurs while the function is executing. Note the use of the Exit  Function statement. Without this statement, the code continues executing, and the error handling code always executes. In other words, the function always returns ERROR.

 

It’s important to understand that the DIVIDETWO function is nonstandard in its approach. Returning an error message string when an error occurs (ERROR) is not how Excel functions work. Excel functions return an actual error value.

 

Chapter 25 contains an example that demonstrates how to return an actual error value

 

Text Box:  

 

from a function.

 

 

 

 

Using Ranges

Many of the custom functions that you develop will work with the data contained in a cell or in a range of cells. Recognize that a range can be a single cell or a group of cells. This section

describes some key concepts to make this task easier. The information in this section is intended

to be practical, rather than comprehensive. If you want more details, consult Excel’s online help.

 

 

Chapter 25 contains many practical examples of functions that use ranges. Studying

 

Text Box:  

 

those examples helps to clarify the information in this section.

 

 

 

 

The For Each-Next construct

Your Function procedures often need to loop through a range of cells. For example, you may write a function that accepts a range as an argument. Your code needs to examine each cell in

the range and do something. The For  Each-Next construct is very useful for this sort of thing. The syntax of the For  Each-Next construct is

 

 

For  Each  element  In  group

[instructions]

[Exit  For]

[instructions] Next  [element]


 

 

 

 

 

The following Function procedure accepts a range argument and returns the sum of the

squared values in the range:

 

 

Function  SUMOFSQUARES(rng  as  Range) Dim  total  as  Double

Dim  cell  as  Range total  =  0

For  Each  cell  In  rng

total  =  total  +  cell  ^  2

Next  cell

SUMOFSQUARES  =  total

End  Function

 

 

The following is a worksheet formula that uses the SumOfSquares function:

 

 

=SumOfSquares(A1:C100)

 

 

In this case, the function’s argument is a range that consists of 300 cells.

 

 

In the preceding example, cell and rng are both variable names. There’s nothing

 

Text Box:  

 

special about either name; you can replace them with any valid variable name.

 

 

 

 

Referencing a range

VBA code can reference a range in a number of different ways:

 

h  Using the Range property

 

h  Using the Cells property

 

h  Using the Offset property

 

 

The Range property

You can use the Range property to refer to a range directly by using a cell address or name. The following example assigns the value in cell A1 to a variable named Init. In this case, the state- ment accesses the range’s Value property.

 

 

Init  =  Range(“A1”).Value

 

 

In addition to the Value property, VBA enables you to access a number of other properties of a range. For example, the following statement counts the number of cells in a range and assigns

the value to the Cnt variable:


 

 

 

Cnt  =  Range(“A1:C300”).Count

 

 

The Range property is also useful for referencing a single cell in a multicell range. For example, you may create a function that is supposed to accept a single-cell argument. If the user specifies

a multicell range as the argument, you can use the Range property to extract the upper-left cell

in the range. The following example uses the Range property (with an argument of “A1”) to return the value in the upper-left cell of the range represented by the cell argument.

 

 

Function  Square(cell  as  Range) CellValue  =  cell.Range(“A1”).Value Square  =  CellValue  ^  2

End  Function

 

 

Assume that the user enters the following formula:

 

 

=Square(C5:C12)

 

 

The Square function works with the upper-left cell in C5:C12 (which is C5) and returns the value squared.

 

Many Excel worksheet functions work in this way. For example, if you specify a multi-

cell range as the first argument for the LEFT function, Excel uses the upper-left cell

 

Text Box:  

 

in the range. However, Excel is not consistent. If you specify a multicell range as the argument for the SQRT function, Excel returns an error.

 

 

 

The Cells property

Another way to reference a range is to use the Cells property. The Cells property accepts two arguments (a row number and a column number), and returns a single cell. The following statement assigns the value in cell A1 to a variable named FirstCell:

 

 

FirstCell  =  Cells(1,  1).Value

 

 

The following statement returns the upper-left cell in the range C5:C12:

 

 

UpperLeft  =  Range(“C5:C12”).Cells(1,1)

 

 

 

If you use the Cells property without an argument, it returns a range that consists of

 

Text Box:  

 

all cells on the worksheet. In the following example, the TotalCells variable contains the total number of cells in the worksheet:

 

 

TotalCells  =  Cells.Count


 

 

 

 

 

The following statement uses the Excel COUNTA function to determine the number of nonempty

cells in the worksheet:

 

 

NonEmpty  =WorksheetFunction.COUNTA(Cells)

 

 

 

The Offset property

The Offset property (like the Range and Cells properties) also returns a Range object. The Offset property is used in conjunction with a range. It takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can

be positive (down or right), negative (up or left), or zero. The following example returns the value one cell below cell A1 (that is, cell A2) and assigns it to a variable named NextCell:

 

 

NextCell  =  Range(“A1”).Offset(1,0).Value

 

 

The following Function procedure accepts a single-cell argument and returns the sum of the eight cells that surround it:

 

 

Function  SumSurroundingCells(cell) Dim  Total  As  Double

Dim  r  As  Long,  c  As  Long

Total  =  0

For  r  =  –1  To  1

For  c  =  –1  To  1

Total  =  Total  +  cell.Offset(r,  c) Next  c

Next  r

SumSurroundingCells  =  Total   cell

End  Function

 

 

This function uses a nested For-Next loop. So, when the r loop counter is –1, the c loop counter goes from –1 to 1. Nine cells are summed, including the argument cell, which is Offset(0,  0). The final statement subtracts the value of the argument cell from the total.

The function returns an error if the argument does not have eight surrounding cells (for example,

if it’s in row 1 or column 1).

 

To better understand how the nested loop works, following are nine statements that perform exactly the same calculation:

 

 

Total  =  Total  +  cell.Offset(–1,  –1)   upper  left

Total  =  Total  +  cell.Offset(–1,  0)  ‘left

Total  =  Total  +  cell.Offset(–1,  1)  ‘upper  right

Total  =  Total  +  cell.Offset(0,  –1)  ‘above

Total  =  Total  +  cell.Offset(0,  0)  ‘the  cell  itself


 
 

 

 

 

 

Total  =  Total  +  cell.Offset(0,  1)  ‘right

Total  =  Total  +  cell.Offset(1,  –1)  ‘lower  left

Total  =  Total  +  cell.Offset(1,  0)  ‘below

Total  =  Total  +  cell.Offset(1,  1)  ‘lower  right

 

 

Some useful properties of ranges

Previous sections in this chapter give you examples that used the Value property for a range. VBA gives you access to many additional range properties. Some of the more useful properties

for function writers are briefly described in the following sections. For complete information on a particular property, refer to Excel’s online help.

 

 

The Formula property

The Formula property returns the formula (as a string) contained in a cell. If you try to access the

Formula property for a range that consists of more than one cell, you get an error. If the cell

does not have a formula, this property returns a string, which is the cell’s value as it appears in the

Formula bar. The following function simply displays the formula for the upper-left cell in a range:

 

 

Function  CELLFORMULA(cell)

CELLFORMULA  =  cell.Range(“A1”).Formula

End  Function

 

 

You can use the HasFormula property to determine whether a cell has a formula.

 

 

The Address Property

The Address property returns the address of a range as a string. By default, it returns the

address as an absolute reference (for example, $A$1:$C$12). The following function, which is not all that useful, returns the address of a range:

 

 

Function  RANGEADDRESS(rng) RANGEADDRESS  =  rng.Address

End  Function