0

I've been working on a project for my team at work to use. The Excel 2003 spreadsheet is on an NT NFS so they can all use it. I like XLAs, but one annoying thing about them is it seems that they don't seem to be workbook dependent. Installing it for one installs it for the rest, and vice versa. With other languages at compile time you can easily link libraries for specific source files.

I was wondering is it possible to have workbook dependent Excel VBA custom libraries and how to do this? Thanks

Wes
  • 1,183
  • 3
  • 23
  • 51
  • The beauty of VBA is that the code is tied to the excel document itself. What are you doing that requires the add-in? Using the add-in will add more maintenance, and generally are used if you need code to apply across all workbooks equally. – Fink Dec 28 '11 at 16:05
  • You mean, other than modules that you can export and import as *.bas files? If that does not satisfy the "workbook dependent Excel VBA custom libraries" requirement, then please clarify why not. – Jean-François Corbett Dec 28 '11 at 18:13

2 Answers2

1

I use CustomDocumentProperties for this purpose. The add-in contains all the code and the "template" is the document to work on. In the template, create a CustomDocumentProperty called "MyApp" or whatever. In the add-in, use application level events to monitor workbook_open calls. In that event, check to see if the opened workbook contains the CustomDocumentProperty for your app. If it does, expose the UI of the add-in (like make the menus visible).

See also http://www.dailydoseofexcel.com/archives/2004/07/16/custom-document-properties/

In that example, I use the Window_Activate event. If the correct type of workbook is activate, I show the custom toolbar. If it's deactivated, I hide it.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • When I said a document what I should have said was this document is just the master file. Each team member remotely makes changes to an external Excel document and remotely "commits" these changes to the master file, which was the file I was referring to. The reason for this is I need the ability to merge changes made to shapes, and Share Workbooks doesn't do this, and only one person can have write access to the document at a time. Anyway these development areas all share mostly the same VBA code, and every time a change gets made to one it must be made to the others. – Wes Dec 29 '11 at 03:37
  • Import/export modules won't work because every time a change gets made to the .bas file it must be re-imported manually for each and every remote Excel document. Add-ins solve this because I only need to make changes to a single place once. – Wes Dec 29 '11 at 03:46
  • In response to Dick, I will exploit what you suggested because I think it helps me a little bit. But the fundamental problem still remains, if you have a bunch of excel workbooks open and only one of them uses an Add-in, how to not have that add-in installed for all of them and just one of them? Maybe I'm just nitpicking. – Wes Dec 29 '11 at 04:44
0

The solution I went with was to write my own custom code for loading and unloading add-ins depending on which workbooks are opened. This was painful but it accomplished the functionality I desired perfectly. So that they could be shared by everybody, I put them on a NFS and made them read-only. When I want to change the libraries, I open them for write briefly.

Wes
  • 1,183
  • 3
  • 23
  • 51