Speeding Up Of A Macro

[bloubul]

Hi All

How do one speed up the running of any macro.
I have some macro's that runs forever and a bit before they are done.
I only uses one spreadsheet with macro's at a time.

Or is there a extra code that needs to be added to the macro to give it some juice.

BlouBul

 

[Frank]

Hi All

How do one speed up the running of any macro.
I have some macro's that runs forever and a bit before they are done.
I only uses one spreadsheet with macro's at a time.

Or is there a extra code that needs to be added to the macro to give it some juice.

BlouBul

Its impossible to answer that question fully without knowing what the macro does.
It’s good practice to make sure only ONE spreadsheet ( of ANY kind macro or no macro) is loaded into your PC if it has macros.

Some macros just read data from a spreadsheet, which takes a little time, store it and manipulate data without further reference to the spreadsheet.
One can make sure the spreadsheet doesn’t recalculate by turning that feature off whilst the macro runs. One can also turn off screen updating so one sees a frozen screen while it runs.
When the number crunching is done they output the results to the spreadsheet ( whilst the screen is frozen) and that adds a little more time. After which screen updating and recalculation is turned back on. Generally a few seconds is all that is required for this type of macro, start to finish.

The slowest macros are those which change a spreadsheet value, then wait for the spreadsheet to recalculate all the new values, read the new values created from the spreadsheet and store them, then change the spreadsheet value again , wait for the spreadsheet to recalculate .... and so on over and over again possibly hundreds of times. If it takes one second for the sheet to recalculate, and it’s doing it 1000 times then that’s at least 1000 seconds it will take to run. 20 minutes!
The recalculation time of a spreadsheet depends on how many cells need to update to correctly service the macro reading it, ( could depend on how many lotto results are in your database if they are all needed in the calculations), whether your spreadsheet is up to date or in compatibility mode and on the speed of your processor.

Many of my macros are of the second type, as I’m primarily a spreadsheet writer and use macros as a last resort. Why write VBA to do what Excel does by design? I don’t have time or inclination to write macros that do the work of the spreadsheet.

The last spreadsheet I uploaded was the curved ball analysis for the UK lotto. The xlsm version with 2369 draws to analyse with 11 different consecutives sought and 300 different skips ( 3300 recalculations of the sheet) tested took 2minutes 20 seconds to run. I find that acceptable.

Strangely, the xls version of exactly the same sheet with exactly the same number of draws took just 1 minute to run.
Why ? I haven’t the slightest idea - that’s Microsoft for you.
Every macro that relies on spreadsheet recalculation can be speeded up by minimising formulas in unnecessary areas, sticking only to those actually needed for the task in hand.

So the key is keep the number of complex formulas small, keep the number of results being worked on small, avoid spreadsheet recalculation if possible and screen updates within the macro, run only one spreadsheet at a time and get a fast processor. Easier said than done, but quicker than doing it manually.

 

[bloubul]

Frank

Thank you for the in depth explanation of the workings of a macro.
I will use the info to speed up my macro's.

BlouBul

 

[Frank]

Frank

Thank you for the in depth explanation of the workings of a macro.
I will use the info to speed up my macro's.

BlouBul

Just to prove a point, you might like to try these 2 macros on a BLANK spreadsheet. With no other spreadsheet loaded into Excel. they are WRITELIST and WRITELIST2
The code in them is identical except that the second macro has code which disables both screen updating and spreadsheet recalculation during the macro run.

All each macro does is write 1000 lines of arithmetic to the first three columns of a spreadsheet. There is a test to see if the number in the first column is a multiple of 10, to decide what to do in column 3. What it does is not important, its nonsense just to give Excel some work to do. What is important is HOW LONG IT TAKES. Both macros have timers built in so you can compare.

Notice the difference between the one that recalculates every time it writes to the spreadsheet and the one that doesn't ?

Now try the same experiment with a large spreadsheet ALSO loaded into Excel, just sitting in the background.

When you run the first macro you might want to go and make a cup of coffee ...
When you run the second one, you wont have time to.

Try it and see..


Sub WRITELIST()
Dim i As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
StartTime = Timer
Columns("A:C").Select
Selection.ClearContents

Range("a1").Select
For i = 1 To 1000
ActiveCell.Offset(i, 0).Value = i
ActiveCell.Offset(i, 1).Formula = "=RC[-1]*R[-1]C[-1]"
If i Mod 10 = 0 Then
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-2]"
Else
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-1]"
End If
Next


'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub


Sub WRITELIST2()
Dim i As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
StartTime = Timer
Columns("A:C").Select
Selection.ClearContents


Range("a1").Select

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 To 1000
ActiveCell.Offset(i, 0).Value = i
ActiveCell.Offset(i, 1).Formula = "=RC[-1]*R[-1]C[-1]"
If i Mod 10 = 0 Then
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-2]"
Else
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-1]"
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

 

[Frank]

Strangely, the xls version of exactly the same sheet with exactly the same number of draws took just 1 minute to run.[/COLOR]
Why ? I haven’t the slightest idea - that’s Microsoft for you.

Going back to curvedballAnalysis_lite, where the xlsm version was 922kb and the macro took over 2 minutes to run whilst the .xls version was 1.8mb in size but the macro took only 1 minute to run. The sheets are otherwise identical.

I decided to 'upgrade' the .xls version to 'macro enabled' version and save as a new xlsm file. This cured the problem. The new xlsm file now runs its macro in 1 minute!

Explain that!

 

[bloubul]

Frank

I thank you, I will look at it over the weekend as I'm off and have time.

BlouBul

 

[bloubul]

Frank
Well I'm lost for words, even with the xls curvedballAnalysis_lite open, the macro's are finished before you can blink an eye, I tried it with some multiple spreadsheets open, but no slowing down.

Are there a small timer code that can be added to any macro or not.

Thank You Sir.

BlouBul

 

[Frank]

Frank
Well I'm lost for words, even with the xls curvedballAnalysis_lite open, the macro's are finished before you can blink an eye, I tried it with some multiple spreadsheets open, but no slowing down.

Are there a small timer code that can be added to any macro or not.

Thank You Sir.

BlouBul

Well I'm gobsmacked at your reply Bloubul. It would have been helpful if you'd quoted the run times, thats why there was a timer included.

For your information, on my High spec i7 PC running 64 bit excel the times are :-

No other spreadsheet loaded.
Writelist 1........ 3.22 seconds
Writelist 2........ 1.45 seconds

With CurvedballAnalysis_lite loaded
Writelist 1........ 58.14 seconds
Writelist 2........ 1.45 seconds

With another large spreadsheet loaded
Writelist 1........ 770.06 seconds ( almost 13 minutes)
Writelist 2........ 1.77 seconds

So I stand by my earlier comments.

I cant believe you you'd want to slow down a macro, one way is to add a wait command inside the loop
Application.Wait (Now + TimeValue("0:00:10")) is a 10 second pause.

There are also ways of pausing for less than a second, ( so many milliseconds). The code is different for Excel 32 bit and Excel 64 bit, you can google that if you are interested.

Has anyone else tried therse macro speed tests ? How long did they take ?

 

[bloubul]

Frank

The last thing I want to do is to slow down the macro, I prefer speed.
I cannot remember all of the previous spreadsheets that I've used, but this time I can.

The first sheet contains 6 macro's
Second sheet contains 7 macro's,
Third sheet contains 4 macro's
Fourth sheet contains 3 macro's
Fifth sheet contains 6 macro's
Six sheet contains 6 macro's.
You can draw your own conclusions

Here are the screen dumps of the tests............ http://www.mediafire.com/file/bjky5n55k5ct2he/Macro_Testing.pdf/file

BlouBul

 

[Frank]

Frank

The last thing I want to do is to slow down the macro, I prefer speed.
I cannot remember all of the previous spreadsheets that I've used, but this time I can.

The first sheet contains 6 macro's
Second sheet contains 7 macro's,
Third sheet contains 4 macro's
Fourth sheet contains 3 macro's
Fifth sheet contains 6 macro's
Six sheet contains 6 macro's.
You can draw your own conclusions

Here are the screen dumps of the tests............ http://www.mediafire.com/file/bjky5n55k5ct2he/Macro_Testing.pdf/file

BlouBul

Sorry. I misunderstood your question regarding timer code. It didn’t make sense since the code I supplied already had timer code written in it, so couldn’t understand why you were asking the question.

Yes, I rest my case - however I don’t believe the number of macros on a loaded background sheet has anything to do with it, provided those macros are not ALSO running. It’s the sheer size in terms of cells containing formulas on the background sheet (s) that matters. If you have a sheet loaded with 2000 draws and each draw has associated with it 36 formulas ( for example) that’s 72000 formula cells plus the data cells that Excel is storing/accessing.. Excel has to store all this at the same time as the sheet you are currently interested in AND run it’s macro. It’s no wonder it slows down your macro.