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

**Download Practice Workbook**

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

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

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

Firstly, 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**. And 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 1Kg 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.

**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 used a
**Sub**procedure 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**. And 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.

**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 declared a variable,
**NumberX**as 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â€™ll call it then 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 created 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 quite 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
```

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