VBA Custom Excel Function with dropdown list

Question

I have my custom function in Excel (VBA), but I'm trying to do something like that list within the CELL function:

Try in Excel:

=CELL(info_type [ and then here appears the dropdown list ]

So, I want:

=MyFunction(my_variable [ and my custom dropdown list here ]

The list should appear in Excel, not in the VBA window.

Does anybody know how to do this?

Sorry, I can't post a picture.

possible duplicate of [VBA Function argument list select](http://stackoverflow.com/questions/5340002/vba-function-argument-list-select) — Grade 'Eh' Bacon, Aug 21 '15 at 20:20
Take a look at the link there; seems to have what you're looking for. — Grade 'Eh' Bacon, Aug 21 '15 at 20:20
It is closely to what I'm looking for, but the list should be in Excel, not in VBA window. If you have Excel, open it and type " =CELL( ", then you will see the list. I was reading that post moments ago. — David Segovia, Aug 21 '15 at 20:28
Well it appears that the unfortunate answer is that no, there isn't a method of doing the dropdown in Excel as a UDF. However I've only spent the time needed to google that other answer, so I admit I have no prior knowledge of this. — Grade 'Eh' Bacon, Aug 21 '15 at 20:34

score 1 · Answer 1 · answered Aug 21 '15 at 23:33

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.

VBA Custom Excel Function with dropdown list

1 Answers1

Linked