How to Create VBA Custom Function in Excel (2 Easy Methods)

This tutorial will demonstrate how to create an excel VBA custom function. While working in Excel VBA, we often need to use custom functions. Users can use these functions as regular functions of Excel. When the inbuilt functions of Excel don’t meet our requirements, in those cases, we need to use custom functions of Excel VBA. In this article, we will discuss 2 suitable methods of creating an excel VBA custom function.

Download Practice Workbook

You can download the practice workbook from here.

What Is Excel VBA Custom Function?

A custom function is a custom-made function by users according to their needs. It is a collection of commands coded in VBA and returns the desired output. Sometimes, we need to create manual controls that aren’t built-in functions of excel. In those cases, we need to use the VBA custom function. Otherwise, Excel will show ambiguous name errors or other errors. So, we will try to learn how to create an excel VBA custom function.

Benefits of Using VBA Custom Function in Excel

We use VBA custom functions for various reasons such as.

• Creating a custom function can simplify and shorten our formulas by combining two or more Excel built-in functions. Simple and shorter formulas are easily understandable.
• Another important reason is, we can write functions to perform operations that are impossible to perform in other ways.

2 Easy Methods to Create VBA Custom Function in Excel

There are 2 methods available for creating an excel VBA custom function. If you follow the steps correctly, you should learn how to create an excel VBA custom function on your own. The steps are:

1. Using Insert Function Dialog Box

In this case, we aim to create an excel VBA custom function using the Insert Function Dialog box. This dialog box is used when you want to add new functions that aren’t built-in in excel. The dialog box is easy to use and edit. So, we can learn this method to ease our work. We can fulfill our goal by following the below steps.

Steps:

• First, arrange a dataset like the below image. In this case, we have the Data in column B and the Sign in column C. We’ll use this dataset to describe the whole method.

• Next, go to the Developer tab and then use the Macros option to open the VBA dialog box.

• Now, the dialog box will appear on your screen. In the dialog box, give the file a desired Macro name and press the Create option.

• After that, press the Run option the VBA window will appear on the screen. Insert the desired there and save the code like the below image.

Function NumberSign(Number)
    If IsNumeric(Number) Then
        Select Case Number
            Case Is < 0
                NumberSign = "Negative"
            Case 0
                NumberSign = "Zero"
            Case Is > 0
                NumberSign = "Positive"
        End Select
    Else
        NumberSign = ""
    End If
End Function

• Furthermore, insert the following formula in cell C5.

• In addition, after pressing the Enter button, you will get the result for the cell. Then, use the Fill Handle option to get the desired result.

• Finally, you will get the desired result like the following image.

2. Use of Macro Options Method

We want to create an excel VBA custom function using the Macro Options method. We can use the VBA code to add a new custom function. Choosing this command will display the Insert Function dialog box. Custom functions are listed in the User Defined category. When you select the function from the list, you can then use the Function Arguments dialog box to specify the arguments for the function, as shown in the figure below. You can use the custom functions with other elements in your Excel formula. The steps of this method are.

Steps:

• At first, arrange a dataset like the below image. In this case, we have the Data in column B and the Sign in column C. We’ll use this dataset to describe the whole method.

• Second, press the Alt+F11 options to open the VBA Then go to the Module option in the Insert option to create a new VBA window. After that, press the Run option to use the code.
• Third, insert the following VBA code in the window like the following image.

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

• Forth, insert the following formula in cell C5.

• Fifth, after pressing the Enter button, you will get the result for the cell. Then, use the Fill Handle option to get the desired result.

• Last, you will get the desired result like the following image.

Things to Remember

• The file must be saved as Excel Macro-Enabled Workbook. Otherwise, the VBA code won’t work.
• In the case of creating the new custom function, we suggest using a new name otherwise excel will show an ambiguous error if it finds out the same name as existing or used before.
• In some cases, the already used file won’t give the correct results. So, we suggest using a fresh new workbook for these methods.
• While inserting the formula in the cell, focus on the function name. Otherwise, it won’t work properly.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to create an excel VBA custom function. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.