Using an Array of Ranges in VBA - Excel

Question

Does VBA support using an array of range variables?

dim rangeArray() as range
dim count as integer
dim i as integer

count = 3

redim rangeArray(1 to count)

for i = 1 to count
  msgbox rangeArray(i).cells(1,1).value
next

I can't get it to work in this type of application. I want to store a series of ranges in a certain order as a "master copy". I can then add, delete, sort or do whatever to this array and then just print it out to a series of ranges in excel. It doesn't seem like excel supports this - it just forces you to store your data in the spreadsheet and you have to reread it in order to use it.

Answer 1

It's not completely clear for me what you're talking about.

If you're asking about an ability to create Ranges that map to nothing and exist on their own, then no, there's no way. A Range object is just something that refers to a certain area of a worksheet. It doesn't have any storage of its own or something. Several different instances of Range class can refer to the same worksheet area, too.

And if you just want to store some references in an array, then that's fine, go for it. The only problem with your code is that you don't initialize the array elements before using them: as the Range is a reference type, all elements get initialized with Nothings by default.

Answer 2

No, arrays can't hold objects. But oObjects can hold objects. I think what you may want is a Range object that consists of various specific other Range object. In this example, rMaster is my "array" that holds three cells.

Sub StoreRanges()

    Dim rMaster As Range
    Dim rCell As Range

    Set rMaster = Sheet1.Range("A1")
    Set rMaster = Union(rMaster, Sheet1.Range("A10"))
    Set rMaster = Union(rMaster, Sheet1.Range("A20"))

    For Each rCell In rMaster
        MsgBox rCell.Address
    Next rCell

End Sub

With my new found knowledge that arrays can hold ranges (thnx jtolle), here's an example of how you would store ranges in an array and sort them

Sub UseArray()

    Dim aRng(1 To 3) As Range
    Dim i As Long

    Set aRng(1) = Range("a1")
    Set aRng(2) = Range("a10")
    Set aRng(3) = Range("a20")

    BubbleSortRangeArray aRng

    For i = LBound(aRng) To UBound(aRng)
        Debug.Print aRng(i).Address, aRng(i).Value
    Next i

End Sub

Sub BubbleSortRangeArray(ByRef vArr As Variant)

    Dim i As Long, j As Long
    Dim vTemp As Variant

    For i = LBound(vArr) To UBound(vArr) - 1
        For j = i To UBound(vArr)
            If vArr(i).Value > vArr(j).Value Then
                Set vTemp = vArr(i)
                Set vArr(i) = vArr(j)
                Set vArr(j) = vTemp
            End If
        Next j
    Next i

End Sub

Answer 3

It's not entirely clear what you want to do, but...

If you want a collection, why not use a VBA Collection Object?

Dim myRanges as New Collection

A Collection.Item can be any object, including a Range.

A Range object doesn't hold data; it holds a reference to worksheet cells. If you want the Range contents in your collection, you'll have to copy them to and from the worksheet.

As with Java, your VBA variables are ephemeral, whether in an Array or Collection. If you want to close the file and have the data there when you open it again, you have to have it in worksheet cells. The worksheets are your persistence mechanism.

I'm going to take a big leap here so if I'm way off, ignore me. What I think you're looking for suggests setting up a separate worksheet as your "database", populated with List/Table objects holding your raw data. In front of that, is your "user sheet" where you do the interesting stuff, referring to the data in the database sheet. Name everything.