Using Range Array 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 not get it to work in this type of application. I want to keep a number of ranges in a specific order as a "master copy". Then I can add, delete, sort, or do anything in this array, and then just print it to a range of ranges in excel. Excel doesn't seem to support this - it just forces you to store your data in a spreadsheet, and you have to reread it in order to use it.

Answer 1

No, arrays cannot contain objects. But o Objects may contain objects. I think you might need a Range object that consists of another specific Range object. In this example, rMaster is my "array" that contains 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 knowledge found that arrays can contain ranges (thnx jtolle), here is an example of how you will 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 2

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

If you need a collection, why not use a VBA collection object?

 Dim myRanges as New Collection 

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

The Range object does not contain data ; it contains a link to the cells in the worksheet. If you want the contents of a range in your collection, you will have to copy them to a worksheet.

As in Java, your VBA variables are ephemeral, whether in an array or in a collection. If you want to close the file and have data there, when you open it again, you must have it in the cells of the worksheet. Worksheets are your save mechanism.

I'm going to make a big jump here, so if I leave, ignore me. I think you are looking for a proposal to create a separate sheet as your "database" populated with List / Table objects with your raw data. Before that, this is your “user sheet”, where you make interesting material, referring to the data in the database sheet. Name it all.

Answer 3

It is not entirely clear to me what you are talking about.

If you ask about the ability to create Range , which have nothing in common and exist on their own, then no, there is no way. A Range object is simply something that refers to a specific area of ​​the worksheet. It does not have its own repository or anything else. Several different instances of the Range class may also refer to the same area of ​​the worksheet.

And if you just want to keep some links in an array, then that's fine, go for it. The only problem with your code is that you do not initialize the elements of the array before using them: since Range is a reference type, all elements are initialized with Nothing by default.