修改后,Excel VBA将我的图表系列截断为16,384个值

我正在使用Excel 365

我有一个图表,其中包含许多XY散点数据-可能超过20,000个值。但是,数据中的某些异常值超出了正常范围,以致于范围被压缩并且图表变得不那么有意义。我想浏览数据并清理这些离群值以用于视觉目的,但实际上并没有更改原始数据。抱歉,这听起来令人困惑,但这就是设置。

我遇到的问题是,当我运行清理例程时,我正在丢失数千个值。例如,图表中有一个序列,其中包含约20,100个XY数据点。我正在尝试创建一个高通滤波器,以滤除任何超过平均值3倍的东西-因此,如果Y值超过平均值3倍,我会将其设置为平均值3倍。

运行代码后,我的起始序列值的范围是1到39914-基本上,我认为我不需要超过40K的条目。我仔细检查了数据,清理了“过高”的值,然后更改了原始序列的值……最终得到的范围是1到16384。我似乎遇到了某种内存限制,不适用于初始数组/集合/任何东西,但确实适用于我的处理方式。这是完整的代码:

Sub ExportCharts()  ' Iterate through all charts and output to PNG files:
    Dim chartname As String
    Dim thevalues As Variant
    Dim objChrt As ChartObject
    Dim myChart As Chart
    Dim seriescount, i, stopper As Long
    Dim highpass, thevalue As Double
    Dim s1 As Series
    Dim save1 As String

    chartname = "XYZ"

    Set objChrt = Sheets("1080p Med").ChartObjects("FrameTime_Chart")
    Set myChart = objChrt.Chart
    Set s1 = myChart.SeriesCollection(1)

    save1 = s1.Formula ' So I can restore the original formula for the chart

    i = 1
    stopper = 0
    highpass = (Worksheets("1080p Med").Range("C111").Value) * 3 'this is the high pass filter value
    thevalues = s1.Values
    Do Until stopper <> 0 Or i > UBound(thevalues)
        If IsEmpty(thevalues(i)) Then
            stopper = 1
        Else
            If i > 40000 Then 'ignore more than 40,000 items for now
                stopper = 1
            Else
                If thevalues(i) > highpass Then
                    thevalues(i) = highpass
                End If
            End If
        End If
        i = i + 1
    Loop
    s1.Values = thevalues
   ' thevalues here has a range of 1 to 39914 and s1 also has a range of 1 to 39914.
   ' After the above line executes, s1 has a range of 1 to 16384. What's going on!?

    myChart.Refresh 'Not sure this is working -- I want the chart to redraw with the new data

    Call ExportChart(myChart, chartname & "_3_FrameTime") 'Write out a PNG file of the chart.

    s1.Formula = save1  ' Restore the original data to the chart
End Sub

仅修改s1.Values数组会更容易,但这显然是不允许的-否则我做错了。我不知道这可能是同一根本原因的一部分。任何Excel人员都想看一下,对此我将不胜感激。 :-)