Application.MacroOptions - Does it currently work in Excel for Mac?

Question

Is it the case that Application.MacroOptions is no longer supported in Excel for Mac (version 16.26, 2019)?  As described by Microsoft (1) it should provide descriptions to to display a user-defined function (UDF) in a built-in or new category within the Insert Function dialog box, a description and status bar text.

This seems to be be an old chestnut, as discussed in various online postings, e.g. most usefully in (2) and (3) below.

I have tried doing this until blue in the face, using the simplest imaginable UDF that adds two numbers together, below. Application.MacroOptions doesn’t seem to work at all under any circumstances in my version of Excel for Mac. I have tried running the code (below) when Excel starts by calling a sub from the Auto_Open() in my PersonalMacroWorkbook. The code fails, error 2004. I’ve tried running it by calling the same code after excel opens, either in the PersonalMacroWorkbook, or in a .xlsm workbook other than the one containing the VBA code for the UDF to be described; in both cases, again the code fails with the same error.

Finally, I’ve tried running the Application.MacroOptions code in the workbook containing the UDF to be described, being sure to do so before first use of my UDF. Here, the code runs with no error - I can step though it to confirm that,  BUT, it has absolutely no effect. If the Formula Builder is already open when I type the name of the UDF in a cell, I do see the name of my UDF and its result in the Formula Builder. But that happens anyway, regardless of whether or not code containing Application.MacroOptions for the UDF has been run. And absolutely nothing specified in Application.MacroOptions appears in the Formula Builder or elsewhere; my UDF doesn’t appear under any category regardless what numerical or string value is given for the  Category argument, and nothing appears in the status bar or in the Formula Builder under Fx or Syntax where info is shown for the built-in Excel functions.

So should I conclude that Application.MacroOptions is no longer supported in Excel for Mac? Or have I missed something and more undocumented steps are required to make it work? It would be mighty helpful for Microsoft to clarify this so that Excel for Mac users need not to waste hours trying to get this to work.

Any insights into this would be greatly appreciated!

(1) https://docs.microsoft.com/en-us/office/vba/api/excel.application.macrooptions

(2) https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/11597082-full-implementation-of-the-application-macrooption

(3) https://answers.microsoft.com/en-us/msoffice/forum/all/167-vba-unable-to-add-description-to-udf/5255f924-84e7-4c0d-bc83-fb4944242988

___________________________

Sub DescribeUDF()

Dim ArgDesc(1 To 2) As String

    ArgDesc(1) = "Val1: first number to be multiplied"

    ArgDesc(2) = "Val2: second number to be multiplied [optional]; if missing, Val1 is also used for Val2"

   '  This to confirm the code has run and from where.

    MsgBox "Running DescribeUDF() in " & ActiveWorkbook.Name

    Application.MacroOptions _

        Macro:="TestAdd", _

        Category:=14, _

        Description:="Add two numbers", _

        ArgumentDescriptions:=ArgDesc, _

        StatusBar:="TestAdd(Val1 As Variant, Optional Val2 As Variant) As Variant"

End Sub

___________________________

Public Function TestAdd(Val1 As Variant, Optional Val2 As Variant) As Variant

    If IsMissing(Val2) Then

        Val2 = Val1

    End If

    TestAdd = Val1 + Val2

End Function

Answer 1

Thanks, that look exactly like what I'm trying to achieve.  But sadly doesn't seem to work on the Mac, unless someone can show me otherwise…

Answer 2

Mark,

Re:  MacroOptions

Following worked on Windows/XL2010; may/may not work on a Mac.

It has been awhile and was very frustrating to work on. 

'---

Sub DescribeUDF()

Dim ArgDesc As Variant

ArgDesc = Array("first number to be multiplied", _

                "second number to be multiplied, if omitted, first number is used")

Application.MacroOptions macro:="TestAdd", Category:=14, Description:="Add two numbers"

Application.MacroOptions macro:="TestAdd", ArgumentDescriptions:=ArgDesc

End Sub

Public Function TestAdd(Val1 As Variant, Optional Val2 As Variant) As Variant

 If IsMissing(Val2) Then

    Val2 = Val1

 End If

 TestAdd = Val1 + Val2

End Function

'---

'---

Custom_Functions add-in (19 new functions)

-Not for Macs-

Download from MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents