Call Private Sub From Different Module

[Richard]

Hi,

Is it possible to run a macro from module1, which calls/runs a private sub
in module2.


Thanks
Richard

 

[Sam Wilson]

Pretty sure the answer's no - you'd have to make it a public sub

 

[Richard]

Sam

Not the answer I was hoping for, but thanks anyway. Thought I was missing
something really obvious.

Regards
Richard

 

[Sam Wilson]

Is there a reason you don't want to have a public sub? There may be another
way round the problem.

 

[Richard]

Really it's only to keep everything nice and tidy, so that when another user
has to use the workbook and run a macro there is less chance of the wrong
this being run. I can easily keep the macros within the same module, but
this is a little cumbersome when updating code.

 

[Sam Wilson]

You could put a dummy parameter in the sub you would rather keep private:

Public Sub test(ByVal dummy As String)

MsgBox "Hello"

End Sub

This won't then be visible to anyone wanting to run a macro from outside the
VBE window, and if you want to call it from another macro you can pass a
dummy parameter:

sub demo

call test("X")

end sub

 

[Dave Peterson]

In module1:
Option Explicit
Sub testme01()
Application.Run "Testme02"
End Sub

In module2:
Option Explicit
Private Sub testme02()
MsgBox "hey, this is private"
End Sub

 

[Rick Rothstein]

Why not make the variable Optional... the Sub will still not appear in the
Macro listing and the OP will not have to pass it anything in order to use
it...

Public Sub TestMe(Optional Dummy As String)
MsgBox "Hello"
End Sub

And then your Demo subroutine can call it like this...

Sub Demo()
Call TestMe
End Sub

or, more simply, like this...

Sub Demo()
TestMe
End Sub