How change link source in long formula when source moved

[Guest]

Hello!
Plz, help me change link source in LONG formula when source moved or renamed.
When open workbook with such links or on the other computer, formula
damages, and change source already crashed.
Method ChangeLink of workbook object does'nt work too.
How to do my "user defined functions" Excel feels as its own?
May be, I should place my xla in a special folder?
Thank you in advance.
Irina

 

[Guest]

I have tried to do this and I think that the simple answer is that you may
have to do this manually. There are multiple limits on formulae length -
array formulae are limited to (I think) 120 characters, but regular formulae
are (I think) 255 characters. But they are treated differently if entered in
VBA or in the user interface. You can check out Chip Pearsons website - I
think he documents all the lengths somewhere.

When I encountered this, rather than spend ages on working out the
limitations I just threw a dialogue box asking the user to manually amend the
links and the restart the code.

Sorry for not being able to answer positively, thought the workaround many
be useful though.

 

[Guest]

Thank you for the answer.
Evidently, it is acceptable way to avoid length limitations, but I couldn't
understand when exactly I should threw a dialogue box, because when workbooks
is opened, it is already damaged. Unfortunately, Excel application has no
event "BeforeOpen".
Anyway, thank you very much, I'll try to do something in that way.

 

[michael.beckinsale]

Irina,

I have used the following method to achieve what you want. It may not
be very efficient and depending on the size of your workbook may be
quite slow but it will definitely be quicker that amending the
formula's manually. From memory this is what l did,


Use the Workbook_Open event to suppress Excel's alerts

ie Application.DisplayAlerts = False

then do a find / replace

ie find =
replace '=

this effectively changes the formula to text and avoids having to wait
for Excel to re-calculate all the time. (You might consider turning the
calculation mode to manual)

Then do a find / replace to change the workbook name

ie find currentlink.xls
replace newlink.xls

Than convert the text back to formula's

ie find '=
replace =

When l was doing it l seem to remember having a few syntax problems so
make sure that save your file before testing the code and test the code
on a small number of cells first.

Regards

Michael Beckinsale

 

[Guest]

I found answer myself. It is a known bug of Microsoft Excel, it is described
in
Article ID: 817578. It is written, that the bug is fixed in Microsoft Office
XP SP3, but it is not the truth.