Declaring subs Public vs Private

[Rick]

When is it correct to declare a sub Public vs Private? What is the advantage?

 

[The Dude]

A private sub will be seen only inside the module, that is you cannot call it
from another module.

A public sub (default) is seen from everywhere by everyone.

The main advantage I see about private subs are that they don't show in the
dialog when you press the "Play" button on the VBA toolbar - though you can
still call them by typing the entire name. Also the fact that they cannot be
called from other modules can prevent mistakes if you ever want to use the
same name for two subs (which is not recommended anyways but...).

 

[Mike H.]

I don't have all the answers but I do know this:
If a sub is declared private one can NOT see it by hitting Alt-F8. It is
"private" and doesn't show up on the list of executable macros. This is
quite convenient for instance if you have a macro called UnprotectAllSheets
that turns off sheet protection. You'd certainly not want users to see it on
the list of macros to execute. However there are times having a private
macro causes it to not be able to be called from other modules, I think. I
don't know the exact extent of this. Perhaps someone else will enlighten
both of us. I think a private sub is only available to be called from within
the module it resides.

 

[Bob Phillips]

A private sub can only be called from within the procedure that it is
within. Such procedures will not be seen from within the macro list.

A public sub can be called from any module, within that project or any other
project. Such procedures will be seen from within the macro list, unless the
procedure has arguments, in which case it won't. However, if the option,
Option Private Module is also used, even Public subs cannot be called from
another project, and will not be seen within the macro list.

Functions are not shown in the macro list, whether they are private or
public.

If the sub is within a class module, it has to be preceded with the class
name even if it is public. So you can make the Workbook_Open procedure
public, it is normally private by default, and call it from within the same
project using ThisWorkbook!Workbook_Open.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Opps, that should be ThisWorkbook.Workbook_Open, not
ThisWorkbook!Workbook_Open.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Thomlinson]

In addition to what everyone else has said there is a programming concept
called encapsulation. The underlying rule is to keep everything as private
and stand alone as possible. The more subs and variables you make public the
more things you need to consider when something goes wrong. By keeping things
private things stay a lot neater and tidier. It also makes things a lot
easier to modify down the road. For example if you wnat to modify a sub that
is private then you only need to look at the other subs in the same module to
see if your changes will cause a problem elsewhere. If however the same sub
was declared public then you need to look at all of the code in the entire
project to confirm that there is no conflict. That is a real waste of time if
you did not use the sub outisde of the current module.

 

[Rick]

Thank you all for the information: Now how should one construct the module?
What should be declared Private vs not declared at all, just sub name(parms)?

 

[Bob Phillips]

IMO you should always declare, explicitly stating whether you want it to be
Private or Public.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick]

Bob: One last thing, How do I create a called public macro, so that
different pgms can call it.

Thanks to all who have contributed information.

 

[Bob Phillips]

Either put it in an addin, set a reference to the project that contains the
macro, or use Application.Run.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)