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.

alt text

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.

alt text

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.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext