updating links through VBA causing sheet recalculation

I have an addin with UDFs that my department uses, when opening a file with these functions in we have to relink it to our own copy of the addin. As this only takes a second I decided to write it into the addin however when the VBA relinks formulas to the addin it performs a full sheet recalculation which on sheets this size can take up to 10mins.

manualy fixing the links doesn't cause a full sheet recalc, why is the VBA version doing this and how do I stop it?

Don't you have a central location that you can store the add-in? It will resolve this problem and make it much more simple to maintain.

Originally Posted by Leon V (AW)

also each users addin contains a modifiable table that they fill in with things that need to be added to worksheets in the addin which would not work with a network copy which would have to be read only.

Add-ins should ideally be read only. If you require user data to be stored it should be stored in a separate file from the code.

What is your code for changing the links?

Please Login or Register  to view this content.

censored name just in case (MoD stuff).

the origonal code from Jan (see here) had a class macro that was set to check every workbook automatically but it wouldn't work for me (I think it was writen for Xl 2003)

the line "ActiveWorkbook.ChangeLink vLink, ThisWorkbook.Name, xlExcelLinks" is where it starts doing a full recalc

If you run that code manually, not from a Ribbon control, does it behave the same way?

Yes. and it also still does it if I take it out of the addin and put it in the workbook

I think you have no option then - probably because you are running VBA the other VBA updates (this is common occurrence with UDFs).