Event macro does not run a Private sub

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

Excel XP & Win XP
I have a simple BeforeSave macro:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call UpdateAll
End Sub

The UpdateAll macro is a Private macro as:
Private Sub UpdateAll()
'Stuff
End Sub

The event macro errored out as not being able to find the sub. When I
removed the "Private" in the first line of the UpdateAll macro, it worked
fine.
Why is that? Thanks for your time. Otto
 
The Private declaration indicates that the procedure is not visible to
code outside the module in which the procedure is defined. So, if you
have UpdateAll in a module other than the ThisWorkbook module and it
is marked Private, it won't be found. When you use neither Public nor
Private in a regular code module, Public is the default.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Hi,

Just one thing for future consideration, you don't need to use Call in this
kind of situation. You can simply enter the name of the subroutine. There
is also a third way to call code and that is with the Run command. Each of
these approaches has its uses.

Now a little more on calling code in other modules - If you put code in the
thisWorkbook object or Sheet1 object for example as Public (not Private) you
can't call it from a another module by using its name instead you must
qualify it that means if the code is call Sub myMessage you must call it with
Sheet1.myMessage.
Then it will be executed.
Note this same approach can be used to run code in user forms from outside
the user form. UserForm1.Message would run the Public Sub Message() or the
Sub Message() coded on the user form.

Add the word Private Sub Message() and you won't be able to run it even when
you qualify the name.


If any of this is helpful, please click the Yes button.
 
ShaneDevenshire said:
Hi,

Just one thing for future consideration, you don't need to use Call in
this
kind of situation. You can simply enter the name of the subroutine.
Click to expand...

That is true, but it is a far better practice IMO to use Call to show that
the code is calling another procedure.
 
Chip
Thanks for that. There is no end to this learning thing. I thought
"Private" simply hid that macro from the Tools - Macro - Macros list. I'll
go back to passing a dummy value to hide the macro. Thanks again. Otto
 
Shane
Thanks for the help. Otto
ShaneDevenshire said:
Hi,

Just one thing for future consideration, you don't need to use Call in
this
kind of situation. You can simply enter the name of the subroutine.
There
is also a third way to call code and that is with the Run command. Each
of
these approaches has its uses.

Now a little more on calling code in other modules - If you put code in
the
thisWorkbook object or Sheet1 object for example as Public (not Private)
you
can't call it from a another module by using its name instead you must
qualify it that means if the code is call Sub myMessage you must call it
with
Sheet1.myMessage.
Then it will be executed.
Note this same approach can be used to run code in user forms from outside
the user form. UserForm1.Message would run the Public Sub Message() or
the
Sub Message() coded on the user form.

Add the word Private Sub Message() and you won't be able to run it even
when
you qualify the name.


If any of this is helpful, please click the Yes button.
Click to expand...
 
Whether a variable or procedure is visible to other procedures and/or
module is called "scope". See http://www.cpearson.com/Excel/Scope.aspx
for details about scope as it relates to procedure and variable
declarations.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Or add

Option Private Module

to the module declaration, it will then be exposed to all modules, but not
'seen' from Excel.
 
Bob
Do you have that right? I have the following in my HowTo file:
"You don't need to put private in front of each sub. You can type Option
Private Module at the top of the module and all the macros within that
module will be Private."
What you are saying seems to be the opposite except for the "visible" part.
Thanks for your time. Otto
 
Back
Top