Analysis ToolPak - VBA

When the "Analysis-ToolPak" add-in is installed you will see an additional project in your project window called "FUNCRES.XLAM".
You must have the "Analysis-ToolPak - VBA" add-in installed if you want to use any of the extra worksheet functions from your VBA code.
You must also have the "Analysis-ToolPak" add-in installed in order for this to work. You will receive a "Type Mismatch" error otherwise.

When the "Analysis-ToolPak - VBA" add-in is installed you will see an additional project in your project window called "ATPVBAEN.XLAM".
This add-in actually just provides the interface for the "ANALY32.XLL" file and both these files can be found in the following folders:

Excel 365 - C:\Program Files\Microsoft Office\Office16\Library\Analysis\ 
Excel 2021 - C:\Program Files\Microsoft Office\Office16\Library\Analysis\ 
Excel 2019 - C:\Program Files\Microsoft Office\Office16\Library\Analysis\ 
Excel 2016 - C:\Program Files\Microsoft Office\Office15\Library\Analysis\ 

Using the Data Analysis

You can use the macro recorder to help pass in the correct parameters.

Application.Run "atpvbaen.xlam!Histogram", _ 
                 ActiveSheet.Range("C6:D10"), _ 
                 ActiveSheet.Range("E2"), _ 
                 , , true, true, false, false 

If you are using Excel 2003, change the filename to "atpvbaen.xla".

Using the Functions

These functions can be called in exactly the same way as the regular worksheet functions.

Call Msgbox(Application.WorksheetFunction.Complex",3,3)) = 3+3i 

You can also call these functions using the Application.Run("Function Name","Parameters") syntax although this is only available for backwards compatibility.

Call Msgbox(Application.Run("atpvbaen.xla!Complex",3,3)) = 3+3i 

The parameters passed in MUST ALWAYS be range objects and not just strings containing a range

Adding an additional Reference

You can alternatively add an explicit reference to the "atpvbaen.xls" file. This can be done by selecting (Tools > References) within the Visual Basic Editor.

This will allow the functions to be called without any prefix, although including a reference to the file reminds people that the function is defined in a separate file.

Call MsgBox(complex(3, 3)) = 3+3i 
Call MsgBox([atpvbaen.xls].complex(3, 3)) = 3+3i 

If you are going to qualify the function then the square brackets are needed as the library name contains a full stop.

Important

If you have both the "Analysis-ToolPak" add-in and the "Analysis-ToolPak - VBA" addin installed, you will see duplicate functions appear in the (Insert > Function) dialog box. It does not matter which one you use (uppercase or lowercase) as it will automatically be converted to the correct one.
All the Analysis-ToolPak functions will return a valid error value if they are passed the incorrect parameters. This error value can be detected using the ISERROR function.
An error will occur if you try to run an Analysis-ToolPak function when the addins are not installed. It is worth writing a quick function to check that the add-ins are installed before calling any of the functions.

Debug Version

It seems that the VBA Analysis Toolpak add-in that was distributed with Excel 2002 and Excel 2003 is a debug version.
When you select (Tools > Add-ins) and load the Analysis ToolPak - VBA add-in the following code gets written to the Immediate window.
The two lines at the bottom appear whenever you call an Analysis ToolPak function from VBA (these lines are generated from WEEKNUM() function).