Microsoft Office Tutorials and References
In Depth Information
udfs And Errors
You might wonder what happens when an error occurs with a UDF. For example,
what if the SumColor function is entered into a cell with an illogical range
argument address such as =SUMCOLOR(A2:XYZ) ? Or, what if a UDF attempts to divide a
number by zero?
When a UDF attempts to do what it cannot do, the cell will display a #VALUE!
error. Whereas a failed macro will result in a runtime error with an imposing
Message Box to announce the error and a debug option to identify the offending
code line, such is not the case with a failed UDF. Even though it is a VBA item, a
failed UDF will only return the #VALUE! error. With larger UDFs, finding the cause
of the error can be a real chore. Therefore, it’s a good idea to test each code line in
the Immediate window as you write your larger UDFs.
Sometimes, you want a UDF to return a value and then do nothing else until you purposely cause
it to recalculate. An example is if you want to produce a random number in a cell but keep that
number constant until you decide to change it again, if ever. The worksheet function RAND() will
return a random number but it recalculates whenever the worksheet recalculates or any cell in
that worksheet is edited. This UDF will return an unchanging (static) random number between
1 and 100:
Function StaticRandom() As Double
StaticRandom = Int(Rnd() * 100)
The function entry for the cell is:
You’ll notice that the StaticRandom UDF does not require an argument. Even
so, the empty parentheses must immediately follow the function’s name in the
first code line, and when entering the UDF in a cell, the parentheses must be
included as you see in this example.
Now with the StaticRand UDF in its current state, its returned random number will not change
unless you purposely call the UDF, such as if you select the cell, press the F2 key, and press Enter,
or if you press Ctrl+Alt+F9 to force a calculation on all cells.