How does "Private" work

[Otto Moehrbach]

Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word "Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list, so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

 

[Bob Phillips]

Otto,

These two modules are in different code modules, one in a sheet code module,
the other in (I presume) a normal code module. You cannot invoke Private
procedures across different modules.

If you include an 'Option Private Module' in the code module, those
procedures will not show in the macro list, but will still be available to
other modules in that project. The one thing you won't be able to do is to
invoke them from another project (workbook).

 

[Don Guillett]

Put it in the same sheet module and you won't get the message.

 

[Otto Moehrbach]

Bob
Thanks for your help and thanks for taking the time to explain it to me.
Otto

 

[Otto Moehrbach]

Don
Thanks for your help but how can I put a sheet macro and a regular macro
in the same module? I can put all the code in the sheet macro and that
would do it. Thanks again. Otto

 

[steve]

Otto,

From the VBA help on "Sub Statement"

Private Optional. Indicates that the Sub procedure is accessible only to
other procedures in the module where it is declared.

Put the sub in the sheet module and use

Private Sub Worksheet_Activate()
Macro1
End Sub

Private Sub Macro1()
*code*
End Sub

 

[Tim Zych]

Or another way is to leave it private, but run it using Run, which can call
private procedures and functions:

Application.Run "UpdateSummary" 'The Application part is optional

 

[Harald Staff]

Hi Otto

Lots of good explanations are provided.
Great thing with macros on standard module are that they can be called from
everywhere. But they can also be started from the Tools > Macro menu, which
sometimes is a very bad idea. A typical set of macros in my apps are "unlock
everything" and "re-lock everything", used by lots of procedures. I don't
want any of my users to ever access those.

So put Private in the name and they disappear from the menu. But then they
can't be called from other modules either. Which is a huge problem. My
solution to this -I never use Private: Put an optional parameter to it
that's not used for anything:

Sub RunStuff(Optional RightNow As Boolean)
MsgBox "Hello world"
End Sub

-and it disappears completely from the user, but still it can be called from
all modules:

Sub SomewhereElse()
Call RunStuff
End Sub

 

[Otto Moehrbach]

That's a good point to remember. Thanks. Otto

Just to note, there might be issues if code is moved behind the worksheet.

Unqualified range references in sheet code always reference the sheet that
holds the code, whereas in a module, they reference the active sheet. It
depends on what your code does, to determine if putting it behind a sheet is
the best choice. As a matter of personal preference, I tend to allow some
sheet level event code behind the sheet, but not much more. Hard to say how
it would affect your macro because we don't know what it does. Since your
original goal was to simply hide the macro visibility from the Tools menu,
you may be safest simply by adding Option Private Module at the top of the
module and making the sub public, like Bob suggested (although I like Run
for some things too).

Best regards,
Tim Zych