PDA

View Full Version : Solved: Re-calculating cells with unrecognized functions



CycleTimeCha
07-22-2011, 08:24 PM
I have an application in which formulas containing user functions are inserted in cells possibly before the code for the user functions exists. This possibility is something I cannot eliminate.

The code for the user functions is eventually brought into the workbook through a process which Chip Pearson calls extensibility. One way to describe extensibility is the programmatic copying of VBA modules from one workbook to another.

If a function call-out exists before the function itself exists, #NAME? appears in the cell, as you would expect.

BUT HERE IS THE CRUX OF THE PROBLEM:

#NAME? stays in the cell, even after the code has been copied. This is true even after invoking Application.CalculateFullRebuild, which tells me Excel is failing to re-calculate cells which formerly had a #NAME? error.

Any ideas on how to get Excel to re-calculate cells which formerly had a #NAME? error?

Aussiebear
07-23-2011, 01:03 AM
Have a good look at the Excel help files for #Name error. Logic would suggest that the initial workbook needs to be rectified.

Aflatoon
07-25-2011, 01:33 AM
I think the first thing I would try, assuming you really cannot alter the sequence of events, would be to run a Replace operation replacing = with = in order to replicate re-entering the formulas.

CycleTimeCha
07-25-2011, 08:51 PM
Thanks, the = for = replacement works perfectly