Efficiency vs functionality in range-type array and non-type array assignment with Range

Question

I am aware that this;

Arr() =Range("E2:X2500")

...then doing stuff with the Arr, and then dumping back using:

Range("E2:X2500")=Arr()

is many folds more efficient (faster) that looping through and referencing cells directly. It's light speed!

But, this range-to-array assignment only grabs cells' value. Is there a way to assign an actual range (continuous or not) into an array (with the same light speed) in such a way that you could then treat array items the way you would refer to cells, like:

arr(23).row 'getting a row number

Or;

If Arr(23).Value ="Pending" then arr(23).font.bold=1 else arr(23).font.bold=0

I know i can dim a range-type array where each item can store an actual single cell range. But this array cannot be handled the same way - with one liner assignment:

Dim Arr () as Range
Set Arr = Range("E2:X2500") 'error

Instead, I would need to iterate each cell and assign it to the next item in the range-type array, which would allow me to treat items the way I'd refer to cells, but take substantially longer to load as I'm dealing with a Loop.

Also how would I dump a range-type array back into the sheet with the same ease and effectiveness of the one liner assignment? I think the only way would be to use a loop yet again, correct?

Side question : Speedwise, is it any better to refer to cells via a range-type array over referring to cells directly via the sheet, or are both basically the same?

Thanks!

Answer 1

Well, the array use will save a lot of code running time. But, there are some issues which must be understood:

1. First thing when work in VBA and your project increases, is to properly declare your variables. Try making a reflex in putting Option Explicit on top of all your modules. In the array case, the thing, from this point of view stays like that:

   Dim Arr() As variant, arr1 As Variant

   Both declarations work in excel. But the second one is recommended (on mai taste), when you need an array from a range. When you want building a, let us say, result array, it will be zero based and you must take care of the range size where the values will be returned.

2. The array content cannot be retrieved exactly like you tried in your question in case of not fix/known number of elements. Look at the next test code:

   Sub testArrays() Dim sh As Worksheet, rng As Range, arrTest As Variant Set sh = ActiveSheet Set rng = sh.Range("A1:F4") arrTest = rng.value sh.Range("J1").Resize(UBound(arrTest, 1), UBound(arrTest, 2)).value = arrTest End Sub

It is recommended to use arrTest = sh.Range("A1:F4").value. Using range Value. Excel is able to understand what you need according to your declaration, but it is good for you to differentiate somehow, from the way of the range definition.

Sometimes, you need to build an array during analyzing of the dynamic range. If you cannot know the new array dimensions and need to Redim (Preserve), only the second dimension of the array can be re-dimensioned and Transpose function must be use, in such a case. And finally the resulted array can be properly loaded in a range, only if you know the array number of rows and columns.

3. You can deduce the range row, from the array row, in the next way: If we are referring to the above arrTest we know that its first row is first row of the sheet and it has 5 columns.

So, arrTest(3, 1) will be sh.Range("A3").Value and its row would be 3.

Then, arrTest(3, 4) will be sh.Range("D3").Value and its row would be also 3.

If your array comes from a range starting with the fifth row, you must add four in order to obtain the sheet row extracted from the array row...

So, your example can be transformed in:

If arrTest(3, 4) ="Pending" then sh.Cells(3, 4).Font.Bold=1 Else sh.Cells(3, 4).Font.Bold=0

4. Now if you need a ranges array, you cannot do it in the way you tried. You must use the ranges address and build the range at the end:

   Sub testArraysBis() Dim sh As Worksheet, rng As Range, rng1 As Range, lastCol As Long Dim rng2, arrTest As Variant, arrT As Variant, arrF As Variant Set sh = ActiveSheet lastCol = sh.Cells(1, Cells.Columns.Count).End(xlToLeft).column Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(4, lastCol)) Set rng1 = sh.Range("A5:F6") arrT = Array(rng.Address, rng1.Address) arrTest = rng.value Debug.Print UBound(arrTest), LBound(arrTest) sh.Range("J1").Resize(UBound(arrTest, 1), UBound(arrTest, 2)).value = arrTest Set rng2 = sh.Range(arrT(0)) Debug.Print rng2.Address arrF = sh.Range(arrT(0)).value Debug.Print UBound(arrF, 2) End Sub

rng2 range will be built using the address string, extracted from arrT array. An array (arrF) can also be extracted from the arrT first element...

5. Epilog: The best way, in terms of speed, is to load the range in arrays, make all processing using them (in memory and very fast due to this aspect), but the most important issue is to build another array (or even a range, using Union) and retrieve the data AT ONCE. Sending of each partial processing result to a cell/range consumes a lot of time and other resources, for a big range size...