VBA : auto chart area

最新推荐文章于 2024-04-14 12:19:10 发布

原创最新推荐文章于 2024-04-14 12:19:10 发布 · 512 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#vba #integer #c

OTHERS 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个使用VBA（Visual Basic for Applications）自动生成Excel图表的示例代码。该代码能够遍历工作簿中的所有工作表，删除已存在的图表对象，并为每个工作表生成一个新的折线图，数据源来自各工作表的指定区域。

Sub Auto_Open()

    Dim sheetCount As Integer
    Dim BaseRowNum As Integer
    Dim AvailableRowNum As Integer
    Dim i As Integer
    sheetCount = Application.Sheets.Count
    BaseRowNum = 23

    For i = 1 To sheetCount

        If Sheets(i).ChartObjects.Count >= 1 Then
        Sheets(i).ChartObjects(1).Delete
        End If

        Set mychart = Sheets(i).ChartObjects.Add(Left:=10, Width:=400, Top:=100, Height:=200)
        With mychart.Chart
        .ChartType = xlLine
        .HasLegend = False
        End With

        AvailableRowNum = Application.WorksheetFunction.CountA(Sheets(i).Range("$B$" & BaseRowNum & ":$B$65535")) + BaseRowNum - 1
        Rem Application.Sheets(i).ChartObjects(1).Chart.SetSourceData Source:=Sheets(i).Range("$B$" & BaseRowNum & ":$C$" & AvailableRowNum), PlotBy:=xlColumns

        mychart.Chart.SeriesCollection.NewSeries
        mychart.Chart.SeriesCollection(1).Values = Application.Sheets(i).Range("C" & (BaseRowNum + 1) & ":C" & AvailableRowNum)
        mychart.Chart.SeriesCollection(1).XValues = Application.Sheets(i).Range("B" & (BaseRowNum + 1) & ":B" & AvailableRowNum)

    Next i
End Sub