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: Idea to load/unload an Excel Add-in file when a Workbook is opened... Good Idea / Bad Idea??

Excel: Idea to load/unload an Excel Add-in file when a Workbook is opened... Good Idea / Bad Idea??

Excel: Idea to load/unload an Excel Add-in file when a Workbook is opened... Good Idea / Bad Idea??

(OP)
I was looking at ways people close out Excel add-in files (XLA files) when working with VBA, because I'd like to forefully close an XLA file whenever a used workbook closes, because there have been hiccups here and there that I've known about since I've been here, and I hope closin the file will help.

In this thread on another site:
http://www.ozgrid.com/forum/showthread.php?t=45118
,

The OP eventually pulled together this script that he stated worked great for what he was doing, and that simply telling Excel to close the file did not work.

CODE

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    AddIns("Custom Functions").Installed = False 
End Sub 
 
Private Sub Workbook_Open() 
    AddIns.Add Filename:=ThisWorkbook.Path & "\Custom Functions.xla" 
    AddIns("Custom Functions").Installed = True 
End Sub 

My question is: Can anyone vouch for this type setup working well or else can anyone think of any issues I might run into if I tried setting up workbooks to do this?

Furthermore, if that idea works, I think it would work EVEN better for me. When I first began working in my current job, I found instances where the "Test" version of an addin was being called by "production" workbooks when they should have been pulling in the "production" excel add-in file. So that caused all sorts of headaches. I've corrected all that I am aware of to date, but the above gave me (I think) a great idea: I can tell Excel to load the correct add-in based on the folder structure the opened Excel file lies within.

With all those thoughts together: Can anyone tell me this is a crazy idea and that it's more likely to blow up in my face rather than help?

Thanks for any thoughts

p.s.: Additional point of clarification. With my current setup, the XLA file is referenced within the VBE by files that use it. So I have to add a reference to the XLA file for each of the Excel files. If it's a file in test that is moved to production, then I must manually remove the .XLA test file, and then add the .XLA production file under Tools->References in the VBE.

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

RE: Excel: Idea to load/unload an Excel Add-in file when a Workbook is opened... Good Idea / Bad Idea??

(OP)
I think I may have considered a problem with this method. In order for it to work, I believe I'd have to change security settings at everyone's computer that would use the workbooks to allow VBA access to the visual basic editor:

Quote (Trust Center Settings)

Developer Macro Settings
Trust access to the VBA project object model

So the benefit doesn't seem to outweigh the particular cost.

I can try it on my own machine just to see, though, using the AddIns("Custom Functions").Installed = True setup to see how that works.

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

RE: Excel: Idea to load/unload an Excel Add-in file when a Workbook is opened... Good Idea / Bad Idea??

(OP)
So far the linked method does not seem to work for the xla files I'm trying to use.

The more I think about the use case scenario, the more I realize it's probably not worth trying on my end anyway. If I want to automatically check things, I probably would be better served to just loop through all working files in a folder, and verify they are pointing to the correct xla file myself from my machine.

"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