Excel UDF not appearing in drop down menu

Question

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

excel 2003 does not show the formula when we type whereas the higher version show them. Also correctly said by @Gary the function should reside in standard module. — Santosh, Mar 21 '14 at 14:31
I guess I just have to add the UDF's to the standard module every time I want them to show up in the drop-down list. This is unfortunate but I will make it work. Thanks all. — , Mar 25 '14 at 14:39

score 5 · Accepted Answer · answered Mar 21 '14 at 14:28

5

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

answered Mar 21 '14 at 14:28

Gary's Student

95,722
10
59
99

Could you elaborate please? I honestly don't know what you mena by standard module. I have my UDF's all saved in an xlam file located at `C:\Users\UserName\AppData\Roaming\Microsoft\AddIns`, and they are available within every workbook. I thought that this was the proper method of saving UDF's so that they can be called within any spreadsheet? – Mar 21 '14 at 14:41
A standard module is what you get if you select Insert-> Module from the VBE menu. The resulting module will sit under the 'modules' folder. We suspect that your code is in modules like 'ThisWorkbook' or 'Sheet1' – Chris Spicer Mar 21 '14 at 14:52
1

My code is in a module I created and named `UDFs`. (I like to keep it simple) This module is under a project named `Data_Team_UDFs`. I dragged my `"UDFs"` mudule from my Data_Team_UDFs project, and placed it in the standard module. Now my functions appear in the drop down menu. But do I have to go into the VBA editor and do this every time? I am building these functions for my team members and would like them to just appear on their own if possible. Moving a module between projects every time I open a new sheet is a hassle. – Mar 21 '14 at 15:01

score 0 · Answer 2 · edited May 23 '17 at 11:55

Another poster has already covered the need for the code to be in a standard module. With regards the argument descriptions, you should look at the MacroOptions code in this answer - although it only works in Excel 2010 or later.

For Excel 2007 and earlier, the only solution I have seen is in an article by JK Pieterse. This involves using the ExecuteExcel4Macro and looks a bit complicated.

Excel UDF not appearing in drop down menu

2 Answers2

Linked