Code to check if Analysis TookPak is installed

Sticking this in the workbook module should do it. You can do it for your users by placing this code in the ThisWorkbook module of the workbbook you are sending.

To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.




Private Sub Workbook_Open()
On Error GoTo EH
If Not AddIns("Analysis ToolPak").Installed Then
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End If
Exit Sub
EH:
MsgBox "FYI, the Analysis ToolPak is not available on your system," & vbCrLf & _
"so the operations in this workbook will not work properly.", 48, "Be advised..."
Err.Clear
End Sub

Thanks Tom

In the meantime, I did create a simple code that detected if the TookPak was not installed upon the Open event, displaying a message box if so. This code was in a new workbook that I had opened to test the code, ie there was nothing else in the workbook.

To test it, I uninstalled the TookPak, closed the workbook, and then reopened the workbook, with the msgbox displaying as intended.

I then cut and pasted this code into the This Workbook module in a very large model I am working on (with many circular references), uninstalled the ToolPak and then closed the workbook.

Upon reopening the workbook, instead of the message displaying, a VBA error message was displayed, saying error in hidden workbook (btw I have password protected the VBA code). The workbook then had #VALUE errors all through the workbook (these took some time to resolve!!).

Any idea why the error message was displayed instead of my msgbox, and are the #VALUE error messages connected with the VBA error message, or would they instead be due to the fact that the TookPak was not installed (btw re-installing the TookPak did not resolve the issue)?

Any thoughts greatly appreciated.

Cheers

pvr928

Hard to say why that happened. Perhaps if you post the code you wrote that caused this behavior, someone may be able to shed light on it. Sometimes, VBA Project references can be established or disengaged when a certain workbook is opened, and sometimes the version a workbook is being run on can cause odd behavior when the app was created on a different version. Hard (at least for me) to diagnose from a distance the why of what happened to you, but maybe someone reading this has had that happen to them and can offer an explanation.