Creating a Box Plot (Box and Whisker chart) in Excel VBA

This code calculates the required statistics (minimum, first quartile, median, third quartile, and maximum) and creates the chart based on these values.

Objective:

• The code will take a range of data, calculate the necessary statistics for the box plot (minimum, first quartile, median, third quartile, and maximum), and then create a chart from those values.

1. Preparing Data

Before running the code, ensure your data is in a column in Excel. Let’s assume your data is in column A.

2. Create a VBA Module

1. Press Alt + F11 to open the VBA editor.
2. From the Insert menu, select Module to create a new module.
3. Copy and paste the following code into the module.

VBA Code for Creating a Box Plot

Sub CreateBoxPlot()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim Min As Double, Q1 As Double, Median As Double, Q3 As Double, Max As Double
    Dim BoxChart As ChartObject
    Dim CalcTable As Range
    Dim SerieData As Range
    ' Set the active worksheet
    Set ws = ActiveSheet   
    ' Set the range for the data (e.g., A2:A101)
    Set dataRange = ws.Range("A2:A101")   
    ' Calculate the necessary statistics for the box plot
    Min = Application.WorksheetFunction.Min(dataRange)
    Q1 = Application.WorksheetFunction.Quartile_Inc(dataRange, 1)
    Median = Application.WorksheetFunction.Median(dataRange)
    Q3 = Application.WorksheetFunction.Quartile_Inc(dataRange, 3)
    Max = Application.WorksheetFunction.Max(dataRange)   
    ' Insert a temporary table to store the results
    Set CalcTable = ws.Range("C2:C6")
    CalcTable.Cells(1, 1).Value = Min
    CalcTable.Cells(2, 1).Value = Q1
    CalcTable.Cells(3, 1).Value = Median
    CalcTable.Cells(4, 1).Value = Q3
    CalcTable.Cells(5, 1).Value = Max   
    ' Create a box plot chart object
    Set BoxChart = ws.ChartObjects.Add(Left:=300, Width:=400, Top:=100, Height:=300)
    BoxChart.Chart.ChartType = xlColumnClustered   
    ' Add series to the chart
    BoxChart.Chart.SeriesCollection.NewSeries
    BoxChart.Chart.SeriesCollection(1).XValues = Array("Min", "Q1", "Median", "Q3", "Max")
    BoxChart.Chart.SeriesCollection(1).Values = CalcTable   
    ' Add a title to the chart
    BoxChart.Chart.HasTitle = True
    BoxChart.Chart.ChartTitle.Text = "Box Plot"   
    ' Customize the chart (hide the column bars)
    BoxChart.Chart.SeriesCollection(1).Format.Fill.Visible = msoFalse
    BoxChart.Chart.SeriesCollection(1).Format.Line.Visible = msoFalse   
    ' Add a line chart to connect the points
    BoxChart.Chart.SeriesCollection.NewSeries
    BoxChart.Chart.SeriesCollection(2).XValues = Array("Min", "Q1", "Median", "Q3", "Max")
    BoxChart.Chart.SeriesCollection(2).Values = CalcTable
    BoxChart.Chart.SeriesCollection(2).ChartType = xlLine
    BoxChart.Chart.SeriesCollection(2).Format.Line.Color = RGB(0, 0, 0)   
    ' Show specific points for Min, Q1, Median, Q3, Max
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerBackgroundColor = RGB(0, 0, 255)   
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerBackgroundColor = RGB(0, 255, 0)   
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerBackgroundColor = RGB(255, 0, 0)   
    ' Clear the temporary calculation table
    CalcTable.ClearContents  
End Sub

Explanation of the Code:

1. Defining Variables:
   • ws: The active worksheet.
   • dataRange: The range of data for which the box plot will be created.
   • Min, Q1, Median, Q3, Max: The statistical values needed for the box plot.
   • BoxChart: An object for the chart that will be created.
   • CalcTable: A temporary table used to store the statistical values.
2. Calculating Required Statistics:
   • The Min, Q1, Median, Q3, and Max values are calculated using Excel’s built-in functions (MIN, QUARTILE_INC, MEDIAN, MAX).
3. Creating the Chart:
   • A clustered column chart (xlColumnClustered) is created, and the calculated values (Min, Q1, Median, Q3, Max) are added to it as series.
   • The chart’s type is changed to a line chart (xlLine) to connect the points representing the statistical values.
4. Customizing the Chart:
   • The column bars are hidden using msoFalse, as we only want to see the connecting lines.
   • Specific markers (circles) are added at each of the statistical points (Min, Q1, Median, Q3, Max) to make them visually distinct.
5. Cleanup:
   • The temporary table (CalcTable) is cleared after the chart is created.

How to Run the Code:

1. Enter your data in column A (for example, from A2:A101).
2. Press Alt + F8, select CreateBoxPlot, and click Run.
3. A box plot chart will appear on the worksheet, showing the minimum, first quartile, median, third quartile, and maximum values.

Customization:

You can customize the chart’s colors, marker sizes, and the chart’s position by modifying the corresponding parameters in the code. If you want to display additional elements or further customize the look of the box plot, you can do so using Excel’s chart formatting options through VBA.