Optimize VBA Macro Summing Code

  • I need to do something similar to a mathematical summation. The code works fine, but its slow.

    I've tried some of the VBA Speeding up tips but they don't seem to help on this macro.

    Having 1000 rows takes about 16 sec, however having 2000 rows takes about 65 sec (4x the time), 3000 rows 148s (9x), and so on. I need to do it with almost 250.000 rows, and with the current code that would take almost 12 days.

    Any idea how to make this faster? Thanks!

    Here is the code

  • Re: Optimize Inneficient VBA Macro Sum Code


    regards/junho lee

  • Re: Optimize Inneficient VBA Macro Sum Code

    what blackjack is trying to do is following:

    ∑∑|Ai – Aj| with first summation is from 1 to count of row(1000) and second summation is also from 1 to count or row (1000)

    for 3 rows its like
    suma = |A1-A1| + |A1-A2| + |A1-A3|+ |A2-A1| + |A2-A2|+ |A2-A3| + |A3-A1| + |A3-A2| + |A3-A3|

    Small knowledge of Maths can help us to reduce time

    As |A1-A2| = |A2-A1|

    Suma = 2 * ∑∑|Ai – Aj| where i goes from 1 to 1000 while j goes from i+1 to 1000

    so for 3 row it will be

    suma = 2 * (|A1-A2| + |A1-A3|+|A2-A3|)

    Hope the logic is clear

    So the new code will be

    Hope this reduces time by 50% as 9 calculations initally has been reduced to 4

    there is nothing wrong in coding but blackjack was trying to do it hard way!!!
    espere que yo acertara blackjack

    [SIZE="4"]Anupam Shrivastava[/SIZE]

  • Re: Optimize Inneficient VBA Macro Sum Code

    One thing that speeds up the code is to process the data in memory, rather than reading from the sheet:

    It still doesn't scale too well, but processes 10 000 integer entries in ~10 seconds on my PC.

    What's your data like? Small integers, floating point numbers etc? There might be some additional optimization we could base on that.

  • Re: Optimize Inefficient VBA Macro Sum Code

    Minor correction:

    For i = LBound(data) To UBound(data) - 1

    ... though it won't change the result, because the inside loop won't execute when i = UBound(data)

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Optimize Inefficient VBA Macro Sum Code

    Thanks so much for the help!

    With the new code it takes about 6 seconds to do 10.000 rows (now its like 266x faster).


    The final code is

  • Re: Optimize Inefficient VBA Macro Sum Code

    I found an error with the optimized code...It doesn't work correctly with over 65.536 rows.

    I tried with 65.536 rows and works correctly, with 65.537 it will return a lower value -wrong- and take less than a second to make the calculation.

    Looks like it is some limitation with the Transpose function. I guess Office 2007 is still using the old 65.536 row-limited function?

    Any idea how to solve this issue?

    Thanks again!

  • Re: Optimize VBA Macro Summing Code

    I know .xls (2003 files) have the 65.536 rows limitation.

    I'm working with a .xlsm (2007 macro enabled Workbook) file, and I'm currently testing with about 68.000 rows.

    I ran this with my old macro (took about 24 hours), and now I tried with the new one, and I realized I got a different value. However, if I do the same with 1000 rows for example, I get the same result with both codes.

    The problem is, that for some reason, the new VBA code doesn't work properly if I do calculations with a range over 65.536 rows. I guess its something with the Transpose function.

  • Re: Optimize VBA Macro Summing Code

    Quote from Dave Hawley

    How do you know which result is correct?

    Because the sum with the 68.000 numbers is much faster and give a much smaller number than the one with 67.000 numbers, which is impossible. 1.2342e11 compared with 1.4546e14.

    With the original code (not optimized) the sum is bigger with the 68.000 number than with 67.000.

  • Re: Optimize VBA Macro Summing Code

    Like Anupam Shrivastava said.

  • Re: Optimize VBA Macro Summing Code

    The last code works perfect!!! what was the problem i don't get it??
    BlackJack it work very very fast!! and give the same number!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!