Optional Arguments in VBA – Usage with Examples

Today, we will learn to use VBA to use the Optional Arguments in a Function or a Sub Procedure. Arguments are used to supply the necessary information to the called Procedure.

So let us start learning.

What are Optional Arguments in VBA?

Arguments are used to supply important information to the Procedure when it is called. These arguments can be Variables or Objects. As the name suggests, optional arguments are not mandatory to be supplied to the Function or Sub Procedure. If the user uses the function, it is his choice whether to supply the value of optional parameters. If the user does not supply the optional argument’s value, then its default value is used.

For example, the VLOOKUP Function in Excel has three mandatory arguments and one optional argument range_lookup having its default value as TRUE.

We will learn to write User Defined Functions or Sub Procedures having optional arguments along with their default values.

You can download the following practice workbook.

Syntax – Optional Arguments

The optional arguments are always defined in the parameters list in the Procedure declaration to the right of mandatory arguments.

Function <function_name> (arg1 As <Data_Type>, Optional arg2 As <Data_Type> = <Default_Value>) As <Return_Type> 
'statements
End Function

Here arg2 is made optional. We use the optional keyword to define an optional argument. We also specify the default value of the optional argument using the Assignment operator.

The default value is used by the Procedure when the user does not supply the value for the Optional Argument.

We can also use optional arguments to define a Sub Procedure.

Sub <Procedure_Name) (arg1 As <Data_Type>, Optional arg2 As <Data_Type> = <Default_Value2>, Optional arg3 As <Data_Type> = <Default_Value3>,.............)
'statements
End Sub

The Data Type of an Optional Argument cannot be a User Defined Type (UDT).

Passing Value of Optional Arguments

When we define a Procedure having optional arguments, they are defined after the mandatory arguments in the parameter list.

It is not mandatory to specify the value of the Optional Argument. There are two ways to pass optional arguments.

• Pass parameters in a fixed order as defined in Function arguments.
• Using Named Arguments to pass the parameters in any order.

We will learn to use the optional argument mechanism with the help of examples.

Examples to Learn Usage of Optional Arguments in VBA

In this section of the blog, we will learn to implement the usage of optional arguments with the help of taking simple examples.

Example 1 – Sub Procedure Multiples of a Number

In this example, we will define a Sub Procedure that prints the Multiples of a Number. The Sub Procedure contains the following parameters.

• number – This is the number whose multiples we wish to print.
• upto (optional)- This is the total number of multiples we wish to print. This is going to be an optional argument and its default value will be 10.
• column (optional) – This is the column number in which we wish to print the result in Excel worksheet. By default, its value is 1 which implies that if no column is mentioned then the multiples are printed in cells of column A.

First, we will define the Sub Procedure in a new module as follows.

Sub printMultiples(number As Integer, Optional upto As Integer = 10, Optional column As Integer = 1)
'sub procedure to print multiples of a number
Dim i As Integer
For i = 1 To upto
    
    Cells(i, column).Value = number * i
    Cells(i, column).Interior.Color = RGB(255, 182, 193)

Next i
End Sub

Now we will call this Sub procedure printMultiples from another Sub procedure named as callingProcedure.

When you write the name of this Sub procedure and hit the spacebar key, you will see the parameters list at the bottom. The optional arguments are enclosed in square brackets.

Sub callingProcedure()
'calling the printMultiples Method
'printing 5 multiples of 3, default value of column is used (1)
printMultiples 3, 5
'printing multiples of 4, default value of upto is used (10), column number is 3
printMultiples 4, , 3
'printing 8 multiples of 10, column number is 5
printMultiples 10, 8, 5
End Sub

Now when you run the callingProcedure, it will call the printMultiple procedure there times with different parameter values.

Example 2 – Function to Extract Non-Numerical Characters from a Text String

In this example, we will write a function that will remove the numerical characters from the text string and return a text string that does not contain any numerical value.

This function required two arguments.

• CellRef – This is the cell in the worksheet that contains the text string. This has to be a single cell or the function would return a #VALUE! error.
• TextCase (Optional) – This argument converts the result into upper case. By default, its value is FALSE.

Function GetText(CellRef As Range, Optional TextCase = False) As String
Dim length As Integer
Dim result As String
'getting number of characters in string using Len function
length = Len(CellRef)
'looping through each character in string
For i = 1 To length
'extracting character of string using Mid function
'appending it to the result if the character is a not a numerical value
If Not (IsNumeric(Mid(CellRef, i, 1))) Then
'append to the result
result = result & Mid(CellRef, i, 1)
End If
Next i
'if optional argument is True then convert all non numerical characters to upper case
If TextCase = True Then
'UCase to convert to upper case
result = UCase(result)
End If
'assigning value of result to function returned GetText
GetText = result
End Function

Place this code in the module.

We can now use this function in the worksheet.

=GetText(A1,TRUE)

This brings us to an end.

Thank you for reading. ❤