VBA – Using the Analysis ToolPak in VBA

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on June 29, 2023

This tutorial will show you how to use the Analysis ToolPak in VBA.

The Analysis ToolPak in Excel is an add-in created to enable you to do complicated Data Analysis.   If you wish to call these functions in VBA code, you need to enable the Analysis ToolPak-VBA Add-in.

Enabling the Add-In

Click on the File Menu, and then click Options, Add-ins.

vba analysis toolpack add in

Click on the Go button.

vba-analysis-toolpack-add in enable

Make sure the Analysis ToolPak-VBA is ticked and click OK.

You will now be able to call the Functions contained in the Excel Analysis ToolPak from VBA.

Using the Moving Average in VBA

Consider the following worksheet.

vba analysis toolpak sheet

To use a function from the add-in, you need to use Application.Run and then the name of the function you wish to call.

To calculate the moving average in column F, you can write the following code.

Sub CalculateMovingAverage()
   Dim rngInput As Range
   Dim rngOutPut As Range
'populate the ranges
   Set rngInput = Range("E6:E10")
   Set rngOutPut = Range("F6:F10")
'call the function from the add in
   Application.Run "Moveavg", rngInput, rngOutPut, , False, True, False
End Sub

vba analysis toolpak sheet generated

If you do not want to output a chart, amend the Application.Run line to this

 Application.Run "Moveavg", rngInput, rngOutPut, , False, False, False


VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples