2

When I add a public function to a standard code module in Excel it shows up in the Insert Function wizard in the catch-all category "User Defined."

Is there any way to:

  1. Keep the function public (so it may be called from other modules) but hide it from the Excel function wizard?
  2. Make the function appear in a built-in category (e.g., "Financial", "Statistical", "Logical")?
  3. Create my own category (e.g., "Nonsensical") and have my function appear there?

I only need to be able to make one of the above cases work, but I'd be curious to know if each one is possible.

Note: I will gladly split this into three separate questions if the community feels that is more appropriate. I am thinking there may be a single answer with only slight variation among the three questions.

Community
  • 1
  • 1
mwolfe02
  • 23,787
  • 9
  • 91
  • 161

4 Answers4

4

Is there any way to [...] Keep the function public (so it may be called from other modules) but hide it from the Excel function wizard?

Put Option Private Module at the top of your module. Done.

http://msdn.microsoft.com/en-us/library/aa266185(v=vs.60).aspx

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
2

Application.MacroOptions is the command you are after

This example adds a user-defined macro called TestMacro to a custom category named My Custom Category. 

Function TestMacro()
    MsgBox ActiveWorkbook.Name
End Function

Sub AddUDFToCustomCategory()
    Application.MacroOptions Macro:="TestMacro", Category:="My Custom Category"
End Sub
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • That doesn't work as you've written it. Category is looking for an integer. From the help file: ***Category*** Optional **Variant**. An integer that specifies the macro function category (Financial, Date & Time, or User Defined, for example). – mwolfe02 Jun 21 '12 at 19:50
  • Category can also take a string, and if it doesn't match a built in definition, it will create a new category – SeanC Jun 21 '12 at 20:00
  • I must be doing something wrong then, because I get a "Runtime error '1004': Method 'MacroOptions' of object '_Application' failed" when I try to run it with a string. If I pass an integer (e.g., 1) it runs successfully. – mwolfe02 Jun 21 '12 at 20:19
  • perhaps it's version dependent. I'm using Excel 2007 – SeanC Jun 21 '12 at 20:29
  • That makes sense. I'm testing on Excel 2002. – mwolfe02 Jun 21 '12 at 21:03
  • 1
    @mwolfe02: For integer values see this link http://msdn.microsoft.com/en-us/library/bb209988%28v=office.12%29.aspx – Siddharth Rout Jun 21 '12 at 21:18
2

I answered this in a foonote to a question about VBA Date functions: here's a straight copy-and-paste of what I wrote there, on using VB 'Attribute' statements to create function descriptions and a Function Wizard category ID:

A Tip for people still using Excel 2003:

If you (or your users) are going to call IsDateEx() from a worksheet, put these two lines in, immediately below the function header, using a text editor in an exported .bas file and reimporting the file, because VB Attributes are useful, but they are not accessible to the code editor in Excel's VBA IDE:

Attribute IsDateEx.VB_Description = "Returns TRUE if TestDate is a date, and is within ± 20 years of the system date.\r\nChange the defaulte default ± 20 years boundaries by setting values for LimitPastDays and LimitFutureDays\r\nIf you are checking an array of dates, ALL the values will be tested: set FirstColumnOnly TRUE to check the leftmost column only."

That's all one line: watch out for line-breaks inserted by the browser! ...And this line, which puts isDateEX into the function Wizard in the 'Information' category, alongside ISNUMBER(), ISERR(), ISTEXT() and so on:

Attribute IsDateEx.VB_ProcData.VB_Invoke_Func = "w\n9"

Use "w\n2" if you prefer to see it under the Date & Time functions: beats hell outta losing it in the morass of 'Used Defined' functions from your own code, and all those third-party add-ins developed by people who don't do quite enough to help occasional users.

I have no idea whether this still works in Office 2010.

...And that's all I can suggest. Function Wizard categories and the associated function (and parameter!) descriptors aren't really accessible to VBA developers. Some of this is available to old-school VB coders, who have access in their IDE to VB.Attribute statements; and you can sort-of slip it into your code with a bit of text file manipulation.

Community
  • 1
  • 1
Nigel Heffernan
  • 4,636
  • 37
  • 41
  • +1 This is excellent info for Excel pre-2007. I marked the other answer as accepted because it should work for more people going forward. I appreciate you taking the time to post this, though, because it's difficult information to come by. – mwolfe02 Sep 03 '12 at 18:47
2

Public Functions are only visible to Excel's formula bar is they reside in a "regular" *.bas Module. Public Functions belonging to classes are not visible to the formula bar. If you add this together with the fact that we can simulate a static class, you can effectively hide functions.

If you create a class module and then export it, you will find Attribute VB_PredeclaredId = False in the header. Setting this to true creates a default global instance of the class that can be used much like a static class in a more modern language.

For example, create a new class module named Math and add this function to it.

Public Function Add(a As Integer, b As Integer) As Long
    Add = a + b
End Function

Export and remove the file, then open it in notepad. Change the predeclared Id to true.

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Math"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Save and Import the file back into your project.

Now, in a regular module, you can call the function with a qualified call. Just like any other class method. Only now, we have a default instance of the class to work with. There's no mucking around with New or Set, it just works.

Sub Test()
    Debug.Print Math.Add(1,2)
End Test
RubberDuck
  • 11,933
  • 4
  • 50
  • 95