英文:
How to dynamically set a doughnut chart size in vba?
问题
这是我现在所拥有的:
这是我想要的:
英文:
I have been racking my brain to fix this issue. For reasons I cannot figure out, the doughnut charts are displaying really tiny. So, I have a bunch of charts that I am generating on the fly and stacking them next to each other. I am setting the chart dimensions on the fly, but there is so much empty space inside the border of the chart that can be used to make the doughnut bigger. I am enclosing some additional details below:
What I want:
This is the code that I have:
Set ws = ActiveSheet
Const numChartsPerRow = 3
Const TopAnchor As Long = 8
Const LeftAnchor As Long = 380
Const HorizontalSpacing As Long = 3
Const VerticalSpacing As Long = 3
Const ChartHeight As Long = 125
Const ChartWidth As Long = 210
Counter = 0
For Each zChartSet In ws.ChartObjects
zChartSet.Delete
Next zChartSet
While j <= iTeamMemberCount
ActiveSheet.Shapes.AddChart2(251, xlDoughnut).Select
ActiveChart.SetSourceData Source:=Worksheets("Analytics Team Stats").Range("E" & j & ":F" & j)
ActiveChart.FullSeriesCollection(1).Select
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=""series1"""
ActiveChart.FullSeriesCollection(1).Values = "={1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}"
ActiveChart.ChartTitle.Select
Selection.Caption = Split(Worksheets("Analytics Team Stats").Range("A" & j), ",")(1) & " - " & Format(Worksheets("Analytics Team Stats").Range("E" & j), "0%")
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Explosion = 15
ActiveChart.ChartGroups(1).DoughnutHoleSize = 55
ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.0500000007
.Solid
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.5
.Transparency = 0
.Solid
End With
ActiveChart.FullSeriesCollection(1).Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = Worksheets("Analytics Team Stats").Range("A" & j)
ActiveChart.FullSeriesCollection(2).Values = Worksheets("Analytics Team Stats").Range("E" & j & ":F" & j)
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).AxisGroup = 2
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).Points(1).Select
Selection.Format.Fill.Visible = msoFalse
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).Points(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.0500000007
.Transparency = 0
.Solid
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0.1999999881
.Solid
End With
ActiveChart.SetElement (msoElementLegendNone)
j=j+1
wend
答案1
得分: 0
为了自动化环的厚度过程,您应该这样思考:a) 厚度是高度和宽度中较小者的函数。 b) 找到最小可能的宽度和高度值,然后测试哪个DonutHoleSize的值是令人满意的。 c) 用最大可能的高度或宽度中较小的那一个进行同样的操作。因此,我们将得到两对数字(x1,y1)和(x2,y2),其含义如下:对于最小的宽度/高度,x1使我满意的DonutHoleSize值为y1,对于最大宽度/高度,x2使我满意的DonutHoleSize值为y2。
您现在可以应用线性插值的方法,以找到宽度/高度> = x1且<= x2的DonutHoleSize的令人满意的值。公式如下:
Public Function linear_interpolation(x1 As Double, y1 As Double, x2 As Double, y2 As Double, ByVal x As Double) As Double
If (x2 - x1) = 0 Then '除零错误
linear_interpolation = 0# '或者其他任何值...也可以是y1或y2 ...
Else
linear_interpolation = y1 + (((x - x1) * (y2 - y1)) / (x2 - x1))
End If
End Function
Function minOf(a As Variant, b As Variant) As Variant
If (a < b) Then minOf = a Else minOf = b
End Function
在代码中的使用:
ActiveChart.ChartGroups(1).DoughnutHoleSize = linear_interpolation(280, 75, 85, 50, minOf(ActiveChart.Chart.ChartArea.Width, ActiveChart.Chart.ChartArea.Height))
在我的测试中,对于280, 75和85, 50这两对数字是令人满意的,您可以按照我上面描述的方式进行调整。
英文:
To automate the ring thickness process, you should think like this: a) Thickness is a function of the smaller of height and width. b) Find the smallest possible width-height value, then test which value of DonutHoleSize is satisfactory. c) Do the same with the smallest of the largest possible height or width. So we will have two pairs of numbers (x1, y1) and (x2, y2) which are read as follows: For the minimum width/height x1 satisfies me for DonutHoleSize the value y1, and for the maximum width/height x2 satisfies me for the DonutHoleSize the value y2.
You can now apply the method of linear interpolation to find a satisfactory value for DonutHoleSize for a width/height >= x1 and <= x2. The formula is as follows:
Public Function linear_interpolation(x1 As Double, y1 As Double, x2 As Double, y2 As Double, ByVal x As Double) As Double
If (x2 - x1) = 0 Then 'division by zero
linear_interpolation = 0# 'or anything else...maybe y1 or y2 ...
Else
linear_interpolation = y1 + (((x - x1) * (y2 - y1)) / (x2 - x1))
End If
End Function
Function minOf(a As Variant, b As Variant) As Variant
If (a < b) Then minOf = a Else minOf = b
End Function
'and the usage in code:
ActiveChart.ChartGroups(1).DoughnutHoleSize = linear_interpolation(280, 75, 85, 50, minOf(ActiveChart.Chart.ChartArea.Width, ActiveChart.Chart.ChartArea.Height))
In my own tests, pairs 280,75 and 85,50 are satisfactory, you can as I described above, adjust them.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论