Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
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 "Peter T" <peter_t@discussions wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
"Peter T" <peter_t@discussions wrote in message ... Hi Stuart, 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. 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! 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). 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Private Sub" v "Sub"
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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |