VBA Custom Excel Function with dropdown list

Here is a clunky approach that might work. The idea is to use the Worksheet_Change event to capture instances where you enter an incomplete version of your function at which stage an on-the-fly data validation drop down list is displayed on the cell giving you possible ways to complete the function. Then, again using Worksheet_Change, the completed version is detected, turned into a formula, and the data validation is removed.

As a proof of concept -- I wrote a version of sin which allows the user to select "Degrees" or "Radians":

Function Sine(ParamArray args() As Variant) As Variant
    On Error GoTo err_handler
    If args(1) = "Degrees" Then
        Sine = Sin(args(0) * Application.WorksheetFunction.Pi() / 180)
    Else
        Sine = Sin(args(0))
    End If
    Exit Function
err_handler:
    Sine = "=Sine(" & args(0) & ","
End Function

This throws an uncaught error if the user doesn't at least give an angle.

Then, in Worksheet_Change I used:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim choices As String, angle As String, tval As String

    On Error GoTo err_handler
    tval = Target.Value
    If tval Like "=Sine(*," Then
        angle = Mid(tval, 7)
        angle = Left(angle, Len(angle) - 1) 'get rid of comma
        choices = angle & " Degrees, " & angle & " Radians"
        Target.Validation.Add xlValidateList, Formula1:=choices
        Target.Select
    ElseIf tval Like "* Degrees" Then
        Target.Validation.Delete
        Target.Formula = "=Sine(" & Val(tval) & ", ""Degrees"")"
        Target.Offset(1).Select
    ElseIf tval Like "* Radians" Then
        Target.Validation.Delete
        Target.Formula = "=Sine(" & Val(tval) & ", ""Radians"")"
        Target.Offset(1).Select
    End If
err_handler:
End Sub

It works like this. In A1 (say) type =Sine(45 and hit enter. You will see the following (after clicking on the drop down arrow):

Then, after selecting e.g. "45 Degrees" the formula in A1 becomes

=sine(45, "Degrees")

and the value (0.707107) is displayed, and the data validation has been removed.

A variation of the idea that might be more flexible is to display a userform by the cell rather than rely on this data-validation hack.