VBA - Nesting Macros

December 2016


VBA - Nesting Macros

com

Introduction

There are several approaches that can be used to call a macro from another one (depending on whether your are calling a Sub or function). In addition, you need to pay special attention when handling event procedures or calling a procedure from another workbook.

Calling a Sub from a Sub


Without parameters

Both Subs are in the same module and have no parameters. The Macro2 is calling Macro1:

Sub Macro1()
    MsgBox "Hello world!"
End Sub

Sub Macro2()
    Call Macro1
End Sub



If you don't want to use the Call statement:

Sub Macro1()
    MsgBox "Hello world!"
End Sub

Sub Macro2()
    Macro1
End Sub



It is not recommended though, as the Call statement makes the code much more readable.

When the Subs are in different modules and have no parameters, there's no requirement to specify the module. But still you have to think about the maintenance of your code. By indicating which module is being used, you will save some valuable time when debugging your code. So if the Macro1 is in Module1 and Macro2 in Module2:

Sub Macro1()
    MsgBox "Hello world!"
End Sub

Sub Macro2()
    Call Module1.Macro1
End Sub

With parameters

The method is identical. Just add the parameters in parentheses:

With the Call statement
Sub Macro1(Nb1 As Long, Nb2 As Long)
    Range("A1") = Nb1
    Range("A2") = Nb2
End Sub

Sub Macro2()
    Call Macro1(18254, 654897)
End Sub


Without the Call statement
Sub Macro1(Nb1 As Long, Nb2 As Long)
    Range("A1") = Nb1
    Range("A2") = Nb2
End Sub

Sub Macro2()
    Macro1 18254321, 654897
End Sub


If the macros are in different modules:
Sub Macro2()
    Call Module1.Macro1(918254321, 654897)
End Sub

Calling a Function from a Sub

What is a function? What differentiates a function from a Sub? A function is an instruction that returns a value based on specific parameters . A Sub is also an instruction which depends on parameters (if any), but it doesn't return any value.
Example of Function:
Function Addition(Nb1 As Double, Nb2 As Double) As Double
Addition = Nb1 + Nb2
End Function


In this example the function is declared "As Double" (this type declaration is optional.) The value it returns will be of the Double type. Given that the function returns a value, you will need to provide storage of this value in the calling Sub. This can be either an Excel cell, a variable, a control etc ...
 Sub Macro2()
  Dim Somme As Double
  Somme = Addition(1234.56, 654.32)
  MsgBox Somme
End Sub

Function Addition(Nb1 As Double, Nb2 As Double) As Double
Addition = Nb1 + Nb2
End Function 

Nb: Parameters enclosed in parentheses and separated by commas.

Calling a Function from a Function

We will use the same principle!

Using an intermediate variable

In this example we will store the result in an intermediate variable:
 Sub Macro2()
  Dim Somme As Double
  Somme = Addition(1234.56, 654.32)
  MsgBox Somme
End Sub

Function Addition(Nb1 As Double, Nb2 As Double) As Double
  Dim VarNb As Double
  VarNb = MultiplieParDeux(Nb2)
  Addition = Nb1 + VarNb
End Function

Function MultiplieParDeux(Nb As Double) As Double
  MultiplieParDeux = Nb * 2
End Function

Without using an intermediate variable:

 Sub Macro2()
  Dim Somme As Double
  Somme = Addition(1234.56, 654.32)
  MsgBox Somme
End Sub

Function Addition(Nb1 As Double, Nb2 As Double) As Double
  Addition = Nb1 + MultiplieParDeux(Nb2)
End Function

Function MultiplieParDeux(Nb As Double) As Double
  MultiplieParDeux = Nb * 2
End Function

Calling an event procedure

What is an event procedure? An event procedure is a statement that is automatically activated when the user interacts with a particular object. The object can be either a sheet, a workbook, a control ... Examples of events: opening or closing of workbook, change sheet, choosing from a list, typing in a control etc ...
Example:
The user changes the value of cell A1: "pas mal" will be displayed in B1 if A1 is greater than 10.
===Implementation:====
  • Select the sheet
  • Right-click on the sheet tab / View Code.
  • In the code window, insert the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'Si la cellule concernée par le changement n'a pas pour adresse A1 => on quitte
If Target.Address <> "$A$1" Then Exit Sub
'Si la cellule (donc A1) est > 10 alors on place "Pas mal!" en B1
If Target.Value > 10 Then Target.Offset(0, 1) = "Pas mal!" Else Target.Offset(0, 1) = "Pas terrible!"
End Sub

With the Call statement

If the called procedure is in the same module as your event procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
'Si la cellule concernée par le changement n'a pas pour adresse A1 => on quitte
If Target.Address <> "$A$1" Then Exit Sub
'Si la cellule (donc A1) est > 10 alors on place "Pas mal!" en B1
If Target.Value > 10 Then Target.Offset(0, 1) = "Pas mal!" Else Target.Offset(0, 1) = "Pas terrible!"
End Sub

Sub MaMacro()
Dim monRange As Range
Set monRange = Sheets("Feuil1").Range("A1")
Call Worksheet_Change(monRange)
End Sub


By cons, if the calling procedure is not in the same module, you will get the following error:
  • Compile error: Sub or Function not defined

VBA is unable to access to the event procedure. To remedy this:

The CallByName function

Syntax: CallByName(Objet, NomProcédure, TypeAppel, Args())
  • Objet: Required. Of the Object type. Designates the object involved in the procedure.
  • NomProcédure: Required. String. The name of the called procedure.
  • TypeAppel: Required. CallType: Method, Set, Let or Get.
  • Args(): Optional. Parameters to be passed to the called function.


In our example, we shall place maMacro in Module1 and try to call the Worksheet_Change Sub. But first, it is necessary to declare our event procedure (when declared as private, it can't be used in the module).
Sub Worksheet_Change(ByVal Target As Range)
'Si la cellule concernée par le changement n'a pas pour adresse A1 => on quitte
If Target.Address <> "$A$1" Then Exit Sub
'Si la cellule (donc A1) est > 10 alors on place "Pas mal!" en B1
If Target.Value > 10 Then Target.Offset(0, 1) = "Pas mal!" Else Target.Offset(0, 1) = "Pas terrible!"
End Sub


Now place the MyMacro Sub in Module 2.
 Sub MaMacro()
Dim monRange As Range
Set monRange = Sheets("Feuil1").Range("A1")
CallByName Worksheets("Feuil1"), "Worksheet_Change", VbMethod, monRange
End Sub


Another example:
Upon clicking on a button in a UserForm (UserForm2), trigger the Combobox1_Change event in UserForm1...
 Private Sub CommandButton1_Click()
CallByName UserForm1, "ComboBox1_Change", VbMethod
End Sub

Call a procedure in another workbook

We will use the Run method. It will be specified, including the workbook containing the called procedure and the name of the procedure.

Calling a Sub

Case 1: The file is already open. In this case, there's no need to specify the path:

Sub TestRun()
Application.Run "'Classeur1.xlsm'!Module2.Macro2"
End Sub


Case 2: The file is closed. In this case, the full path must be specified:
Sub TestRun()
Application.Run "'C:\Users\franck\Desktop\Classeur1.xlsm'!Module2.Macro2"
End Sub

Calling a Function

When calling a function from the same workbook, you will need a variable to store the returned value. A variable. We'll have to also communicate all the parameters to be passed to the function from the calling Sub.
We will use the Addition Function located in the Classeur1.xlsm:

Function Addition(Nb1 As Double, Nb2 As Double) As Double
Addition = Nb1 + Nb2
End Function 



To call our main procedure [Principale()] in the workbook [Classeur2.xlsm], we need the following code:

Workbook already opened
Sub Principale ()
Dim Somme As Double
  Somme = Run("'Classeur1.xlsm'!Module2.Addition", 1234.56, 654.32)
  MsgBox Somme
End Sub



Workbook is closed
Sub Principale ()
Dim Somme As Double
  Somme = Run("'C:\Users\franck\Desktop\Classeur1.xlsm'!Module2.Addition", 1234.56, 654.32)
  MsgBox Somme
End Sub


NB: If your workbook was previously closed, it will open after executing the macro. So remember to close it again ... using a code!
Sub TestRun()
Dim Somme As Double
  Somme = Run("'Classeur1.xlsm'!Module2.Addition", 1234.56, 654.32)
  Workbooks("Classeur1.xlsm").Close False
  MsgBox Somme
End Sub

Related :

This document entitled « VBA - Nesting Macros » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.