MacLochlainns Weblog

While working on a VBA write-up for some documentation on Excel 2007/2010, I ran into some interesting parameter validation rules for Excel User-Defined Functions (UDFs). I found that optional values are suppressed when you pass a cell reference that points to an empty cell.

Let’s say you develop a simple test function like the following. You may expect that it returns the number passed as a call parameter or the default value of the opt variable, 22, but it doesn’t.

1
2
3
4

Function OptionalOverride(Optional opt As Variant = 22)
  ' Return the unfiltered call parameter.
  OptionalOverride = opt
End Function

A call to an OptionalOverride function like this works when the cell reference points to a value, but fails when it points to an empty cell. It returns a value of 0 with a numeric data type, as verified by the built-in TYPE function.

=OptionalOverride(D1)

This behavior means you always need to check for empty cell references and reassigns them a value inside an if-block. That is if you really want the default value to apply in all cases. The function includes the explicit call parameter assignment in the modified function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Then
    opt = 21
  End If
  ' Return the unfiltered call parameter unless empty then return 21.
  OptionalOverride = opt
End Function

The modified function returns (a) the call parameter value, (b) 21 when the call parameter points to (references) an empty cell, (c) 22 when you exclude the variable from the call parameter list, or (d) any string value found in the call parameter. The return of a string value is clearly not the desired behavior.

You must modify the if-block by checking whether the call parameter is some data type other than a number before assigning a default value. The following demonstrates the final parameter validating function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Or Not IsNumeric(opt) Then
    opt = 21
  End If
  ' Return the filtered call parameter.
  OptionalOverride = opt
End Function

Hope this helps a few folks trying to avoid that ugly #VALUE! error returning from your UDFs.