lena.marie
07-16-2017, 06:28 AM
Hi,
I encountered the following problem several times now, however, I haven't found a satisfactory solution yet. Hopefully one of you guys knows.
I have quite a large Excel file, with lots of formulas, thus, if one changes a number, recalculating the entire file can take easily up to ten seconds. Maybe more. In this file I wrote a VBA function that basically takes a number from one sheet, plugs it into a specific cell, recalculates the Excel file, and then copies the result to some other sheet. Quite straight forward. At the beginning of the VBA program, I set the calculation mode to 'manual', and later, in the code, I use Application.Calculate to trigger the recalculation of the file after plugging in the new number. However, when I run the file several times, I often get different results. And I think that's because Excel doesn't wait until the entire file is recalculated, but instead moves on before that already, which causes some confusion later on. I used the same code over and over again in other projects of mine as well, but typically, those files are never that large, so recalculation goes quickly, which means, no problems occur. Does anyone of you know how I can check whether the entire file has been recalculated and only then move on?
PS: Something I already tried but also didn't work is the following:
Sub Whatever()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
... Code
Application.Calculate
If Application.CalculationState <> xlDone Then Application.Calculate
... Code
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
ActiveSheet.DisplayPageBreaks = True
End Sub
I encountered the following problem several times now, however, I haven't found a satisfactory solution yet. Hopefully one of you guys knows.
I have quite a large Excel file, with lots of formulas, thus, if one changes a number, recalculating the entire file can take easily up to ten seconds. Maybe more. In this file I wrote a VBA function that basically takes a number from one sheet, plugs it into a specific cell, recalculates the Excel file, and then copies the result to some other sheet. Quite straight forward. At the beginning of the VBA program, I set the calculation mode to 'manual', and later, in the code, I use Application.Calculate to trigger the recalculation of the file after plugging in the new number. However, when I run the file several times, I often get different results. And I think that's because Excel doesn't wait until the entire file is recalculated, but instead moves on before that already, which causes some confusion later on. I used the same code over and over again in other projects of mine as well, but typically, those files are never that large, so recalculation goes quickly, which means, no problems occur. Does anyone of you know how I can check whether the entire file has been recalculated and only then move on?
PS: Something I already tried but also didn't work is the following:
Sub Whatever()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
... Code
Application.Calculate
If Application.CalculationState <> xlDone Then Application.Calculate
... Code
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
ActiveSheet.DisplayPageBreaks = True
End Sub