Excel Formulas: Links vs References

[kjv1611]

We have a centralized XLA file that is referenced by VBA in several different Excel workbooks. For some reason, though, the formulas end up forcing a link to the same file where we already have a VBA reference. This doesn't happen all the time, but just so far in a couple of cases that I can find. In one case (what brought it to my attention), Excel prompts the user at each open of the file to "Update Links" and I'd really like to get rid of that prompt, as well as the link altogether.

Can anyone explain why Excel is linking to the XLA file when I've already created a reference? Is there some way I can search the custom FUNCTIONS in the XLA file and find the answer?

Do some custom VBA Functions require linking to the file if external vs just used as a reference? If so, how can I change those functions to not require the link?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[LaurieHamlin]

I'm not sure I completely understand - did you copy the worksheet?
I've seen this in formulas copied from one workbook to another. I just do a replace on the old workbook name with replace with field blank.
I hope that helps you.

 

[kjv1611]

No, it's not a copy. I know what you're talking about - common issue, and usually pretty easy to fix.

This one is an Excel add-in file (.XLA), not a standard workbook. There are no worksheets to copy from. Just VBA Functions used in formulas in various Excel workbooks.

We connect to this .XLA file by Tools -> References in the VB Editor window, then reference the formulas as if already in the active workbook. Works fine, but I've found a couple that are creating links to the .XLA file which baffles my mind, as I cannot see why in the world it's happening. No formulas reference the file by name. There are no data connections, just links. If I remove the link, it removes half the formulas, so I have to re-enter those. Then at some point while entering the formulas, it'll attempt to relink to the XLA file.

Seems crazy to me, but there must be a specific reason.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I'm not overly concerned with it, but it just bugs me. No one else seems concerned, but I'd like to correct it all the same.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[LaurieHamlin]

Did you try formatting the cells?

 

[kjv1611]

Not sure I follow. You mean the number format or cell color or something? Not sure how that would change anything. The only thing I modified after removing the link was to enter in the correct formulas.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[LaurieHamlin]

It probably won't make a difference, but try formatting it to text.

 

[kjv1611]

I may try that later just to verify, but I'd be shocked to no end if formatting a cell changed links at all. I'm in the middle of some other items where I cannot test anything, but I do plan on looking at a copy of one of the affected workbooks again soon. Thanks

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57