# How to Make VBA Function with Arguments in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed

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 a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF