ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How does "Private" work (https://www.excelbanter.com/excel-programming/277100-how-does-private-work.html)

Otto Moehrbach[_4_]

How does "Private" work
 
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[_5_]

How does "Private" work
 
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).

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
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





Don Guillett[_4_]

How does "Private" work
 
Put it in the same sheet module and you won't get the message.

"Otto Moehrbach" wrote in message
...
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





Otto Moehrbach[_4_]

How does "Private" work
 
Bob
Thanks for your help and thanks for taking the time to explain it to me.
Otto
"Bob Phillips" wrote in message
...
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).

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
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







Otto Moehrbach[_4_]

How does "Private" work
 
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
"Don Guillett" wrote in message
...
Put it in the same sheet module and you won't get the message.

"Otto Moehrbach" wrote in message
...
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







steve

How does "Private" work
 
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

--
sb
"Otto Moehrbach" wrote in message
...
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





Tim Zych[_2_]

How does "Private" work
 
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


"Otto Moehrbach" wrote in message
...
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





Harald Staff[_5_]

How does "Private" work
 
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

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"Otto Moehrbach" skrev i melding
...
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





Otto Moehrbach[_4_]

How does "Private" work
 
That's a good point to remember. Thanks. Otto
"Tim Zych" wrote in message
...
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


"Otto Moehrbach" wrote in message
...
Steve
You taught me something I didn't know. I didn't know I could put a
regular (non-event) macro in a sheet module. Thanks. Otto
"steve" wrote in message
...
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

--
sb
"Otto Moehrbach" wrote in message
...
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














All times are GMT +1. The time now is 12:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com