Wait for calc and print in macro

  • Thread starter Thread starter brett.kaplan
  • Start date Start date
B

brett.kaplan

Hi,

I am trying to create a macro that will load an ID into one cell, and
many other cells will calculate based off of that ID. Some of these
calculations are from external sources, so it could take between 10-30
seconds for everything to update. Once all the cells on the sheet are
updated, I want to print the sheet, then load in the next ID, and
repeat the process.

Right now, I have no issue with the code for loading the ID or
printing the sheet. The problem is that the print happens before the
cells are done calculating.

To try to remedy this, I put in an Application.Wait command for 10
seconds, and then added a Do While Loop that if the CalculationState
<> xlDone, then to wait another 10 seconds. However, it still prints
before anything is updated.

Any thoughts on how to fix this?

Thanks!
Brett
 
Use the Application.OnTime method to wait before printing:

Sub SetValue()

'code to increment cell value
Application.CalculateFull
Application.OnTime Now() + TimeValue("00:00:05"), "PrintSheet"
End Sub

Sub PrintSheet()
Sheets("SheetName").PrintOut
SetValue
End Sub

HTH,
Bernie
MS Excel MVP
 
Oops, I forgot to update the time from my testing:

For 1 minute wait:
Application.OnTime Now() + TimeValue("00:01:00"), "PrintSheet"
 
Thanks! I was incrementing the cell value by using a loop, and it
doesn't seem to work when I do that; ie, it doesn't wait the 5 seconds
before updating the new cell, so it is printing before the calcs are
updated still.

Any thoughts?
 
The key is calling the print from the first sub, which then calls the first sub to complete the
loop.

Something like this, if you are incrementing cell A1, say from 1 to 10. Start with a 0 in cell A1,
then run SetValue

Sub SetValue()
Range("A1").Value = Range("A1").Value + 1
Application.CalculateFull
Application.OnTime Now() + TimeValue("00:01:00"), "PrintSheet"
End Sub

Sub PrintSheet()
Sheets("SheetName").PrintOut
If Range("A1").Value < 10 Then SetValue
End Sub

For further help, you need to more fully describe your looping - perhaps post your code....

HTH,
Bernie
MS Excel MVP


Thanks! I was incrementing the cell value by using a loop, and it
doesn't seem to work when I do that; ie, it doesn't wait the 5 seconds
before updating the new cell, so it is printing before the calcs are
updated still.

Any thoughts?
 
Back
Top