How to Execute VBA Function Procedure in Excel (2 Easy Ways)

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

Method 1 – Calling Custom Functions from Procedure to Execute VBA Function

Step 1:

Go to the Developer tab of the Ribbon.

Step 2:

From the VBA window, choose Module from the Insert tab.

Step 3:

Enter the following code.
Through the code, we will set the custom function named Number_Sign(Number).

'Assigning the function name
Function Number_Sign(Number)
'Setting up condtion for different types of number
If IsNumeric(Number) Then
Select Case Number
Case Is < 0
Number_Sign = "Negative"
Case 0
Number_Sign = "Zero"
Case Is > 0
Number_Sign = "Positive"
End Select
Else
Number_Sign = "Not a Number"
End If
End Function

VBA Breakdown

We assign the function name.

Function Number_Sign(Number)

We set four conditions to show the sign of a number.
The first condition is to see if the number is less than 0 and the number sign would be Negative.
The second condition is to see if the number is equal to 0 and the function will show Zero as the result.
The third condition will show Positive as a result if the number is greater than 0.
If the assigned cell dose does not contain a number, the answer will be Not a Number.

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

Step 4:

Save the code and go back to your current worksheet.
In cell C5, you will see that the custom function from the VBAcode will appear after entering its name.
In the cell, enter the following formula of the custom function to check the sign of the numbers in column B.

=Number_Sign(B5)

Step 5:

Press Enter to see the sign of the number in cell B5 that is positive.
Use the AutoFill feature to drag the formula to the lower cells of column C.

Method 2 – Using Custom Functions in Worksheet Formula

2.1 Precede Function Name with File Reference

Step 1:

In cell C5, enter the following formula.

='VBA Functions.xlsm'!Number_Sign(B5)

You have to keep the VBA Functions.xlsm workbook open while entering the formula.

Step 2:

Press Enter and the worksheet will call the reference of the sheet where the custom function is.
Browse to the current location of the custom function’s worksheet, select it and press OK.

Step 3:

You will see the result for cell B5.
Use the Fill Handle tool to fill in the rest of the cells.

2.2 Set Up Reference to Workbook

In the following sample dataset, a list of references is presented, including all open workbooks. Place a checkmark in the item that refers to the workbook that contains the custom function. If the workbook isn’t open, click Browse to choose the workbook.

Step 1:

You will see the error in the result column after closing the reference worksheet.

Step 2:

To fix this, go to the VBA window.
Choose References from Tools.

Step 3:

The References-VBAProject box will open.
Select the correct reference for the worksheet, select Browse.

Step 4:

Select the worksheet that contains the custom function and add it as a reference.

Step 5:

You will see the box from step 4.
Check if the location and name of the reference file are correct, and press OK.

Step 6:

You will find the solution to the problem referencing the workbook.

2.3 Create Add-in

You can create an add-in from a workbook that contains function procedures. In this case, you don’t need to use the file reference when you use one of the functions of the workbook. You need to find the installed add-in in your workbook.

Download Working File

Number Check.xlsm

VBA Functions.xlsm