VBA 对象变量或未设置块变量

Question

此代码的要点是在新工作表中创建图表，它确实如此。在此之后，当我单击按钮以在名为相同的新工作表中再次生成图表时，它应该删除该工作表并创建一个新的生成图表。

它创建了图表，但是当我返回单击按钮时，它会在按钮所在的工作表中生成图表并抛出错误 91：对象变量或未设置块变量。

调试将我指向以下行：

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Demand Line Chart"

下面是代码：

Sub HistoricalDemand()
' Creates a line chart for the demand column
For Each ws In Worksheets
If ws.Name = "Demand Line Chart" Then
Application.DisplayAlerts = False
Sheets("Demand Line Chart").Delete
Application.DisplayAlerts = True
Exit For
End If
Next
Columns("A:A").Select
Selection.NumberFormat = "[$-en-US]mmm-yy;@"
Range("A:A,E:E").Select
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
' Try With command here
ActiveChart.SetSourceData Source:=Range("A:A,E:E")
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Demand Line Chart"
' Places line chart in a new worksheet called Demand Line Chart
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 70
Selection.MajorTickMark = xlNone
With ActiveChart
.Axes(xlCategory).Select
.Axes(xlCategory).MajorUnit = 2
.ChartTitle.Select
.ChartTitle.Text = "Historical Demand"
.SetElement (msoElementLegendRight)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Historical Demand"
With Selection.Format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
ActiveChart.ChartArea.Select
End With
End Sub

Answer 1

使用 VBA 编写代码时的一些建议：

1. 使用显式选项，这样您就不会遇到未定义变量的意外行为
2. 始终缩进代码(请参阅 www.rubberduckvba.com 可帮助您处理数据的免费工具(
3. 尝试分离定义变量的逻辑并重用它们
4. 除非绝对必要，否则避免使用 select

查看并自定义代码，使其符合您的需求。

您可以通过按F8并逐行执行来查看代码中发生的情况。

法典：

Public Sub HistoricalDemand()
' Creates a line chart for the demand column
Dim targetSheet As Worksheet
Dim sourceDataSheet As Worksheet
Dim sourceDataRange As Range
Dim targetChart As Shape
Dim targetSheetName As String
Dim targetChartTitle As String
Dim dataLastRow  As Long
' Define parameters
targetSheetName = "Demand Line Chart"
targetChartTitle = "Historical Demand"
' Set a reference to the source data sheet
Set sourceDataSheet = ThisWorkbook.Worksheets("DATA")
' Find last row in data sheet
dataLastRow = sourceDataSheet.Cells(sourceDataSheet.Rows.Count, 1).End(xlUp).Row
' Set reference to the source range
Set sourceDataRange = sourceDataSheet.Range("A1:A" & dataLastRow & ",E1:E" & dataLastRow)
' Delete the sheet if exists
For Each targetSheet In Worksheets
If targetSheet.Name = targetSheetName Then
Application.DisplayAlerts = False
' This will delete the matching sheet
targetSheet.Delete
Application.DisplayAlerts = True
Exit For
End If
Next
' Apply format to the first column (A)
sourceDataSheet.Range("A1:A" & dataLastRow).NumberFormat = "[$-en-US]mmm-yy;@"
' Add the chart
Set targetChart = sourceDataSheet.Shapes.AddChart2(332, xlLineMarkers)
' Apply the target chart settings
With targetChart.Chart
.SetSourceData Source:=sourceDataRange
.Location Where:=xlLocationAsNewSheet, Name:=targetSheetName
' Places line chart in a new worksheet called Demand Line Chart
.Axes(xlCategory).TickLabels.Orientation = 70
.Axes(xlCategory).MajorTickMark = xlNone
.Axes(xlCategory).Select
.Axes(xlCategory).MajorUnit = 2
.ChartTitle.Text = targetChartTitle
.SetElement (msoElementLegendRight)
.ChartTitle.Format.TextFrame2.TextRange.Characters.Text = targetChartTitle
With .ChartTitle.Format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
End With
End Sub

让我知道它是否有效