Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
So, I've come to find that the problem I had earlier was caused by using the
end statement, which clears the value of all variables upon it's execution. Can anyone suggest a way to Set all variables in the auto_open macro in such a way that their values will be maintained after code execution stops? I would like to avoid copying all the set statements I use to each procedure... But I suppose I will if I've got to :) -Mike-hime |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
If you declare your variables as Public in the General Declarations (not within a module) the values will be retained after the code runs.
ie Public SomeVariable as Integer Sub Some_Sub() --Your code here end sub Hope that gets you what you need. TerryK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
I was under the impression that you are correct... but even though I have
coded it that way, after I run a procedure if I try to run it agian, it will cause an error saying that my variables aren't defined. It seems buggy to me. "TerryK" wrote in message ... If you declare your variables as Public in the General Declarations (not within a module) the values will be retained after the code runs. ie Public SomeVariable as Integer Sub Some_Sub() --Your code here end sub Hope that gets you what you need. TerryK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
Are the variables declared in the same Module or UserForm as the code that is refering to them
If not you might need to qualify them more strictly. for exampl SomeUserForm General Declaration Public SomeVariable as Intege --------- Module Sub SomeSub( x= SomeUserForm.SomeVariabl end su I agree it seems buggy sometimes, I have found it best to be as explict as I can when calling variables and procedures. The small amount of extra typing gets rid of lots of unexplainable surprises TerryK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
I agree, good long code is better than short pretty code. :)
I'm pretty sure the problem isn't the scope. I'm actually declaring the variables as public at the begining of the auto_open module from outside the procedures, and then setting the variables in the auto_open procedure like this: Option Explicit Public MyVar as Worksheet Sub Auto_Open() Set MyVar = Worksheet("Sheet1") End Sub The odd thing about this is that it works the first time. I can run any macro with that variable in it, and it will work just fine. However, when that macro finishes and I try to run another I get an error statement. I've checked to make sure there is no code reseting the variables, and there isn't. I can only attribute this unusual behavior to a bug... So, I did what someone suggested to me and I made a sub routine that sets all the variables and I call that at the begining of every other procedure requiring those variables. It's a pain, but what can you do? Right ;) "TerryK" wrote in message ... Are the variables declared in the same Module or UserForm as the code that is refering to them? If not you might need to qualify them more strictly. for example SomeUserForm General Declarations Public SomeVariable as Integer ---------- Module1 Sub SomeSub() x= SomeUserForm.SomeVariable end sub I agree it seems buggy sometimes, I have found it best to be as explict as I can when calling variables and procedures. The small amount of extra typing gets rid of lots of unexplainable surprises. TerryK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More questions about variables and procedures...
Tom Ogilvy had this comment in another post about scope variables :
Make sure you don't have any plain END statements in your code. This resets variables. Don't hit the reset button in the VBE. This clears you variables as well. -- Regards, Tom Ogilvy This is a long shot but are there any plain END statements in your code. TerryK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating sub procedures | Excel Discussion (Misc queries) | |||
VBA function Procedures.... | Excel Worksheet Functions | |||
VBA functions and Procedures.... | Excel Worksheet Functions | |||
Sequential Sub Procedures | Excel Programming | |||
Using Sub-procedures w/in a function | Excel Programming |