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

[kjv1611]

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.

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

 

[kjv1611]

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:

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

 

[kjv1611]

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