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.
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.
- First of all, go to the Developer tab of the Ribbon in your current worksheet.
- Secondly, you will see the VBA window.
- From there, choose Module from the Insert tab.
- 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
- First of all, we assign the function name.
- 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
- 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.
- 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.
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.
- First of all, in cell C5 write the following formula.
- Here, you have to keep the VBA Functions.xlsm workbook open while writing the formula otherwise you will not get the result.
- 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.
- 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 Tools ➪ References 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.
- First of all, you will see the error in the result column after closing the reference worksheet.
- Secondly, to fix this, go to the VBA window like the first method.
- Then, choose References from Tools.
- Thirdly, the References-VBAProject box will appear.
- Here, to select the correct reference for the worksheet, select Browse.
- Then, select the worksheet that contains the custom function and add it as a reference.
- 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.
- 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.
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 🙂