Microsoft Office Tutorials and References
In Depth Information
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
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:
Select the range that will contain the conditional formatting.
For example, select A1:G20.
Choose Home ➜ Styles ➜ Conditional Formatting ➜ New Rule.
In the New Formatting Rule dialog box, select the option labeled Use a Formula to
Determine Which Cells to Format.
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:
Click the Format button to specify the formatting for cells that meet this condition.
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.