如何在VBA中动态设置甜甜圈图的大小?

huangapple go评论66阅读模式
英文:

How to dynamically set a doughnut chart size in vba?

问题

这是我现在所拥有的:

如何在VBA中动态设置甜甜圈图的大小?

这是我想要的:

如何在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 have right now:
如何在VBA中动态设置甜甜圈图的大小?

What I want:

如何在VBA中动态设置甜甜圈图的大小?

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   &#39;division by zero
      linear_interpolation = 0#  &#39;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 &lt; b) Then minOf = a Else minOf = b
End Function

&#39;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.

huangapple
  • 本文由 发表于 2023年4月11日 11:46:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/75982248.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定