20 Mar 2010 07:16 UTCSat 20 Mar 2010 - 7:16 am UTC
I have an add-in module (Module 1.xlam) that I have used for several years. I want to create a new one so I can group functions by type. I made a copy of the current module with a different name (Module 2.xlam). It is in the same folder as the first one
C:\Documents and Settings\Administrator\Application
When I open VBA (Alt-F11), I see Module 1, but not Module 2.
Can someone tell me how to "install" this new add-in module?
Excel 2007 on Win XP
20 Mar 2010 12:19 UTCSat 20 Mar 2010 - 12:19 pm UTC
Hi, Prof Wonmug:
How did you create your copy of the current module? If you want a second copy of the same module with a different name, my suggestion would be to use Excel's File > Save As menu function, rather than to make a file copy via Windows Explorer or via the command file. A problem with the latter is that the second add-in file will internally have the same module name as the first and the VBA Project Explorer will not be able to display them distinctly.
But I wonder if you would not benefit from creating a second VBA module within the same add-in file, but having a new and different module name?
Looking forward to your clarification,
20 Mar 2010 19:20 UTCSat 20 Mar 2010 - 7:20 pm UTC
Yes, I made a copy of the .xlam file using Windows Explorer and gave it a new name.
I think we need to clarify terms here.
In the \Addins folder, there are two files:
Add-in 1.xlam The original add-in file
Add-in 2.xlam The copy I made
When I open Excel and then open VBA, I see [Add-in 1] listed as a VBAProject. It has 4 modules: Test, Util, Sheet1, and ThisWorkbook. I created Test and Util. I put new functions in the Test module until they are debugged, then I move them to the Util module. This has been working for several years.
Now I have several new functions connected to a new project. I want to create a new Project so I can keep the functions that are peculiar to that project together and not cluttering up my general add-in. I also may want to send them to some colleagues.
So, I don't want to create a new module in [Add-in 1].
With that in mind, I don't understand your suggestion to use Excel's File | Save As.... I don't see that option from the VBA Editor toolbar and I can't see how it will help to do it from Excel itself, because I am not trying to create a new workbook.
20 Mar 2010 19:30 UTCSat 20 Mar 2010 - 7:30 pm UTC
Here's an experiment to try. Go into the directory:
C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns
with Windows Explorer, and pull up the Properties for Module2.xlam (if that's the name of the new file). Under the Summary tab (third tab of that dialog box) revise the Title and Comments fields to something different than what you had in Module2.xlam. Click OK to save and close the dialog.
Now in Excel with a Workbook open (not the VBA Editor), click on the menu item Tools > Add-ins..., which brings up an Add-Ins dialog box. You will probably see your old Module1 add-in in that list, under whatever "internal" name you've given it.
Click the Browse... button at right and navigate to the AddIns directory as above (under the user's Documents and Settings directory) where both the old and new .xlam files are located. Click on the new one (to highlight it) and hit the OK button.
Now you're back at the list of "installable" Add-Ins, and you should see a check in the box next to Module2. However the name is not Module2 but whatever you changed the Title to in the Properties (earlier step), and if you highlight that Add-In, the Comment you changed in the Properties will be displayed at the bottom of the dialog. Leave the checkbox marked and click
Go into the VBA Editor and you will see the "Project" Module2 in the list with Module1 at upper left.
20 Mar 2010 22:14 UTCSat 20 Mar 2010 - 10:14 pm UTC
Microsoft has moved things around a lot in Excel 2007, so my experiment lacked a few important details.
The "Tools" menu is almost not to be found. What one needs to do (from the Worksheet view rather than the VBA view) is to click the Office 2007 logo in the upper left corner, more or less where File used to be.
This brings up a long drop down menu, at the very bottom of which (not in the list) are a couple of buttons. You want the one that says "Excel options". That brings up a dialog on which there is an Add-Ins item (sort of like tabs but running along the left side). Clicking the Add-Ins item takes you to a list of Add-ins, ordered first as to the Add-ins that are "active" and then those that are "inactive".
However to move an add-in from "inactive" to "active" one has to do one further piece of business. Along the lower left margin is a label "Manage" next to a drop down list, followed by a button that says Go...
Make sure the drop down list is showing Excel Add-ins and hit the Go... button. This brings up the same simple checkbox dialog we used to reach by the menu Tools > Add-ins. Checkboxes are there next to "installed" add-ins, and if you want to activate one already in your Addins directory, you just need to mark the checkbox and hit OK. (The Browse button is still there as well if you need to copy an add-in from a different directory.)
After the add-in is installed in this way, it will appear in the VBA editor more or less the way it appeared in earlier Excel versions (there's an outline control on the upper left that lists "projects").
I hope this Answer will get you moving forward again!