I eventually got the "Application.MacroOptions" method to work. My original problem was I was attempting to run the Sub to set up the description on an already loaded Add-in function. If the Description Sub is attached to the original Module it will work
run from a standard VBA project or from the Add-in. However, and there is always a however, the MS documentation on the Application MacroOptions Method claims that you can have your function inserted into any of 32 Categories, the standard predefined (list
in the documentation is out of date for the current Excel) and a whole pile of custom categories. Also if you set the Category parameter to some text it will create a new category with that name. Unfortunately this is wrong for Excel 16.7 MAC. If you attempt
to go beyond 20 categories you get an error. If you set the Category parameter to some text value, you don't get an error, neither do you get a new Category with that name and your UDF turns up in the All list. You can insert your function into any of the
predefined lists or the user defined function category (Category:=14).
I have informed MS with the :( face.
Also once your function is loaded and you attempt to use the Formula Builder to insert you function you can find it listed in whatever category you inserted it into, when highlighted, the Description is given and the Syntax of the function and the explanation
of the values to be passed to the function. All great. If you then use the Insert Function button, Excel will instantly crash. If you manually type in the function in the spreadsheet it works fine. I have found, that any user defined function, when attempting
to use the Formula Builder Insert Function button causes Excel to crash.
And yes I have sent MS another :( face on this one as well.
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.