Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-26-2020, 08:51 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default Problems with vba frequency distribution histogram

In the worksheet, column A is the data to be processed, column c is the separation point, column d is the data interval, column e is the data frequency, column f is the data frequency, and column g is the data frequency divided by the group distance,This code adapts to each column of data containing the header.


The header name of the worksheet c list is the separation point, the name of the d list header is the interval, the name of the e list header is the frequency, the name of the f list header is the frequency, and the name of the g list header is the frequency divided by the group distance.


the excel file:
cplans.xlsm


the code is as follows:
the first question is:How to optimize this running code.







Option Explicit



Sub maxmin()
Dim xU As Range, vMin, vMax, MinADS$, MaxADS$
Dim p, q, groupsp, drow
Set xU = ActiveSheet.Columns("a")
vMin = Application.Min(xU)
vMax = Application.Max(xU)
MinADS = xU.Find(vMin, Lookat:=xlWhole).Address(0, 0)
'MaxADS = xU.Find(vMax, Lookat:=xlWhole).Address(0, 0)
'MsgBox MinADS & " " & CDec(vMin) & vbCrLf & MaxADS & " " & CDec(vMax)
[b1] = "Min"
[b2] = vMin
[b3] = "Max"
[b4] = vMax
[b5] = "gap"
[b6] = vMax - vMin
[b7] = "gropspace"
[b9] = "gropno"
p = ActiveSheet.Cells.Rows.Count
q = ActiveSheet.Cells(p, 3).End(xlUp).Row
If q >= 2 Then
groupsp = Cells(q, 3) - Cells(q - 1, 3)
[b8] = groupsp
Else
groupsp = [b8].Value
End If
'Range("c3:C" & q).Clear
If q <= 1 And [b8].Value = "" Then Exit Sub
If [c1].Value = "" And [b8].Value <> "" Then Exit Sub
For drow = q + 1 To ActiveSheet.Cells.Rows.Count
Cells(drow, 3) = Cells(drow - 1, 3) + groupsp
If Cells(drow, 3) = vMax Then Exit For
If Cells(drow, 3) > vMax Then Exit For
Next drow

'MsgBox groupsp
[b10] = ActiveSheet.Cells(p, 3).End(xlUp).Row - 1
End Sub


Sub histogram()
Dim p, q, t, i, n, d, j, m, r, s, x, l
Dim arr, brr, title
Columns("D:G").ClearContents
'Locate the number of rows in which the last row of data
'in the reference column is located.
p = ActiveSheet.Cells.Rows.Count
q = ActiveSheet.Cells(p, 3).End(xlUp).Row
'Set the maximum area where the data is located to an array.
'A is listed as the column in which the data source is located which name is arr.
'brr where the data is located to an array.
arr = Range("A1").CurrentRegion
brr = ActiveSheet.Range("c1:g" & q)
Range("d1:g" & UBound(brr)).ClearContents
'the no.of the data and the space
n = UBound(arr) - LBound(arr) + 1
d = brr(3, 1) - brr(2, 1)
'title is the header of the colum
title = Array("Group", "Frequency", "Frequency", "Frequency/group space")
'write the header of the data area
i = 0
For t = LBound(brr, 2) + 1 To UBound(brr, 2)
brr(1, t) = title(i)
i = i + 1
Next t
'Process the data in column c of the worksheet
'and generate interval data in column d
'Build data grouping intervals.
brr(2, 2) = "[" & 0 & "," & brr(1, 1) & ")"
For i = LBound(brr) + 1 To UBound(brr)
If IsNumeric(brr(i - 1, 1)) Then brr(i, 2) = "[" & brr(i - 1, 1) & "," & brr(i, 1) & ")"
Next i
'process the data in column a and produce the frequency
For j = 2 To UBound(brr)
brr(j, 3) = 0
For m = 1 To UBound(arr)
'If arr(m, 1) >= brr(j, 1) And arr(m, 1) < brr(j - 1, 1) Then brr(j, 3) = 0


If arr(m, 1) < brr(j, 1) And arr(m, 1) >= brr(j - 1, 1) Then brr(j, 3) = brr(j, 3) + 1
If Not IsNumeric(brr(j - 1, 1)) And arr(m, 1) < brr(j, 1) And arr(m, 1) >= 0 Then brr(j, 3) = brr(j, 3) + 1
Next
brr(j, 4) = brr(j, 3) / n
brr(j, 5) = brr(j, 4) / d
Next
ActiveSheet.Range("c1:g" & q) = brr
'sum of the data
x = UBound(brr) + 1
Cells(x, 4) = "Total"
'add the number
For r = LBound(brr, 2) + 2 To UBound(brr, 2)
For l = LBound(brr) + 1 To UBound(brr)
Cells(x, r + 2) = Cells(x, r + 2) + brr(l, r)
Next l
Next r
End Sub
Reply With Quote
  #2  
Old 08-26-2020, 09:07 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

in this case,I want to use the result of macro range(d1:d6) and range(g1:g6) to draw the histogram,so I copy the two columns data to columns k and l from cells k1.because macro quote the range("k1:l6")can produce the good look histogram.
the result is like this:

20200827120613.png

use the code as follows:
Sub AddChart1()
Dim chtChart As Chart
Set chtChart = Charts.Add
With chtChart
.SetSourceData Source:=Sheet1.Range("K1:L6"), PlotBy:=xlRows
.ChartType = xlColumnClustered
.HasDataTable = True
.ApplyDataLabels
.HasTitle = True
.ChartTitle.Text = "histogram"
.HasLegend = True
.Name = "histogram"
End With
Set chtChart = Nothing
End Sub
Reply With Quote
  #3  
Old 08-26-2020, 09:14 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

but when i use the code as follws,some questions happened:
Sub copyd()
Dim p, q
Columns("k:l").ClearContents
p = ActiveSheet.Cells.Rows.Count
q = ActiveSheet.Cells(p, 4).End(xlUp).Row
Range("D1" & q - 1).Select
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Range("G1:G" & q - 1).Select
Application.CutCopyMode = False
Selection.Copy
Range("L1").Select
ActiveSheet.Paste

Dim chtChart As Chart
Set chtChart = Charts.Add
With chtChart
.SetSourceData Source:=ActiveSheet.[k1].CurrentRegion, PlotBy:=xlRows
.ChartType = xlColumnClustered
.HasDataTable = True
.ApplyDataLabels
.HasTitle = True
.ChartTitle.Text = "histogram"
.HasLegend = False
.Name = "histogram"
End With
Set chtChart = Nothing
End Sub


application alert:
20200827121045.png
histogram each colunm have space between each other,and the column have the some color,not the different colours:
20200827120955.png
Reply With Quote
  #4  
Old 08-26-2020, 09:24 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default how to write this code?

The data source is in column A, but because of changes in C's data, i.e. the starting data value and step used to draw histograms, the grouping changes, resulting in changes in the data in column d, which is next to the other columns, and the data used in the drawing is only d columns and g columns. So the code needs to be adaptive, and the data area referenced by the drawing automatically matches as the d column changes, but in the above instance, when you change the data source area in the drawing program, the resulting picture is a single-colored histogram, and the spacing between each column is not close together. If you want to implement automatic reference data areas, the resulting picture is the first to appear in the specification effect, how to rewrite the code? Thank you!
Reply With Quote
  #5  
Old 08-28-2020, 04:14 PM
p45cal's Avatar
p45cal p45cal is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

This is all quite complex - especially the code!
I've got a few questions first, because the code could perhaps be much shorter and simpler.
1. Do you have a version of Excel where some of the formulae (eg FREQUENCY) automatically spill into adjacent cells when committed to the sheet?
2. On Sheet1 of the attached, in cell J7 to J12 is a formula which gives the same results as yours in E2:E6. It's one line of code to place that formula there and one more line to convert that range to plain values. Could you work with that?
3. On Sheet6 at cell D6 there's a pivot table - it wouldn't take many lines of code to adjust the source data for that pivot and to adjust the grouping. Could you work with that?
4. What version of Excel are you using?
5. Is it a standalone desktop version or a subscription version.
Attached Files
File Type: xlsx msofficeforums45540plansv2.xlsx (34.3 KB, 9 views)

Last edited by p45cal; 08-29-2020 at 03:45 AM.
Reply With Quote
  #6  
Old 08-29-2020, 02:37 AM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

1.So far, no version has been found to overflow
2.The formula works normally
3.the source data for that pivot and to adjust the grouping.works normally
4.I'm using office365
5.Is it a standalone desktop version



My idea is to use VBA code to generate data group number, grouping interval, frequency, image, etc.Thank you!
Reply With Quote
  #7  
Old 08-29-2020, 03:47 AM
p45cal's Avatar
p45cal p45cal is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by leeqiang View Post
VBA code to generate data group number, grouping interval
What's the logic behind determining the group number and interval?
Reply With Quote
  #8  
Old 08-29-2020, 05:20 AM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
What's the logic behind determining the group number and interval?


My logic is like this, worksheet A is listed as the data source. The first step is to use the code to find the maximum, minimum, and range in the data. Then manually enter the starting number and group distance of the data split point according to the actual situation. The second step uses code codes to automatically generate grouping intervals, the data frequency contained in each group interval, and then the frequency and frequency divided by the value of the group distance. Like my own complex and cumbersome code generation effect. The third step uses the interval as the abscissa and the frequency divided by the group distance as the ordinate to draw a frequency distribution histogram. Each column of the picture is required to have no spacing next to each other, and each column has a different color. The graph you just made with the pivot table is very good, the effect is best if the columns have different colors next to each other. Thank you!
Reply With Quote
  #9  
Old 08-29-2020, 05:28 AM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Use codes to find the maximum, minimum and range in the data. Then manually enter the starting number and group distance of the data split point according to the actual situation. The code code is used to automatically generate the grouping interval, and the last data separation point is the first time the accumulated value is equal to or greater than the maximum value of the data source under the previous grouping logic. The grouping ends.

my code as following:
p = ActiveSheet.Cells.Rows.Count
q = ActiveSheet.Cells(p, 3).End(xlUp).Row
If q >= 2 Then
groupsp = Cells(q, 3) - Cells(q - 1, 3)
[b8] = groupsp
Else
groupsp = [b8].Value
End If
'Range("c3:C" & q).Clear
If q <= 1 And [b8].Value = "" Then Exit Sub
If [c1].Value = "" And [b8].Value <> "" Then Exit Sub
For drow = q + 1 To ActiveSheet.Cells.Rows.Count
Cells(drow, 3) = Cells(drow - 1, 3) + groupsp
If Cells(drow, 3) = vMax Then Exit For
If Cells(drow, 3) > vMax Then Exit For
Next drow

'MsgBox groupsp
[b10] = ActiveSheet.Cells(p, 3).End(xlUp).Row - 1
End Sub
Reply With Quote
  #10  
Old 08-29-2020, 05:34 AM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

The reason for using vba instead of the perspective chart is that when I change the two manually entered values mentioned above, the code can automatically generate new data and charts. Thank you!
Reply With Quote
  #11  
Old 08-29-2020, 06:03 AM
p45cal's Avatar
p45cal p45cal is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

A start:
In Sheet1 of the attached, click the button.
In Sheet2 some negative numbers in the source data have been used.
In Sheet5 the same rusults as yours were obtained by using an alternative line of vba code starting vStart=… (see comments in the code).
Attached Files
File Type: xlsm msofficeforums45540plansv3.xlsm (34.4 KB, 9 views)
Reply With Quote
  #12  
Old 08-29-2020, 06:10 AM
p45cal's Avatar
p45cal p45cal is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by leeqiang View Post
The reason for using vba instead of the perspective chart is that when I change the two manually entered values mentioned above, the code can automatically generate new data and charts. Thank you!
I was asking if the 3 for the group interval was always 3 or if it needed to be calculated from the source data or from perhaps a cell value in the sheet. As it is I've used a hard-coded 3.
Reply With Quote
  #13  
Old 08-30-2020, 06:23 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
I was asking if the 3 for the group interval was always 3 or if it needed to be calculated from the source data or from perhaps a cell value in the sheet. As it is I've used a hard-coded 3.

In my code, both the starting value and the group pitch are manually entered. Specifically, enter the group spacing in cell B8, and enter the starting data value in cell C1. A judgment condition is also set. When cells C1 and C2 have data and cell B8 has no input data, the group distance can be obtained by subtracting the value of cell C1 from the value of cell C2. Then automatically generate all the separation points of the data, and then other data.

These logics are reflected in my following code:
p = ActiveSheet.Cells.Rows.Count
q = ActiveSheet.Cells(p, 3).End(xlUp).Row
If q >= 2 Then
groupsp = Cells(q, 3) - Cells(q - 1, 3)
[b8] = groupsp
Else
groupsp = [b8].Value

End If
'Range("c3:C" & q).Clear
If q <= 1 And [b8].Value = "" Then Exit Sub
If [c1].Value = "" And [b8].Value <> "" Then Exit Sub
For drow = q + 1 To ActiveSheet.Cells.Rows.Count
Cells(drow, 3) = Cells(drow - 1, 3) + groupsp
If Cells(drow, 3) = vMax Then Exit For
If Cells(drow, 3) > vMax Then Exit For
Next drow

'MsgBox groupsp
[b10] = ActiveSheet.Cells(p, 3).End(xlUp).Row - 1
End Sub
Reply With Quote
  #14  
Old 08-30-2020, 06:38 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
I was asking if the 3 for the group interval was always 3 or if it needed to be calculated from the source data or from perhaps a cell value in the sheet. As it is I've used a hard-coded 3.

Your code is very concise and clear. You set the group distance to 3, and the data start value is automatically generated according to the calculation. I want to be able to manually input the two values of the data start value and the data group distance, which can be entered in a dialog box. Thank you!
Reply With Quote
  #15  
Old 08-30-2020, 06:44 PM
leeqiang leeqiang is offline Problems with vba frequency distribution histogram Windows 10 Problems with vba frequency distribution histogram Office 2019
Advanced Beginner
Problems with vba frequency distribution histogram
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by leeqiang View Post
Your code is very concise and clear. You set the group distance to 3, and the data start value is automatically generated according to the calculation. I want to be able to manually input the two values of the data start value and the data group distance, which can be entered in a dialog box. Thank you!





Manually input the two values of the data start value and the data group distance, you can set a dialog box to input. This is because these two values can be entered according to the actual situation, which has greater flexibility. The function of the code is to quickly generate a series of data and charts in time after changing these two values.

If the pivot table can automatically change into a new chart with the generated data, it is also possible to use the pivot table to generate the chart.
Reply With Quote
Reply

Tags
histogram

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to draw the histogram? leeqiang Excel Programming 1 08-27-2020 05:19 PM
Problems with vba frequency distribution histogram Frequency of Count Ifs keeble Excel 4 02-01-2017 10:33 AM
Formula to calculate Frequency lwls Excel 1 03-30-2015 05:40 AM
Problems with vba frequency distribution histogram Trying to count frequency borninscorpio Excel 4 07-31-2013 12:44 AM
Frequency of a name bryant03 Excel 1 06-27-2012 10:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft