1. Install Analysis Toolpak Excel

I 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 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) True Then MsgBox 'Please install the Analysis ToolPak.'

Install Analysis Toolpak Excel

& vbCr & vbCr & 'Choose Tools Add-Ins. ' & vbCr & 'then check the box for Analyiss ToolPak, and click OK.' End 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 WorkbookOpen subroutine will appear with no code. Type in Call CheckToolPak and then save the file.