ActiveSheet.Shapes.AddChart.Select是VBA中的代码，放在VB中如何才能确保不会出错？

[yiyaozjk]

ActiveSheet.Shapes.AddChart.Select 这句经常任打开了一个其他的EXCEL文件时，再执行下面代码都会报错91 对象变量或with块变量未设置错误

Private Sub test()
Dim i As Integer
Set xlBook1 = xlApp.Workbooks.Open("Z:\TEMP_临时文件夹\解决局域网共享\组网控制软件\组网控制软件\输出文件.xls")
Set xlBook2 = xlApp.Workbooks.Open("Z:\TEMP_临时文件夹\解决局域网共享\组网控制软件\组网控制软件\电子看板编号.xls")
xlBook1.Sheets(1).Rows("60:100").Delete
For i = 2 To 28
xlBook1.Sheets(1).Cells(60, i) = xlBook2.Sheets(1).Cells(i, 2) '写加工组别名称
xlBook1.Sheets(1).Cells(61, i) = xlBook2.Sheets(1).Cells(i, 5)
xlBook1.Sheets(1).Cells(62, i) = xlBook1.Sheets(1).Cells(i, 5)
Next
xlBook1.Sheets(1).Cells(61, 2) = "预计产能"
xlBook1.Sheets(1).Cells(62, 2) = "实际产能"
xlBook2.Close
Set xlBook2 = Nothing
xlBook1.Sheets(1).Activate

ActiveSheet.Shapes.AddChart.Select 经常任打开了一个其他的EXCEL文件都会报错91 对象变量或with块变量未设置
ActiveChart.SetSourceData Source:=xlBook1.Sheets(1).Range("$B$60:$AB$62")
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Characters.Text = "实际产能与计划产能比较图" '写图表标题
ActiveChart.ChartTitle.Characters.Font.ColorIndex = 18
ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "生产线" & " 报告时间:" & Now() '写Y轴标题
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "数量" '写X轴标题
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartGroups(1).Overlap = 100
ActiveChart.SeriesCollection(2).Points(1).Select
ActiveSheet.ChartObjects(1).Left = xlBook1.ActiveSheet.Range("b59").Left '设置图表的左位置
ActiveSheet.ChartObjects(1).Top = xlBook1.ActiveSheet.Range("b59").Top '设置图表的位置
ActiveSheet.ChartObjects(1).Width = 1500
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(2).ApplyDataLabels
ActiveChart.SeriesCollection(2).Points(1).DataLabel.Text = "19"
For i = 1 To 26
If ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text > ActiveChart.SeriesCollection(2).Points(i).DataLabel.Text Then
ActiveChart.SeriesCollection(2).Points(i).Interior.ColorIndex = 4 '4为绿 3为红
End If
Next
xlBook1.Sheets(1).Rows("60:100").Delete
Application.DisplayAlerts = False
xlBook1.Close savechanges:=True
'xlBook1.Close True
Application.DisplayAlerts = True
Set xlBook1 = Nothing

Unload Me
End Sub

[yiyaozjk]

有没有高手出手指导一下>>>

[Jia_H]

当然会出错了。这些ActiveSheet, ActiveWindow都是指当前活动的对象，VBA中可以使用，因为在运行时已经有对应的上下文环境（打开的Excel文件）把它们设定好了。你在VB中需要定义一下要使用的对象，然后再操作这些对象而不是那些ActiveXXX对象。如下用于代替ActiveSheet，其他的类似替换一下就好了：

Dim curSheet As Worksheet

Set curSheet = xlBook1.Sheets(1)