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

Sometimes, while working in Excel, users need some functions or formulas that are not pre-existed in Excel. That means users will not find such functions automatically in the formula bar. But with the help of VBA coding, they can apply the correct conditions and syntax to make such functions. These functions are known as custom functions. In this article, I will show you how to execute VBA function procedure in Excel.


Download Working File

If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically. To activate the Insert Function dialog box choose Formulas ➪ Function Library ➪ Insert Function Wizard.


2 Easy Ways to Execute VBA Function Procedure in Excel

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

We know how to create a macro Sub procedure. Macro Sub procedure can be executed in many ways. But, the Function procedure can be executed only in two ways:

  • Function procedure can be called from another VBA procedure.
  • You can use a worksheet formula like predefined Excel functions.

In this article, you will see two different ways to execute VBA function procedure in Excel. In the first method, I will create a custom function through VBA coding and call it in the worksheet for working purposes. In the second approach, we will use such custom functions in another workbook to do the same.

To illustrate the article, we will use the following sample data set.

2 Easy Ways to Execute VBA Function Procedure in Excel


1. Calling Custom Functions from Procedure to Execute VBA Function

Custom functions can be called from another VBA procedure (VBA functions or VBA Subprocedures, whatever it is). Say you have designed a function called Number_Sign(Number). The function takes a number from the worksheet and checks if the number is positive or negative. The steps for performing this procedure are as follows.

Step 1:

  • First of all, go to the Developer tab of the Ribbon in your current worksheet.

Calling Custom Functions from Procedure to Execute VBA Function Procedure in Excel

Step 2:

  • Secondly, you will see the VBA window.
  • From there, choose Module from the Insert tab.

Step 3:

  • Thirdly, copy the following code and paste it into the module.
  • Here, through the code, I 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

Applying VBA Code for Calling Custom Functions from Procedure to Execute VBA Function Procedure in Excel

VBA Breakdown

  • First of all, we assign the function name.
Function Number_Sign(Number)
  • Secondly, 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 then the number sign would be Negative.
  • Then, the second condition is to see if the number is equal to 0 and then the function will show Zero as the result.
  • Thirdly, the third condition will show Positive as a result if the number is greater than 0.
  • Lastly, if the assigned cell dose does not contain a number then 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:

  • Fourthly, save the code and go back to your current worksheet.
  • Then, in cell C5, you will see the custom function from the VBA code will appear after typing its name.
  • In the cell, write the following formula of the custom function to check the sign of the numbers in column B.
=Number_Sign(B5)

Step 5:

  • Fifthly, press Enter to see the sign of the number in cell B5 that is positive.
  • Then use the AutoFill feature to drag the formula to the lower cells of column C.

2. Using Custom Functions in Worksheet Formula

We use a custom function in a worksheet formula just like we use predefined Excel functions. However, you have to make sure that Excel can locate your custom function.

There are two cases: your function procedure can be in the same workbook or in a different workbook. If the function procedure is in the same workbook, you don’t need to do anything to locate the function for Excel. If the function is defined in a different workbook, you may have to instruct Excel where to find the function.

Read More: A detailed analysis of Excel VBA Function Procedure Arguments

You can do this in the following three ways:

2.1 Precede Function Name with File Reference

For example, say you want to use the function Number_Sign(Number) function and we define it in VBA Function.xlsm workbook. You want to use the Number_Sign(Number) function in Number Check.xlsm workbook (now you’re working with this file). You can use a reference for this procedure. To do this, follow the following steps.

Step 1:

  • First of all, in cell C5 write the following formula.
='VBA Functions.xlsm'!Number_Sign(B5)
  • Here, you have to keep the VBA Functions.xlsm workbook open while writing the formula otherwise you will not get the result.

Precede Function Name with File Reference to Execute VBA Function Procedure in Excel

Step 2:

  • Secondly, after pressing Enter, the worksheet will call the reference of the sheet where the custom function is.
  • Here, browse to the current location of the custom function’s worksheet, select it, and lastly press OK.

Step 3:

  • Thirdly, you will see the result for cell B5 after completing the previous two steps in cell C5.
  • Then, use the Fill Handle tool to show results for the lower cells of column C.

2.2 Set Up Reference to Workbook

You can create references to the workbook where the custom function is defined. In this process, you don’t have to precede the custom function name with the workbook name. In the VBA Editor, choose ToolsReferences to establish a reference to another workbook.

See the following figure. 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. The detailed steps for this procedure are as follows.

Step 1:

  • First of all, you will see the error in the result column after closing the reference worksheet.

Set Up Reference to Workbook to Execute VBA Function Procedure in Excel

Step 2:

  • Secondly, to fix this, go to the VBA window like the first method.
  • Then, choose References from Tools.

Step 3:

  • Thirdly, the References-VBAProject box will appear.
  • Here, to select the correct reference for the worksheet, select Browse.

Step 4:

  • Then, select the worksheet that contains the custom function and add it as a reference.

Step 5:

  • Fifthly, you will see the box from step 4 again.
  • Then, ensure the location and name of the reference file are correct, and then press OK.

Step 6:

  • Finally, you will find the solution of the problem regarding 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 just need to check that the  if you find the installed add-in in your workbook.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to execute VBA function procedures in Excel by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

Happy Excelling 🙂

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo