Microsoft KB Archive/270844

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

Article ID: 270844

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition

-



This article was previously published under Q270844



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.



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(&quot;Excel.application&quot;) 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 & &quot;\analysis\analys32.xll&quot; Set oAddIn = oXl.AddIns.Item(&quot;Analysis ToolPak&quot;) ' Register all the Analysis ToolPak functions ' See Microsoft Knowledge Base Article 213489 oXl.RegisterXLL &quot;Analys32.xll&quot; ' 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 & &quot;\analysis\atpvbaen.xla&quot; oXl.Workbooks(&quot;atpvbaen.xla&quot;).RunAutoMacros 1 'Excel 2007 uses the xlam file extension 'oXl.Workbooks.Open oXl.LibraryPath & &quot;\analysis\atpvbaen.xlam&quot; 'oXl.Workbooks(&quot;atpvbaen.xlam&quot;).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 = &quot;87&quot; Set oRange = oSheet.Cells(2, 1) oRange.Value = &quot;27&quot; Set oRange = oSheet.Cells(3, 1) oRange.Value = &quot;45&quot; Set oRange = oSheet.Cells(4, 1) oRange.Value = &quot;62&quot; Set oRange = oSheet.Cells(5, 1) oRange.Value = &quot;3&quot; Set oRange = oSheet.Cells(6, 1) oRange.Value = &quot;52&quot; Set oRange = oSheet.Cells(7, 1) oRange.Value = &quot;20&quot; Set oRange = oSheet.Cells(8, 1) oRange.Value = &quot;43&quot; Set oRange = oSheet.Cells(9, 1) oRange.Value = &quot;74&quot; Set oRange = oSheet.Cells(10, 1) oRange.Value = &quot;61&quot; ' Create and populate the Bin Range Set oRange = oSheet.Cells(1, 2) oRange.Value = &quot;20&quot; Set oRange = oSheet.Cells(2, 2) oRange.Value = &quot;40&quot; Set oRange = oSheet.Cells(3, 2) oRange.Value = &quot;60&quot; Set oRange = oSheet.Cells(4, 2) oRange.Value = &quot;80&quot; ' Chart the Histogram on a new Worksheet ' See Microsoft Knowledge Base article 213489 oXl.Run &quot;ATPVBAEN.XLAM!Histogram&quot;, oXl.ActiveSheet.Range(&quot;$A$1:$A$10&quot;), _ &quot;&quot;, oXl.ActiveSheet.Range(&quot;$B$1:$B$4&quot;), _ False, False, True, False ' True = Chart 'Excel 2007 uses the xlam file extension 'oXl.Run &quot;ATPVBAEN.XLAM!Histogram&quot;, oXl.ActiveSheet.Range(&quot;$A$1:$A$10&quot;), _ '       &quot;&quot;, oXl.ActiveSheet.Range(&quot;$B$1:$B$4&quot;), _ '       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.

