Register or Login to browse without ads

Tue 2 Sep 2014 - 4:17 am UTC

Home | Ask a Question | Browse Questions

ANSWERED on Sat 20 Mar 2010 - 10:14 pm UTC by mathtalk

Question: Creating add-in modules in Excel 2007

Please carefully read the Disclaimer and Terms & conditions.
Priced at $25.00
The customer tipped the researcher $25.00

Actions: Add Comment

Sat 20 Mar 2010 - 7:16 am UTC

Question

prof wonmug
Customer

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
Data\Microsoft\AddIns

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

 
 

Sat 20 Mar 2010 - 12:19 pm UTC

Uclue Researcher Request for clarification

mathtalk
Researcher

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,
mathtalk

 

Sat 20 Mar 2010 - 7:20 pm UTC

Question clarification

prof wonmug
Customer

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.

 

Sat 20 Mar 2010 - 7:30 pm UTC

Uclue Researcher Request for clarification

mathtalk
Researcher

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.

regards, mathtalk

 

Sat 20 Mar 2010 - 10:14 pm UTC

Uclue Researcher Answer

mathtalk
Researcher

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!

regards, mathtalk

 
 

Sun 21 Mar 2010 - 1:12 am UTC

Accepted and rated

prof wonmug
Customer

This solution does exactly what I needed and the instructions are perfectly clear.

Thank you for the help and for a valuable tool. :-)

 

Sun 21 Mar 2010 - 2:34 am UTC

Comment

prof wonmug
Customer

BTW: The keyboard shortcut for the Office 2007 Office button is Alt-F, just like the File option in earlier version, which is replaced.

You can get to the Add-In list from the keyboard with just a few keystrokes:

1. Alt-f  (Office button)
2. i      (Excel options)
3. Down arrow until Add-Ins is highlighted
4. Alt-g  (Go...)

 

Actions: Add Comment

 

Frequently Asked Questions | Terms & Conditions | Disclaimer | Privacy Policy | Contact Us | Spread the word!

© 2014 Uclue Ltd