Pass VBA Variables Between Macros

In Excel you can pass variables (an argument) between macros at run time. This allows you to have multiple modules and pass the value of a variable from macro A and send that value into macro B. The following is a short YouTube video that explains how to pass variables between macros. No Excel file is needed to sit through the presentation.

 
 

 The following is an example of the syntax for to start the process;

Sub UseVar(ByVal Col as Integer)

The information between the brackets is known as an Argument. There needs to be two procedures, A Calling Procedure and a Called Procedure. The Calling Procedure is the one where the Variable is passed from and the Called Procedure is the one the Variable is passed to.


The following is a very simple example of passing one parameter between a Calling Procedure to the Called Procedure (the procedure with the arguments);

Sub CopyCol() 'VBA Calling Procedure
Dim Col As Integer
Col=1
Call UseVar(Col)
End Sub

Sub UseVar(ByVal Col as Integer) 'VBA Called Procedure
Dim rng As Range
Set rng=Columns(Col)
rng.Copy rng.Offset(, 1)
End Sub

In the above example there is just one Argument and it is an Integer. An important point is the arguments type must match. So if the variable is declared as an integer it cannot be passed as a String for example.

The following is an example of a VBA procedure which passes two Variables;

Sub CallingPro() 'Excel VBA to pass 2 variables.
Dim rng As Range
Dim str As String

Set rng=Range("a1:a3")
str="Yes"

Call AddVal(rng, str)
End Sub

Sub AddVal(ByRef rng As Range, ByVal str As String)
rng.Value=str
End Sub

In the above example the Range (rng) will be transferred from CallingPro to the first argument of the AddVal procedure and the String (Str) will be transferred to the second argument of the AddVal procedure.