VBA for Excel 2007 tutorial-create column chart


Create column chart

In this VBA code example, you will learn to create a simple clustered column chart as shown below. The data source used in generating chart is populated by VBA code in to Excel worksheet by using random numbers generator function(rnd). To add a chart to the Excel sheet, you need to use the AddChart() method of the Shapes collection. The AddChart() method has the following prototype:

Shapes.AddChart(Type,Left,Top,With,Height)

-Type indicates the type of chart to be create. Type of chart can be xl3DArea, xl3DBarClustered, xl3DColumn, xl3ColumnClustered, xl3DColumnStacked,etc.
-Left, Top arguments specify x-coordinate, y-coordinate to which the chart will be shown on Excel sheet.
-Width, and Height represents the width and height of chart (chart size).

The five arguments are optional. You can accept the default values by leaving them blank.

VBA for Excel: VBA example code to create a column chart

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.



VBA code for this example:



Option Explicit
Private Sub Worksheet_Activate()
 
GenerateChart
 
End Sub
 
Sub GenerateChart()
   
    Dim i As Integer
    'Fill Source data
    For i = 1 To 10
        Randomize
         Range("A" & i) = Int(100 * Rnd) + 1
         Range("B" & i) = Int(100 * Rnd) + 1
    Next
 
    'Select the data source
    Range("A1:B10").Select
    'Create a clusted column chart
    ActiveSheet.Shapes.AddChart xl3DColumnClustered, 200, 100
    ActiveSheet.Shapes(1).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$10"), PlotBy:=xlColumns
             
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.