Fastest way to reset to zero an array

[Charles]

Hello

This is a relatively simple question but I can't find it on google
group. What is the fastest way to reset an array to zero (or the value
by default). (in this case, performance is key, every micro-second
counts!)

Intuitively, I would say that going through every single cell and
setting it to zero must be under-efficient, as VBA has to interpret a
let more instructions that if there is a function that already does
that. I was thinking to "redim". But zero-ing an array is not redim's
main purpose. Is there a better way?

Thanks
Charles

 

[Helmut Weber]

Hi Charles,

good question. In the end it might mean,
how to get rid of a once declared variable,
or how to free before allocated memory.

Don't know much about Excel.
But to redim an array of 10000000 strings
takes about 7 seconds here and now.
For only 1000000 strings, it takes 0.7 seconds.
Seems to be linear.

Not that the array wasn't dimensioned at all,
but redimensioned from the start.
Otherwise redim fails.

Sub Test056()
Dim t As Double ' time
Dim lCnt As Long ' a logn counter
ReDim sArr(1 To 1000000) As String
For lCnt = 1 To 1000000
sArr(lCnt) = Format(lCnt, "0000000")
Next
t = Timer
ReDim sArr(0)
MsgBox Format(Timer - t, "00.000") ' 0.7
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Dave Peterson]

Do you mean array or range? I'm confused about your use of "cell" in your
question.

But I'd start by looking at Erase in VBA's help (for an array--not for a range):

Dim myArr(1 To 3, 1 To 2) As Long
Dim iCtr As Long
Dim jCtr As Long

For iCtr = 1 To 3
For jCtr = 1 To 2
myArr(iCtr, jCtr) = iCtr * jCtr
Next jCtr
Next iCtr

Erase myArr

 

[Jim Rech]

Is there a better way?

Erase ArrayName

--
Jim
| Hello
|
| This is a relatively simple question but I can't find it on google
| group. What is the fastest way to reset an array to zero (or the value
| by default). (in this case, performance is key, every micro-second
| counts!)
|
| Intuitively, I would say that going through every single cell and
| setting it to zero must be under-efficient, as VBA has to interpret a
| let more instructions that if there is a function that already does
| that. I was thinking to "redim". But zero-ing an array is not redim's
| main purpose. Is there a better way?
|
| Thanks
| Charles
|

 

[Peter T]

Do you mean a VB array, as suggested by the subject line, or a range of
cells as implied when you say "going through every single cell"

If a VB array you can ReDim the array or use the Erase function. Both
methods will reset the array without need to iterate each element.

However with large arrays that you will want to use again before they go out
of scope, surprisingly it can be faster to loop through the array resetting
default value, "" with string arrays, 0 with number arrays or Empty if a
Variant array.

If you are talking about worksheet cells, to enter 'zero' or any other value
into multiple cells simply
rng.Value = 0 ' same as Ctrl-Enter

or clear / clearcontents

Regards,
Peter T

 

[Charles]

Sorry, I meant a VBA array. And I don't want to "delete" the array to
free up memory but to reset it to zero (or whatever is the initial
value) in order to work again with a clean array. Which seems to be
what "erase" does. There seems to be different opinions on this
newsgroup! Would you say "erase" or a loop going through every single
component of the array is faster?

Charles

 

[Jim Rech]

but to reset it to zero (or whatever is the initial value)

That's exactly what the Erase statement does to an array. From Help:

"Reinitializes the elements of fixed-size arrays and releases dynamic-array
storage space."


--
Jim
| Sorry, I meant a VBA array. And I don't want to "delete" the array to
| free up memory but to reset it to zero (or whatever is the initial
| value) in order to work again with a clean array. Which seems to be
| what "erase" does. There seems to be different opinions on this
| newsgroup! Would you say "erase" or a loop going through every single
| component of the array is faster?
|
| Charles
|

 

[Charles]

Ooops. Just saw that erase won't do the trick. Most of my arrays are
dynamic and in that case erase will not only reset the elements of the
array but free up the memory.

I guess I should go for the loop then.

 

[Peter T]

You can use Redim

If you are dealing with large string arrays you may find it faster to Loop

Regards,
Peter T

 

[Charles]

No mostly arrays of double.

Also I did a surprising test. It looks like doing
for i = 1 to 10000
x=x+1
next i
for i = 1 to 10000
x=x+1
next i

is taking 10% longer than
for i = 1 to 10000
x=x+1
x=x+1
next i

which seems to back my suspicion that giving more code to interpret is
taking longer even if at the end of the day the code did the same
number of operations.

 

[Charles]

Actually I should have tested it myself first. Redim is spectacularly
faster than a loop:

with the following code I get loop: 73.19s, redim: 0.95s

Sub tttt()
Dim A() As Double
ReDim A(1 To 10000)
Dim T1 As Double, T2 As Double, t As Double
Dim i As Long, j As Long
For i = 1 To 100000
t = Timer
For j = 1 To 10000
A(j) = 0
Next j
t = Timer - t
T1 = T1 + t
t = Timer
ReDim A(1 To 10000)
t = Timer - t
T2 = T2 + t
Next i

MsgBox Round(T1, 2) & " s vs " & Round(T2, 2) & "s"
End Sub