# VBA - Large Arrays are inefficient

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• Jun 16th, 2013, 12:50 PM
onair
VBA - Large Arrays are inefficient
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?
• Jun 16th, 2013, 12:52 PM
Norie
Re: VBA - Large Arrays are inefficient
How were you doing the calculations?
• Jun 16th, 2013, 01:00 PM
onair
Re: VBA - Large Arrays are inefficient
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
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
• Jun 16th, 2013, 01:15 PM
Rick Rothstein
Re: VBA - Large Arrays are inefficient
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...

Code:

```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...

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...

Code:

`arrRng = Range(3, 5).Resize(FinalRow - 2, FinalColumn - 4)`
and the assigning back of the array to this...

Code:

`Range(3, 5).Resize(FinalRow - 2, FinalColumn - 4) = arrRng`
• Jun 16th, 2013, 01:42 PM
Rick Rothstein
Re: VBA - Large Arrays are inefficient
Quote:

Originally Posted by 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...

Code:

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

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

Code:

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

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

Code:

`Range(3, 5).Resize(UBound(arrRng), UBound(arrRng, 2)) = arrRng`
• Jun 16th, 2013, 01:50 PM
onair
Re: VBA - Large Arrays are inefficient
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.
• Jun 16th, 2013, 02:25 PM
Rick Rothstein
Re: VBA - Large Arrays are inefficient
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.
• Jun 16th, 2013, 03:43 PM
Rick Rothstein
Re: VBA - Large Arrays are inefficient
Quote:

Originally Posted by 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/Te...ysInMemory.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)...
• Jun 16th, 2013, 06:13 PM
mirabeau
Re: VBA - Large Arrays are inefficient
@Rick,

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 ...
• Jun 16th, 2013, 11:55 PM
Rick Rothstein
Re: VBA - Large Arrays are inefficient
Quote:

Originally Posted by mirabeau
@Rick,
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/Te...oryRevised.xls