Microsoft small business knowledge base

Article ID: 142387 - Last Review: June 30, 2004 - Revision: 4.3

This article was previously published under Q142387

On This Page


This article illustrates how you can use Automation in a Visual Basic program to create an Excel workbook that contains a chart embedded on a worksheet.


Steps to Create Example Program

  1. Start a new project in Visual Basic. Form1 is created by default.
  2. Add a command button (Command1) to Form1.
  3. Add the following code to the Command1_Click event procedure:
      Private Sub Command1_Click()
        Dim oXL As Object        ' Excel application
        Dim oBook As Object      ' Excel workbook
        Dim oSheet As Object     ' Excel Worksheet
        Dim oChart As Object     ' Excel Chart
        Dim iRow As Integer      ' Index variable for the current Row
        Dim iCol As Integer      ' Index variable for the current Row
        Const cNumCols = 10      ' Number of points in each Series
        Const cNumRows = 2       ' Number of Series
        ReDim aTemp(1 To cNumRows, 1 To cNumCols)
        'Start Excel and create a new workbook
        Set oXL = CreateObject("Excel.application")
        Set oBook = oXL.Workbooks.Add
        Set oSheet = oBook.Worksheets.Item(1)
        ' Insert Random data into Cells for the two Series:
        Randomize Now()
        For iRow = 1 To cNumRows
           For iCol = 1 To cNumCols
              aTemp(iRow, iCol) = Int(Rnd * 50) + 1
           Next iCol
        Next iRow
        oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
        'Add a chart object to the first worksheet
        Set oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart
        oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols)
        ' Make Excel Visible:
        oXL.Visible = True
        oXL.UserControl = True
    End Sub
  4. Press the F5 key to run the program, and click the command button.
Results: Microsoft Excel starts, and a new workbook is created. Random data is added to cells A1:J2 and a chart is embedded on the first worksheet. The embedded chart uses the random data for its source.

  • 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 5.0 Enterprise Edition
kbhowto kbprogramming KB142387
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support