Create Charts in Excel VBA

Objective of the Code:

This code creates a simple chart based on the data from an Excel worksheet, customizes the chart’s appearance, and allows you to adjust chart elements such as titles, axes, and colors.

VBA Code to Create a Chart:

Sub CreateChart()
    ' Declare variables for the worksheet and chart
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rangeData As Range 
    ' Assign the active worksheet to the variable ws
    Set ws = ActiveSheet   
    ' Define the data range for the chart (for example A1:B10)
    Set rangeData = ws.Range("A1:B10")   
    ' Create a chart object in the worksheet (position 100x100, size 400x300)
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)   
    ' Set the data source for the chart
    chartObj.Chart.SetSourceData Source:=rangeData   
    ' Set the chart type (e.g., a clustered column chart)
    chartObj.Chart.ChartType = xlColumnClustered   
    ' Customize the chart title
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Chart"   
    ' Customize the title for the X-axis (horizontal)
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Months"   
    ' Customize the title for the Y-axis (vertical)
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales in $"   
    ' Change the color of the chart columns
    With chartObj.Chart.SeriesCollection(1)
        .Interior.Color = RGB(0, 112, 192) ' Blue color
    End With   
    ' Add a legend (optional)
    chartObj.Chart.HasLegend = True
    chartObj.Chart.Legend.Position = xlLegendPositionBottom   
    ' Activate the worksheet
    ws.Activate
End Sub

Explanation of the Code:

Declaring Variables:

Dim ws As Worksheet
Dim chartObj As ChartObject
Dim rangeData As Range

• • ws : A variable representing the worksheet where the chart will be added.
  • chartObj : A variable for the chart object itself.
  • rangeData : The range of cells containing the data to be displayed in the chart.

Selecting the Active Worksheet:

Set ws = ActiveSheet

This line assigns the currently active worksheet to the variable ws. This means the chart will be added to whichever sheet is active when you run the code.

Defining the Data Range:

Set rangeData = ws.Range("A1:B10")

The range of data you want to include in the chart is specified here. This range should contain the values for both the X and Y axes of the chart.

Creating the Chart Object:

Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)

This line creates a chart on the worksheet at a specific position (100 pixels from the left, 100 pixels from the top) and with dimensions 400×300 pixels.

Setting the Data Source for the Chart:

chartObj.Chart.SetSourceData Source:=rangeData

The chart is linked to the specified data range (rangeData), so it will display the values contained in that range.

Setting the Chart Type:

chartObj.Chart.ChartType = xlColumnClustered

This line sets the type of chart. In this case, it’s a clustered column chart (xlColumnClustered). You can change the chart type by modifying this line (for example, for a line chart, you can use xlLine).

Customizing the Chart Title:

chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Sales Chart"

This section enables the chart title and sets its text to « Sales Chart ». You can customize the title as needed.

Customizing Axis Titles:

chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Months"
chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales in $"

These lines add titles to the chart axes:

• • The X-axis (category axis) gets the title « Months ».
  • The Y-axis (value axis) gets the title « Sales in $ ».

Customizing the Column Colors:

With chartObj.Chart.SeriesCollection(1)
    .Interior.Color = RGB(0, 112, 192) ' Blue color
End With

This section changes the color of the columns in the chart to blue (using the RGB function).

Adding a Legend:

chartObj.Chart.HasLegend = True
chartObj.Chart.Legend.Position = xlLegendPositionBottom

The legend is enabled and positioned at the bottom of the chart. If you don’t want a legend, you can disable this by setting HasLegend = False.

Finalizing and Refreshing the Worksheet:

ws.Activate

This line reactivates the worksheet after creating the chart, so you can immediately see the chart in your Excel window.

Conclusion:

This code creates a simple chart using VBA, but it can easily be customized to meet your specific needs. You can change the data range, chart type, colors, titles, and more. It provides a good foundation for automating the creation and customization of charts in Excel using VBA.