Excel UDF not appearing in drop down menu

user2993456 picture user2993456 · Mar 21, 2014 · Viewed 11.8k times · Source

I wrote a User Defined Fucntion in Excel. It works great with no issues. I even wrote a description for it under the object properties menu.

The problem is, my UDF never shows up in the Excel drop down menu that appears when I start to type a function. I want the user to be able to see my UDF, named removeNumbers, when they go into a cell and start to type out a function.

I would also like them to be able to see the description which I wrote, just like the standard Excel functions.

And finally, is there a way that I can provide a description for each argument which my function takes as input?

Here is the actual code, although I don't think it will be necessary to answer my questions.

Function removeNumbers(sInput As String, sChoice As Boolean) As String
    Dim sSpecialChars As String
    Dim i As Long

    If (sChoice = True) Then 'if true is selected, will remove all number including 0
    sSpecialChars = "0123456789" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")

    Next
    End If

    If (sChoice = False) Then 'if false is selected, will remove all numbers excluding zero
    sSpecialChars = "123456789" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")

    Next
    End If

    removeNumbers = sInput
End Function

Answer

Gary's Student picture Gary's Student · Mar 21, 2014

To make the function appear in the drop-down you must place it in a standard module rather than the worksheet code area.