Speed Up gone wrong - made it slower

[Chuck6475]

First I have dealt with the .exd issue, but since the update my spreadsheet is acting strange.

Background:
This spreadsheet started as a 2003 workbook with no macros or vba. It had some 35 - 40 spreadsheet used as user interfaces or calculation tables. As the options were expanded it got macros. (First time for me, an old time procedural programmer.) Not elegant but they worked. That wasn't enough so on to UserForms and VBA. The spreadsheet grow to 16mb without data. Strange things happened periodically, so I moved it to Excel 2010. It went from 16mb to less than 4mb.

Currently:
I recently added some additional features and wanted to address the speed with more vigor. I have been turning Calculations from Auto to Manual etc. for some time with success, but my UDF struggle sometimes to be current. Therefore I sprinkled in a few .calculate.full statements.

Most likely because of my coded skills the calculations got out of hand. So I deleted all of my Application.calculations = statements and replaced them with a routine from Kenneth Hobs called SpeedOn and SpeedOFF.

With Application
. Calculation = glb_origCalculationMode
. ScreenUpdating = True
. EnableEvents= true
. DisplayAlerts = true
. CalculateBeforeSave = true
.Cursor = xlDefault
.StatusBar = false
.EnableCancelKey = xlInterrupt
End with

Above is SpeedOn code, SpeedOff toggle these items. I put in a test to insure that SpeedOn was not called twice in a row as this would set the calculation to manual for good.

There is a great deal of interaction between the spreadsheet and VBA.

With this addition the program crawls. Significantly worse than before. Why?

 

[starl]

The code you posted would slow the workbook down.. it would properly be called at the end of your procedure to turn all the settings back on. Is that how you are using it? You call the code SpeedOn but to me, it turns the Speed Off...

If you have a lot of formulas in your workbook that will slow it down and increase its size. Size increase can also be caused by a "dirty" sheet - Excel thinks there are more rows/columns used then actually are (do a CTRL+END - are you in the boonies where there's no data? If so, you have a dirty workbook).

The code looks fine to me and I use something similar.. perhaps you aren't calling it correctly?

 

[jtakw]

Use SpeedOFF at the beginning of your macro, and SpeedON at the end of your macro.

 

[Chuck6475]

I can't tell you how nice it is to get a response. Thanks. I'm totally frustrated and Microsoft's recent update didn't help.

The code you posted would slow the workbook down.. it would properly be called at the end of your procedure to turn all the settings back on. Is that how you are using it? You call the code SpeedOn but to me, it turns the Speed Off...

You're correct I should have called that code SpeedOff, which it is.

Option Explicit
Public glb_SpeedCop As Integer
Public glb_origCalculationMode As Integer
 
Sub SpeedOn(Optional StatusBarMsg As String = "Running SpeedOn macro...")
    If glb_SpeedCop <> 1 Then
        glb_SpeedCop = 1
    glb_origCalculationMode = Application.Calculation
  
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Cursor = xlWait
        .StatusBar = StatusBarMsg
        .EnableCancelKey = xlErrorHandler
    End With
    End If
    
End Sub

I added SpeedCop to insure that I didn't end up in manual mode permanently.

.......The code looks fine to me and I use something similar.. perhaps you aren't calling it correctly?

Totally possible actually probably a lock at least some times. I added them to the beginning and end of modules that appeared to need it. I didn't do it to text box coloring or navigation type stuff.

I also added it to the UserForms. Now this is why I had to add SpeedCop. With mutlipage wizard this code goes all over the place, following it drives me nuts. I know it is my coding style but it is too big to start over.

My assumption is in Manual mode the spreadsheet itself is not recalculated but individual cells controlled by VBA are updated via the VBA code, not any cells that reference that altered cell.

Thanks again for helping.

 

[starl]

If you have multiple subs being called then you should only put this in the main subs... I'm not familiar with the code you are using, so I can't offer specific advice. But based on your comments - you call a userform. If that call to the userform is a Main Sub, then you should have your code in there

Sub myMainSub()
SpeedOff
userform.show
SpeedOn
end sub

any code called by the userform should NOT have the Speed calls.
When you have calculations off, if you place formulas with vba, they will calculate. If you need to force the rest of the workbook to update its calculations, then use can use Application.Calculate. This performs a calculation - it does NOT turn the Calculate back on.

 

[Chuck6475]

If you have multiple subs being called then you should only put this in the main subs... I'm not familiar with the code you are using, so I can't offer specific advice. But based on your comments - you call a userform. If that call to the userform is a Main Sub, then you should have your code in there

Sub myMainSub()
SpeedOff
userform.show
SpeedOn
end sub

any code called by the userform should NOT have the Speed calls.
When you have calculations off, if you place formulas with vba, they will calculate. If you need to force the rest of the workbook to update its calculations, then use can use Application.Calculate. This performs a calculation - it does NOT turn the Calculate back on.

I have to go to the dentist but this seems this has to be backwards to me. Perhaps there in is my problem. SpeedOn sets Calculation to Manual, SpeedOff sets it to whatever it was when SpeedOn was called in the first place. What would be the value in glb_origCalculationMode if SpeedOff is called before SpeedON?

</pre>

 

[jtakw]

I've had a look at Kenneth Hobs' code: VBA Express : Excel - Speed Up Code

You have SpeedOn code and SpeedOff code backwards, SpeedOn should be placed before your macro, and SpeedOff at the end, and the info provided by Kenneth Hobs is detailed.

Option Explicit 
 
Public glb_origCalculationMode As Integer 
 
Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...") 
    glb_origCalculationMode = Application.Calculation 
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
        .DisplayAlerts = False 
        .Cursor = xlWait 
        .StatusBar = StatusBarMsg 
        .EnableCancelKey = xlErrorHandler 
    End With 
End Sub 
 
Sub SpeedOff() 
    With Application 
        .Calculation = glb_origCalculationMode 
        .ScreenUpdating = True 
        .EnableEvents = True 
        .DisplayAlerts = True 
        .CalculateBeforeSave = True 
        .Cursor = xlDefault 
        .StatusBar = False 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 
 
Sub FillSlow() 
    Dim c As Range, r As Range, startTime, EndTime 
    Set r = Range("A1:C1000") 
    r.ClearContents 
    startTime = Timer 
    For Each c In r 
        c.Select 
        c.Formula = "=Row()*Column()" 
    Next c 
    DoEvents 
    EndTime = Timer 
     
    MsgBox "Total Time: " & EndTime - startTime 
    [A1].Select 
End Sub 
 
 
 
Sub FillFast() 
    Dim c As Range, r As Range, startTime, EndTime 
    Set r = Range("A1:C1000") 
    r.ClearContents 
    startTime = Timer 
     
    On Error GoTo ResetSpeed 
    SpeedOn 
     
    For Each c In r 
        c.Select 
        c.Formula = "=Row()*Column()" 
    Next c 
    DoEvents 
    EndTime = Timer 
     
    MsgBox "Total Time: " & EndTime - startTime 
    [A1].Select 
     
ResetSpeed: 
    SpeedOff 
End Sub