Disable Calculation in Excel

[comconrk]

Several months ago I started a thread when my excel macro was running very slowly. Some of you gave me very good advice.

I have just upgraded to a new laptop and this macro is now running very slowly again.

The two things that I did several months ago, using your advice, were the followng

1. Application.Screen.Updating = False (I set it to true before exiting the macro)
2. wk = 0
For Each wksht_loop In ActiveWorkbook.Worksheets
wk = wk + 1
Worksheets(wk).EnableCalculation = False
Next wksht_loop

The workbook is set for manual calculation, but the sheet is definitely updating after each VBA command, which is causing the slowness.

What have I forgotten?

Thanks very much,

 

[xlbo]

set this at the START of the code
application.calculation = xlcalculationmanual
and then at the end
application.calculation = xlcalculationautomatic

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[comconrk]

I did add the command you suggested; however, I still have a problem. Possibly I am thinking incorrectly.
I thought the following code would not populate the cells until I issues an Application.ScreenUpdating = True command.
However, when running this code each worksheet is being updated as it goes. Could this be causing the slowness?

Thanks very much.


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

wk = 0
For Each wksht_loop In ActiveWorkbook.Worksheets
wk = wk + 1
Worksheets(wk).EnableCalculation = False

Next wksht_loop
For i = 3 To Max_Rows


If Sheet8.Cells(i, 3) = Last_Roll_No And Sheet8.Cells(i, 1) = Category Then
Total_Qty_On_Hand = Total_Qty_On_Hand + Sheet8.Cells(i, 6)
Else

If Total_Qty_On_Hand <> 0 Then ' Use rolls which still have paper and were not received after the end date of this report
If Year(Date_Rec) < Current_Year Or (Year(Date_Rec) = Current_Year And Month(Date_Rec) <= Current_Month) Then ' Do not include rolls received after ending date
'Worksheets("Aging_Roll_Summary").Activate
New_Row = New_Row + 1
On Error Resume Next

Sheet13.Cells(New_Row, 1).Value = Category
Sheet13.Cells(New_Row, 2).Value = Last_Roll_No
Sheet13.Cells(New_Row, 3).Value = Date_Rec
Sheet13.Cells(New_Row, 4).Value = Total_Qty_On_Hand
Sheet13.Cells(New_Row, 5).Value = Unit_Cost
Sheet13.Cells(New_Row, 6).Formula = "=D" & New_Row & "*E" & New_Row
Sheet13.Cells(New_Row, 7).Formula = "=TODAY()-" & "C" & New_Row
Sheet13.Cells(New_Row, 8).Formula = "=VLOOKUP(G" & New_Row & ",J$1:K$8,2)"


End If
End If
' Worksheets("Receipts").Activate
Date_Rec = Sheet8.Cells(i, 2)
Last_Roll_No = Sheet8.Cells(i, 3)
Category = Sheet8.Cells(i, 1)
Total_Qty_On_Hand = Sheet8.Cells(i, 6)
Unit_Cost = Sheet8.Cells(i, 8)
End If

Next i

 

[cLFlaVA]

Maybe try:

Application.Visible = False

.
.
.

Application.Visible = True

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[comconrk]

Thanks for the suggestion. I did try it, but my computer still runs slowly. HOwever, when I moved to another computer the macro, with your suggestions included, ran very quickly. It is quite confusing.

My new laptop is a Dell Latitude D505 with a 600MHZ Intel M Processor. This is supposed to translate to a 2GHZ regular Intel Processor.

I will next see what is different in the setups between the two computers.

Thanks for all your good suggestions.

 

[macropod]

Hi,

Here's a set of generic routines I use for speeding up processing and displaying progress on the status bar, plus a macro to trim cells, showing how the other routines are implemented.

Cheers

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim Percent As Integer
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
    CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
    CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
    Cell.Replace What:=Chr(160), Replacement:=Chr(32)
    Cell.Value = Application.Trim(Cell.Value)
    I = I + 1
    If Int(I / CellCount * 100 + 0.5) = Percent + 1 Then
        Percent = Percent + 1
        Application.StatusBar = Percent & "% Trimmed"
    End If
Next Cell
MsgBox "Finished  trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub