Converting XLL addin functionality to a native VB module : Problem refreshing formulae

Question

Hey all

I have a .xll plugin that registered a few functions which we use in our excel sheets in a number of places. The performance was very poor, and we didn't have the original code, so we replicated the functionality by writing the functions in VB in a module. The functions work as expected and performance is much better.

The problem is the sheets do not automatically recognize the new functions. For example, suppose a cell's formula is "=CustomFunction()" which was earlier defined in the xll, and now has been defined in the VB. This will keep showing as "#NAME" unless I manually edit the cell, and press enter. It then correctly picks up the VB formula and calculates.

The standard tricks of recalculating formulaes : Ctrl/Alt/Shift/F9 haven't worked.

Has any one experienced this? Any idea how to solve this?

Answer 1

I would do a global search and replace from =CustomFunction to =TempCustomeFunction. Then a global rename back to =CustomFunction. This will force Excel to re-evaluate the functions. (I have a vague idea that you might need to save, shutdown and restart after he first replace, but try it first to be sure).

I am pretty sure I have encounted a similar problem and doing that fixed it for me. I guess if you have a lot of workbooks to fix, you might need to do this via automation.