Calling private procedures
A procedure declared private can only be accessed from other procedures within the same module. Private sub procedures do not appear on the macro list in Excel. Private functions do not appear on the function list in Excel.
Calling a private procedure from another module
Code 1 (Module 1) contains a Private sub procedure (lines 4 to 7), and a Private function procedure (lines 9 to 12)
Code 1: Module 1 - two procedures - M1Macro (private sub), and M1Function (private function)
'' [Module 1(Code)] Option Explicit Private Sub M1Macro() '' Display a simple message box MsgBox "M1Macro" End Sub Private Function M1Function(Arg1 As Double) '' Return the cube of Arg1 M1Function = Arg1 ^ 3 End Function
To call the M1Macro from another module (Module2 in code 2 and 3), use the Application.Run method.
With private procedures, you must include the module name with the macro name. For example, use Module1.M1Macro to run the M1Macro in Module1 from another module. In the code 2 example in lines 2 to 10, RunM1aMacro and RunM1bMacro are equivalent. In line 5, the macro name is passed by position. In line 9, the macro name is passed by name.
Code 2: Module 2 - calling a private macro
' [Module2 (Code)] Option Explicit Sub RunM1aMacro() Application.Run "Module1.M1Macro" End Sub Sub RunM1bMacro() Application.Run Macro:="Module1.M1Macro" End Sub
To call the M1Function from Module2, use the Application.Run method, where the Macro argument refers to the function procedure. In code 3 line 18, the macro (function) name and argument are passed by position. In line 23, the macro (function) name and argument are passed by name.
Code 3: Module 2 - calling a private function
' [Module2 (Code)] Sub RunM1aFunction() Dim Num As Double Num = Application.Run("Module1.M1Function", 2) End Sub Sub RunM1bFunction() Dim Num As Double Num = Application.Run(Macro:="Module1.M1Function", Arg1:=2) End Sub
- This example was developed in Excel 2010 :: VBA 7
- The 2012 version of this page: O'Connor I, (2012), Calling private procedures pdf [107 KB]
- Revised: Sunday 6th of November 2016 - 09:18 AM, [Australian Eastern Standard Time (EST)]