时间戳

首页 > 解决方案 > 使用 VBA 的动态多重图表

excel - 使用 VBA 的动态多重图表

问题描述

我尝试使用 VBA 创建动态图表，例如我有 5 个学生我需要创建 5 个图表

对于每个学生。

Sub Macro4()

Rows("2:2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet1!$2:$2")
Rows("3:3").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet1!$3:$3")
Rows("4:4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet1!$4:$4")
Range("D12").Select
ActiveWorkbook.Save
End Sub

我使用宏创建这个只是为了测试，但我尝试动态地这样做，因为如果我有超过 100 名学生，这将是困难的，等等。

我希望你们帮助我

谢谢

标签： excelvba

解决方案

请尝试下一个方法。无需任何选择。选择没用，它只消耗 Excel 资源：

Sub AddCharts()
 Dim sh As Worksheet, ch As Shape, chartNo As Long
 Dim prevWith As Long, i As Long

 Set sh = ActiveSheet  'the sheet where the charts to be created
 'chartNo = 5
 chartNo = = Worksheets("Sheet1").Range("A" & rows.count).End(xlUp).row - 1

 For i = 1 To chartNo
    Set ch = sh.Shapes.AddChart
    ch.Chart.ChartType = xlColumnClustered
    ch.Chart.SetSourceData Source:=Range("Sheet1!$" & i + 1 & ":$" & i + 1)
    ch.Chart.Parent.left = sh.Range("A1").left + prevWith 'here the first left chart position to be set
    prevWith = ch.Chart.Parent.width 'the chart width, the next one will be added to its right side
 Next i

 ActiveWorkbook.Save
End Sub

推荐阅读