为什么此代码（涉及Excel图表）导致“Microsoft Excel已停止工作”崩溃？ [英] Why does this code (involving Excel charts) cause "Microsoft Excel has stopped working" crash?

问题描述

我在Excel 2010 VBA中编写了一个程序，它执行数值分析，生成一些图表作为单独的表格。该代码在决定运行完成时效果很好，但是这段代码的任意内容会导致整个Excel应用程序的
致命崩溃。我将代码缩小到下面你看到的内容。通过执行以下步骤可以可靠地复制崩溃：

I've written a program in Excel 2010 VBA which does a numerical analysis that produces some charts as separate sheets. The code works great when it decides to run to completion, but something about this snippet of code arbitrarily causes a fatal crash of the whole Excel application. I narrowed down the code to what you see below. The crash can be duplicated reliably by performing the following steps:

1）创建一个新工作簿，并确保它有一个名为"工作表"的工作表。工作表Sheet" （默认情况下应该存在）。

1) Create a new workbook and make sure it has a worksheet named "Sheet1" (which should exist by default).

2）转到VBA并添加模块。

2) Go to VBA and add a module.

3）将以下代码粘贴到模块中：

3) Paste the following code into the module:

Option Explicit
Dim iRunCounter As Integer

Sub RunLoop()
    For iRunCounter = 1 To 100
        Application.StatusBar = iRunCounter
        Call MakeAxisCharts(40)
        DoEvents
    Next iRunCounter
    Application.StatusBar = False
End Sub

Sub MakeAxisCharts(iNumStats As Integer)
    Dim myChart As Chart
    Dim mySeries As Series
    Dim iChart As Integer
    
    Application.DisplayAlerts = False
                    
    'Opposite Axis Plots:
    For iChart = 1 To 6
                    
        On Error Resume Next
        Charts(iChart & "-" & iChart + 6).Delete
        On Error GoTo xErr
                
        Set myChart = Charts.Add
        With myChart
            .Location Where:=xlLocationAsNewSheet, Name:=iChart & "-" & iChart + 6
            .ChartType = xlXYScatterLinesNoMarkers
                        
            'Axis A-B
            Set mySeries = .SeriesCollection.NewSeries
            mySeries.ChartType = xlXYScatterLinesNoMarkers
            mySeries.Name = "=Sheet1!R1C" & 71 + iChart
            mySeries.XValues = "=Sheet1!R2C71:R" & CStr(iNumStats) & "C71"
            mySeries.Values = "=Sheet1!R2C" & 71 + iChart & ":R" & CStr(iNumStats) & "C" & 71 + iChart
            Set mySeries = Nothing
                        
            'Axis A
            Set mySeries = .SeriesCollection.NewSeries
            mySeries.ChartType = xlXYScatterLinesNoMarkers
            mySeries.Name = "=Sheet1!R1C" & 77 + iChart
            mySeries.XValues = "=Sheet1!R2C71:R" & CStr(iNumStats) & "C71"
            mySeries.Values = "=Sheet1!R2C" & 77 + iChart & ":R" & CStr(iNumStats) & "C" & 77 + iChart
            Set mySeries = Nothing
            
        End With
    
    Next iChart
            
xErr:
    If Err.Number <> 0 Then
        MsgBox "Error #" & Err.Number & ": " & Err.Description
    End If
    Set mySeries = Nothing
    Set myChart = Nothing
    Application.DisplayAlerts = True
End Sub

4）执行RunLoop子例程。

4) Execute the RunLoop subroutine.

这里的任何人都可以在我的代码中找到导致此错误的缺陷吗？就此而言，这里的任何人都可以重现这次崩溃吗？任何想法或建议都将深表感谢。

Can anyone here find a flaw in my code that would cause this error? For that matter, can anyone here reproduce this crash? Any thoughts or suggestions would be sincerely appreciated.

-John

推荐答案

我想我已经弄清楚了这段代码的问题。它在以下声明中任意失败：

I think I've figured out the issue with this code. It was failing arbitrarily at the following statement:

mySeries.ChartType = xlXYScatterLinesNoMarkers

mySeries.ChartType = xlXYScatterLinesNoMarkers

我发现我可以用以下代码替换此语句以产生相同的结果：

I found that I can replace this statement with the following code to produce the same result:

mySeries。 Format.Line.Visible = msoTrue

mySeries.Format.Line.Style = msoLineSingle

mySeries.MarkerStyle = xlMarkerStyleNone

mySeries.Format.Line.Visible = msoTrue
mySeries.Format.Line.Style = msoLineSingle
mySeries.MarkerStyle = xlMarkerStyleNone

我不明白为什么ChartType语句失败。出于某种原因，Excel不喜欢将Series对象的ChartType属性设置为  xlXYScatterLinesNoMarkers。我没有用其他ChartType值测试它，但是这个值总是在
或更晚的时间内失败。现在，我很高兴能够找到解决办法。如果有人对为什么ChartType语句不起作用，我有兴趣知道。

I don't understand why the ChartType statement fails. For some reason, Excel does not like the Series object's ChartType property to be set to xlXYScatterLinesNoMarkers. I didn't test it with other ChartType values, but this one always fails sooner or later. For now, I'm relieved to have found a work-around. If anyone has some insight into why the ChartType statement does not work, I'd be interested to know.

这篇关于为什么此代码（涉及Excel图表）导致“Microsoft Excel已停止工作”崩溃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！