The code inside a subroutine or function often needs information passed to it.
You can define this information by adding parameters to the declaration allowing you to pass in arguments.
When a variable is passed to a subroutine or function it is called an argument.
The value used in place of the parameter when we make the procedure call is called an argument.

Empty Parentheses

When you have empty parentheses at the end of a subroutine or function it indicates that no variables can be passed in.

Public Sub ProcedureName() 
End Sub

ByVal or ByRef

VBA allows you to pass variables into subroutines and functions in two ways.
You can specify either ByVal or ByRef for each of the variables that are passed in.
The ByVal and ByRef distinction for subroutine and function parameters is very important to make.

Sub Proc_Name(ByVal sName As String) 
End Sub

Sub Proc_Name(ByRef sName As String)
End Sub

In VBA all objects are passed by reference.
ByRef - the address to the object is passed.
ByVal - a copy of the address to the object is passed.

Declaring their Data Type

Procedures that use arguments should always define the data type of these arguments and not leave them blank.
Not declaring the data type will resort in all arguments have the Variant data type.

Sub Proc_Name(Name) 
End Sub

Sub Proc_Name(Name As String)
End Sub

It is important that the data that is passed as the argument matches the data type defined for that argument.
You will get a type mismatch error if any of the arguments do not match their corresponding data type.

Call Proc_Name("Russell") 

Dim sName As String
Call Proc_Name(sName)

Returning Multiple Variables

A function can only return a single value however you can use a ByRef variable as a way of passing another piece of information back to the calling routine.
You can even use a ByRef argument to return a value from a subroutine.


There is one downside to using ByVal in some situations it can consume a lot of memory (and time) to make a copy (or duplicate) of an argument rather than just passing in a reference.
This is only applicable to large objects.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext