Analysis ToolPak 


In addition to the many functions that are built in to Excel itself, there are dozens of additional functions for Dates, Statistics, Finance, and Engineering  provided by the Analysis Tool Pack (ATP).  The add-in module must be installed before you can use the function in it.  

 

 

Installing The Analysis Tool Pack 

Since the ATP is provided free with Excel, you already have it on your computer.  To install it and make its functions available in your workbooks, go to the Tools menu, and select the Add-Ins item.  This will display a dialog box which lists all of the Add In modules that Excel found in the standard Library folder (typically C:\Program Files\Microsoft Office\Office\Library).  Find Analysis ToolPak in the list, and place a check mark in the box next to it. 

If you try to use functions from the ATP when it is not installed, you'll receive a #NAME? error. 

 

 

Using ATP Functions In VBA Procedures

You can call most standard Excel worksheet functions in VBA procedures using the syntax

Result = Application.WorksheetFunction.Sum(Range("A1:A10"))

 

         

However, using ATP functions in VBA requires a bit more work to get things set up.   In Excel open the Add-Ins dialog as described above, and put a check next to Analysis ToolPak VBA in the list.  If you are not going to use the ATP functions in your workbook, but only from within VBA you don't need to install Analysis ToolPak -- you only need the  Analysis ToolPak VBA module.   Then, open the VBA Editor, and select your workbook from the Project Explorer window.  You can open this window from the View menu, or by pressing Ctrl+R.  Go to the Tools menu, and select the References menu item.  From the list of references, find and check atpvbaen.xls (which is short for Analysis Tool Pack Visual Basic for Applications English).  

 

 


Now, you can use any function in the ATP just as you would use a standard VBA function.  For example, 

Result = MRound(SomeVar, 5) 

If there is the possibility that one of your own procedures may have the same name as an ATP function, you can fully qualify the APT function call with the APT library.  This will ensure that the proper function is called. E.g,

Result = [atpvbaen.xls].MRound(SomeVar,5)

The square brackets are required around the library name, because it contains a period, which is a non-standard character in library names.