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

Example


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.


Syntax

  1. Application.Run(Macro,Arg1,...,Agr30)

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