User Defined Functions Excel 2016 MAC

I'm attempting (with not a lot of luck) to add descriptions for my User Defined Functions and their arguments in Excel 2016 MAC version 15.18. The latest version of the VBA editor is so amazingly basic that it is now impossible to add any form of description to UDFs. So I'm attempting to use the "Application.MacroOptionsmethod that various users on the net suggest and as described in https://msdn.microsoft.com/en-us/library/office/ff838997.aspx.

The VBA code I have used is:

    Application.MacroOptions _

        Macro:=FuncName, _

        Description:=FuncDesc, _

        Category:=FuncCat, _

        ArgumentDescriptions:=ArgDesc

When the code is executed the following Compile error occurs:

 

and the argument "ArgumentDescriptions:=" is highlighted. If this particular argument is removed from the Application.MacroOption then the error does not occur. Why? Am I doing something wrong or is the "Application.MacroOption" method not fully implemented for Excel 2016 MAC?

The variable "ArgDesc" is declared as "Dim ArgDesc(1 To 6) As String" and each element of the array has the description for each function argument, e.g.:

    ArgDesc(1) = "Section_Angle = angle (in degrees 0-360) of the cross-section line - MANDATORY"

    ArgDesc(2) = "Dip_Angle = angle of dip (in degrees 0-90) - MANDATORY"

    ArgDesc(3) = "Azimuth_Angle = dip direction angle (in degrees 0-360) - OPTIONAL"

    ArgDesc(4) = "Strike_Angle = strike angle (in degrees 0-360) - OPTIONAL"

    ArgDesc(5) = "Azimuth_to_Section_Angle = the angle between the azimuth and the" & _

                    " section line (in degrees 0-360) - OPTIONAL"

    ArgDesc(6) = "Strike_to_Section_Angle = the angle between the strike and the" & _

                    " section line (in degrees 0-360) - OPTIONAL"

It does not appear that the array is causing the problem but the argument "ArgumentDescription" that is described in the Excel VBA reference.

Any comments or suggestions would be greatly appreciated.

 

Question Info


Last updated November 17, 2019 Views 780 Applies to: Have the experts at Excelchat fix your formulas for free. See how.
Answer
Answer

In addition to my original post. I have got the Application.MacroOptions method to work. My original mistake was trying to run a Description Sub for a Function already loaded as an Add-in or in a different Module. If the Function and the Description Sub are in the same module, this method does work. Also there does appear to be some problems with the documentation on this method. Link in my post above. In Excel 2016 16.7 Mac you can only set the Category between 1 and 20 (not 1 to 32 in the MS documentation), if you go above 20 a runtime error occurs. Also if you set the Category to some text type (i.e. "My Functions") no error is raised, however, no new category is generated with the name you gave it and your function will only show up in the "All" list.

Another quirk that occurs with all my UDF, if I use the Formula Builder "Insert Function" button to insert a UDF, Excel will instantly crash. Works fine for the predefined Excel functions. If my UDF are inserted manually in the spreadsheet they work just fine.

MS have been informed with the :( face.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.