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
