Create a custom function
When you frequently use a non-standard function, it may be an idea to create a custom function at workbook start-up using a User-defined function (UDF). The IF(ISERROR) function is one example of this.
We can create a custom IF(ISERROR) function in two easy steps:
1. Implement the custom function:
___________________________________
Public Function IFISERROR(ByRef Evaluate As Variant, _
ByRef Default As Variant) As Variant
If IsError(Evaluate) Then
IFISERROR = Default ‘if an error, returns default
Else
IFISERROR = Evaluate ‘no error, evaluates function
End If
End Function
___________________________________
The function is now available to use – this is accessible from the User Defined category in Insert Function. However, this offers no guide how the function works. You can add this information be registering the UDF in step 2.
2. Register the custom UDF:
___________________________________
Sub CreateCustomFunction()
Dim strDescription As String
strDescription = “A custom IF(ISERROR) function: ” & _
vbLf & “=IF(ISERROR(function),default value,function)”
‘this text will appear in the function dialog box
Application.MacroOptions Macro:=”IFISERROR”, _
Description:=strDescription, _
Category:=9 ’9=Information category of UDF category names
End Sub
___________________________________
Run the CreateCustomFunction (you can run this from the Workbook_Open event) and your new custom function will appear in the function dialog box:
