Excel has a lot of built-in functions, but we can make our own functions according to our recruitment or applications. In this article, weâ€™ll introduce how to make VBA function with different types of arguments with simple codes and vivid illustrations.

**Table of Contents**hide

**Make VBA Function with Arguments in Excel: ****4 Examples **

**1. Creating VBA Function with Single Argument**

First, weâ€™ll learn how to create a user-defined function with a single argument. Weâ€™ll make a function to convert Kg to Gram. The function name is **Kgtog**.

**Steps:**

- Insert the following codes in a module-

```
Option Explicit
Sub Kgtog()
Dim k As Double
k = ConvertKgtog(20)
Debug.Print k
End Sub
Function ConvertKgtog(CKG As Double) As Double
Â Â ConvertKgtog = CKG * 1000
End Function
```

- Then go back to your sheet.

**Code Breakdown:**

- First, we created a
**Sub**procedure-**Kgtog**. - Then declared
**k**as**Double**. Weâ€™ll use this**k**to call the function. - In the second section of the code, we defined a function name-
**ConvertKgtog**. And again declared a variable**CKG**as**Double**. - As 1 kg is equal to 1000g, So we multiplied
**CKG**by**1000**.

- Now to convert 5kg to gram, insert the following formula using our function-

`=ConvertKgtog(E4)`

- Then just hit the
**ENTER**button to get the output.

**Read More:** How to Use VBA Input Function in Excel

**2. Making Function with Multiple Arguments in Excel VBA**

Now weâ€™ll make a function that contains multiple arguments. This time, weâ€™ll make a function to calculate the difference between two numbers named **CalculateNum_Difference**.

**Steps:**

- In a new module type the following codes-

```
Option Explicit
Function CalculateNum_Difference(Number1 As Integer, Number2 As Integer) As Double
Â Â CalculateNum_Difference = Number2 - Number1
End Function
Sub Number_Difference()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Num_Diff As Double
Number1 = "5"
Number2 = "10"
Num_Diff = CalculateNum_Difference(Number1, Number2)
Debug.Print Num_Diff
End Sub
```

- Next, go back to your sheet.

**Code Breakdown:**

- First, we defined a function named
**CalculateNum_Difference**using the**Function**statement and declared two variables**Number1**and**Number2**as Integer within it. - Then a
**Sub**procedure was used to call the function-**Number_Difference**. - Later, declared the arguments of the functions
**Number1**and**Number2**as Integer. - Finally, declared
**Num_Diff**as**Double**which is used to arrange the function.

- Insert the following formula in
**Cell E6**using the function-

`=CalculateNum_Difference(E4,E5)`

- Hit the
**ENTER**button to finish.

And see, itâ€™s returning the difference between the two numbers.

**3. Making VBA Function with Optional Argument **

In this section, weâ€™ll make a function with an optional argument. That means, if we skip the argument then it will choose a fixed value for the argument.

**Steps:**

- Write the following codes in a new module-

```
Option Explicit
Function CalculateNum_Difference_Optional(Number1 As Integer, Optional Number2 As Integer) As Double
If Number2 = 0 Then Number2 = 100
Â Â CalculateNum_Difference_Optional = Number2 - Number1
End Function
Sub Number_Difference_Optional()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Num_Diff_Opt As Double
Number1 = "5"
Num_Diff_Opt = CalculateNum_Difference_Optional(Number1)
Debug.Print Num_Diff_Opt
End Sub
```

- After that, go back to a sheet to apply the function.

**Code Breakdown:**

- This code will work like the previous code just for the second argument, we declared the
**Number2**variable with the**Optional**statement. We used the**If**statement to set the fixed value for the second argument- 100 when it is not selected.

- In
**Cell E6**, apply the following formula using our user-defined function-

`=CalculateNum_Difference_Optional(E4)`

- Then hit the
**ENTER**button.

As we didnâ€™t set the second argument, it took the fixed number 100 as its second argument.

**Read More:** How to Use VBA User Defined Function

**4. Creating VBA Function with Default Argument**

We can set a default argument for a function, which means weâ€™ll never select that argument, a default value will be chosen always.

**Steps:**

- Insert the following formula in a new module-

```
Option Explicit
Sub Number_Difference_Default()
Dim NumberX As Integer
NumberX = CalculateNum_Difference_Default(Number1)
MsgBox NumberX
End Sub
Function CalculateNum_Difference_Default(Number1 As Integer, Optional Number2 As Integer = "100") As Double
CalculateNum_Difference_Default = Number2 - Number1
End Function
```

- Now go to your sheet to apply the function.

**Code Breakdown:**

- First, we declared a
**Sub**procedure-**Number_Difference_Default**. - Then declare a variable,
**NumberX**as an Integer which weâ€™ll use to call the function. - In the second section of the codes, we created the function named
**CalculateNum_Difference_Default**and declared two variables within it for the argument.

- To try the function, insert the following formula in
**Cell E6**â€“

`=CalculateNum_Difference_Default(E4)`

- Finally, just press enter to finish.

Have a look, we chose only one argument and the second argument is the default value- 100. So the formula subtracted 5 from 100.

**How to Create a VBA Function Without Arguments**

Now weâ€™ll make a function that will need no argument. That means weâ€™ll just apply the function and it will always return a default value always.

**Steps:**

- In a new module, type the following codes-

```
Function Return_Value() As Integer
Â Â Return_Value = 100
End Function
```

- Soon after, you will get the function available in the worksheets.

- Insert the following formula using our function-

`=Return_Value()`

- Hit the
**ENTER**button and it will return 100 always.

**How to Call a VBA Function from a Sub Procedure**

Now weâ€™ll learn how to call a Function from a Sub procedure. Here, weâ€™ll make a function that whenever we call it will bold the cell range **E4:E45**.

**Steps:**

- Insert the following codes in a new module-

```
Function mReturn_Value() As Range
Â Â Set mReturn_Value = Range("E4:E8")
End Function
Sub Test_Return_Value()
Dim miRg As Range
Set miRg = mReturn_Value
miRg.Font.Bold = True
End Sub
```

- After that, just run the codes.

**Code Breakdown:**

- First, we created a function named,
**mReturn_Value**and declared it as**Range**, which will select the specified range. - Then create a
**Sub**procedure â€“**Test_Return_Value**to call the function. - Declared another variable
**miRg**as**Range**. - Used the
**Set**statement to connect with the function. - Finally,
**Font.Bold**will make bold the cell range.

Now see, the range has got bold.

**How to Use ByVal and ByRef Arguments in a VBA Function**

While using VBA, itâ€™s necessary to understand the use of **ByVal **and **ByRef **Keywords. Before applying them you should have the proper knowledge about them. In this section, weâ€™ll show their application.

**Steps:**

- Insert the following codes in a module-

```
Option Explicit
Sub Using_ByRef()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByRef n As Long)
n = 100
End Sub
```

- Then press
**F8**to see the output line by line.

We called the second **Sub **in the first section by using **ByRef**. **ByRef **always passes the original value so itâ€™s returning 100.

- But have a look at these codes, we used
**ByVal**here and**ByVal**always passes the value only not the reference or original one, then the value can be replaced from the calling procedure or method. So now itâ€™s returning 1 instead of 100.

```
Option Explicit
Sub Using_ByVal()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByVal n As Long)
n = 100
End Sub
```

**Download Practice Workbook**

You can download the free Excel workbook from here and practice independently.

**Conclusion**

Thatâ€™s all for the article. I hope the above procedures will be good enough to make VBA function with arguments in Excel. Feel free to ask any questions in the comment section and give me feedback. Visit our site to explore more.