Application.Calculation issues

[c.davidson]

I'm creating a program in excel to predict performance of one of our products depending on the conditions of the product. There are multiple calculations and formulas across multiple sheets to eventually give the results. In VBA traditionally we start the code with :

"Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual"

To speed the program up a bit.

And at the end of the routine we set it back to True and xlCalculationAutomatic respectively. Throughout the routine if it gets to a point that a value needs to be calculated to use elsewhere, we stick in a "Application.Calculate".

Now, I'm running off my code and it gets to the point where I want to calculate and take a figure from the spreadsheet, but after the "Application.Calculate" it isn't calculating and the code is picking up the old value that is still displayed in the cell.

I've tried different ways of getting it to calculate:

"Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual"

"If Not Application.CalculationState = xlDone then
Do Events
End If"

"Do Until Application.CalculationState = xlDone
Do Events
Loop"

All of these still didn't let the workbook calculate the values, and the "Do Until" method just got stuck in an infinite loop, the calculationstate was never equal to xldone.

As soon as I stop the code the values calculate within a second.

Can anyone help?

Thanks

[PFDave]

Need to see your full code or a sample workbook please then we'll be able to offer some advice

[c.davidson]

Unfortunately I can't do that as there is a lot of sensitive information within the workbook and the coding can not be disclosed, however I did find a solution. As I had created a custom function within the workbook, I had to include a DoEvents line along with the application.calculate for it to update.

[PFDave]

Unfortunately I can't do that as there is a lot of sensitive information within the workbook and the coding can not be disclosed, however I did find a solution. As I had created a custom function within the workbook, I had to include a DoEvents line along with the application.calculate for it to update.

Nice one, glad you got it sorted