excel - 运行时错误 91 对象变量或未设置块变量

Question

这个简单的代码得到这个错误。为什么？

 Sub ScaleAxes()
With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = ActiveSheet.Range("A33").Value
 End With

结束子

标签： excelvba

Answer 1

图表/图表对象调查

• （OP 的）设置只是一个带有嵌入式图表（对象）的工作表。
• 以下说明了编写代码的各种方法。除了最后一个测试什么是活动的之外，所有的程序都做同样的事情。选择其中一个或创建另一个。
• 这只是一个对象调查，因为我对图表一无所知，也不想查看文档。您可能应该调查Charts vs Worksheets vs Sheets和Chart vs ChartObject和Axes vs Axis。

编码

Option Explicit

Sub TestChart()
    Dim ch As Chart: Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
    With ch.Axes(xlCategory, xlPrimary)
        .MaximumScale = ActiveSheet.Range("A33").Value
    End With
End Sub

Sub TestChartObject()
    Dim cho As ChartObject: Set cho = ActiveSheet.ChartObjects("Chart 1")
    With cho.Chart.Axes(xlCategory, xlPrimary)
        .MaximumScale = ActiveSheet.Range("A33").Value
    End With
End Sub

Sub testAxesActiveSheet()
    With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory, xlPrimary)
        .MaximumScale = ActiveSheet.Range("A33").Value
    End With
End Sub

Sub testAxesTripleParent()
    With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory, xlPrimary)
        .MaximumScale = .Parent.Parent.Parent.Range("A33").Value
    End With
End Sub

Sub testAxes()
    With ActiveSheet
        Dim axs As Axis
        Set axs = .ChartObjects("Chart 1").Chart.Axes(xlCategory, xlPrimary)
        axs.MaximumScale = .Range("A33").Value
    End With
End Sub

Sub testAxesChart()
    With ActiveSheet
        Dim ch As Chart: Set ch = .ChartObjects("Chart 1").Chart
        Debug.Print ch.Name ' Result?: 'Sheet1 Chart 1'
        Dim axs As Axis: Set axs = ch.Axes(xlCategory, xlPrimary)
        axs.MaximumScale = .Range("A33").Value
    End With
End Sub

Sub testAxesChartObject()
    With ActiveSheet
        Dim cho As ChartObject: Set cho = .ChartObjects("Chart 1")
        Debug.Print cho.Name ' Result: 'Chart 1'
        Dim axs As Axis: Set axs = cho.Chart.Axes(xlCategory, xlPrimary)
        axs.MaximumScale = .Range("A33").Value
    End With
End Sub

Sub TestActiveChart()
    On Error GoTo SheetError
    Debug.Print "ActiveSheet Name: " & ActiveSheet.Name
    On Error GoTo ChartError
    Debug.Print "ActiveChart Name: " & ActiveChart.Name
ProcExit:
    Exit Sub
SheetError:
    Debug.Print "No active sheet."
    Resume Next
ChartError:
    Debug.Print "No active chart."
    Resume Next
End Sub