Variant Array retaining data in it after procedure finished

[Guest]

Hi,

I hope someone can help cos I am stuck!

I have a procedure that creates an array (variant). The variant/array's size
is dependant on various criteria it encounters at run-time so I'm using
'redim preserve' each time during a loop to make it bigger. At the end of the
code running, the array is typically (20 * 2* 76) parts big, and I'm done
with using it - the data in it's been used etc.

My problem is that; I open Excel. Run the procedure. It works perfectly. I
run the procedure again, the variable is twice as big as it was before at the
end of the procedure = somehow, it's kept the data in it after the code has
stopped.

How is that possible?? It works fine if I close Excel again & run it again,
obviously but all the books I've read say that the life of a variable only
exists whilst code is running...

Code execution is stopped by an 'Exit Sub' in front of a load of things that
error trappers Goto & then Return to the code above it. The code stops as it
should at the 'Exit Sub'.

Any help much appreciated as always.

Thanks,

Steven Douglas

 

[Tom Ogilvy]

Sounds like the array is declared outside a procedure, so it is a global
variable and therefore its lifetime is the lifetime of the open workbook.
Or, you are using some type of global counter as the basis for resizing the
array and that is the item that is retaining its value.

 

[Guest]

Yep, that'll be it. The variable is declared at the top of the module -
outside of the procedure. I didn't know that that meant it (and others) were
then live throughout the life of the workbook... oh dear....

Anyway. Cheers.

Doug