Create dynamic range charts with VBA in Excel

Step 1: Set Up Your Data

To begin, you need to have data that can be used to create a dynamic chart. For this example, we’ll assume you have a simple data set in the following format:

Make sure that your data is organized in a table-like structure, where each column has headers (in this case, « Date » and « Sales »).

Step 2: Insert a Chart Manually (Optional)

Before writing any VBA code, it’s a good idea to manually insert a basic chart to understand how the chart will look. To do this:

1. Highlight the data range you want to plot.
2. Go to the Insert tab on the Ribbon.
3. Choose a chart type, such as a Line Chart or Column Chart.
4. This step is optional but will help visualize what your VBA-generated chart will look like.

Step 3: Write the VBA Code

Now, let’s write a VBA macro to make the chart dynamic. A dynamic chart means that it will automatically adjust to changes in the data range, such as when new data is added.

Here’s the code that will create a dynamic chart:

Sub CreateDynamicChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim lastRow As Long
    Dim dataRange As Range
    Dim chartRange As Range   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to your sheet name   
    ' Find the last row of data in the Sales column (assuming data is in column B)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Define the data range dynamically (change A1:B1 to match your headers and columns)
    Set dataRange = ws.Range("A1:B" & lastRow)   
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)   
    ' Set the data range for the chart
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Set the chart type (Line chart as an example)
    chartObj.Chart.ChartType = xlLine   
    ' Set chart title and axis titles
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Over Time"
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Date"
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales"
End Sub

Explanation of the Code:

1. Worksheet and Chart Object Setup:
   • The ws variable represents the worksheet where the data is stored. You need to change « Sheet1 » to the actual name of your worksheet.
   • The chartObj variable is used to create the chart object.
2. Finding the Last Row:
   • We determine the last row in the « Date » column (column A) using ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row. This helps ensure the range dynamically adjusts based on the number of rows of data.
3. Defining the Data Range:
   • dataRange refers to the range of data you want to plot. It is dynamically created by referencing the range from A1:B followed by the lastRow value, ensuring the range automatically adjusts when new data is added.
4. Creating the Chart:
   • The chartObj = ws.ChartObjects.Add line creates a new chart and places it on the worksheet with the specified size and position.
   • The SetSourceData Source:=dataRange assigns the dynamic data range to the chart.
   • The chart type is set using chartObj.Chart.ChartType = xlLine (you can change this to xlColumn, xlBar, etc., depending on the chart type you want).
5. Setting Titles:
   • The chart title and axis titles are configured using the ChartTitle.Text and AxisTitle.Text properties.

Step 4: Run the Code

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module and paste the VBA code into the module.
3. Press F5 or go back to Excel and run the macro by pressing Alt + F8, selecting CreateDynamicChart, and clicking Run.

Output:

Once you run the macro, Excel will automatically generate a chart that is dynamic. When you add new data to the table (in the « Date » and « Sales » columns), the chart will update to include the new values.

Key Points:

• The dynamic range ensures the chart adjusts as data is added.
• The lastRow calculation helps in determining the size of the data range.
• The chart type can be changed to any desired type (line, column, bar, etc.).