setting chart series formula fails intermittently in Excel 2007/2010

I have many routines that set the .Formula of chart series. In one routine I'm getting intermittent run time errors that say...

A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference.

I'm confident there is nothing wrong with the formula string. If I click the Debug button, and then press F5, the code continues and executes correctly without the error.

This problem is unique to Excel 2007 and 2010. It does not happen with Excel 2003. The fact that the code continues by pressing F5 must be a clue, but I'm clueless.

Does anyone have any suggestions on what the problem could be?

Thanks,

Brian in Austin, Texas

VBA does lots of weird things like this, I have a bit of code in a project that takes a snapshot of a worksheet and pastes it to another worksheet in the same workbook which fails the first time it's run every time. I fixed it by simply adding an "on error try again" section of code that literally runs exactly the same routine twice.....and it works fine the second time.

Try that on yours.

Thanks for the suggestion. I tried it and at first I thought it worked, but after more testing I found it doesn't. I tried using a counter to repeat the command several times after the error, but that didn't help either.

If I delete the sheet on which the chart appears, my macro makes a new sheet and chart, and that get its running again, but that only works sometimes so is not a cure all.

I'm going to work on the idea that the problem is the chart. I do .Formula edits on many other charts without any trouble. This problem is unique to this particular chart. It is copied from my own template file of charts. The template chart shouldn't be any different from all the others that are in that file, but maybe it is.

Brian in Austin Texas

I think I cured it.
I tried working on the formatting and such of the source chart. That didn't help.
I tried Application.ScreenUpdating = True right before the .Formula statement. That worked, but I'd like something better.
I tried ActiveChart.Refresh but that didn't work.
I tried ActiveSheet.Calculate and that worked, and that is my final solution for now.

Cheers,

Brian in Austin Texas

I forgot to say that one or the morals of this story is that when a macro displays a VBA error message, and you go into the debugger and immediately press F5 and the macro continues with no trouble, then check if the Calculation setting in Excel is part of the problem.

Brian in Austin Texas