vba - Excel VBA - 更新图表比例时出错

Question

我找到了一种更新图表的最大和最小比例的方法。我调整了代码以找到最后一行的最大比例值。下面是代码:

Sub ScaleAxes()

Dim LastRow, LastRow2 As Long

  With ActiveChart.Axes(xlCategory, xlPrimary)
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .MaximumScale = LastRow
    .MinimumScale = ActiveSheet.Range("A2").Value
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
    .MaximumScale = LastRow2
    .MinimumScale = ActiveSheet.Range("B2").Value
  End With
End Sub

但是我收到“对象不支持此属性或方法”错误。我不确定我的代码的哪一部分是错误的。希望你们能帮助我。

Answer 1

.Cells不是 Axes 的属性类(class)和您的With声明它读取您想要访问 Axes 的该属性什么时候，你很可能试图得到 Cells图表所在的工作表的属性。要获得对工作表的引用，您可以使用:

Dim ws As Worksheet

Set ws = ActiveChart.Parent.Parent

然后使用 ws获得 LastRow 时的引用值(value):

Option Explicit

Sub ScaleAxes()

    Dim LastRow, LastRow2 As Long
    Dim ws As Worksheet

    Set ws = ActiveChart.Parent.Parent

    With ActiveChart.Axes(xlCategory, xlPrimary)
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        .MaximumScale = LastRow
        .MinimumScale = ActiveSheet.Range("A2").Value
    End With
    With ActiveChart.Axes(xlValue, xlPrimary)
        LastRow2 = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        .MaximumScale = LastRow2
        .MinimumScale = ActiveSheet.Range("B2").Value
    End With

End Sub