ExcelBanter - Question about a Variable being Nothing

Thanks that explains it.

"Jim Thomlinson" wrote:

Variables (or objects as in this case) are either persistent or volatile.
Public variables are persistent. To give you an idea what is going on when
you start the spreadsheet your system reserves space for all of your global
variables (on the heap). These memory allocations will persist for the
duration that you have the spreadsheet open. You can refer to global
variables at any point and they will return back the last value that was
passed to them. So in your case as soon as you point Var1 and myRange at a
range, then that range is held in memory until you do something to change it.
The other form of persistent variables are any varaibles declared as static
within a procedure. They are also stored on the heap.

Volatile variables are any variables declared within a sub or function.
Memory is only allocated to these variables when the procedure is call and
the memory is destroyed once the function or sub has ended (memory is set
aside on the stack and not the heap).

As a general rule of thumb you want to minimize you use of global variables
as they can be extremely difficult to debug as the value is accessible to the
entire program so if at any point the value is incorrect it can be difficult
to determine just which function or procedure cause the problem.

As a complete aside you declaration is probably not what you think it is.
You might thingk you have 2 range objects but you actually have one range
object and one variant. Check out this link...
http://www.cpearson.com/excel/variables.htm

--
HTH...

Jim Thomlinson

"J@Y" wrote:

This is a simplified version of the code. I actually use Var1 in another sub,
that's why I have it as Public. What exactly does the Public declaration do
to my variable in this case?

"Pranav Vaidya" wrote:

the only problem is with declaration.
Declare variable Var1 inside sub B and your problem should be solved.
Remove the public declaration of Var1.
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

"J@Y" wrote:

This is my Code:
Public Var1, MyRange as Range

Sub A

For c = 1to 10 do
Call Sub B
Next c

end Sub A

Sub B

Set Var1 = MyRange.Find("Blah", lookat:=xlPart, _
Searchorder:=xlByRows)

if Not (Var1 is Nothing) then
Debug.print "Var1 exist"
end if

end Sub B

Sub B gets looped 10 times. I run into this problem where if Var1 is found
in one iteration, in the next iteration, even though it doesn't refer to any
Range, it is not recognized as Nothing anymore. Why is that?