Reading and writing ranges

Many VBA tasks involve transferring values either from an array to a range or from a range to an array. For some reason, Excel reads from ranges much faster than it writes to ranges. The WriteReadRange procedure that follows demonstrates the relative speeds of writing and reading a range.

This procedure creates an array and then uses For-Next loops to write the array to a range and then read the range back into the array. It calculates the time required for each operation by using the Excel Timer function.

Sub WriteReadRange() Dim MyArray() Dim Time1 As Double

Dim NumElements As Long, i As Long

Dim WriteTime As String, ReadTime As String

Dim Msg As String

NumElements = 60000

ReDim MyArray(1 To NumElements)

' Fill the array

For i = 1 To NumElements

' Write the array to a range Time1 = Timer For i = 1 To NumElements

Next i

WriteTime = Format(Timer - Time1, "00:00")

' Read the range into the array Time1 = Timer For i = 1 To NumElements

ReadTime = Format(Timer - Time1, "00:00")

' Show results

MsgBox Msg, vbOKOnly, NumElements & " Elements" End Sub

On my system, it took 12 seconds to write a 60,000-element array to a range but only 1 second to read the range into an array.

0 0

Post a comment