Create High-Low-Close Chart with Excel VBA

To create a High-Low-Close chart (typically used for financial data) using Excel VBA, we can use the ChartObjects.Add method to add a chart, and then configure it as a High-Low-Close chart by using the appropriate chart type and data range. Below is a detailed VBA code with an explanation of each step:

VBA Code for High-Low-Close Chart:

Sub CreateHighLowCloseChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    Dim dataRange As Range
    Dim xValues As Range
    Dim highValues As Range
    Dim lowValues As Range
    Dim closeValues As Range
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range for your data (ensure the data is in a table with Date, High, Low, and Close)
    ' Assuming the data is in columns A, B, C, and D with a header row
    Set dataRange = ws.Range("A1:D10") ' Adjust the range based on your data
    ' Define individual data ranges
    Set xValues = ws.Range("A2:A10") ' Dates
    Set highValues = ws.Range("B2:B10") ' High prices
    Set lowValues = ws.Range("C2:C10") ' Low prices
    Set closeValues = ws.Range("D2:D10") ' Close prices   
    ' Add a new chart to the worksheet
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    Set chart = chartObj.Chart
    ' Set the chart type to High-Low-Close
    chart.ChartType = xlStockHLC   
    ' Set the data for the chart (High-Low-Close)
    chart.SetSourceData Source:=dataRange   
    ' Set the X-axis to the dates
    chart.Axes(xlCategory).CategoryNames = xValues   
    ' Set the series for High, Low, and Close
    chart.SeriesCollection.NewSeries
    chart.SeriesCollection(1).XValues = xValues
    chart.SeriesCollection(1).Values = highValues
    chart.SeriesCollection(1).Name = "High"   
    chart.SeriesCollection.NewSeries
    chart.SeriesCollection(2).XValues = xValues
    chart.SeriesCollection(2).Values = lowValues
    chart.SeriesCollection(2).Name = "Low"   
    chart.SeriesCollection.NewSeries
    chart.SeriesCollection(3).XValues = xValues
    chart.SeriesCollection(3).Values = closeValues
    chart.SeriesCollection(3).Name = "Close"   
    ' Format chart title
    chart.HasTitle = True
    chart.ChartTitle.Text = "High-Low-Close Chart"   
    ' Format axis titles
    chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Date"
    chart.Axes(xlValue, xlPrimary).HasTitle = True
    chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Price"   
    ' Customize other chart elements as needed (e.g., colors, labels)
    ' Example: change series color
    chart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' High series - Red
    chart.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 255, 0) ' Low series - Green
    chart.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 0, 255) ' Close series - Blue  
    ' Adjust axis scaling (optional)
    chart.Axes(xlValue).MinimumScale = WorksheetFunction.Min(closeValues) * 0.9
    chart.Axes(xlValue).MaximumScale = WorksheetFunction.Max(closeValues) * 1.1
End Sub

Explanation of the Code:

1. Setting up the Worksheet and Ranges:
   • The code starts by defining the worksheet ws where your data is located (Sheet1 in this example).
   • The data for the High-Low-Close chart is assumed to be in columns A (Date), B (High), C (Low), and D (Close). The data range (dataRange) is set to include these columns.
2. Creating the Chart:
   • The ChartObjects.Add method adds a chart to the worksheet. The Left, Width, Top, and Height properties define the size and position of the chart on the sheet.
   • The chart.ChartType = xlStockHLC sets the chart type to a High-Low-Close chart, which is commonly used in financial data to show stock prices.
3. Setting Data for the Chart:
   • The X-values are set to the dates (xValues), and the Y-values are set for High (highValues), Low (lowValues), and Close (closeValues).
   • The SeriesCollection.NewSeries method creates a new data series for each of the High, Low, and Close values.
   • Each series is assigned the corresponding values for High, Low, and Close.
4. Customizing the Chart:
   • A title is added to the chart using chart.HasTitle = True and chart.ChartTitle.Text.
   • Axis titles for the Category axis (Date) and Value axis (Price) are added.
   • Colors for each series (High, Low, Close) are customized using Format.Line.ForeColor.RGB.
5. Optional Customizations:
   • The axis scaling is adjusted by setting the minimum and maximum values for the Value axis based on the Close data (MinimumScale and MaximumScale).
   • You can further customize the chart with labels, data markers, or other visual elements as needed.

Key Notes:

• The data range and sheet names must be adjusted to fit your specific dataset.
• The xlStockHLC chart type is ideal for financial data, but you can change the chart type to another one (e.g., xlLine) if you need a different format.
• The chart’s appearance can be modified in many ways—color, line thickness, markers, etc.

This code will generate a dynamic High-Low-Close chart that updates whenever the underlying data in the specified range changes.