Improving Calculation Performance in Excel–Part 3
January 9, 2011 2 Comments
Who doesn’t use array in Excel VBA can skip this article. But I guess most of us use array in VBA but not all of us understand that different array manipulation methods can impact the calculation performance. Below is another dump calculation that tried to sum the total for each row in 3 different approaches. The first one is my favorite, short and simple and I would try to avoid the last one because it would take longer time to type the code.
startTime1 = Timer()
For i = 1 To 1000000
Total = 0
For j = 1 To 20
Total = Total + Table(i, j)
Next
Next
endTime1 = Timer()
MsgBox sCalcType & " " & CStr(Round(endTime1 – startTime1, 6)) & " Seconds", _
vbOKOnly + vbInformation, "Array Manipulation"
startTime2 = Timer()
For i = 1 To 1000000
Total = Table(i, 1) + _
Table(i, 2) + _
Table(i, 3) + _
Table(i, 4) + _
Table(i, 5) + _
Table(i, 6) + _
Table(i, 7) + _
Table(i, 8) + _
Table(i, 9) + _
Table(i, 10) + _
Table(i, 11) + _
Table(i, 12) + _
Table(i, 13) + _
Table(i, 14) + _
Table(i, 15) + _
Table(i, 16) + _
Table(i, 17) + _
Table(i, 18) + _
Table(i, 19) + _
Table(i, 20)
Next
endTime2 = Timer()
MsgBox sCalcType & " " & CStr(Round(endTime2 – startTime2, 6)) & " Seconds", _
vbOKOnly + vbInformation, "Array Manipulation"
startTime3 = Timer()
For i = 1 To 1000000
Total = Col01(i) + _
Col02(i) + _
Col03(i) + _
Col04(i) + _
Col05(i) + _
Col06(i) + _
Col07(i) + _
Col08(i) + _
Col09(i) + _
Col10(i) + _
Col11(i) + _
Col12(i) + _
Col13(i) + _
Col14(i) + _
Col15(i) + _
Col16(i) + _
Col17(i) + _
Col18(i) + _
Col19(i) + _
Col20(i)
Next
endTime3 = Timer()
MsgBox sCalcType & " " & CStr(Round(endTime3 – startTime3, 6)) & " Seconds", _
vbOKOnly + vbInformation, "Array Manipulation"
Below are the results but I am not going to tell you what approach is the quickest. I want you to try the code yourself and see if you can reproduce similar results. You can see the difference between the quickest and slowest approaches is nearly 300%.
So next time you use array in VBA, think about how you should write the code because they are not the same in term of performance!
- Improving Calculation Performance in Excel–part 1
- Improving Calculation Performance in Excel–Part 2
- Improving Calculation Performance in Excel–Part 3
- Improving Calculation Performance in Excel–Final
Andrew Chan is the owner and founder of ALG Inc.
We help you to make better and faster decisions!
About Andrew Chan
Go Beyond Excel 
Another thing to consider is the data type of the table itself. When left as a variant, it can be more than twice as slow than a well-typed variable (i.e. Long). Avoiding variants (where possible) is common for most experienced programmers, but an inexperienced programmer may consider it ‘easier’ and nearly equivalent (one forum poster even said it is ‘superior’ because they considered it ‘future proofing’ their code – but experience tells me otherwise).
-Doug.
Good suggestion! This is another excellent point that I should cover but forgot. Thank for bringing it up.
Most business programmers have a full time job, e.g. accountant, financial analyst, HR analyst… etc. They prefer to spend their training budget on their full time job, I would do that as well. So when they come to VBA programming, their development strategy is CPM (copy and paste, then modify). They won’t try to understand what is behind the code as long as they have the right results. No one can blame them! It is not their job!