Welcome to Blogs @ Andrew Qu
Blog Index
All blogs
Search results

Excel VBA Dynamic Charts

Summary

In this blog, I am going to create a bar chart using vba in an Excel sheet.

Preparation work

Before you start, you need to prepare you excel work book so that it can run macros, enabled developer view (VBA) etc.

  1. Create a new workbook, named dbdemo
  2. Enable developer view: Right click menu area -> [Customize Ribon] -> [Main Tabs] -> [Developer]
  3. Enable macro: [Developer] -> [Macro security] -> [Macro Settings] -> [Enable all macros(not...)]
  4. Such files will be saved as "*.xlsm" where m is for macro.
Do not forget to save the work book.

Adding UI to [View] sheet

We will add a button as shown below:

  1. In "Developer" Ribbon, make sure [Design Mode] is high lighted.
  2. Click [Insert] -> [ActiveX controls] -> [Button]
  3. Once created, right click the buttton, select "Properties".
  4. Change (Name) to "CreateChart", and Caption to "Create Chart"
When button [Create Chart] is clicked, we will run a macro and create a chart.

Do not forget to save the work book.

Creating the Chart
  1. Goto sheet "View" and double click the "Create Chart" button
  2. Visual Basic code window should open. In the function stub, add the following code:
Private Sub CreateChart_Click()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    Dim YValues(1 To 5) As Integer
    Dim XValues(1 To 5) As String
    
    ' Delete all charts
    For Each myChtObj In ActiveSheet.ChartObjects
       If Left(myChtObj.Name, 4) = "DCT_" Then
           myChtObj.Delete
       End If
    Next

    ' Create new chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=10, Width:=375, Top:=40, Height:=125)
    myChtObj.Name = "DCT_10"
    XValues(1) = "A12"
    XValues(2) = "B14"
    XValues(3) = "C15"
    XValues(4) = "D16"
    XValues(5) = "E18"
        
    With myChtObj.Chart
        .ChartType = XlChartType.xlColumnClustered
        For iColumn = 1 To 3
            YValues(1) = iColumn + 2
            YValues(2) = iColumn + 4
            YValues(3) = iColumn + 6
            YValues(4) = iColumn + 2
            YValues(5) = iColumn + 5
            
            With .SeriesCollection.NewSeries
                .Name = "Version" & iColumn
                .XValues = XValues
                .Values = YValues
            End With
        Next
    End With
End Sub
The result is shown as follows :
Ads from Google
Dr Li Anchor Profi
www.anchorprofi.de
Engineering anchorage plate design system
©Andrew Qu, 2015. All rights reserved. Code snippets may be used "AS IS" without any kind of warranty. DIY tips may be followed at your own risk.