mike.magill
New Member
- Joined
- Jun 24, 2009
- Messages
- 22
I have a routine that, amongst other things, alters some cell values that in turn determine the content of a series of charts. Each time the charts are changed, the routine copies the chart and pastes it as a picture into another worksheet.
I am 99% certain the code is sound but, for some reason, the routine will regularly but not always fail at one or more of the copy/paste parts. If you press debug and retry, the code will no longer fail and will continue perfectly. This suggest to me that the spreadsheet is slow in updating and the routine is trying to execute a line before the system is ready. The code that fails is similar to the following:
I have tried using an error handler that waits 3 seconds and then tries again up to 10 times but that does not always work (i.e. it fails more than 10 times).
Does anyone have any idea why the error is happening or how I can improve the error handler? Also, if I run it without the error handler and just debug and resume each error the routine takes about 5 minutes but with the error handler it takes c.15 minutes (if it completes at all)!
I am 99% certain the code is sound but, for some reason, the routine will regularly but not always fail at one or more of the copy/paste parts. If you press debug and retry, the code will no longer fail and will continue perfectly. This suggest to me that the spreadsheet is slow in updating and the routine is trying to execute a line before the system is ready. The code that fails is similar to the following:
Code:
Sheet10.ChartObjects(ChrtName).CopyPicture
Sht.Paste Destination:=Sht.Range(PasteRange)
I have tried using an error handler that waits 3 seconds and then tries again up to 10 times but that does not always work (i.e. it fails more than 10 times).
Code:
ErrorHandler:
ErrorCount = ErrorCount + 1
If ErrorCount < 10 Then
Application.Wait (Now + TimeValue("0:00:03"))
Resume
Else
MsgBox "Error message"
End If
Does anyone have any idea why the error is happening or how I can improve the error handler? Also, if I run it without the error handler and just debug and resume each error the routine takes about 5 minutes but with the error handler it takes c.15 minutes (if it completes at all)!