Add-ins and #NAME errors

[Schizoid Man]

I have an Excel spreadsheet that was saved with a series of add-in function
calls. The add-in wasn't loaded at the time, and the UDF cells contained
#NAME errors. When I opened the sheet on a machine that did contain the
relevant add-in, I expected that hitting F9, Shift+F9 or at the very most
Ctrl+Alt+F9 would recalculate all the cells including the ones with the
#NAMEs.

However this was not the case. The only way I could fix this sheet was by
using an F2+Enter combination on the broken #NAME cells. Since the range was
relatively large, I used a VBA macro with SendKeys.

Though I did manage to fix my problem, I'm a bit dissatisfied with the
solution. It's not particularly elegant, and if the range is large then the
SendKeys operation can take some time.

Moreover, I'd like to understand why the #NAME cells didn't self-correct
when the relevant add-in was present.

Thanks in advance,
Schiz

 

[Gord Dibben]

One quick way to F2 + Enter a large range of cells is to do an edit>replace

Select all cells and Edit>Replace

What: =

With: =

Replace all will update all formulas.

Your error should have gone away when you loaded the workbook on a machine
with the add-in unless the linking path was different to the relevant
add-in?


Gord Dibben MS Excel MVP

 

[Schizoid Man]

One quick way to F2 + Enter a large range of cells is to do an
edit>replace

Select all cells and Edit>Replace

What: =

With: =

Replace all will update all formulas.

Your error should have gone away when you loaded the workbook on a machine
with the add-in unless the linking path was different to the relevant
add-in?

Hello Gord,

Thanks for the reply. No, I've seen this problem occur with two different
Excel add-ins. The #NAME error persists unless a brute force F2+Enter is
performed on the erroneous cells. How do I determine the linking path
configuration?

Thank you,
Schiz

 

[Schizoid Man]

One quick way to F2 + Enter a large range of cells is to do an
edit>replace

Select all cells and Edit>Replace

What: =

With: =

Replace all will update all formulas.

Your error should have gone away when you loaded the workbook on a machine
with the add-in unless the linking path was different to the relevant
add-in?


Gord Dibben MS Excel MVP

Hi Gord,

I found the source of the error: http://support.microsoft.com/kb/291058. The
solution is do a Find-Replace action on the formulas, which is easy enough
to do. Definitely a lot more elegant that iterating through the entire
range.

Schiz

 

[Peo Sjoblom]

Wasn't that what Gord suggested?

--


Regards,


Peo Sjoblom

 

[Schizoid Man]

Wasn't that what Gord suggested?

Hello Peo,

Yes - you're right, the ultimate solution was to do a replace in the code
using VBA, so exactly what Gord suggested. What had (and frankly, has) me
puzzled is that Gord said that if I recalculate the sheet on a machine that
has the required add-in the calculations should work out of the box.

However my experience is that this is not the case. Without a replace of the
'=' sign this error will not go away. Any ideas why this occurs?

Regards,
Anuj