How to create an Excel histogram by using Automation and Analysis ToolPak

View products that this article applies to.

Summary

This article provides a sample Visual Basic project that shows how to use the Analysis ToolPak (ATP) to generate a histogram. This article also illustrates basic techniques for implementing and using the Analysis ToolPak add-in library, and the Analysis ToolPak Visual Basic for Applications (VBA) template add-in with Automation.

↑ Back to the top

More information

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
Add a Command button to Form1.
On the Project menu, select References, and then select the Microsoft Excel Object Library. Note that the version varies depending on the Microsoft Office version that is installed on your system.

Add the following code to the Command1_Click event:

Private Sub Command1_Click()
''' This project requires project references to
''' The Excel object library - e.g. Excel9.olb or Excel8.olb

    Dim oXl As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRange As Excel.Range
    Dim oAddIn As Excel.AddIn

    'Launch Excel and make it visible
    Set oXl = CreateObject("Excel.application")
    oXl.Visible = True
    Set oBook = oXl.Workbooks.Add
    Set oSheet = oBook.Worksheets.Item(1)
    
    ' Add the Excel Analysis ToolPak library
    oXl.AddIns.Add FileName:=oXl.LibraryPath & "\analysis\analys32.xll"
    Set oAddIn = oXl.AddIns.Item("Analysis ToolPak")
    
    ' Register all the Analysis ToolPak functions
    '  See Microsoft Knowledge Base Article 213489
    oXl.RegisterXLL "Analys32.xll"
    
    ' Add the Excel Analysis ToolPak - VBA AddIn -
    '  it's the Automation interface to the Analysis ToolPak library
    ' Now open the .xla so that you can run its Auto_Open macro now, and others later.
    ' See Microsoft Knowledge Base article 213489
    oXl.Workbooks.Open oXl.LibraryPath & "\analysis\atpvbaen.xla"
    oXl.Workbooks("atpvbaen.xla").RunAutoMacros 1
    
    'Excel 2007 uses the xlam file extension
    'oXl.Workbooks.Open oXl.LibraryPath & "\analysis\atpvbaen.xlam"
    'oXl.Workbooks("atpvbaen.xlam").RunAutoMacros 1
    
    ' Fill the worksheet with some data
    ' Create and fill the Input Range - See Microsoft Knowledge Base
    ' Article 141684 or 214269
    Set oRange = oSheet.Cells(1, 1)
    oRange.Value = "87"
    Set oRange = oSheet.Cells(2, 1)
    oRange.Value = "27"
    Set oRange = oSheet.Cells(3, 1)
    oRange.Value = "45"
    Set oRange = oSheet.Cells(4, 1)
    oRange.Value = "62"
    Set oRange = oSheet.Cells(5, 1)
    oRange.Value = "3"
    Set oRange = oSheet.Cells(6, 1)
    oRange.Value = "52"
    Set oRange = oSheet.Cells(7, 1)
    oRange.Value = "20"
    Set oRange = oSheet.Cells(8, 1)
    oRange.Value = "43"
    Set oRange = oSheet.Cells(9, 1)
    oRange.Value = "74"
    Set oRange = oSheet.Cells(10, 1)
    oRange.Value = "61"
    
    ' Create and populate the Bin Range
    Set oRange = oSheet.Cells(1, 2)
    oRange.Value = "20"
    Set oRange = oSheet.Cells(2, 2)
    oRange.Value = "40"
    Set oRange = oSheet.Cells(3, 2)
    oRange.Value = "60"
    Set oRange = oSheet.Cells(4, 2)
    oRange.Value = "80"
    
    ' Chart the Histogram on a new Worksheet
    ' See Microsoft Knowledge Base article 213489
    oXl.Run "ATPVBAEN.XLAM!Histogram", oXl.ActiveSheet.Range("$A$1:$A$10"), _
            "", oXl.ActiveSheet.Range("$B$1:$B$4"), _
            False, False, True, False ' True = Chart
            
    'Excel 2007 uses the xlam file extension
    'oXl.Run "ATPVBAEN.XLAM!Histogram", oXl.ActiveSheet.Range("$A$1:$A$10"), _
    '        "", oXl.ActiveSheet.Range("$B$1:$B$4"), _
    '        False, False, True, False ' True = Chart
    
    'Clean up
    Set oAddIn = Nothing
    Set oRange = Nothing
    Set oSheet = Nothing
    Set oBook = Nothing
    oXl.UserControl = True
    Set oXl = Nothing

End Sub

Run the project, and then click the Command button on Form1. Results: The histogram's input range and bin range are added to Sheet1!A1:A10 and Sheet1!B1:B4, respectively. The histogram's output range and chart are then added to a new worksheet ply in the workbook.