Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Histogram in Excel Using VBA (With Easy Steps)

A histogram helps to analyze and visualize data very quickly. For that, it has great importance in data analysis. Here, we will discuss the stepwise procedures to create a histogram in Excel using VBA macro code.

Histogram Using VBA


Download Practice Workbook

You can download the practice workbook from here for a better understanding.


What Is Histogram in Excel?

A histogram apparently looks quite similar to a bar chart. Except, the histogram is the representation of the frequency (Number of times that data appears) of data which is represented by rectangular shapes. Instead of specific data, the bars of the histogram can also represent a range of data. Histograms can be created in Excel very easily. We can create a histogram by using the Statistical Chart feature of Excel or by running a simple piece of VBA code.


Step-by-Step Procedures to Create Histogram in Excel Using VBA

In this section, we will show the stepwise procedures to create a histogram using a simple piece of VBA code. Without any further delay, let’s jump to the stepwise procedures.


STEP 1: Create Dataset for Histogram

  • Primarily, we have to include some numeric values in the dataset.
  • For the demonstration, we have included some numeric values in the Marks

Dataset for histogram

  • Before any further steps, we have to select the data from the Marks column including the header.

Selecting data to make the histogram

Read More: How to Make a Histogram in Excel Using Data Analysis


STEP 2: Open VBA Window

  • Then, go to the Developer tab and select Visual Basic to open the VBA window.
  • Also, you can open the VBA window by pressing Alt + F11.

Opening VBA Window

  • Afterward, in the VBA window select Insert > Module.

Inserting Module

Read More: Difference Between Excel Histogram and Bar Graph


Similar Readings


STEP 3: Type a Suitable Code in the VBA Module

  • After that, it’s time to write the code for creating the histogram. You can copy the following piece of code in the appeared code window.
Sub Create_Histogram()
Dim src_sht As Worksheet
Dim new_sht As Worksheet
Dim selected_rng As Range
Dim title As String
Dim x As Integer
Dim scor_cel As Range
Dim num_scores As Integer
Dim count_rng As Range
Dim nw_chart As Chart

    Set selected_rng = Selection
    Set src_sht = ActiveSheet
    Set new_sht = Application.Sheets.Add(After:=src_sht)
    title = selected_rng.Cells(1, 1)
    new_sht.Name = title & " Histogram Using VBA"

    x = 1
    For Each scor_cel In selected_rng.Cells
        If Not IsNumeric(scor_cel.Text) Then
            new_sht.Cells(x, 1) = title & "/Scores"
        Else
            new_sht.Cells(x, 1) = scor_cel
        End If
        x = x + 1
    Next scor_cel
    num_scores = selected_rng.Count

    Const BIN_SIZE As Integer = 10
    Dim num_bins As Integer
    num_bins = 100 \ BIN_SIZE

    new_sht.Cells(1, 2) = "Bins"
    For x = 1 To num_bins - 1
        new_sht.Cells(x + 1, 2) = x * BIN_SIZE - 1
    Next x


    new_sht.Cells(1, 3) = "Frequency"
    Set count_rng = new_sht.Range("C2:C" & num_bins + 1)
    count_rng.FormulaArray = "=FREQUENCY(A2:A" & _
        num_scores & ",B2:B" & num_bins & ")"

    new_sht.Cells(1, 4) = "Score Range"
    For x = 1 To num_bins - 1
        new_sht.Cells(x + 1, 4) = "'" & _
            10 * (x - 1) & "-" & _
            10 * (x - 1) + 9
        new_sht.Cells(r + 1, 4).HorizontalAlignment = _
            xlRight
    Next x
    x = num_bins
    new_sht.Cells(x + 1, 4) = "'" & _
        10 * (x - 1) & "-100"
    new_sht.Cells(x + 1, 4).HorizontalAlignment = xlRight

    Set nw_chart = Charts.Add()
    With nw_chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=new_sht.Range("C2:C" & _
            num_bins + 1), _
            PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, _
            Name:=new_sht.Name
    End With

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = title & " Histogram Using VBA"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, _
            xlPrimary).AxisTitle.Characters.Text = "Marks/Scores"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text _
 _
            = "Frequency"

        .SeriesCollection(1).XValues = "='" & _
            new_sht.Name & "'!R2C4:R" & _
            num_bins + 1 & "C4"
    End With
    ActiveChart.SeriesCollection(1).Select
    With ActiveChart.ChartGroups(1)
        .Overlap = 0
        .GapWidth = 0
        .HasSeriesLines = False
        .VaryByCategories = False
    End With

    x = num_scores + 2
    new_sht.Cells(x, 1) = "Average"
    new_sht.Cells(x, 2) = "=AVERAGE(A1:A" & num_scores & _
        ")"
    x = x + 1
    new_sht.Cells(x, 1) = "Std. Deviation"
    new_sht.Cells(x, 2) = "=STDEV(A1:A" & num_scores & ")"
End Sub

Typing VBA Code

Code Explanation

  • In this part, we declared a subprocedure named Create_Histogram. Also, we declared some variables.
Sub Create_Histogram()
Dim src_sht As Worksheet
Dim new_sht As Worksheet
Dim selected_rng As Range
Dim xtitle As String
Dim x As Integer
Dim scor_cel As Range
Dim inum_scores As Integer
Dim count_rng As Range
Dim nw_chart As Chart
  • Afterward, this part copies the data to the new sheet.
Set selected_rng = Selection
Set src_sht = ActiveSheet
Set new_sht = Application.Sheets.Add(After:=src_sht)
xtitle = selected_rng.Cells(1, 1)
new_sht.Name = xtitle & " Histogram Using VBA"
  • Then, this part of code creates a new sheet.
x = 1
For Each scor_cel In selected_rng.Cells
If Not IsNumeric(scor_cel.Text) Then
new_sht.Cells(x, 1) = xtitle & " /Scores"
Else
new_sht.Cells(x, 1) = scor_cel
End If
x = x + 1
Next scor_cel
num_scores = selected_rng.Count
  • Here, we created bin separators.
Const BIN_SIZE As Integer = 10
Dim num_bins As Integer
num_bins = 100 \ BIN_SIZE
  • This section counts the bins.
new_sht.Cells(1, 2) = "Bins"
For x = 1 To num_bins - 1
new_sht.Cells(x + 1, 2) = x * BIN_SIZE - 1
Next x
  • Further, this part makes the range labels.
new_sht.Cells(1, 3) = "Frequency"
Set count_rng = new_sht.Range("C2:C" & num_bins + 1)
count_rng.FormulaArray = "=FREQUENCY(A2:A" &num_scores & ",B2:B" & num_bins & ")"
  • Now, the code makes the counts.
new_sht.Cells(1, 4) = "Score Range"
For x = 1 To num_bins - 1
new_sht.Cells(x + 1, 4) = "'" &10 * (x - 1) & "-" &10 * (x - 1) + 9
new_sht.Cells(x + 1, 4).HorizontalAlignment =xlRight
Next x
x = num_bins
new_sht.Cells(x + 1, 4) = "'" &10 * (x - 1) & "-100"
new_sht.Cells(x + 1, 4).HorizontalAlignment = xlRight
  • After that, the code creates the chart.
Set nw_chart = Charts.Add()
With nw_chart
.ChartType = xlColumnClustered
.SetSourceData Source:=new_sht.Range("C2:C" &num_bins + 1),PlotBy:=xlColumns
.Location Where:=xlLocationAsObject,Name:=new_sht.Name
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = xtitle & " Histogram Using VBA"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, _
xlPrimary).AxisTitle.Characters.Text = "/Scores"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text= "Frequency"
  • lastly, this part displays score ranges on the X-axis.
.SeriesCollection(1).XValues = "='" &new_sht.Name & "'!R2C4:R" &num_bins + 1 & "C4"
End With
ActiveChart.SeriesCollection(1).Select
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
x = num_scores + 2
new_sht.Cells(x, 1) = "Average"
new_sht.Cells(x, 2) = "=AVERAGE(A1:A" & num_scores &")"
x = x + 1
new_sht.Cells(x, 1) = "StdDev"
new_sht.Cells(x, 2) = "=STDEV(A1:A" & num_scores & ")"
End Sub

Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)


STEP 4: Run VBA Code for Creating Histogram

  • Further, press the Run option from the window.

Running the vba code for histogram

Final Histogram Output:

  • Finally, we will see a Marks Histogram worksheet created with data for the histogram.

Output dataset for histogram.

  • Also, we will see a histogram for our data created in the same worksheet.

The final output of the histogram.

  • In addition, we can do a bit of formatting on the output dataset.

A bit of formatted dataset in Excel

  • Also, we can do some formatting (like giving labels and creating colored borders, etc.) on the histogram for better visualization.

Final Formatted Histogram Output in Excel

Read More: How to Plot Histogram in Excel (5 Easy Ways)


Conclusion

The histogram is quite popular for analyzing data. Here, we have discussed the stepwise procedures to create a histogram using VBA code in Excel. Hope, you will be able to create a histogram using the VBA code now. If you have any queries or suggestions, please let us know by commenting. Visit our ExcelDemy Website for similar articles regarding Excel.


Related Articles

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo