Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. Add a Command button to Form1.
  3. 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.
  4. 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
  5. 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.

↑ Back to the top


References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
213489 Add-ins don't load when using CreateObject command
141684 How to use the Histogram add-in
214269 How to use the Histogram add-in in Excel 2000
For more information about Office Automation, visit the following Microsoft Web site:

↑ Back to the top


Keywords: kbexpertiseinter, kbautomation, kbhowto, kbprogramming, KB270844

↑ Back to the top

Article Info
Article ID : 270844
Revision : 7
Created on : 3/29/2007
Published on : 3/29/2007
Exists online : False
Views : 413