Application.MacroOptions not appearing under Function help from Formula bar

Question

The function below works, but when I go to the Function bar, Excel reports no help available.  Shouldn't Application.MactoOptions provide that? I have other functions in the same VBAProject that use Application.MacroOptions and they do show help available.

Function DateOf(ByVal occur As Integer, ByVal day As Integer, month As Integer, year As Integer) As Date

'occur is the occurrence in the month, 1 through 5, 5 being the last. If occur is 0, then day is the day of the month

'day is the day of the week, where 1=Sunday through 7=Saturday. If occur is 0, then day is day of the month

'month and year are calendar month and year

Dim ArgDesc(4) As String

ArgDesc(0) = "occur is the occurrence in the calendar month, 1 through 5, 5 being the last. If occur is 0, then day is the day of the month."

ArgDesc(1) = "day is the day of the week, where 1=Sunday through 7=Saturday. If occur is 0, then day is day of the month"

ArgDesc(2) = "the calendar month"

ArgDesc(3) = "the calendar year"

Application.MacroOptions Macro:="DateOf", _

        Description:="Calculates the date of the nth occurence in a calendar month.", _

        Category:="Fiscal Dates", _

        ArgumentDescriptions:=ArgDesc

Dim dayadjust As Integer

If occur < 0 Or occur > 5 Then

    DateOf = CVErr(xlErrValue)

    Exit Function

End If

If occur = 0 Then

    If IsDate(DateSerial(year, month, day)) Then

        DateOf = DateSerial(year, month, day)

        Exit Function

    Else

        DateOf = CVErr(xlErrValue)

        Exit Function

    End If

End If

If day >= DateTime.Weekday(DateSerial(year, month, 1), vbSunday) Then

    dayadjust = 1

    Else

        dayadjust = 0

End If

If DateTime.month(DateSerial(year, month, 1 + ((occur - dayadjust) * 7) + (day - DateTime.Weekday(DateSerial(year, month, 1), vbSunday)))) > month Then

    If IsDate(DateSerial(year, month, 1 + ((occur - 1 - dayadjust) * 7) + (day - DateTime.Weekday(DateSerial(year, month, 1), vbSunday)))) Then

        DateOf = DateSerial(year, month, 1 + ((occur - 1 - dayadjust) * 7) + (day - DateTime.Weekday(DateSerial(year, month, 1), vbSunday)))

        Exit Function

        Else

            DateOf = CVErr(xlErrValue)

            Exit Function

    End If

    Else

       If IsDate(DateSerial(year, month, 1 + ((occur - dayadjust) * 7) + (day - DateTime.Weekday(DateSerial(year, month, 1), vbSunday)))) Then

            DateOf = DateSerial(year, month, 1 + ((occur - dayadjust) * 7) + (day - DateTime.Weekday(DateSerial(year, month, 1), vbSunday)))

            Exit Function

            Else

                DateOf = CVErr(xlErrValue)

                Exit Function

        End If

End If

End Function

Moved from: Office / Excel / Windows 10 / Office 2016

Answer 1

Thank you for your reply. It turned out, the user was running Excel on a Mac and had to apply a patch from Microsoft. They applied the patch and the code began to work.

Answer 2

I am assuming that the Help you are referring to is as per the Screen Shot belowwhen you click the Insert Function Help to the left of the actual formula barand that you are not referring to Help files.

If above is correct then delete the code below from the UDF and run the code in a separate sub at sometime prior to using the UDF.

After running the sub once, it appears that it is no longer required because the info is saved with the workbook. Even if the sub is deleted from the workbook and the workbook is saved, closed, Excel closed and then re-open Excel and the workbook then the help still works.

The info is only available in the workbook in which the code is run. It does not appear to be available in other workbooks containing a copy of the same UDF and that are opened concurrently in the same instance of Excel. (The help code needs to be run separately for each workbook).

Sub FunctionHelp()

    Dim ArgDesc(4) As String

    ArgDesc(0) = "occur is the occurrence in the calendar month, 1 through 5, 5 being the last. If occur is 0, then day is the day of the month."

    ArgDesc(1) = "day is the day of the week, where 1=Sunday through 7=Saturday. If occur is 0, then day is day of the month"

    ArgDesc(2) = "the calendar month"

    ArgDesc(3) = "the calendar year"

    Application.MacroOptions Macro:="DateOf", _

            Description:="Calculates the date of the nth occurence in a calendar month.", _

            Category:="Fiscal Dates", _

            ArgumentDescriptions:=ArgDesc

End Sub