Excel VBA, Arrays and Ranges

When modifying worksheets and ranges, one option would be to modify the worksheet directly using the commands I’v explained in the article Referencing Ranges In Excel Using VBA but that would be very inefficient. Every time you read or write something to an excel sheet, you will be consuming considerable resources. A great way to improve your codes efficiency would be to minimize reading and writing to and from the excel sheet by following these steps:

  1. Read all the data you need from the worksheet to an array (using one call).
  2. Make all modifications required to that array.
  3. Print the data from the array to the worksheet (using one call).

In this article I will explain how this can be done.

Jump To:

Copying Data from a Range to Array:

In this example I will use the sheet below:

Excel Sheet With Data

The code below copies the the data from A2:C11 to an array. For more information about referencing ranges please see Referencing Ranges In Excel Using VBA:

Sub Example1()
'dynamic array
Dim arrValues() As Variant
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub

Keep in mind that whenever you copy data from a range to an array (even if the range is a single column) , you will end up with a 2D array. The arrays index will start at “1”.

Common Errors When Copying Data from a Range to an Array:

The array must be declared dynamic. Using a static array will trigger an error. The code below will trigger a Compile Error Can’t Assign to Array Error:

Sub Example2()
'static array
Dim arrValues(1 To 10) As Variant
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub

Another thing to keep in mind is that the array’s data type must be declared variant. The code below will trigger a Run-time error ’13’: Type mismatch Error:

Sub Example3()
'string type array
Dim arrValues() As String
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub

Processing the Array:

After copying the required data from the range to the array, you can make the required changes to the array. The code below changes the array of variant data type to an array of strings. Although keep in mind that this conversion is not always necessary.

Sub example4()
Dim arrValues() As Variant
'the string array
Dim arrStrings() As String
Dim i As Integer
Dim j As Integer

arrValues = Range(Cells(2, 1), Cells(11, 3))
'resising the string array to match the array of values
ReDim arrStrings(1 To UBound(arrValues), 1 To UBound(arrValues, 2))
For i = 1 To UBound(arrValues)
For j = 1 To UBound(arrValues, 2)
arrStrings(i, j) = arrValues(i, j)
Next j
Next i

End Sub

The following code transposes the array of data

Sub example5()
Dim arrValues() As Variant
'the transposed array
Dim arrTransposed() As String
Dim i As Integer
Dim j As Integer

arrValues = Range(Cells(2, 1), Cells(11, 3))
'resising the transposed array to match the array of values
ReDim arrTransposed(1 To UBound(arrValues, 2), 1 To UBound(arrValues))
For i = 1 To UBound(arrValues)
For j = 1 To UBound(arrValues, 2)
arrTransposed(j, i) = arrValues(i, j)
Next j
Next i

End Sub

Copying Array Values to a Worksheet Range:

The code below creates a 10X3 array with the same data as the example above. It then copies the array to the range “A2:C11”. For more information about referencing ranges and working with strings please see Referencing Ranges In Excel Using VBA and VBA Excel String Processing and Manipulation

Sub example6()
Dim arrValues(1 To 10, 1 To 3) As Variant
Dim i As Integer
For i = 1 To 10
arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i

Range("A2:C11") = arrValues
End Sub

The result:

Copying Array to Range

Note that when copying an array to a range, the array data type does not need to be a variant. We could have used the following code as well:

Sub example7()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i


Range("A2:C11") = arrValues
End Sub

Also had we chosen “A2:C5” instead of a range matching the array size, the extra rows would have been omitted:

Sub example8()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i


Range("A2:C5") = arrValues
End Sub

The result:

Copying Array to Range (Omiting Rows)Had we chosen a range larger than the array dimensions, the extra rows would have been copied as “NA”:

Sub example9()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i
Range("A2:C13") = arrValues
End Sub

Copying Array to Range (Extra Rows)

See Also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

3 thoughts on “Excel VBA, Arrays and Ranges”

  1. Marcin says:

    Pedrum,

    Prior to reading your article I’ve rewritten my macro from using ranges to array. My source array has 90k+ rows and my output array has even more rows. While using ranges my code execution time was circa 120-240 minutes depending on machine. Now execution time is max 1 minute.

    To all your reader: I strongly encourage you to use this technique. It requires more programming skills (not easy debugging, variable watch and immediate commands) but after few hours of development it will pay off.

    Cheers

    Marcin

    1. pedrumj says:

      Hi there

      I’m glad to see the article has helped you out 🙂

  2. Dann says:

    One suggestion – it’s better IMHO to use the LBound and UBound if you are using a simple For loop:
    For i = 1 To UBound(arrValues)
    I see u mixed here a bit of both :). See example here: http://www.analystcave.com/vba-vba-array/#Sizing_and_moving_through_VBA_Arrays
    Using the LBound/UBound always is a good practice as it prevents errors in traversing the array. Additionally many people forget how to get the size of an array: UBound(array) – LBound(array) + 1 (not just UBound(array) + 1!!) or Application.CountA(array).

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy