Method 'MacroOptions' of object '_Application' failed

The error occurs due to various reasons

1. The Function corresponding to Macro name is not in a code module (it might be in Sheet/Workbook)

Solution : Copy the Macro to a code module. If a code module is not available, create one by Insert à Module

2. The macro name is not fully qualified. (WorkbookName!MacroName)

Solution: The following code snippet gives an example for prefixing the Macro name with the workbook name

Sub Add_UDF_To_Category()

Application.MacroOptions Macro:="PERSONAL.XLSB!Get_Net_Working_Days", Category:=2, Description:="Returns Net Working Days for 2009"

End Sub

Method 'MacroOptions' of object '_Application' failed