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.
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.
How to Create Histogram in Excel Using VBA: Step-by-Step Procedures
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 column.
- Before any further steps, we have to select the data from the Marks column including the header.
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.
- Afterward, in the VBA window select Insert > Module.
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
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 SubSTEP 4: Run VBA Code for Creating Histogram
- Further, press the Run option from the window.
Final Histogram Output:
- Finally, we will see a Marks Histogram worksheet created with data for the histogram.
- Also, we will see a histogram for our data created in the same worksheet.
- In addition, we can do a bit of formatting on the output dataset.
- Also, we can do some formatting (like giving labels and creating colored borders, etc.) on the histogram for better visualization.
Read More: How to Create Probability Histogram in Excel
Download Practice Workbook
You can download the practice workbook from here for a better understanding.
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.
