Reading worksheet values into arrays

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:

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:

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.


About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 9 Comment(s) on Reading worksheet values into arrays:

  1. 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.

  2. 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?


  3. 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.

  4. Stan Scott writes:

    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:

    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.


  5. 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.


  6. Stan Scott writes:

    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.

  7. Didn't know that! I'll try it out, but I'll assume you are correct.


  8. Stan Scott writes:

    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.


  9. Valtteri writes:

    Thanks for
    Dim arrData() As Variant
    arrData = Range("A1:B29").Value
    It works just great!!

    But is possible to get comments from Range(A1:A4000)? In some cells is comments in some not.


This article is closed to any future comments.
Excel School