Which is faster, copying range to another set of ranges or copying tomemory?

[axwack]

I am working with real time data and came across a problem where if
you the data is changing and you are trying to operate on the data,
you will get an automation problem. My solution, is to snapshot the
data, set it off to the side and then insert that data into a
database. This seems to work because a selection of range stops RTD
feeds.

I'm trying to get the fastest throughput possible. Would it be better
to operate on an in-memory array and then insert into the database or
copy/paste values to another set of cells and then operate on the
data?

 

[Jim Rech]

Would it be better to operate on an in-memory array and then insert intocopy/paste values to another set of cells and then operate on the data?

In memory ops are almost always faster than spreadsheet ops.

Sub SheetOp()
Dim StartTime As Double
Dim Counter As Long
StartTime = Timer
For Counter = 1 To 10000
Cells(Counter, 1).Value = Cells(Counter, 1).Value + 1
Next
MsgBox Timer - StartTime
End Sub

Sub MemoryOp()
Dim StartTime As Double
Dim Counter As Long
Dim Arr As Variant
StartTime = Timer
Arr = Range("A1:A10000").Value
For Counter = 1 To 10000
Arr(Counter, 1) = Arr(Counter, 1) + 1
Next
Range("A1:A10000").Value = Arr
MsgBox Timer - StartTime
End Sub

--
Jim
|I am working with real time data and came across a problem where if
| you the data is changing and you are trying to operate on the data,
| you will get an automation problem. My solution, is to snapshot the
| data, set it off to the side and then insert that data into a
| database. This seems to work because a selection of range stops RTD
| feeds.
|
| I'm trying to get the fastest throughput possible. Would it be better
| to operate on an in-memory array and then insert into the database or
| copy/paste values to another set of cells and then operate on the
| data?
|

 

[axwack]

copy/paste values to another set of cells and then operate on the data?

In memory ops are almost always faster than spreadsheet ops.

Sub SheetOp()
Dim StartTime As Double
Dim Counter As Long
StartTime = Timer
For Counter = 1 To 10000
Cells(Counter, 1).Value = Cells(Counter, 1).Value + 1
Next
MsgBox Timer - StartTime
End Sub

Sub MemoryOp()
Dim StartTime As Double
Dim Counter As Long
Dim Arr As Variant
StartTime = Timer
Arr = Range("A1:A10000").Value
For Counter = 1 To 10000
Arr(Counter, 1) = Arr(Counter, 1) + 1
Next
Range("A1:A10000").Value = Arr
MsgBox Timer - StartTime
End Sub

--

|I am working with real time data and came across a problem where if
| you the data is changing and you are trying to operate on the data,
| you will get an automation problem. My solution, is to snapshot the
| data, set it off to the side and then insert that data into a
| database. This seems to work because a selection of range stops RTD
| feeds.
|
| I'm trying to get the fastest throughput possible. Would it be better
| to operate on an in-memory array and then insert into the database or
| copy/paste values to another set of cells and then operate on the
| data?
|

Jim,

Thank you...you certainly put that one to bed.