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.
Table of Contents
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.
- 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.
- 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.
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.
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.
Download Working File
Happy Excelling ☕