Solved: Maing sure xlCalculationAutomatic = true

[Rejje]

Hi Gurus and thanks for all help so far!

Help needed again. This time it's about potenial error handling. I understand one's got to make all error handling in the code. However I'd like to know if there is a way of having some sort of "übersub" that kicks in Application.Calculation = xlCalculationAutomatic if below happens and where to place it as well. Of course it should not make anything as long a script is running that has set Application.Calculation = xlCalculationManual.

Sub EraseAllData()
Dim answer As VbMsgBoxResult
answer = MsgBox("Do you really want to erase all data?", vbYesNo)

If answer = vbYes Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' THEN HERE'S: "SOME Error!" which means Application.Calculation = xlCalculationManual

This I want to avoid. Is it even possible? If It happens I want Application.Calculation = xlCalculationAutomatic to occur here!

[Bob Phillips]

Dim calcMode As XlCalculation
Dim answer As VbMsgBoxResult

On Error GoTo err_handler
calcMode = Application.Calculation

answer = MsgBox("Do you really want to erase all data?", vbYesNo)

If answer = vbYes Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'etc.
End If

'all is well so exit
Exit Sub

err_handler:
Application.Calculation = calcMode
'do other tidy-up/tear-down code

[Rejje]

Dim calcMode As XlCalculation
Dim answer As VbMsgBoxResult

On Error GoTo err_handler
calcMode = Application.Calculation

answer = MsgBox("Do you really want to erase all data?", vbYesNo)

If answer = vbYes Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'etc.
End If

'all is well so exit
Exit Sub

err_handler:
Application.Calculation = calcMode
'do other tidy-up/tear-down code


Tried it but something doesn't work with this one. Calculation is manual after error in this. What did I do wrong?

Sub ImportHKM()
Dim HKMwb As Workbook
Dim NamesList As Range
Dim Source As Range
Dim target As Range
Dim cell As Range
Dim calcMode As XlCalculation

On Error GoTo err_handler
calcMode = Application.Calculation

Application.ScreenUpdating = False
calcMode = xlCalculationManual

With ThisWorkbook

Set NamesList = .Names("IMPORTLISTA").RefersToRange

Set HKMwb = Workbooks.Open(Range("V_60100").Value)

calcMode = xlCalculationManual

For Each cell In NamesList

Set Source = HKMwb.Names(cell.Value).RefersToRange
Source.Copy
Set target = .Names(cell.Value).RefersToRange
target.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

Next cell

End With

Range("A1").Select

Application.ScreenUpdating = True
calcMode = xlCalculationAutomatic

HKMwb.Close (False)

MsgBox ("HKM har uppdaterats!")

Exit Sub

err_handler:
Application.Calculation = calcMode

End Sub

[Rejje]

Ok - of course I discovered the problem as below. One thing that bothers me if why the first error handling didn't work. Does the dim clear when going to err_handler?

err_handler:
'calcMode = xlCalculationAutomatic THIS DIDN'T WORK?
Application.Calculation = xlCalculationAutomatic
End Sub

[Bob Phillips]

No your problem is this with the With ... End With clause



calcMode = xlCalculationManual


it should be




Application.Calculation = xlCalculationManual


calcMode is just a variable used to save the current calculation mode before you set it within your code, and then restore it afterwards.

[Bob Phillips]

Thinking about it some mnore, the whole thing should be



Sub ImportHKM()
Dim HKMwb As Workbook
Dim NamesList As Range
Dim Source As Range
Dim target As Range
Dim cell As Range
Dim calcMode As XlCalculation

On Error Goto err_handler
calcMode = Application.Calculation

Application.ScreenUpdating = False
calcMode = xlCalculationManual

With ThisWorkbook

Set NamesList = .Names("IMPORTLISTA").RefersToRange

Set HKMwb = Workbooks.Open(Range("V_60100").Value)

calcMode = xlCalculationManual

For Each cell In NamesList

Set Source = HKMwb.Names(cell.Value).RefersToRange
Source.Copy
Set target = .Names(cell.Value).RefersToRange
target.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

Next cell

End With

Range("A1").Select

HKMwb.Close (False)

MsgBox ("HKM har uppdaterats!")

sub_exit:
Application.ScreenUpdating = True
Application.Calculation = calcMode
Exit Sub

err_handler:
MsgBox "Error: " & Err.Number & vbnewline & _
Err.Description
Resume sub_exit
End Sub

[Rejje]

Something's wrong. With last code XlCalculation is manual even after no error occurs.

[Bob Phillips]

I left one of your lines in it



Sub ImportHKM()
Dim HKMwb As Workbook
Dim NamesList As Range
Dim Source As Range
Dim target As Range
Dim cell As Range
Dim calcMode As XlCalculation

On Error Goto err_handler
calcMode = Application.Calculation

Application.ScreenUpdating = False
Application.Calculation= xlCalculationManual

With ThisWorkbook

Set NamesList = .Names("IMPORTLISTA").RefersToRange

Set HKMwb = Workbooks.Open(Range("V_60100").Value)

For Each cell In NamesList

Set Source = HKMwb.Names(cell.Value).RefersToRange
Source.Copy
Set target = .Names(cell.Value).RefersToRange
target.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Next cell
End With

Range("A1").Select

HKMwb.Close (False)

MsgBox ("HKM har uppdaterats!")

sub_exit:
Application.ScreenUpdating = True
Application.Calculation = calcMode
Exit Sub

err_handler:
MsgBox "Error: " & Err.Number & vbnewline & _
Err.Description
Resume sub_exit
End Sub

[Rejje]

Works perfect! Many, many thanks xld!