More questions about variables and procedures...

  • Thread starter Thread starter Mike-hime
  • Start date Start date
M

Mike-hime

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
 
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
 
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 said:
If you declare your variables as Public in the General Declarations (not
Click to expand...
within a module) the values will be retained after the code runs.
 
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
 
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 said:
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
Click to expand...
I can when calling variables and procedures. The small amount of extra
typing gets rid of lots of unexplainable surprises.
 
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
 
Back
Top