excel - VBA-Excel 和大数据集导致程序崩溃

Question

第一次发海报，总体来说是编程新手。我有一个项目，其中我必须建立一个财务模型来挖掘 Excel 中的数据。我已经成功地在 VBA 上构建了该模型。我已经对 3,000 行数据集进行了测试，并且成功了。我将简要解释它的作用。

我在指定日期在多个交易所跟踪指定股票。我下载数据(大约 935,000 行)第一步是将给定交易所的所有数据(大约 290,000 行)复制到新工作表上(这大约需要 8 分钟)，然后创建一个新列来记录买卖价差(12 秒) )，下一步是我遇到的麻烦，我基本上将每行数据排名两次，一列用于出价大小，一列用于要价大小。我创建了一个函数，它使用 Excel 百分位数函数，并根据给定的出价和要价大小进行排名。截至目前，我已运行该宏 35 分钟，但尚未执行。我无法尝试其他宏，因为每个宏都依赖于前一个宏。

所以我的基本问题是，由于我的数据集很大，我的模型不断崩溃。在处理测试数据时代码似乎很好，并且当我运行程序时它不会抛出任何错误，但是数据集越大，它就会崩溃。有没有人有什么建议？如此大量的数据，这正常吗？

提前致谢。假的

这是给我带来麻烦的子程序和函数，子程序接受运行该函数所需的输入，然后弹出到指定的单元格中。该代码应该对三个单独的工作表重复该过程。目前，我希望它在一张纸上工作，因此使用注释不包含循环

Sub Bucketting()

Dim firstRow As Long
Dim lastRow As Long
Dim counter As Long
Dim bidRange As Range
Dim offerRange As Range
Dim bidScroll As Range
Dim offerScroll As Range
Dim Ex As String
Dim i As Integer

'For i = 1 To 1 Step 1 'Sheet Selection Process
 '   If i = 1 Then
  '      Ex = "Z"
   ' ElseIf i = 2 Then
    '    Ex = "P"
   ' Else
    '    Ex = "T"
   ' End If

Sheets("Z").Select 'Sheet selected

With ActiveSheet

    firstRow = .UsedRange.Cells(1).Row + 1
    lastRow = .UsedRange.Rows.Count

   Set bidRange = .Range("F2:F" & lastRow)
   Set offerRange = .Range("G2:G" & lastRow)

    For counter = lastRow To firstRow Step -1

        Set bidScroll = .Range("F" & counter)
        Set offerScroll = .Range("G" & counter)

        With .Cells(counter, "J")
        .Value = DECILE_RANK(bidRange, bidScroll)
        End With

        With .Cells(counter, "K")
        .Value = DECILE_RANK(offerRange, offerScroll)
        End With

    Next counter

End With

Range("J1").Select
ActiveCell = "Bid Rank"

ActiveCell.Offset(0, 1) = "Offer Rank"

'Next i

End Sub

 Function DECILE_RANK(DataRange, RefCell)

    'Credit: BJRaid 
    'DECILE_RANK(The Range of data)
    'Declares the function that can be called in the spreadsheet cell  - enter '=DECILE_RANK(A5:A50,A5)

    'Using the percentile worksheet function calculate where the 10th, 20th etc percentile of the reference range are

    DEC1 = Application.WorksheetFunction.Percentile(DataRange, 0.1)
    DEC2 = Application.WorksheetFunction.Percentile(DataRange, 0.2)
    DEC3 = Application.WorksheetFunction.Percentile(DataRange, 0.3)
    DEC4 = Application.WorksheetFunction.Percentile(DataRange, 0.4)
    DEC5 = Application.WorksheetFunction.Percentile(DataRange, 0.5)
    DEC6 = Application.WorksheetFunction.Percentile(DataRange, 0.6)
    DEC7 = Application.WorksheetFunction.Percentile(DataRange, 0.7)
    DEC8 = Application.WorksheetFunction.Percentile(DataRange, 0.8)
    DEC9 = Application.WorksheetFunction.Percentile(DataRange, 0.9)


    ' Calculate the Decile rank that the reference cell value sits within

    If (RefCell <= DEC1) Then DECILE_RANK = 1
    If (RefCell > DEC1) And (RefCell <= DEC2) Then DECILE_RANK = 2
    If (RefCell > DEC2) And (RefCell <= DEC3) Then DECILE_RANK = 3
    If (RefCell > DEC3) And (RefCell <= DEC4) Then DECILE_RANK = 4
    If (RefCell > DEC4) And (RefCell <= DEC5) Then DECILE_RANK = 5
    If (RefCell > DEC5) And (RefCell <= DEC6) Then DECILE_RANK = 6
    If (RefCell > DEC6) And (RefCell <= DEC7) Then DECILE_RANK = 7
    If (RefCell > DEC7) And (RefCell <= DEC8) Then DECILE_RANK = 8
    If (RefCell > DEC8) And (RefCell <= DEC9) Then DECILE_RANK = 9
    If (RefCell > DEC9) Then DECILE_RANK = 10

End Function

Answer 1

935,000 行对于 Excel 来说已经很多了。喜欢，真的很多。除非使用真实的数据库，如果您的应用程序实际上在每个单元格中放置了 =Percentile(...) ，我建议您尝试使用其他工具。也许是 VBA 本身的某些东西。更一般地说，使用单元格外部的内容 - 然后将结果值存储在单元格中。维护那些相互依赖于 935k 行数据的公式会产生大量开销。