VBA - Large Arrays are inefficient

[onair]

Had to manipulate an array of 55 columns and 700 rows creating a new array of 5 columns and 700 rows summing 55 columns into 5 depending on the column header. Turned out to be extemely slow. Have to switch to the traditional Excel SUMIFs. Is it the number of columns that makes VBA arrays so inefficient?

 

[Norie]

How were you doing the calculations?

 

[onair]

First tried to output manipulated values one by one, it was slow, then tried to output manipulated values into another array and then into an Excel range, slow as well


Sub Calc()
Dim colISO As New Collection
Dim arrRng()
Dim arrISO()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalColumn = Cells(4, 5).End(xlToRight).Column
'Produce 5 columns for grouping values
For c = 5 To FinalColumn
On Error Resume Next
colISO.Add Cells(4, c), CStr(Cells(4, c))
On Error GoTo 0
Next c

'Create an array

ReDim arrRng(3 To FinalRow, 5 To FinalColumn)
ReDim arrISO(7 To FinalRow, 1 To colISO.Count)
For r = 3 To FinalRow
For c = 5 To FinalColumn
arrRng(r, c) = Cells(r, c)
Next c
Next r

'Group 55 columns into 5 (this is fast)
SumCost = 0
For r = 7 To FinalRow
For i = 1 To colISO.Count
SumCost = 0
Cells(6, i) = colISO(i)
For c = 5 To FinalColumn
If arrRng(4, c) = colISO(i) Then
SumCost = SumCost + arrRng(r, c)
End If
Next c

'Tried to output summed values one by one (extremely slow)
'Cells(r, i) = SumCost
'Put summed values into another array for further output into an Excel range (slow as well)
arrISO(r, i) = SumCost
Next i
Next r
Cells(7, 1).Resize(FinalRow, colISO.Count) = arrISO

End Sub

 

[Rick Rothstein]

You are creating the array the slow way. The fast way is to assign the range to a Variant variable... whole creation process is very fast and takes only one line of code. For example...

Dim arrRng As Variant
Const StartRow As Long = 3
Const StartCol As Long = 5
arrRng = Range(StartRow, StartCol).Resize(FinalRow - StartRow + 1, FinalColumn - StartCol + 1)

That's it, the entire range is now stored in arrRng as a two-dimensional array. One thing to note, though, is that the array's lower bound is always 1 for each dimension. This is not a problem because once you are done manipulating its elements, the entire array can be placed back in the worksheet (or a different worksheet) at the same starting point or at a different starting point, again, with just one line of code...

Range(StartRow, StartCol).Resize(FinalRow - StartRow + 1, FinalColumn - StartCol + 1) = arrRng

Note that I used the constants StartRow and StartCol to make it clear how the range is specified (I did not want that +1 to get lost). So, the first code section I posted can be simplified to this...

arrRng = Range(3, 5).Resize(FinalRow - 2, FinalColumn - 4)

and the assigning back of the array to this...

Range(3, 5).Resize(FinalRow - 2, FinalColumn - 4) = arrRng

 

[Rick Rothstein]

This is not a problem because once you are done manipulating its elements, the entire array can be placed back in the worksheet (or a different worksheet) at the same starting point or at a different starting point, again, with just one line of code...

Range(StartRow, StartCol).Resize(FinalRow - StartRow + 1, FinalColumn - StartCol + 1) = arrRng

.....
.....

and the assigning back of the array to this...

Range(3, 5).Resize(FinalRow - 2, FinalColumn - 4) = arrRng

Maybe a better way to do either of the above is like this...

Range(3, 5).Resize(UBound(arrRng), UBound(arrRng, 2)) = arrRng

 

[onair]

Rick, thank you for the reply.

>>Range(StartRow, StartCol).Resize(FinalRow - StartRow + 1, FinalColumn - StartCol + 1) = arrRng<<

This line of code above creates an original array of 55 columns and 700 rows - 55 x 700. Then I group (sum) 55 columns into 5 for each of the 700. My problem is putting the new range, 5 x 700, back into Excel. If i want to drop it as a range I would need to create a new array of the summed values or do it cell by cell. Both options are very slow.

 

[Rick Rothstein]

When you work with arrays, you only interact with the worksheet twice... once to assign the range to the array and once more to put the modified array back into the workbook... all manipulations of data are done using two nested loops. You might think iterating a huge number of cell values would be lengthy (and it would be if you had to go back to the worksheet with each iteration), but when everything is processed in memory, the process goes amazingly fast. Oh, and to pull out a smaller array of data from the larger array... you would Dim a Variant variable and then ReDim it as a two-dimensional array of the correct number of elements. Then iterate the big array filling the smaller array from it using whatever manipulation rules you want... then assign the smaller array to the proper range on the worksheet.

 

[Rick Rothstein]

When you work with arrays, you only interact with the worksheet twice... once to assign the range to the array and once more to put the modified array back into the workbook... all manipulations of data are done using two nested loops. You might think iterating a huge number of cell values would be lengthy (and it would be if you had to go back to the worksheet with each iteration), but when everything is processed in memory, the process goes amazingly fast. Oh, and to pull out a smaller array of data from the larger array... you would Dim a Variant variable and then ReDim it as a two-dimensional array of the correct number of elements. Then iterate the big array filling the smaller array from it using whatever manipulation rules you want... then assign the smaller array to the proper range on the worksheet.

Following up on the above, I made an example worksheet for you to examine. The data consists of 10,000 rows spread across 16 columns. The data is arranged in pairs of columns... Columns A, C, E, G, I, K, M, O contain one of 5 different country names randomly distributed throughout and Columns B, D, F, H, J, L, N, P contain a random number between 1 and 99 (all values are fixed now, but their original assignments were done using the RandBetween function). The code runs through all the country cells (80,000 of them) and sums up their respective numbers and then outputs the totals to Sheet2 starting in cell C3 (just to show the flexibility for outputting data). I left the timing code in so you could see how your own computer stacks up... I have a relatively new computer that is quite fast and it took 0.0178125 seconds to sum up the 80000 numbers from 1 to 99, divide them up into 5 sections (one sum per country) and output the results to the workbook. If you handle the arrays properly, any code using them should be quite, quite fast.

Here is the link to the file... http://www.rickrothstein.com/temp/TestingArraysInMemory.xls

Note: The file is about 2.5Megs in size if that should matter to you; also, you will need to enable macros once you open it)...

 

[mirabeau]

@Rick,

Couple of points about your demo

1. It overlooks the second row of your table in the sums, thus gives wrong result.

2. Certainly it's only an array demo to demonstrate speeds. But it does assume part of the result in advance, viz. which country names actually occur in the table? In many realistic applications of this sort of thing that would also need to be ascertained by the computer. With consequent increase in time taken. How much more time?

3. Really would have preferred to PM you on this, but seems you don't receive PMs, so ...

 

[Rick Rothstein]

@Rick,
Couple of points about your demo
1. It overlooks the second row of your table in the sums, thus gives wrong result.

Thanks for noting that (dumb mistake on my part). Here is a link to the revised file...

http://www.rickrothstein.com/Temp/TestingArraysInMemoryRevised.xls

The original link in Message #8 should also work (I modified the original file in addition to uploading a new copy with this new name), but if you download the file using that original link already and click the link again, your browser will more than likely give you the (incorrect) file from its cache rather than download the file again even though it has changed (the browser will not look inside the file to see it changed). So, you can either clear your browser's cache or click the link above instead.

Certainly it's only an array demo to demonstrate speeds. But it does assume part of the result in advance, viz. which country names actually occur in the table? In many realistic applications of this sort of thing that would also need to be ascertained by the computer. With consequent increase in time taken. How much more time?

Yes, you are right... it was a demo to show speed only... I did not want to burden the OP with trying to wade through the code that would be needed to determine the number of countries and their names on-the-fly while he was trying to absorb the code I used to implement the range arrays.

Thanks again for commenting... I really appreciated it.