excel - 如何在excel VBA中创建一个varios图表

Question

我编写了一个代码来通过 vba excel 创建一个图表。但是我无法编写可以在同一个工作表中生成多个图形的代码，也就是说，我有 4 列，我想创建 4 个图形。

有人可以帮我完成这项任务吗？

这是我的代码：

Sub create_BarChart()
   Dim myWorksheet As Worksheet
   Dim mySourceData As Range
   Dim myChart As Chart
   Dim myShape As Shape   
   Dim myChartDestination As Range
   Set myWorksheet = ThisWorkbook.Worksheets("corelacao")
   Set myChartDestination = myWorksheet.Range("D36:H45")
   Set myShape = myWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DBarClustered)
   Set myChart = myShape.Chart
   With myChart
           .SetSourceData Source:=myWorksheet.Range("B1:C32")
           .ChartTitle.Text = "Analise de correlações"
           .Legend.Left = 250  'posição vertical
           .Legend.Width = 300 '100
          .Parent.Height = 200
          .Parent.Width = 269
          .Parent.Left = 95          
   End With
   With myShape
       .Height = 325 ' resize
       .Top = 300     ' reposition
       .Left = 100   ' reposition
       .Fill.ForeColor.RGB = RGB(230, 225, 220)
       .Fill.Solid
   End With
End Sub

标签： excelvba

Answer 1

我将在数组变量中指定图表的输入范围，并通过数组查看以生成图表：

Dim arr(2) As String
Dim mySourceData As Range
Dim myChart As Chart
Dim myShape As Shape
Dim myChartDestination As Range
Dim myWorksheet As Worksheet
Set myWorksheet = ActiveSheet

'Data area for each chart
arr(1) = "B1:C32"
arr(2) = "B1:B31,D1:D31"

For a = 1 To 2
    Set myChartDestination = myWorksheet.Range("D36:H45")
    Set myShape = myWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DBarClustered)
    Set myChart = myShape.Chart
    With myChart
        .SetSourceData Source:=myWorksheet.Range(arr(a))
        .ChartTitle.Text = "Analise de correlações"
        .Legend.Left = 250
        .Legend.Width = 300
        .Parent.Height = 200
        .Parent.Width = 269
        .Parent.Left = 95
    End With
    With myShape
        .Height = 325
        .Top = 300
        .Left = 100 + ((a - 1) * 250)
        .Fill.ForeColor.RGB = RGB(230, 225, 220)
        .Fill.Solid
    End With
Next