Why could my macro slow down?

[Mathman]

Why could my macro slow down?


Hi


I have a large spreadsheet with millions of formulas with a macro that takes 8hrs to run a full cycle.


Everything for the macro has worked fine until I recently made so changes to check results which doubled the file size. Basically it runs threw a loop, calculates all the data in a new line then repeats the process about 1000 times. The data captured was 1 row and 450 columns wide, to capture the data I wanted for testing this was increased to about 6000 columns. The macro starts off fine but with each passing it slows a little until it gets close to 100 times threw then it starts to really slowdown. I have to stop the sheet, save it, close it which can take a while and the longer I let it run the longer it takes to close then reopen the sheet and continue the macro.


Since it worked fine before with less columns am I just pushing what excel can do. I run this on a 7700K OC to 5GHZ with 32 gigs or ran.


If you have any idea please let me know.


MM​

 

[SpillerBD]

Force Sheet Calculations to occur only when you need them. Hopefully you don't need them at all while waiting on all the loops.
Excel is far more efficient with more rows than columns. 6000 columns? That's seems insane!
"Basically it runs threw a loop, calculates all the data in a new line then repeats the process about 1000 times." All the data? in the entire worksheet or Row?
Can you share your code? or a description of what your code does in some detail?

 

[Mathman]

When the macro runs the 1 loop it's calculates all the data and it goes in it's memory, at the end of each loop that data is then dumped into a separate sheet at which point all the data is sorted. It then takes the data I selected and in 1 row that goes 6000 columns across. The process repeats 1000 to 1500 times. The macro is very long and detailed and it would be a challenge to explain properly. The main thing is that the macro does run fine but only started to slow down when I increased the data collected.


The file size is 120,000KB. I don't think its the macro since it runs fine with my regular file size of about 60,000KB. But this is the first time I've noticed the loop slow down as time goes on, one observation is that at the end of each loop the sheet will display "not responding" and this increases in time with each loop. Am I just trying to get to much out of excel and pushing it too far?


MM​

 

[SpillerBD]

A guess would be, you are probably have memory not being cleared.
So as the VBA runs per line, it sounds like memory usage should very light. Have you watch memory allocation through the Performance Tab of the Task Manager?

I had a macro that was thirty minutes long, till I figured a loop I didn't really need and now runs in about three minutes. Data array for that is 16 x ~80,000 (varies)

 

[offthelip]

Are you loading the entire sheet into a variant array on each iteration? or are accessing the worksheet multiple times on each iteration? The reason I ask is that this is often the cause of slow VBA?
I have posted about this many time on this forum so I have this paragraph ready to paste:

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range accessing one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then process the lines to a variant array and then write the array back to the worksheet, ( two worksheet accesses for each iteration that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.