Add Description To A Custom VBA Function

Problem description

When we give to our colleagues/customers/partners/friends etc. workbooks that contain custom VBA functions it is always useful to include some description about what the functions do, or what their input parameters are. Of course we could either write some comments in the beginning of the VBA code, or write a description anywhere in the workbook (i.e. just before the functions are used).

Although these methods might be convenient for normal or advanced users, new Excel users – in practice – might have some troubles. For example, a new Excel user might not know how to switch to the VBA editor (ALT + F11) in order to see the function description. So, what will happen if the developer has forgotten to add the function description in the workbook? The answer is that the new user will probably NOT use the VBA function, unless he/she finds some information from the developer.

Solution

In order to avoid this unpleasant situation a VBA developer can use the “MacroOptions” method so as to add a description to the custom VBA function, as well as to its arguments. According to Microsoft, “MacroOptions” method (in Excel 2003 VBA Language Reference) corresponds to options in the Macro Options dialog box. You can also use this method to display a user defined function (UDF) in a built-in or new category within the Insert Function dialog box.

In the “FunctionDescription” macro that you will find below I used the “MacroOptions” method in order to automate this procedure. However, you should be aware that you can add description to function’s argument ONLY if you are running the macro in Excel 2010 (or 2013). The arguments’ description is NOT supported in Excel 2007. If you run the “FunctionDescription” macro at your workbook, then your custom function will act like a built-in function, so even a new Excel user will be able to use it.

How to use it

When you use the “FunctionDescription” macro to add description to your own custom VBA functions you should change the following lines of code (red color), according to your needs:

If the macro runs successfully, then your custom function will no longer appear to the UDF category of functions, but into the desired built-in category (see the figure above). Below are the built-in function categories of Excel, in which you can categorize your own function:

Of course you can also define your own custom category (see the VBA code below).

Here is the VBA code for “FunctionDescription” macro. In this example the macro adds description to the “FrictionFactor” function. You can find more about the “FrictionFactor” function here.

Option Explicit

Sub FunctionDescription()

    '------------------------------------------------------------------------
    'This sub can add a description to a selected user-defined function,
    '(UDF) as well as to its parameters, by using the MacroOptions method.
    'After running successfully the macro the UDF function no longer appears
    'to the UDF category of functions, but into the desired category.
    
    'By Christos Samaras
    'Date: 23/07/2013
    'xristos.sa[email protected]
    'https://myengineeringworld.net/////
    '------------------------------------------------------------------------
    
    'Delclaring the necessary variables
    Dim FuncName As String
    Dim FuncDesc As String
    Dim FuncCat As Variant
    
    'Depending on the function arguments define the necessary variables on the arry.
    'Here UDF funciton has four arguments, so four variables are declared.
    Dim ArgDesc(1 To 4) As String
    
    '"FrictionFactor" is the name of the function.
    FuncName = "FrictionFactor"
    
    'Here we add the function's description.
    FuncDesc = "Calculates the friction factor of a pipe using Churchill's equation."
    
    'Choose the built-in function category (it will no longer appear in UDF category).
    'For example, 15 is the engineering category, 4 is the statistical category etc.
    'See the code at the end for all available categories.
    FuncCat = 15
    
    'You can also use instead of numbers the full category name, for example:
    'FuncCat = "Engineering"
    'Or you can define your own custom category:
    'FuncCat = "My VBA Functions"
    
    'Here we add the description for the function's arguments.
    ArgDesc(1) = "Pipe Roughness in m"
    ArgDesc(2) = "Pipe Diameter in m"
    ArgDesc(3) = "Fluid Velocity in m/s"
    ArgDesc(4) = "Fluid Viscosity in m2/s"

    'Using the MacroOptions method add the function description (and its arguments).
    Application.MacroOptions _
        Macro:=FuncName, _
        Description:=FuncDesc, _
        Category:=FuncCat, _
        ArgumentDescriptions:=ArgDesc
    
    'Available built-in categories in Excel.
    'This select case is somehow irrelevelant, but it was added for
    'demonstration purposues.
    Select Case FuncCat
        Case 1: FuncCat = "Financial"
        Case 2: FuncCat = "Date & Time"
        Case 3: FuncCat = "Math & Trig"
        Case 4: FuncCat = "Statistical"
        Case 5: FuncCat = "Lookup & Reference"
        Case 6: FuncCat = "Database"
        Case 7: FuncCat = "Text"
        Case 8: FuncCat = "Logical"
        Case 9: FuncCat = "Information"
        Case 10: FuncCat = "Commands"
        Case 11: FuncCat = "Customizing"
        Case 12: FuncCat = "Macro Control"
        Case 13: FuncCat = "DDE/External"
        Case 14: FuncCat = "User Defined default"
        Case 15: FuncCat = "Engineering"
        Case Else: FuncCat = FuncCat
    End Select

    'Inform the user about the process.
    MsgBox FuncName & " was successfully added to the " & FuncCat & " category!", vbInformation, "Done"
    
End Sub

The zip file contains a sample Excel workbook with the macro described above, as well as a VBA module, which you can import to an existing workbook. Remember that the macro works on Excel 2010 or newer.