How to insert an Excel VBA custom function

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

We can use Excel’s Insert Function dialog box to locate a custom function and insert it in a formula. I have discussed it in this article: How to Create Custom VBA functions and Use them in the Worksheet.

Note: If you define your function procedure with a Private keyword, then you’ll not find it in the Insert Function dialog box. If you create a function only to use in other procedure, not for use as worksheet functions, then it is a good idea to define this function with Private keyword.

Insert an Excel VBA function: Using Insert Function dialog box

You also can add a description of your custom function in the Insert Function dialog box in the following process:

  • First of all, you have to create a function in a module by using the VB Editor.
  • Now activate the Excel workbook that holds your function.
  • Choose Developer ➪ Code ➪ Macros. The Macro dialog box appears.
  • Type the name of the function in the Macro Name field. Notice that the functions are typically not displayed in this dialog box. So you have to enter the function name to find out the function. If you wrongly type the function name or Excel doesn’t find the function, except Create, no other options will work of the dialog box. I am searching for “NumberSign” function, see what happens when I type “NumberSig” in the following image.
How to insert an Excel VBA custom function

Incomplete or wrongly typed function name will not active all the options of the Macro dialog box.

  • After Excel gets the function, it shows all the controls actionable, except Create control. Click the Options button. The Macro Options dialog box appears. See below.
How to insert an Excel VBA custom function

Enter an overview of the function in the description field of Macro Options dialog box.

  • Enter a description of the function and then click OK. You can enter a shortcut key for a function, but it is irrelevant as you can’t use it.
  • The description that you enter is displayed in the Insert Function dialog box.
How to insert an Excel VBA custom function

Insert Function dialog box shows the short description of a function, you select.

Insert an Excel VBA function: Using MacroOptions method

There is another way how you can add description of a custom function. Excel provides a method “MacroOptions” that you can use to do so.

Read More: How to Create Custom VBA functions and Use them in the Worksheet

In our above example, I’ve used Insert Function dialog box to add a function description. I shall use now MacroOptions method to do the same thing, additionally I’ll also add description of the arguments what is not possible using Insert Function dialog box.

My function is NumberSign and it has one argument Number. My function description is: “Check the sign of a number” and Argument description is: “This value will be checked”. I am planning to add this function under “Math & Trig” category (numerically this category is represented as 3). My Subprocedure will like the following one:

Sub CreateArgDescriptions()
    Application.MacroOptions Macro:="NumberSign", Description:="Check the sign of a number.", Category:=3, ArgumentDescriptions:=Array("This value will be checked")
End Sub

You have to run this Subprocedure(CreateArgDescriptions) for at least one time to show the description of the function and argument. After running this procedure, find this function under Math & Trig category in the Insert Function dialog box and click on this to appear Function arguments dialog box.

How to insert an Excel VBA custom function

Use the Function Arguments dialog box to insert argument to a custom function.

Download Working File

vba-functions.xlsm

Happy Excelling ☕


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply