# How to Create a VBA Function with Arguments in Excel – 4 Examples

### Example 1 – Creating a VBA Function with a Single Argument

Create a function to convert Kg to Grams:

Steps:

• Enter the following code in the 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``````
• Go back to the worksheet.

Code Breakdown:

• The Sub procedure- Kgtog is created.
• k is declared as Double. It will be used to call the function.
• The function name is defined: ConvertKgtog. The CKG variable is declared as Double.
• CKG is multiplied by 1000.

• To convert 5kg to grams, use the following formula:
`=ConvertKgtog(E4)`
• Press ENTER to see the output.

Read More: How to Use VBA Input Function in Excel

### Example 2 – Creating a Function with Multiple Arguments in Excel VBA

Create a function to calculate the difference between two numbers:

Steps:

• Enter the following code in the module:
``````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``````
• Go back to the worksheet.

Code Breakdown:

• A function CalculateNum_Difference is defined using the Function statement and  two variables Number1 and Number2 are declared as Integerd.
• A Sub procedure is used to call the function- Number_Difference.
• The arguments of the functions Number1 and Number2 are declared as Integers.
• Num_Diff is declared as Double.

• Use the following formula in E6:
`=CalculateNum_Difference(E4,E5)`
• Press ENTER.

This is the output.

### Example 3 – Creating a VBA Function with an Optional Argument

Steps:

• Enter the following code in the 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``````
• Go back to the worksheet.

Code Breakdown:

• Number2 is declared as variable with the Optional statement. The If statement sets the fixed value for the second argument: 100 if it is not selected.

• In E6, enter the following formula:
`=CalculateNum_Difference_Optional(E4)`
• Press ENTER.

As no second argument was set, 100 was used.

Read More: How to Use VBA User Defined Function

### Example 4 – Creating a VBA Function with a Default Argument

Steps:

• Enter the following code in the 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``````
• Go back to the worksheet.

Code Breakdown:

• The Sub procedure Number_Difference_Default is declared.
• The NumberX variable is declared as Integer and is used to call the function.
• The function CalculateNum_Difference_Default is created and  two variables are declared as argument.

• Enter the following formula in E6:
`=CalculateNum_Difference_Default(E4)`
• Press ENTER.

The second argument is the default value: 100.

## How to Create a VBA Function Without Arguments

Steps:

• Enter the following code in the module:
``````Function Return_Value() As Integer
Return_Value = 100
End Function

``````
• Go back to the worksheet.

• Enter the following formula:
`=Return_Value()`
• Press ENTER.

100 will always be the output.

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

Create a function to bold E4:E45.

Steps:

• Enter the following code in the 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``````
• Run the code.

Code Breakdown:

• The  mReturn_Value function is created and declared as Range.
• The Test_Return_Value Sub procedure calls the function.
• The miRg variable is declared as Range.
• The Set statement connects with the function.
• Font.Bold bolds the cell range.

This is the output.

## How to Use the ByVal and ByRef Arguments in a VBA Function

Steps:

• Enter the following code in the 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``````
• Press F8 to see the output.

The second Sub in the first section is created by using ByRef. It always passes the original value: 100.

• In the code below ByVal was used. It passes the value, not the reference. 1 is returned, 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``````

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF