Removing #NAME? from cells.

[Thief_]

I'm creating a FAQ on how to programmatically install an addin when a
workbook loads. The reason for this is you can not distribute the workbook
because when on someone else's PC, it will not find the addin because the
addin is usually stored in *my* profile. I have one problem with my
solution.

When I distribute the workbook and addin, the addin is not in the user's
profile and has to be manually installed because normally addins are
hard-coded into the calling/linked workbook. Thus, I have written code to
programmatically install the addin when the workbook opens.

My solution involves removing the addin from the workbook and saving the
workbook before distributing it. This causes all cells with a reference to
the Addin's UDF to change value to #Name? I do this to stop the annoying
missing links msgs when the workbook opens on the target PC.

When the workbook opens, it installs the addin programmatically, BUT the
cells with the reference to the addin's UDF still show #NAME? The only way
to "reconnect" the addin's UDF to the cells is to manually edit each cell-
the UDF then shows a result in the edited cell!?

Can I automate this to stop the #NAME? from showing?

Regards

Julian

 

[OJ]

Turn automatic calculation off.
OJ

 

[Thief_]

Thanks for the response. When should I turn it off? Before removing the link
to the addin?

 

[Thief_]

OJ,

That's will not work. Let me explain why:

Cell A1 before removing the addin:

=Mx(9,5)

This is a UDF in the addin.

Cell A1, with Calculation turned off, after removing the addin:

='H:\MxAddin.xla'!Mx(9,5)

I then need to remove the "'H:\MxAddin.xla'!" part by editing the cell so
that when the workbook loads on the target PC it doesn't ask to update
links. This act causes the cell to display #NAME?

Any other ideas?

 

[OJ]

Turn off screenupdating as well? Just a suggestion....