Why does VBA slow down on this?

[sam314159]

We use a multi-thousand line VBA script to format a bunch of spreadsheets and it has been working perfectly for months. I recently added the code below and when it starts executing it goes much slower than everything else. I would say it maybe spend a half second on each one those cell formatting steps and the user has to sit there and watch it all.

1. Is there anything I can do to speed this up?
2. I have used very similar code in other modules and it works very fast, why would it become slower here?

Range("B2:G2").Select
    With Selection
   .MergeCells = True
   .Font.Size = 22
   .Font.Bold = True
   .HorizontalAlignment = xlLeft
   .VerticalAlignment = xlCenter
    End With
'Name
    ActiveCell.FormulaR1C1 = PlotterGUI.txtName
'Date
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
With Selection
   .Font.Bold = True
   .HorizontalAlignment = xlRight
   .VerticalAlignment = xlCenter
   .Font.Size = 12
    End With

[mdmackillop]

Avoid selecting

With Range("B2:G2")
   .MergeCells = True
   .Font.Size = 22
   .Font.Bold = True
   .HorizontalAlignment = xlLeft
   .VerticalAlignment = xlCenter
   .Cells(1, 1).Formula = PlotterGUI.txtName
End With
 'Date
With Range("H2")
   .Formula = "=TODAY()"
   .Font.Bold = True
   .HorizontalAlignment = xlRight
   .VerticalAlignment = xlCenter
   .Font.Size = 12
End With

[Paul_Hossler]

Do you have Application.ScreenUpdating = False?

Also might try setting Application.Calculation to manual and then turn it back on at the end

I was thinking that insertiing =TODAY() on lots of sheets might cause it to keep recalculating

Paul

[mdmackillop]

Why not use a formatted template(s). It might be easier to copy data/formulae to that.

[sam314159]

I actually have screenupdate set to true, didn't know that affected performance. Thanks Paul, I will change that.

I really love the idea of a premade template! I will definitely start doing that on future projects and if disabling screenupdate doesn't help a whole lot then I will probably rewrite this one too.

Thanks for all the suggestions guys. I will make the changes and post results Monday in case someone else has the same questions. (Though I doubt anyone asks questions as newbish as mine lol)

[sam314159]

Ok I tried the following suggestions:

1. Removed =TODAY() all together.
2. Stopped selecting like MD suggested.
3. Turned off screen updates like Paul suggested.

And that reduced my run time from 50 seconds to 38 seconds. I did a couple of more tests and it looks like screenupdate makes the most significant difference, still I am not crazy about turning it off since it serves as our "progress bar".

I still don't understand why takes it so long to execute code like the snippet below:

With Range("B2:G2")
   .MergeCells = True
   .Font.Size = 22
   .Font.Bold = True
   .HorizontalAlignment = xlLeft
   .VerticalAlignment = xlCenter
   .Value = "test"
End With

My script slows down significantly when it goes through the code above so I decided to go through it line by line using he debugger. Each line from the snippet above takes about "one missip" to execute! The .Value is pretty much instantaneous but everything else pops up the rotating hour glass for almost a second.

Is my only real option here to use a pre-built template next time?


Update

I did one more test where I took that exact same code snippet that I posted above and ran in a brand new module in a brand new workbook. It took less than half a second.

Now I'm even more confused, why would the same code take 4 seconds when it's part of the big 6000 line script and much less time when it's in it's own module?

Thanks in advance guys.

[mdmackillop]

Try creating custom style which incorporates all these formats and apply it to the range in one step.

[sam314159]

Thanks MD.

Did you get a chance to read the "Update" I made to my previous post?

[mdmackillop]

You could be triggering a recalculation. Try setting Calculation to Manual and resetting to Automatic on completion.

[Paul_Hossler]

why would the same code take 4 seconds when it's part of the big 6000 line script and much less time when it's in it's own module?

Possibly there's something else happening that you don't realize.

Did you set .Calculation to manual like we suggested? Are there any worksheet event handlers that are running?

If the code has been heavily edited Ron Bovey's CodeCleaner would likely help, or you could manually export the 6000 line module, deleted the original, and re-import it to see

Paul

[rbrhodes]

Hi,

You could test for events by puttin a breakpoint on the .value = "test" line. Run the code to there then <F8> . If it starts running a 'change' event you've found it. Alternatively just turn events off as well:

Sub something
With Application
   .Screenupdating = false
   .EnableEvents = false
   .calculation = xlcalculationmanual
   end with
   '...your code...
With Application
   .Screenupdating = true
   .EnableEvents = true
   .calculation = xlcalculationautomatic
   .calculate
end with

[sam314159]

As Found Run Time: 33 Seconds
Manual Calc Run Time: 33 Seconds
Events Off Run Time: 33 Seconds
No "Select" Run Time: 32 Seconds
ScreenUpdate Off Run Time: 16 Seconds

When I copied the entire module and just run it in a new workbook it ran in 13 seconds with all the bells and whistles still on. The funny part is that have a few other modules within this project that pretty do the same type of cell formatting and they are extremely fast.

[mikerickson]

Have you tried activating a sheet that is not the sheet you are formatting?
Perhaps, that way, the Screen will have no need to update, and you can still use it for your progress bar.