VBA code to "reset" application run and empty preserved arrays
 

Hi,

I've been helping write some VBA code which is executed from a button
on an excel worksheet. Data is read in from the sheet, calculations
are performed in multiple modules (public variables). The problem is
that as some of the arrays which hold the data are dimensioned using
Redim Preserve, which means that when the program ends, the data is
still in the arrays.

If the program is run a 2nd time from the button on the worksheet,
lots of errors are generated in the program, as the arrays are full of
data from the first run. If "reset" is clicked in the VB editor, the
arrays empty & the code runs fine. Is there a way to code this
'reset' button & empty the arrays?

Cheers,
Kate

VBA code to "reset" application run and empty preserved arrays
 

Look in the Excel online help for the keyword "erase" - gets rid of arrays....

--
www.alignment-systems.com


" wrote:

Hi,

I've been helping write some VBA code which is executed from a button
on an excel worksheet. Data is read in from the sheet, calculations
are performed in multiple modules (public variables). The problem is
that as some of the arrays which hold the data are dimensioned using
Redim Preserve, which means that when the program ends, the data is
still in the arrays.

If the program is run a 2nd time from the button on the worksheet,
lots of errors are generated in the program, as the arrays are full of
data from the first run. If "reset" is clicked in the VB editor, the
arrays empty & the code runs fine. Is there a way to code this
'reset' button & empty the arrays?

Cheers,
Kate

VBA code to "reset" application run and empty preserved arrays
 

Kate,
Whilst I would not recommend it, there is End. However, this is rather
abrupt termination of code and clearing of variable.
Go John's way of explicitly Erasing each array, unless you want to
<probably cause more problems than it solves with End, which is the code
equivalent of the Reset button.

NickHK

wrote in message
oups.com...
Hi,

I've been helping write some VBA code which is executed from a button
on an excel worksheet. Data is read in from the sheet, calculations
are performed in multiple modules (public variables). The problem is
that as some of the arrays which hold the data are dimensioned using
Redim Preserve, which means that when the program ends, the data is
still in the arrays.

If the program is run a 2nd time from the button on the worksheet,
lots of errors are generated in the program, as the arrays are full of
data from the first run. If "reset" is clicked in the VB editor, the
arrays empty & the code runs fine. Is there a way to code this
'reset' button & empty the arrays?

Cheers,
Kate