Create Histogram with Excel VBA

Objective:

This VBA code will create a histogram based on a range of data in Excel. The histogram will be created by using Excel’s built-in chart functionality.

VBA Code:

Sub CreateHistogram()
    Dim dataRange As Range
    Dim chartObj As ChartObject
    Dim binRange As Range
    Dim chartTitle As String
    Dim xAxisTitle As String
    Dim yAxisTitle As String
    ' Set the range of data for the histogram
    Set dataRange = Range("A2:A20") ' Modify this range based on your data   
    ' Set the range of bins (optional; if not specified, Excel auto-generates bins)
    Set binRange = Range("B2:B10") ' Modify this to the desired bin range, or leave empty   
    ' Set chart titles
    chartTitle = "Histogram of Data"
    xAxisTitle = "Data Values"
    yAxisTitle = "Frequency"   
    ' Create a chart
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=200, Width:=375, Top:=75, Height:=225)   
    ' Set the chart type to Histogram
    chartObj.Chart.ChartType = xlColumnClustered   
    ' Set the data for the chart
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Apply histogram chart formatting
    With chartObj.Chart
        .HasTitle = True
        .ChartTitle.Text = chartTitle
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = xAxisTitle
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = yAxisTitle       
        ' If bin range is specified, use the bin range for the histogram
        If Not binRange Is Nothing Then
            .Axes(xlCategory).CategoryNames = binRange
        End If       
        ' Set the histogram appearance (optional)
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(100, 149, 237) ' Color of bars
        .SeriesCollection(1).Format.Line.Visible = msoFalse ' Remove border lines
    End With
End Sub

Detailed Explanation:

1. Defining the Data Range:

Set dataRange = Range(« A2:A20 »)

This line defines the range of data you want to create the histogram for. In this case, it’s from cell A2 to A20, but you can modify it according to your dataset.

2. Setting the Bin Range:

Set binRange = Range(« B2:B10 »)

This optional range (binRange) is used to define the bins (categories) for the histogram. If you leave this range empty or don’t set it, Excel will automatically create bins for you.

3. Creating the Chart:

Set chartObj = ActiveSheet.ChartObjects.Add(Left:=200, Width:=375, Top:=75, Height:=225)

This line creates a new chart object on the active sheet, specifying its position and size. Left and Top determine the position of the chart, while Width and Height control the size.

4. Setting the Chart Type:

Chart.ChartType = xlColumnClustered

This sets the chart type to a column chart, which is suitable for a histogram representation. Note: Excel does not have a direct histogram chart type in VBA, but the column chart can be used to simulate a histogram.

5. Setting the Data Source:

Chart.SetSourceData Source:=dataRange

This sets the data source for the chart, which is the range dataRange we defined earlier (the dataset).

6. Formatting the Chart:

• • Titles are set for the chart and the axes using:

.HasTitle = True

.ChartTitle.Text = chartTitle

The above lines enable the chart title and set the title to « Histogram of Data ». Similarly, titles are set for the X-axis and Y-axis.

7. Setting the Bin Categories:

• If Not binRange Is Nothing Then

.Axes(xlCategory).CategoryNames = binRange

• End If

If you have specified a bin range (binRange), this line ensures that the X-axis displays these bin names.

8. Customizing the Chart Appearance:

.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(100, 149, 237)

.SeriesCollection(1).Format.Line.Visible = msoFalse

These lines format the appearance of the bars in the histogram. In this case, the bars are colored using an RGB value, and the borders of the bars are removed.

Notes:

• Bins: If you don’t specify a bin range, Excel will automatically calculate the bin intervals. You can specify bins for more control over how the data is grouped.
• Chart Type: The xlColumnClustered type is used to simulate a histogram. You can also use xlBarClustered for horizontal bars.

How to Use:

1. Open the Excel workbook you want to work with.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module: Insert > Module.
4. Paste the provided code into the module.
5. Press F5 to run the macro and generate the histogram.

This should give you a detailed histogram chart on your Excel worksheet.