Add-ins Referencing

[EA]

I am having lots of trouble understanding Excel references, or more
accurately how the various interfaces work.

When I open Excel I look in Tools | Add-Ins and I see that I have the
following Add-Ins ticked:

Analysis ToolPak
Analysis ToolPak VBA
Solver Add-in
VBA Code Cleaner 4.4

I open up the VBA editor and I see the following Add-in files opened:

atpvbaen.xls (ATPVBAEN.XLA)
funcres (FUNCRES.XLA)
VBACodeCleaner (VBACodeCleaner.xla)

QUESTION ONE - where is the solver Add-in? why is it not open?

In the VBA editor when I look in Tools references I see the following
ticked:

Visual Basic For applications
Microsoft Excel 10.0 Object library
OLE Automation
Microsoft Office 10.0 Object Library

I see the following but unticked (there are lots of others):

atpvbaen.xls
funcres
VBACodeCleaner

QUESTION TWO - Where is the Solver reference, even if it is unticked why it
is not show even?

QUESTION THREE - What does it signify when a reference appears above
unticked, even though it is ticked under Tools | Add-ins?

QUESTION FOUR (AND MOST IMPORTANT) - what is the relationship between the
Tools | Add-ins interface and the VBA Tools | References. If I add a
reference using:

 

[raypayette]

QUESTION ONE - where is the solver Add-in? why is it not open?
*=>*It is not there because it wasn't installed.

QUESTION TWO - Where is the Solver reference, even if it is unticked
why it
is not show even?
*=>*It is not there because it wasn't installed.

QUESTION THREE - What does it signify when a reference appears above
unticked, even though it is ticked under Tools | Add-ins?
*=>* These are the add-ins that are recognized as being available. They
are installed in your computer.

QUESTION FOUR (AND MOST IMPORTANT) - what is the relationship between
the
Tools | Add-ins interface and the VBA Tools | References. If I add a
reference using:
*=>* If you tick the option box of an add-in, you will be able to use
it because it is installed in Excel.

 

[Rob Bovey]

QUESTION ONE - where is the solver Add-in? why is it not open?

Solver is what's called a demand-loaded add-in. These add-ins don't get
opened until you actually use them (it's a bit more complicated than that,
but Solver is probably the only demand-loaded add-in still around, so don't
worry about the details). Click the Tools/Solver menu to open the Solver
dialog, then just close the dialog. If you now look back in the VBE you will
see a new entry, Solver.xla. Clicking the Solver menu forced Excel to open
the Solver add-in.

QUESTION TWO - Where is the Solver reference, even if it is unticked why
it is not show even?

Once you've followed the steps above and Solver.xla is open, you will
see Solver listed in the VBE Tools/References list.

QUESTION THREE - What does it signify when a reference appears above
unticked, even though it is ticked under Tools | Add-ins?

With the exception of Solver, covered above, selecting an add-in in the
Tools/Add-ins menu forces Excel to open that add-in workbook. All open
workbooks, including add-in workbooks (as well as lots of other things that
aren't workbooks) appear in the Tools/References list so that you can use
them if you like.

Visual Basic For applications
Microsoft Excel 10.0 Object library
OLE Automation
Microsoft Office 10.0 Object Library

These four items that you see selected automatically in the
Tools/References dialog are references that are either required in any Excel
VBA project (the first two) or are so commonly used that you'd have trouble
doing without them (the second two).

QUESTION FOUR (AND MOST IMPORTANT) - what is the relationship between the
Tools | Add-ins interface and the VBA Tools | References.

There is really no direct relationship between them. The Tools/Add-in
interface is one way to managing the loading of add-ins in Excel. You could
just as easily use File/Open to do this each time you started Excel, but it
would be a lot less convenient. All open workbooks, regardless of how they
were opened or created and regardless of whether they are regular workbooks
or add-ins, appear in the Tools/References list.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm