Reset Formula without Editing Formula?

I have a weird problem with getting the formula to reset or recalculate itself.

I have a series of cells with complex formulas that include VBA function calls. Here's an example of one:

=IF(LEN(B66)*LEN(E66)*LEN(J66)=0,"",IF(LEN(T66)=0,ReplacementYears(J66,B66,$C$10,"4.1.5"),SpanYears(T66,J66,B66,$C$10,"4.1.5")))

where ReplacementYears and SpanYears are VBA functions returning strings.

The workbook is set to automatically calculate and it works fine when entering the supporting data. But when I delete a row in the spreadsheet, the cells with this formula revert to a "#VALUE!" error. The deleted row does not contain any of the supporting references for the formula. The delete row is also below all applicable data (and thus doesn't affect the relative addressing).

If I recalculate the worksheet, the error remains. If I "edit" the formula by simply clicking in the formula (or F2) and then hit enter, the formula somehow resets itself, the error goes away, and I have the correct formula result in the cell.

This is all weird to me and I can't figure out why it's doing what it's doing. I'm not so worried about it except that through a separate VBA process, I need to be able to utilize the values that are returned in these cells and it all breaks if the #VALUE! error is there instead of the actual value.

So here are my questions...

1) What is it doing and is there something I can do to "fix" the "error"?
2) Is there a way to programmatically work around this issue by forcing a formula "reset" on all of these cells in the worksheet (without pasting in a replica of the formula - there are too many of these formulas all over the worksheet to do them one at a time)?

Thanks!

Mike Ogilvie
Richmond, Virginia

P.S. This is happening in 2003 and 2007.

Mike

Do you have

Please Login or Register  to view this content.

at the top of your UDF? If not, then put it in and this should force the function to recalculate whenever there is a calculation performed on the sheet.

Note that this does generate some overhead so depending on how many instances you have, there could be impacts.

rylo