PDA

View Full Version : Fastest method to read data from sheet



troelsi
04-03-2008, 09:40 AM
Dear Experts!

I'm under the impression that it's faster to read data directly from a sheet rather than first loading those data in to arrays, and then read the data from the arrays when needed. However, I've made some experiments regarding this, and according to these experiments it seems that it is faster to first load the data into an array.

Here is what I've tryed:


'option 1
For Each c In Range("Q2:Q250")
If c.Value = i Then
LagerPf(c, k) = LagerPf(c, k - 1)
End If
Next c

'option 2
For l = 1 To 249
If Cells(l + 1, 17).Value = i Then
LagerPf(l, k) = LagerPf(l, k - 1)
End If
Next l

'option 3
For l = LBound(p) To UBound(p)
If p(l).linie = i Then
LagerPf(l, k) = LagerPf(l, k - 1)
End If
Next l


According to my experiments option 3 is the fastest way, Am I missing something? Any suggestions to a method that would be faster.

Thanks

mdmackillop
04-03-2008, 10:08 AM
Depending upon how you use the data

Dim arr, i As Long
arr = Range("A1:A300").Value
For Each a In arr
MsgBox a
i = i + 1
If i = 3 Then Exit Sub
Next

Norie
04-03-2008, 10:11 AM
It's the other way round - reading data to an array and manipulating the data is faster than working directly with the worksheet.

But, and perhaps this is only a personal thing, manipulating complicates the code.

PS I don't actually see anywehere that you are loading an array with data from a worksheet, am I missing something.:eek: