Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

by Gregory on February 28, 2012

Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older than 2007, check if they have the Analysis ToolPak installed, and then tell them, with a pop-up message, to install the Analysis ToolPak, so all the formulas will work correctly.

Create a Routine to Check for the Analysis ToolPak

Open the Visual Basic Editor (Alt+F11) then choose Insert > Module and enter the following VBA code.

Sub CheckToolPak()
' Checks to see if the Excel version comes before Excel 2007,
' if so then checks to see if the Analysis ToolPak is installed.
' If not, then notify the user and end the program.
' This is required because of the NETWORKDAYS formula
' that is used in the charts.
If CInt(Application.Version) < 12 Then If AddIns("Analysis ToolPak").Installed <> True Then
MsgBox "Please install the Analysis ToolPak." & vbCr & vbCr & _
"Choose Tools > Add-Ins... " & vbCr & _
"then check the box for Analyiss ToolPak, and click OK."
End If
End If
End Sub

The code is somewhat self-explainatory except that Application.Version returns a text value, hence you see the CInt function that converts that text value to an integer so that we can tell if the Excel version is less than 12, which is the version number of Excel 2007.

Create a Workbook Open Event

This will do the trick, but needs a trigger to call the routine. So double-click ThisWorkbook in the Project Explorer of the VBA Editor, then select the drop-down at the top-left of the window – where you see (General) – and select Workbook. The Private Sub Workbook_Open() subroutine will appear with no code.

Type in Call CheckToolPak and then save the file.

Private Sub Workbook_Open()
Call CheckToolPak
End Sub

Now when you open the file with Excel 2003, and don’t happen to have the Analysis TookPak installed, you get the following message.

Analysis ToolPak PopUp

If users have the Analysis ToolPak Add-In installed in Excel 2003 there is no message, nor if the user is using Excel 2007, 2010 or 2011.

Related Posts Plugin for WordPress, Blogger...

Comments on this entry are closed.

Previous post:

Next post: