Verify Analysis ToolPak with VBA

[Horatio J. Bilge, Jr.]

I created a workbook that requires the Analysis ToolPak and Analysis
ToolPak-VBA add-ins to function. I've discovered that many of the people I
gave the workbook to do not have those add-ins enabled, so they get the
#NAME? error.

Is there a way to automatically check for the Analysis ToolPak? I am
thinking that if the ToolPak is not enabled, a message box would pop up
telling the user what the problem is, and how to install the ToolPak.

Thanks!

 

[Harald Staff]

Try to enter an ATP function into a free cell and see if it errs or not.

HTH. Best wishes Harald

 

[Bob Phillips]

What Harald is suggesting (I think!) is that this can be just as well
handled procedurally, when you send out the workbook, send them instructions
on how to check (as Harald suggests) and how to correct.

Don't complicate the code unnecessarily.

 

[Victor Lobo]

I created a workbook that requires the Analysis ToolPak and Analysis
ToolPak-VBA add-ins to function. I've discovered that many of the people
I
gave the workbook to do not have those add-ins enabled, so they get the
#NAME? error.

Is there a way to automatically check for the Analysis ToolPak? I am
thinking that if the ToolPak is not enabled, a message box would pop up
telling the user what the problem is, and how to install the ToolPak.

Thanks!

Hi,

Maybe you can modify the code here:
http://www.cads-sierraleone.org/microsoftexcelltips.htm
(Look under "CHECKING IF YOUR ADD-INS ARE ADDED")


Hope this helps

 

[Victor Lobo]

Hi,

Maybe you can modify the code here:
http://www.cads-sierraleone.org/microsoftexcelltips.htm
(Look under "CHECKING IF YOUR ADD-INS ARE ADDED")


Hope this helps

On reflection, I agree with what Bob and Haral say. Sending out
instructions should be the way to do it.

 

[Gord Dibben]

You could take it out of their hands and enable the add-ins yourself.

Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End Sub

Be sure to change to False on closing the workbook.

If you just want a message as you asked use this example code in
workbook_open

Private Sub Workbook_Open()
Set a = AddIns("Analysis ToolPak")
If a.Installed = True Then
MsgBox "The ATP add-in is installed"
Else
MsgBox "The ATP add-in is not installed"
End If
End Sub


Gord Dibben MS Excel MVP

 

[Bob Phillips]

That only works if it was installed when Excel was installed.

 

[Mike Middleton]

Horatio J. Bilge, Jr. -

You may need to have two sets of VBA code or instructions depending on the
version of Excel. The functions provided by the pre-2007 Analysis ToolPak
are native worksheet functions in Excel 2007 (therefore not requiring the
ATP add-in).

- Mike Middleton
http://www.MikeMiddleton.com

 

[Horatio J. Bilge, Jr.]

I liked the sound of using VBA to enable the ToolPak, but I guess your
suggestion of notification is the best. If a user didn't install the ATP
during setup, I would have to go the simple notification route anyway. I just
added the appropriate instructions to a "Help" sheet in the workbook.

Thanks for the help.
~ Horatio

 

[Horatio J. Bilge, Jr.]

If that is the case, then why do they still have the Analysis ToolPak
available for Excel 2007? I found instructions for loading it in 2007...
http://office.microsoft.com/en-us/excel/HP100215691033.aspx

Thanks,
~ Horatio

 

[Dave Peterson]

All those data|data analysis tools (in xl2003 menus) are still part of the
Analysis toolpak.

 

[Horatio J. Bilge, Jr.]

I see. Thanks.

All those data|data analysis tools (in xl2003 menus) are still part of the
Analysis toolpak.