excel - 创建 Visual Basic 图表

Question

我正在尝试在 VBA 中创建一个条形图，它将显示特定月份的百分比变化。数据还需要在宏中计算。例如，能源系列显示 3 月至 2 月/2 月，而不仅仅是当月的数据。

我该怎么做呢？我插入了我希望显示的图像。

我很感激任何帮助。谢谢！

数据：

我希望 vba 代码显示的内容：

Sub chart()
Dim i As Integer
Dim chart As ChartObject
With ActiveSheet.ChartObjects.Add _
(Left:=90, Width:=375, Top:=75, Height:=225)
.chart.SetSourceData Source:=Sheets("sheet1").Range("B2:D2", "B5:D5")
.chart.HasTitle = True
.chart.ChartTitle.Text = "1-month percent change for X month"
.chart.ChartType = xlBarClustered
.chart.HasLegend = False
End With
End Sub

标签： excelvba

Answer 1

将计算的百分比放入数组并使用series.collection(1).values 属性。

Sub chart()

    Dim i As Integer
    Dim chart As ChartObject
    Dim rng2 As Range
    Set rng2 = Range("C5:E5")

    ' calculate percent
    Dim cell As Range, pcent() As Single, prev As Single, curr As Single
    ReDim pcent(rng2.Count - 1)

    For Each cell In rng2
        curr = CSng(cell)
        prev = CSng(cell.Offset(-1, 0))
        pcent(i) = (curr - prev) / prev
        i = i + 1
    Next

    With ActiveSheet.ChartObjects.Add _
(Left:=90, Width:=375, Top:=100, Height:=225).chart
        .SetSourceData Source:=Sheets("sheet1").Range("B2:E2,B5:E5")
        .HasTitle = True
        .ChartTitle.Text = "1-month percent change for " &   ActiveSheet.Range("B5").Value
        .ChartType = xlBarClustered
        .HasLegend = False
        .SeriesCollection(1).Values = pcent
        .Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "0.00%"
    End With
End Sub