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
Download Practice Workbook
Download the free Excel workbook.
Related Articles
- Excel VBA Function to Return Multiple Values
- How to Execute VBA Function Procedure in Excel
- How to Return a Value in VBA Function
- VBA Sub Vs Function in Excel
- How to Create and Use ColorFunction in Excel
- Difference Between Subroutine and Function in Excel VBA
- How to Create Custom Function in Excel VBA