I have got Macros in my Excel File, which typically do the following:
-----------------------------------------------
Sub xxxx()
....
Dim v As Variant
....
' prepare
Application.ScreenUpdating = False
v = Application.Calculation '(store the current status of re-calculation)
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
....... '(code according to the required functionality)
GoTo ExitHandler
' exit nicely
ExitHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
' Application.Calculate
Application.Calculation = v
Exit Sub
-----------------------------------------------
Some of these macros create a user form allowing to make a selection, what precisely should be done, and at the same time save this selection in one of the worksheets.
In parallel, there are "Repeat" macros, which do exactly the same thing, except they do not ask to make the selection, but just take the selection stored with the first macro and perform the necessary action.
The actions performed are things like copying one or several lines from one worksheet and inserting them into another worksheet or to jump to a cell in another worksheet containing the value of a cell in the currently active line.
My problem now:
In Excel 2010, everything runs well (so, that's not the problem)
In Excel 2013, in several of these macros i get "Excel has stopped working" as follows:
- Only in the macros with the selection form as described above
- When the calculation method is automatic prior to running the macro
- On executing the last command "Application.Calculation = v", i.e. when switching the calculation back on.
In Excel 2016, basically the same effect, only even more often (more of these macros are affected)
I have tried to build in delays before and/or after the command "Application.Calculation = v" with means of a function "Sleep"
For the function "Sleep" I have the following code which takes into account several cases (as the Excel file needs to run under several Office versions):
----------------------------------------------------
#If VBA7 Then 'Necessary for 64 bit Office 2010, optional for 32 bit Office 2010 applications
#If Win64 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong)
#Else
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
#Else 'seems necessary for Office 2007
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
----------------------------------------------------
The workaround til now was to simply not switch on the automatic calculation (and to inform the user about it), but the users complain more and more that I should find a better solution as they often do not pay attention to the fact, that the calculation is now switched off (and wonder about the "behaviour" of Excel.
Has anybody seen this and found a way out of this problem? - Any help would be greatly appreciated, as I have started with this problem "years ago" (when the first machines were running with Excel 2013) and I just built in the workaround, now the company wants to roll out Office 2016 and I get even more problems...
Many thanks in advance!
-----------------------------------------------
Sub xxxx()
....
Dim v As Variant
....
' prepare
Application.ScreenUpdating = False
v = Application.Calculation '(store the current status of re-calculation)
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
....... '(code according to the required functionality)
GoTo ExitHandler
' exit nicely
ExitHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
' Application.Calculate
Application.Calculation = v
Exit Sub
-----------------------------------------------
Some of these macros create a user form allowing to make a selection, what precisely should be done, and at the same time save this selection in one of the worksheets.
In parallel, there are "Repeat" macros, which do exactly the same thing, except they do not ask to make the selection, but just take the selection stored with the first macro and perform the necessary action.
The actions performed are things like copying one or several lines from one worksheet and inserting them into another worksheet or to jump to a cell in another worksheet containing the value of a cell in the currently active line.
My problem now:
In Excel 2010, everything runs well (so, that's not the problem)
In Excel 2013, in several of these macros i get "Excel has stopped working" as follows:
- Only in the macros with the selection form as described above
- When the calculation method is automatic prior to running the macro
- On executing the last command "Application.Calculation = v", i.e. when switching the calculation back on.
In Excel 2016, basically the same effect, only even more often (more of these macros are affected)
I have tried to build in delays before and/or after the command "Application.Calculation = v" with means of a function "Sleep"
For the function "Sleep" I have the following code which takes into account several cases (as the Excel file needs to run under several Office versions):
----------------------------------------------------
#If VBA7 Then 'Necessary for 64 bit Office 2010, optional for 32 bit Office 2010 applications
#If Win64 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong)
#Else
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
#Else 'seems necessary for Office 2007
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
----------------------------------------------------
The workaround til now was to simply not switch on the automatic calculation (and to inform the user about it), but the users complain more and more that I should find a better solution as they often do not pay attention to the fact, that the calculation is now switched off (and wonder about the "behaviour" of Excel.
Has anybody seen this and found a way out of this problem? - Any help would be greatly appreciated, as I have started with this problem "years ago" (when the first machines were running with Excel 2013) and I just built in the workaround, now the company wants to roll out Office 2016 and I get even more problems...
Many thanks in advance!
