"Private Sub" v "Sub"

[Stuart]

What are the differences between "Private Sub" and "Sub".

I am in the process of declaring all my variables in a very large and
complex workbook. (up until now I haven't bothered and am now suffering from
occasional crashes, I am hoping that declaring all variables and using
"Option Explicit" will cure this prob)

One thing that is puzzling me is that once a variable is declared inside one
sub, there appears to be no need to declare it inside another, unless the
"Sub" is a "Private Sub" I don't understand why this is because I am under
the impression taht a variable declared within a sub is unique to that sub.

Another abnormality. I have noticed that throughout my workbook the
"Address" keyword has turned into "addRess" (notice the upper case change)
The lines with this command in still work as they should but why ??
I have undoubtedly used "addRess" as a variable at some point, but I most
certainly don't have such a variable declared now! (I always capitalise a
middle letter of my variables so as they are more identifiable to me)

Since I never need to use a Sub from the macro toolbar, am I better of
having only Private Subs

 

[Peter T]

Hi Stuart,

What are the differences between "Private Sub" and "Sub".

"Private Sub" (or function) can only be called by another within the same
module. If you don't want any of your macros to be called from the Excel
toolbar you could head your modules:
Option Private Module

I am in the process of declaring all my variables in a very large and
complex workbook. (up until now I haven't bothered and am now
suffering from
occasional crashes, I am hoping that declaring all variables and using
"Option Explicit" will cure this prob)

Good idea to use Option Explicit and declare your variables, and fully to
expected data or object type. When done, from vba's main menu, Debug >
Compile. It will break on anything that can't be compiled - investigate,
correct and repeat compile until it completes.

One thing that is puzzling me is that once a variable is declared inside one
sub, there appears to be no need to declare it inside another, unless the
"Sub" is a "Private Sub" I don't understand why this is because I am under
the impression taht a variable declared within a sub is unique to that

sub.

I think you are misunderstanding something here. Unless a variable is
declared at module level you do have to declare similarly named variables in
each procedure, if you use Option Explicit. Otherwise it's simply an
undeclared variable.

Another abnormality. I have noticed that throughout my workbook the
"Address" keyword has turned into "addRess" (notice the upper case
change)

Two points here. First, it's generally recommended not to use the name of a
keyword as a variable. Suggest change addRess to say sAddr (presumably as
string).

Second, when you capitalize a variable in one part of your project, it gets
recapitalized the same way elsewhere. It's either a quirk or extremely
clever depending on your point of view.

Regards,
Peter T

 

[Patrick Molloy]

note that a procedure declared as public in a module declared as Option
Private Module can still be called by any other module, but it won't be
visible throught the excel worksheet menu as a "macro".

What we're discussing in general terms is called SCOPE.
eg a variable dimensioned within a procedure (sub) is only available to that
sub. A variable declared as private in the module's general section can be
use dby any sub in that module, but not by subs in other modules. global
variables are available to all code modules -hence the name

hth

 

[Stuart]

Hi Stuart,

sub.

I think you are misunderstanding something here. Unless a variable is
declared at module level you do have to declare similarly named variables in
each procedure, if you use Option Explicit. Otherwise it's simply an
undeclared variable.

Well, there is something definitely quirky with this workbook. I have
procedures that run just fine with undeclared variables in an "Option
Explicit" module, providing that those variables are declared elsewhere
*Inside* other procedures in the same module. (They are defiantly not
declared at the module level!) Now here is the wacky bit. The procedures
work fine as "Sub" if I make that Sub into "Private Sub" Then the procedure
stops working and asks me to declare!

It would appear that this anomaly is only aparent in this workbook, as I am
unable to repeat it in other workbooks!

Two points here. First, it's generally recommended not to use the name of a
keyword as a variable. Suggest change addRess to say sAddr (presumably as
string).

Yes, I totally agree. It would appear that I may (probably) did have a
variable "addRess" at some time in the past. however it *No* longer exists
in this workbook, however, where ever I try to use the "Address" keyword,
the workbook converts it to "addRess" .... It's now driving me up the wall!

 

[Peter T]

Well, there is something definitely quirky with this workbook. I have

procedures that run just fine with undeclared variables in an "Option
Explicit" module, providing that those variables are declared elsewhere
*Inside* other procedures in the same module. (They are defiantly not
declared at the module level!) Now here is the wacky bit. The procedures
work fine as "Sub" if I make that Sub into "Private Sub" Then the procedure
stops working and asks me to declare!

It would appear that this anomaly is only aparent in this workbook, as I am
unable to repeat it in other workbooks!

I can neither recreate nor explain your anomaly, maybe someone else can.

It doesn't sound right, perhaps rebuild a new wb or try Rob Bovey's
CodeCleaner:

http://www.appspro.com/Utilities/CodeCleaner.htm

Patrick - thanks for clarifying what I "meant" to explain but didn't quite!

Regards,
Peter T

 

[Dick Kusleika]

Yes, I totally agree. It would appear that I may (probably) did have a
variable "addRess" at some time in the past. however it *No* longer
exists in this workbook, however, where ever I try to use the
"Address" keyword, the workbook converts it to "addRess" .... It's
now driving me up the wall!

Once you have that variable, Excel has a very good memory. The only way to
fix this is to declare a variable

Public Address As Variant

Compile, Save. Then delete the Dim line. That will reset whatever memory
VBA had of your different capitalization. I don't know if the Compile and
Save parts are really necessary, but I always do them.