Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
When is it correct to declare a sub Public vs Private? What is the advantage?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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...). "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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. "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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) "Rick" wrote in message ... When is it correct to declare a sub Public vs Private? What is the advantage? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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) "Rick" wrote in message ... When is it correct to declare a sub Public vs Private? What is the advantage? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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. -- HTH... Jim Thomlinson "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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)? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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" wrote in message ... 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)? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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" wrote: 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" wrote in message ... 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)? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring subs Public vs Private
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) "Rick" wrote in message ... 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" wrote: 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" wrote in message ... 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)? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Rick" wrote: When is it correct to declare a sub Public vs Private? What is the advantage? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about private subs | Excel Programming | |||
Excel VBA: Automatically activating Private Subs | Excel Programming | |||
declaring public variable value | Excel Programming | |||
Private subs | Excel Programming | |||
calling private subs | Excel Programming |