Prevent Excel from creating external references

Hi

Background:
I have an add-in that contains various functions that are used across a range of workbooks. The add-in is constantly under development, so it's name changes regularly, it's also stored on a USB device which changes drive letter on different machines.

Problem:
My problem is that Excel will, by default, change formula references for the add-in to external links. Therefore, a workbook loaded with a newer version of the add-in, or one from a different location, doesn't use the functions that are present (which is what I'd like) instead it looks for the original file.

To boil it down, instead of saving cell formulas as

'<filename>'!Function(args)

i'd like it just to keep the following in the cell

Function(args)

even though 'Function' is external

Solutions i've tried:
On opening 'Remove references' converts all external cells to values, which isn't much use
Manually changing the source seems to work but is a bit of a bind every time.
Find/replace '<filename>'! with nothing works, and can be coded to occur automatically, but forces a re-evaluation of all cells on load, which is time consuming.

Thought about trying a 'before save' code to remove external references but it seems longwinded and one of the attractions of a central add-in is that no code need be in the workbooks themselves.

Cheers for reading

Mat

Hi

I've got a partial solution, on workbook open, the following code runs:

Please Login or Register  to view this content.

My problem now is that this triggers a recalculation, which takes a reasonable amount of time, and is unnecssary. Any idea how to prevent it? setting calculation to manual and disabling events have both proved unsucessful so far.

Cheers

Mat