16.7 VBA unable to add description to UDF

Split from this thread.

Jim, even with version 16.7 I get the same results.

 

Question Info


Last updated May 22, 2018 Views 60 Applies to:
Answer
Answer

Jim,

Had a response from MS. They have been able to duplicate my report about UDF and have logged it as a bug.


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.

Answer
Answer

Hi Jim,

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.

How satisfied are you with this response?

Thanks for your feedback.