Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
I'm brand new at VB, so my problem-solving abilties are rather limited... I have 4 public variables that I have defined in the "Declarations" area of Module1. Within Module1, I have a bunch of macros that use these public variables. The 4 variables could be considered "constant", in that I don't want them to change from macro to macro. The error I get is "Compile Error - invalid outside procedure", and it shows up when I try to set the first public variable. My public variable code is below. Any help is greatly appreciated... _____________________________________ Public iRange As Range Public QU, PS, PM As Worksheet Set QU = Sheets("Questionnaire") Set PS = Sheets("Process Summary") Set PM = Sheets("Process Methodology") Set iRange = PS.Range("A6:T26") _____________________________________ -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
You cannot Set those variables in the Declarations section, that must be
within a macro. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "clmarquez" wrote in message ... I'm brand new at VB, so my problem-solving abilties are rather limited... I have 4 public variables that I have defined in the "Declarations" area of Module1. Within Module1, I have a bunch of macros that use these public variables. The 4 variables could be considered "constant", in that I don't want them to change from macro to macro. The error I get is "Compile Error - invalid outside procedure", and it shows up when I try to set the first public variable. My public variable code is below. Any help is greatly appreciated... _____________________________________ Public iRange As Range Public QU, PS, PM As Worksheet Set QU = Sheets("Questionnaire") Set PS = Sheets("Process Summary") Set PM = Sheets("Process Methodology") Set iRange = PS.Range("A6:T26") _____________________________________ -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
Don't set the variable in public:
Public z As Variant Sub Macro1() z = 1 MsgBox (z) End Sub will work Public z As Variant z = 1 Sub Macro1() z = 1 MsgBox (z) End Sub will not work -- Gary''s Student "clmarquez" wrote: I'm brand new at VB, so my problem-solving abilties are rather limited... I have 4 public variables that I have defined in the "Declarations" area of Module1. Within Module1, I have a bunch of macros that use these public variables. The 4 variables could be considered "constant", in that I don't want them to change from macro to macro. The error I get is "Compile Error - invalid outside procedure", and it shows up when I try to set the first public variable. My public variable code is below. Any help is greatly appreciated... _____________________________________ Public iRange As Range Public QU, PS, PM As Worksheet Set QU = Sheets("Questionnaire") Set PS = Sheets("Process Summary") Set PM = Sheets("Process Methodology") Set iRange = PS.Range("A6:T26") _____________________________________ -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
Thanks guys. I understand now. I just thought that if these were going to be set to the same thing, that I could somehow "globally" set them, and not worry about having to re-set them in each individual macro. Instead of setting them in each individual macro, could they somehow be "globally" set once somewhere else outside of public declarations? Maybe in Workbook or something? Or, do you advise against it? -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
You might look into declaring a public constant, but might be an issue with a
sheet. to avoid issues I might use a string constant then adjust other code. Const QU As String = "Questionnaire" "clmarquez" wrote: Thanks guys. I understand now. I just thought that if these were going to be set to the same thing, that I could somehow "globally" set them, and not worry about having to re-set them in each individual macro. Instead of setting them in each individual macro, could they somehow be "globally" set once somewhere else outside of public declarations? Maybe in Workbook or something? Or, do you advise against it? -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
Yes.. If you put the call to "set" your variables in your ThisWorkbook
section when opening the document, then the variables should be set. However, I think that I have seen instances where the values get "corrupted" or lost. I typically use a module that contains a series of functions that return the value I want "globally" set. I did this after I ran into a problem of Excel not being able to maintain a global list of variables on the order of 30 or something.. Or if the program errored, then the variable values were lost. That way I had everything I needed stored in one place, and I could put my eyes on the value if I needed to review it, and not hunt down where I had implemented my global value(s). "clmarquez" wrote: Thanks guys. I understand now. I just thought that if these were going to be set to the same thing, that I could somehow "globally" set them, and not worry about having to re-set them in each individual macro. Instead of setting them in each individual macro, could they somehow be "globally" set once somewhere else outside of public declarations? Maybe in Workbook or something? Or, do you advise against it? -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
GB -
interesting. but the way i see it there are constants, variables and data. OP seemed to be looking for constants. your approach to "remembering" variables seems to me to be addressing what I refer to as data. user action can change the value and if program is terminated and restarted (crash or user shutdown) you still want the "new" value without repeating steps - then it's data and should be written to a range in thisworkbook. variables are used to vary a value durring a session. as to global variables i typically have a separate module "Global Variables" where constants and variables of PROJECT level are declared. I'm not preaching just trying to help and fishing for feedback:) "GB" wrote: Yes.. If you put the call to "set" your variables in your ThisWorkbook section when opening the document, then the variables should be set. However, I think that I have seen instances where the values get "corrupted" or lost. I typically use a module that contains a series of functions that return the value I want "globally" set. I did this after I ran into a problem of Excel not being able to maintain a global list of variables on the order of 30 or something.. Or if the program errored, then the variable values were lost. That way I had everything I needed stored in one place, and I could put my eyes on the value if I needed to review it, and not hunt down where I had implemented my global value(s). "clmarquez" wrote: Thanks guys. I understand now. I just thought that if these were going to be set to the same thing, that I could somehow "globally" set them, and not worry about having to re-set them in each individual macro. Instead of setting them in each individual macro, could they somehow be "globally" set once somewhere else outside of public declarations? Maybe in Workbook or something? Or, do you advise against it? -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
Thanks GB and Vacation's Over for the useful info. GB - I like your ti of returning constants elsewhere, as a backup. I will look to do th same thing.. -- clmarque ----------------------------------------------------------------------- clmarquez's Profile: http://www.excelforum.com/member.php...fo&userid=2938 View this thread: http://www.excelforum.com/showthread.php?threadid=50114 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Public Variables Error
I think that we are somewhat talking about the same thing, depending on your
implementation of the global variable module. I use a retriever function to get the "global" variable from the module. And yes I define the variables as global in that module. But then return the value assigned. I did this because even though I had assigned and was using the actual global variable in a program, if it crashed or ran into some weird situation, it forgot what the value of the global variable was. Therefore I just put it all into one module, and used a retriever function. As with any "value" a user could assign a variable = to the global variable, and then modify the value of the variable to be other than the global variable. So it's still all in the hands of the programmer. But on successive calls to my helper function, it will always return the same value. I agree there is some discrepancy between what we are calling what. And it *does* make a difference. I may have incorrectly used certain terms to convey the idea at hand. Good fish though. :) "Vacation's Over" wrote: GB - interesting. but the way i see it there are constants, variables and data. OP seemed to be looking for constants. your approach to "remembering" variables seems to me to be addressing what I refer to as data. user action can change the value and if program is terminated and restarted (crash or user shutdown) you still want the "new" value without repeating steps - then it's data and should be written to a range in thisworkbook. variables are used to vary a value durring a session. as to global variables i typically have a separate module "Global Variables" where constants and variables of PROJECT level are declared. I'm not preaching just trying to help and fishing for feedback:) "GB" wrote: Yes.. If you put the call to "set" your variables in your ThisWorkbook section when opening the document, then the variables should be set. However, I think that I have seen instances where the values get "corrupted" or lost. I typically use a module that contains a series of functions that return the value I want "globally" set. I did this after I ran into a problem of Excel not being able to maintain a global list of variables on the order of 30 or something.. Or if the program errored, then the variable values were lost. That way I had everything I needed stored in one place, and I could put my eyes on the value if I needed to review it, and not hunt down where I had implemented my global value(s). "clmarquez" wrote: Thanks guys. I understand now. I just thought that if these were going to be set to the same thing, that I could somehow "globally" set them, and not worry about having to re-set them in each individual macro. Instead of setting them in each individual macro, could they somehow be "globally" set once somewhere else outside of public declarations? Maybe in Workbook or something? Or, do you advise against it? -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=501145 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
public variables | Excel Discussion (Misc queries) | |||
Public variables | Excel Discussion (Misc queries) | |||
Public Variables | Excel Discussion (Misc queries) | |||
Public Variables | Excel Programming | |||
Public Variables | Excel Programming |