writing range contents to arrays; best way?

[Matthew Dodds]

I need to dump the contents of an Excel range into an array.
What's the best way? Assuming best means fastest.

The possibilities identified by this newbie are
1. use a name in a header cell to define a range rng and then assign
variant=rng.value

Set rngBCCol = wsh.Range(Range("barcode").Offset(1),
Range("barcode").End(xlDown))
varBCCol = rngBCCol.Value

1b. as 1 but varBCCol = rngBCCol

2. don't bother with rng variable just define using with/end with

With wsh.Range("barcode").Offset(1)
varBCCol = Range(Cells(1), .End(xlDown)).value
End With

2b. as 2 but without .Value

3. don't bother with the name at all but use the contents of the header
cell

With wsh.Cells.Find(what:="barcode")
varBCCol = Range(.Offset(1), .End(xlDown)).Value
End With

3b. as 3 but without .Value


Inspired by recent examples on this forum I attempted to answer the
question myself using the code
below. This proved nothing other than that the code is good at random
number generation. (A sample of
the times is added below)

Two questions, then.
1. Which IS the best way?
2. Why did the code below prove so uninformative, with virtually no
consistency in time taken by each approach from one run to another?

Thanks in advance

Mat
Test results
Ran 7x with MAXITER of 1000
1 2 3 4 5 6 7
test_1a.value 0.2891 0.5000 0.4688 0.2617 0.6016 -0.1016 0.6484
test_1a 0.6797 0.8281 0.8008 0.5898 -0.0703 0.2305 -0.0195
test_1b.value 0.0586 0.2188 0.1797 -0.0313 0.3203 0.6094 0.4805
test_1b 0.4492 0.6016 0.5703 0.3008 0.6992 0.0000 0.9219
test_1c.value 1.3789 0.4297 0.3906 1.1211 0.5313 0.8203 0.7383
test_1c 1.2617 1.2500 1.2188 0.9492 0.3516 0.6406 0.5586

ave stdev %CV
test_1a.value 0.3811 0.2574 67.5388
test_1a 0.4342 0.3821 88.0085
test_1b.value 0.2623 0.2265 86.3591
test_1b 0.5061 0.2958 58.4393
test_1c.value 0.7729 0.3684 47.6642
test_1c 0.8901 0.3745 42.0702
test_1c 0.8901 0.3745 42.0702

Test code
Requires wkb.wsh("arrayDefinition") with single column of data headed
'barcode' for which a name 'barcode' has been defined.

Option Explicit
Sub runThisFunction()
Dim wkb As Workbook
Dim wsh As Worksheet

Set wkb = ThisWorkbook
Set wsh = wkb.Worksheets("arrayDefinition")

Call rangeToArray(wkb, wsh)

End Sub
Function rangeToArray(wkb As Workbook, wsh As Worksheet)

Dim rngBCCol As Range
Dim varBCCol As Variant
Dim i As Integer
Dim l_timer As Long
Const MAXITER = 1000

Debug.Print "" & vbCr

l_timer = Timer
For i = 1 To MAXITER
v
Erase varBCCol
Next
Debug.Print Timer - l_timer & " test_1a.value"
l_timer = Timer
For i = 1 To MAXITER
Set rngBCCol = wsh.Range(Range("barcode").Offset(1),
Range("barcode").End(xlDown))
varBCCol = rngBCCol
Erase varBCCol
Next
Debug.Print Timer - l_timer & " test_1a"
l_timer = Timer
For i = 1 To MAXITER
With wsh.Range("barcode").Offset(1)
varBCCol = Range(Cells(1), .End(xlDown)).Value
Erase varBCCol
End With
Next
Debug.Print Timer - l_timer & " test_1b.value"
l_timer = Timer
For i = 1 To MAXITER
With wsh.Range("barcode").Offset(1)
varBCCol = Range(Cells(1), .End(xlDown))
Erase varBCCol
End With
Next
Debug.Print Timer - l_timer & " test_1b"
l_timer = Timer
For i = 1 To MAXITER
With wsh.Cells.Find(what:="barcode")
varBCCol = Range(.Offset(1), .End(xlDown)).Value
Erase varBCCol
End With
Next
Debug.Print Timer - l_timer & " test_1c.value"
l_timer = Timer
For i = 1 To MAXITER
With wsh.Cells.Find(what:="barcode")
varBCCol = Range(.Offset(1), .End(xlDown))
Erase varBCCol
End With
Next
Debug.Print Timer - l_timer & " test_1c"
End Function

 

[Tom Ogilvy]

Anytime you get a negative number when timing a procedure, you have to
figure you are not doing something right.

Timer has a resolution of about 1 second as I recall.

See this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;172338
How To Use QueryPerformanceCounter to Time Code