Application.MacroOptions and Error 1004

Question

I want to register a function kind of CalculateHours(work_hour, rest_hour) to Excel VBA, in an Addin like Works.xla

I tried using Application.MacroOptions to register the function. The following code is in a Class file, the function is in another module file. They would load when we open Excel and the Addin.

Code:

Private Function AddFunctions()    
    With MyFunction       
        Application.MacroOptions .Name, _
            .Description, , , , , .Category, , , .HelpFilePath                
    End With    
End Function

Expectation:

I wanna get the argument help, function description in Excel function wizard as other built-in functions. With the help button link to my help file.

Result:

Error number: 1004 Application-defined or object-defined error

or

Method "MacroOptions" of object "_Application" failed

Is there anything (or everything) wrong?

I have kind of 10 functions and need to add them automatically to Excel function wizard every time load the Addin.

Answer 1

07/12/2016 well after dragging my function around following pieces of advise from some posts and doing a number of other pointless things, I found this error happens if Application.MacroOptions Description:=FuncDesc exceeds 255 characters. So essentially don't get too verbose with the description of your user defined function, or just add a

If Len(FuncDesc) > 255 then Scary warning message about Run-time error '1004' End if

Answer 2

Another possible issue (and solution) that was affecting me... The function code needs to be written in a module - if it it written in the ThisWorkbook page, Excel won't be able to find the code.

For ease of future readers, here is a compilation of the multiple answers (i.e. list of potential things to check)...

1. The function code needs to be written in a module (not ThisWorkbook)
2. Make sure the function description does not exceed 255 characters
3. If defined in another workbook, try including the workbook name - e.g. Macro:="'PERSONAL.xlsb'!Macro/UDF_Name"
4. If defined in an Excel add-in, call ThisWorkbook.Activate before Application.MacroOptions

Hope this helps, please one-up the respective solution poster if their answer helped you 👍🏼