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.

**Table of Contents**hide

## 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.

**Step 1:**

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

**Step 2:**

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

**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
```

**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
and then the number sign would be**0**.**Negative** - Then, the second condition is to see if the number is equal to
and then the function will show**0**as the result.*Zero* - Thirdly, the third condition will show
as a result if the number is greater than**Positive**.**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
, you will see the custom function from the*C5***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
to see the sign of the number in cell*Enter*that is positive.*B5* - Then use the
feature to drag the formula to the lower cells of column*AutoFill*.*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

**workbook. You want to use the**

*VBA Function.xlsm***function in**

*Number_Sign(Number)***workbook (now youâ€™re working with this file). You can use a reference for this procedure. To do this, follow the following steps.**

*Number Check.xlsm***Step 1:**

- First of all, in cell
write the following formula.*C5*

`='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.

**Step 2:**

- Secondly, after pressing
, the worksheet will call the reference of the sheet where the custom function is.*Enter* - 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
after completing the previous two steps in cell*B5*.*C5* - Then, use the
tool to show results for the lower cells of column*Fill Handle*.*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***to establish a reference to another workbook.**

*References*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.

**Step 2:**

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

**Step 3:**

- Thirdly, the
box will appear.*References-VBAProject* - 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
again.*4* - 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 ðŸ™‚