Reading worksheet values into arrays

September 6, 2008 – 8:10 pm by JP

I've been exploring more with arrays and working with them in code, and I found a page in the MS KB that gives some sample code: Sample Visual Basic macros for working with arrays in Excel (FYI this page is in the VBA search engine). I was inspired in part by some of the work I'd done previously with arrays, and also some newsgroup postings about interactions between VBA and Excel.

I thought I'd share some of the code with you in case you needed to do something in VBA with worksheet values, and wanted it done quickly and efficiently. Apparently, VBA and Excel don't necessarily work well together; worksheet calls in VBA are very costly (relatively speaking), as explained in this post. If your workbook primarily uses VBA to complete its purpose, you'll want to limit the amount of worksheet calls as much as possible; in a perfect situation, to just two: reading the input data into an array, and writing back the completed data in one shot.

That's where arrays come in. In response to a recent newsgroup posting, I posted the following code which reads the contents of a worksheet range into a VBA array:

plain text version

VBA:

Sub Read_Into_Array()
Dim arrData() As Variant
Dim ColACount As Long
Dim i As Long
ColACount = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Count
ReDim arrData(1 To ColACount, 1 To 2)
For i = 1 To ColACount
arrData(i, 1) = Range("A" & i).Value
arrData(i, 2) = Range("B" & i).Value
Next i
End Sub

But according to the MS KB article, all I really needed to do was this:

plain text version

VBA:

Sub Readinto_array()
Dim arrData() As Variant
arrData = Range("A1:B29").Value
End Sub

I tested it and it seems to work exactly the same. I'd be curious to know, memory and efficiency wise, how the arrData variable looks. The first sub strictly delimits its size, while the second one just pushes the range into it.

Enjoy,
JP

Share and Enjoy:

Print This Post |

Email This Post | Permalink |

Subscribe now!

Filed Under: Excel, VBA
Tags: array

This post has 532 views since September 6, 2008 – 8:10 pm.

8 Responses to “Reading worksheet values into arrays”
I don't think there's any difference between the two versions of arrData. The single line of code in the second procedure takes care of dimensioning the array using 1-based indexing.

The difference is how much time is used by the two approaches. With the second approach, reading an array into VBA is a little quicker, but writing an array back to the worksheet is a lot faster.

By Jon Peltier on Sep 7, 2008
What do you mean, reading data into an array is faster than writing the data to the worksheet, or that writing it back to the worksheet will be faster if I use the second method?

--JP

By JP on Sep 7, 2008
Reading is faster than writing, because you are not changing anything, just seeing what is there. Reading the entire range in one go is faster than reading cell by cell. Writing the entire range in one step is faster than writing sell by cell, and (I believe) the percentage gain is greater for writing than for reading.

By Jon Peltier on Sep 7, 2008
Instead of declaring arrData() as Variant, you can just declare arrData as variant. When you read the range into arrData, Excel automatically creates a two-dimensional array, with a base of 1, even if you use "Option Base 0" in the module.

Technically, arrData is NOT an array -- it's a Variant CONTAINING an Array, but you can use it exactly as though it were: using UBound, etc.
As Jon says, reading values from an range is somewhat faster using this technique, but the REAL efficiency comes when writing to a range.

As an experiment, take a 5000 cell range and fill it with dummy data. Compare the time it takes to read the data cell by cell into an Array, compared with using this technique.

You're not limited to values, by the way:

plain text version
VBA:
1. arrData = Range("A1:D5").Formula
(or .FormulaR1C1)

works, as well. Writing the array down to another location on the worksheet is sometimes superior to a Copy process.

Stan

By Stan Scott on Oct 23, 2008
Stan,

I like to use "Dim arrData() As Variant" instead of "Dim arrData As Variant" or even "Dim arrData" for a couple of reasons:

1- It's more consistent and a better habit to declare every variable, instead of relying on VBA to typecast it for you (which it will do 'As Variant' if you don't state explicitly what variable type you want), and

2- The parentheses helps remind me that I plan to use the variable to hold an array.

Thx,
JP

By JP on Oct 24, 2008
I see your point about #2, but since I AM declaring the variable by using "Dim arrData as Variant", but I don't see how point #1 applies -- whether you use arrData() = Variant or arrData = Variant, you're still declaring it as a Variant, so I don't believe there's any difference.

But there's a more important point. If you use the syntax I did, "arrData as Variant", you can then use the uBound and lBound methods on the resulting array. If you use "arrData() as Variant", then both uBound and lBound return 1, instead of the actual bounds.

By Stan Scott on Oct 24, 2008
Didn't know that! I'll try it out, but I'll assume you are correct.

Thx,
JP

By JP on Oct 24, 2008
It's important to realize that this technique, which is extremely useful by the way, is NOT really "reading into an array". When you use this technique, Excel isn't populating an array that you've already dimensioned. Using this technique, Excel "hands you back" an array, which is stored in a variable.

This is why you should use "Dim myVar as Variant". myVar is actually a variant-type variable that stores an array, rather than an actual array. On of the great things about the technique, though, is that you can TREAT myVar as an array: uBound(myVar) and lBound(myVar) return correct values, and you reference if with myVar(r,c) notation.

Stan

By Stan Scott on Oct 24, 2008