Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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 Charles wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
Is there a better way?
Erase ArrayName -- Jim "Charles" wrote in message oups.com... | 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 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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 "Charles" wrote in message s.com... | 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 | |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
You can use Redim
If you are dealing with large string arrays you may find it faster to Loop Regards, Peter T "Charles" wrote in message oups.com... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to reset to zero an array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the fastest way to copy a range to a 2D array? | Excel Programming | |||
Fastest way to find item in an array. | Excel Programming | |||
fastest sorting routine for 2-D array of long values | Excel Programming | |||
Can I reset an array to myarray() | Excel Programming | |||
Can I reset an array to myarray() | Excel Programming |