Variant Array retaining data in it after procedure finished

[Doug]

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.

--
Regards,
Tom Ogilvy



"Doug" <[email protected]> wrote in message
news:[email protected]...
> 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

[Doug]

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Doug" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[bhofsetz]

I am having the same problem with an array retaining values after the macro finishes running.
I understand that if the array is declared at the top of the modue then it is global in scope. However it seems that other variables declared at the top of the same module lose their values once the macro completes.
I need to use the values assigned to the array in one procedure in several later procedures so it needs to be global in scope.
Is there a simple way to clear the array once the macro completes?

Thanks for any insights

Brendon