Improving Calculation Performance in Excel–Part 3

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

image

image

image

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!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

2 Responses to Improving Calculation Performance in Excel–Part 3

  1. Doug Bliss says:

    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.

    • Andrew Chan says:

      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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: