Calling a Private Sub from another module

Question

I have two modules, Module1 and Module2.

In Module1:

Private Function myCheck() As Boolean

if [Operation] = [Something] then

myCheck = True
Else
myCheck = False
End if

End Sub

In Module2, I would like to run myCheck sub in Module 1 then do another operation:

Private Sub Execute()

[Operation 1]

If myCheck = True Then

[Operation 2]
Else
[Operation 3]

End If
End Sub

It does not work. If I place Private Function myCheck within the same module then it works. Is there a special method to call a sub or function from another module?

Answer 1

Use Option Private Module For Module1, then in Module 2:

for a Sub; qualify what you're calling with the Module it's in, like so:

Module1.myCheck()

for a Private Sub; use Application.Run and qualify what you're calling with the Module it's in, like so:

Application.Run ("Module1.myCheck")

Using Private Module hides it's contained sub/s in the Developer > Macros list.

---

Further Reading :-)

Read through the comments in the code below to see what does and doesn't work. To confirm the behaviours yourself: Create a new Excel, open Developer > Visual Basic, insert 3 Modules. Copy the below code blocks into the relevant modules.

'In Module1
Option Explicit

Sub ScopeTrials()

'NOTES:
'   Only NormalSubIn_NormalModule shows in Developer > Macros.

'   As the default without a keyword is Public I have called
'   these "Normal". I.e. you can use Public or Nothing wherever
'   Normal is.

'   A line commented out shows what doesn't work.

NormalSubIn_NormalModule
Call NormalSubIn_NormalModule
Application.Run ("NormalSubIn_NormalModule")  'Not recommended!

NormalSubIn_PrivateModule
Call NormalSubIn_PrivateModule
Application.Run ("NormalSubIn_PrivateModule")  'Not recommended!

'PrivateSubIn_NormalModule
'Call PrivateSubIn_NormalModule
'Module2.PrivateSubIn_NormalModule
'Call Module2.PrivateSubIn_NormalModule
Application.Run ("PrivateSubIn_NormalModule")  'Fails with duplicates! See Explanation
Application.Run ("Module2.PrivateSubIn_NormalModule")

'PrivateSubIn_PrivateModule
'Call PrivateSubIn_PrivateModule
'Module3.PrivateSubIn_PrivateModule
'Call Module3.PrivateSubIn_PrivateModule
Application.Run ("PrivateSubIn_PrivateModule")  'Fails with duplicates! See Explanation
Application.Run ("Module3.PrivateSubIn_PrivateModule")

'Explanation: if there is an identical sub in another Private Module, then this fails
'with Runtime Error 1004 (Macro not available or Macros Disabled), which is Misleading
'as the duplication and/or nonspecified module is the problem.
'I.e. always specify module!
'Also, this only fails when the line is actually run. I.e. Compile check doesn't find this
'when starting to Run the code.

End Sub

---

'In Module2
Option Explicit



Sub NormalSubIn_NormalModule()  'only THIS sub shows in Developer > Macros

    MsgBox "NormalSubIn_NormalModule"

End Sub


Private Sub PrivateSubIn_NormalModule()

    MsgBox "PrivateSubIn_NormalModule"

End Sub

---

'In Module3
Option Explicit
Option Private Module


Sub NormalSubIn_PrivateModule()

    MsgBox "NormalSubIn_PrivateModule"

End Sub


Private Sub PrivateSubIn_PrivateModule()

    MsgBox "PrivateSubIn_PrivateModule"

End Sub

Answer 2

You can use Application.Run to do this:

Application.Run "Module1.myCheck"

The macro will stay "invisible" for the users if they display the Macros Dialog Box (Alt+F8), since it's still private.

Edit #1

A second option is to introduce a dummy variable as an optional parameter in the Sub, like this:

Public Sub notVisible(Optional dummyVal As Byte)
    MsgBox "Im not visible because I take a parameter, but I can be called normally."
End Sub

This too, will hide the macro in the Macros Dialog Box (Alt+F8), but it can now be invoked the usual way.