Microsoft Office Tutorials and References

In Depth Information

**Function Procedures**

You’ll notice that unlike
Sub
procedures, your
Function
procedures don’t appear in the Macro

dialog box when you issue the Developer➜Code➜Macros command. In addition, you can’t

choose a function when you issue the VBE Run➜Sub/UserForm command (or press F5) if the

cursor is located in a
Function
procedure. (You get the Macro dialog box that lets you choose a

macro to run.) As a result, you need to do a bit of extra up-front work to test your functions

while you’re developing them. One approach is to set up a simple procedure that calls the

function. If the function is designed to be used in worksheet formulas, you’ll want to enter a simple

formula to test it.

In a conditional formatting formula

When you specify conditional formatting, one of the options is to create a formula. The formula

must be a logical formula (that is, it must return either
TRUE
or
FALSE
). If the formula returns

TRUE
, the condition is met, and formatting is applied to the cell.

You can use custom VBA functions in your conditional formatting formulas. For example, here’s a

simple VBA function that returns
TRUE
if its argument is a cell that contains a formula:

Function CELLHASFORMULA(cell) As Boolean

CELLHASFORMULA = cell.HasFormula

End Function

After defining this function in a VBA module, you can set up a conditional formatting rule so that

cells that contain a formula contain different formatting:

1.

Select the range that will contain the conditional formatting.

For example, select A1:G20.

2.

Choose Home
➜
Styles
➜
Conditional Formatting
➜
New Rule.

3.

In the New Formatting Rule dialog box, select the option labeled Use a Formula to

Determine Which Cells to Format.

4.

Enter this formula in the formula box — but make sure that the cell reference argument

corresponds to the upper-left cell in the range that you selected in Step 1:

=CELLHASFORMULA(A1)

5.

Click the Format button to specify the formatting for cells that meet this condition.

6.

Click OK to apply the conditional formatting rule to the selected range.

Cells in the range that contain a formula will display the formatting you specified. Figure 10-3

shows the New Formatting Rule dialog box, specifying a custom function in a formula.