Why the code is slow although using arrays

[YasserKhalil]

Hello everyone
I am trying to build a code but make it general .. That's because there are a lot of changes and new columns added and other columns may be changed in place. So I thought of using modVariables to put the columns numbers in variables and to be easier to follow and edit later
in modVariable I put these lines

Please Login or Register  to view this content.

And in ThisWorkbook module I put these lines

Please Login or Register  to view this content.

In modUtility I used some UDFs

Please Login or Register  to view this content.

Now when trying the following code that SUMs some columns, I found it takes about 5 seconds for only 7000 rows

Please Login or Register  to view this content.

The code is simple as it sums columns together and put the results in array
What makes the code slow...? I like to hear your ideas
I can't attach sample now but it is so simple and the data are just numbers ..

[Marc L]

Hi,

what takes time is writing data to cells …
How many time requires the execution to achieve the loop, so before updating data in cells ?

[bakerman2]

@ Marc

How many time requires the execution to achieve the loop

I know the answer to that question.

7000 rows x 12 jumps back and forth to UDF's per Row = 84000.

[YasserKhalil]

Thanks a lot for replies. What's the best alternative in such cases ..?
I guessed using arrays would help but it seems not too much.

[Marc L]

So compare the time execution with another code.

Avoid data type conversion like for example Val, can't you work directly with numbers rather than strings ?

[YasserKhalil]

Thanks a lot for the advice.. I will try to avoid VAL .. but is such a point may affect in that way?

[Marc L]

It's peanut in a normal code but if you are looping x times you multiply x times the data types conversion !
It's weird to work with arrays but not with well data types as before looping you can create once a data types conversion in an array
and use it within the loop without the need to convert anything ! In a recent thread needing some 'combinatorics engine'
I divided the time execution by seven just avoiding the data types conversion in the engine used within a loop …

[YasserKhalil]

Can you provide me with the link please to have a look?

[Marc L]

As only the more efficient code was posted so you can't compare with the first try.
It's just a question of obvious logic, the less operations within the loop, the faster …

Same question than post #2 : how many time execution the loop needs actually, 2-3 seconds ?

[YasserKhalil]

I don't know exactly how many time execution the loop needs actually!!
Maybe about two seconds

[Marc L]

According to your initial post, if the loop needs only 2 seconds so the data allocation from array to range needs 3 seconds !
And these 3 seconds can't be reduced or without any code just with Excel formulas if possible …

So for the process time execution you can try to create a data numeric array in order you do not ever need to use Val
- or any data type conversion statement - to compare how much time this way may save
but as the 3 seconds to allocate data to cells still remains …

[YasserKhalil]

Thanks a lot. I already was using formulas in the code using the traditional methods and this would take about 15 seconds so I tried to think in a different way..

[Marc L]

So you have divided the time by 3, that's nice, isn't it ?

[YasserKhalil]

Yes that's nice no doubt. But I am greedy and searching for more speed as I am sure this would be done in less than a second ..

[Marc L]

As per posts #2 & 11 how can it be done in less than a second as your array requires 3 seconds to be written to cells ?‼

[YasserKhalil]

I will try to do my best. I will not give up easily

[Marc L]

Using an array, you can only try to improve the time calculation …

[MrShorty]

I did not try to piece together all of your code into something, so I made something from scratch. A spreadsheet with about 7000 rows x 52 columns of random numbers (pasted as values), then ran code like this

Please Login or Register  to view this content.

The results of my test on my machine:
1st time -- read 350000 cells into array=90 ms, copy 12 columns into new array=20 ms, write 50000 cells to spreadsheet=50 ms.
2nd time -- read 350000 cells into array=90 ms, copy 12 columns with call to max function=700 ms, write 50000 cells to spreadsheet=50 ms.
3rd time -- read 350000 cells into array=90 ms, sum 12 pairs of columns=40 ms, write 50000 cells to spreadsheet=50 ms.
4th time -- read 350000 cells into array=90 ms, sum 12 pairs of columns with call to max function=1400 ms, write 50000 cells to spreadsheet=50 ms.

I obviously did not take the time to test every bottleneck or every other variation, but, from those 4 tests, it appears to me that call Excel's MAX() function adds a significant amount of time to the processing. You can use the Timer function in your own code similar to what I have done here to test for bottlenecks associated with your own project.

I might also add this observation. I built a spreadsheet only that used SUM(MAX(INDEX(...)),MAX(INDEX(...)),...) functions that performs a similar set of summations. That spreadsheet only approach took about 2 seconds to process 1E5 random records. I know most of your projects are VBA heavy, but, if processing time is important, it might be worth considering non-VBA approaches (worksheet functions in a spreadsheet or pivot tables or similar).

[YasserKhalil]

Thank you very much for this detailed reply. I recognize now that using Application.MAX is the cause and I implemented that in the code using the formulas and tried to implement that into the arrays and this gives me perfect results ..
Thank you very much for all of you