In this chapter you will learn how to create our own function in VBA. This is called UDF or User Defined Functions.
To create our own function we have to use Function keyword and end with End Function keyword.
Arguments
We can pass arguments to a function which need to be used for calculate the output. For example, If we are creating a function for the Area of a rectangle then we need length and width as input.
Function Rectangle_Area(lenght As Double, width As Double)
Rectangle_Area = lenght * lenght
End Function
Optional Arguments
To pass an optional argument we need put Optional keyword before the argument . You can use multiple optional arguments. You should keep all the optional argument at the end.
Function Rectangle_Area(length As Double, Optional width)
If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If
End Function
Access the Function from Worksheet
We can access the VBA function on the excel sheet on the same way as we use the excel function like-Sum, Average etc.
For example, copy the below formula in the module.
- Go to Visual Basic Editor (Press Alt+F11)
- Insert a Module (Alt+I+M)
- Paste the below code in the module
Function Rectangle_Area(length As Double, Optional width)
If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If
End Function
Not go to excel sheet and type the formula Rectangle_Area
Passing Arguments By Value and By Reference
We can pass an argument in VBA function in two ways:
ByVal – The argument is passed by Value. This means that just the value is passed to the function and therefore, any changes that are made to the argument inside the procedure will be lost when the procedure is exited.
ByRef – The argument is passed by Reference. This means that the address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be remembered when the procedure is exited.
Let’s see the below example for more understanding-
- Go to Visual Basic Editor (Press Alt+F11)
- Insert a Module (Alt+I+M)
- Paste the below code in the module
Function Get_Squire(ByVal i As Integer)
i = i * i
Get_Squire = i
End Function
Sub Check()
Dim x As Integer
x = 5
MsgBox Get_Squire(x)
MsgBox x
End Sub
Now go to the worksheet and run the Check sub procedure by pressing Alt+F8.
You will get below given message
Now put the ByRef in place of ByVal
Function Get_Squire(ByRef i As Integer)
i = i * i
Get_Squire = i
End Function
Now go to the worksheet and run the Check sub procedure by pressing Alt+F8.
You will get below given message
Basically, when we are passing the argument ByVal then orginal value of x is not getting changed but when we are passing the argument ByRef then original value of x is getting changed.
Note: VBA consider by default ByRef .
Application.Volatile
Application.Volatile is used to recalculate the function automatically whenever the worksheet recalculate.
We can use Application.Volatile in the starting of the function as given below.
Function Rectangle_Area(length As Double, Optional width)
Application.Volatile
If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If
End Function