INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel Formulas: Links vs References

Excel Formulas: Links vs References

(OP)
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

RE: Excel Formulas: Links vs References

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.

RE: Excel Formulas: Links vs References

(OP)
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

RE: Excel Formulas: Links vs References

(OP)
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

RE: Excel Formulas: Links vs References

Did you try formatting the cells?

RE: Excel Formulas: Links vs References

(OP)
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

RE: Excel Formulas: Links vs References

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

RE: Excel Formulas: Links vs References

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close