VBA-Excel und großen Datenmengen verursacht Programmabsturz

Ersten mal das poster und der neuen Programmierung im Allgemeinen. Ich habe ein Projekt, in dem ich aufbauen müssen, ein finanzielles Modell von mir für die Daten in excel. Ich gelungen, Gebäude, sagte Modell auf VBA. Ich lief tests auf 3000 Linie dataset, und es war erfolgreich. Ich werde kurz erklären, was es tut.

Ich verfolgen, eine bestimmte Aktie an einem bestimmten Tag auf mehreren Börsen. Lade ich die Daten (etwa 935,000 Linien) Der erste Schritt ist, kopieren Sie alle Daten für einen bestimmten exchange (rund 290.000 steht) auf ein neues Blatt (dies dauert etwa 8min), dann habe ich eine neue Spalte erstellen, melden Sie den bid-ask spread (12secs), der nächste Schritt ist das, was Im Probleme mit, ich grundsätzlich Rang jeder Zeile die Daten zweimal, eine Spalte für die Ausschreibung Größe und eine Spalte für Ask-size. Ich erstellte eine Funktion, die excel verwendet Perzentil-Funktion und Reihen, die basierend auf, in denen der angegebene bid-und ask-size landet. Wie jetzt, ich habe das Makro ausgeführt für die letzten 35min und hat noch ausführen. Ich kann nicht versuchen, die anderen Makros, da jedes makro von dem vorherigen abhängt.

Also meine grundlegende Frage ist, da mein Datensatz ist groß, mein Modell abstürzt.Der code scheint in Ordnung zu sein bei der Arbeit mit den test-Daten, und Sie werden nicht werfen Fehler, wenn ich das Programm starte, aber mit dem größeren Datensatz es einfach abstürzt. Hat jemand irgendwelche Vorschläge? Ist das normal mit so großen Datenmengen?

Vielen Dank im Voraus.
Sham

Hier ist der sub-und function-das ist mir die Mühe, die sub nimmt die erforderlichen Eingaben, um die Funktion auszuführen und dann erscheint in der zugewiesenen Zelle. Der code ist wohl wiederholen Sie den Vorgang für drei separate Blätter. Für jetzt, würde ich mag es zu arbeiten auf einem Blatt, somit benutzt die Kommentare, um nicht die Schleife

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

Welchen code verwenden Sie, um den Zugriff auf die Zellen?
Haben Sie ging durch den code, um zu versuchen und sehen, was genau möglicherweise nicht wie vorgesehen funktioniert? Vielleicht eine Endlosschleife oder sowas???
Nick-Wenn ich Schritt durch den code funktioniert es Prima.
Lance-Im vba verwenden, sollte poste ich den aktuellen code?
Ich denke, deine Frage ist viel zu breit/mehrdeutig zu bekommen, viel in der Art von konkreten Antworten. Sie sind auf jeden Fall über eine Menge von Daten für excel, ja, aber kannst du eingrenzen, was ist problematisch? Was meinst du mit "crash"? Excel sterben, oder bekommst du einen VBA-Fehler? Sagen Sie, dass der code funktioniert perfekt, wenn Sie Schritt für Schritt durch, aber nicht, wenn Sie nur lassen Sie es laufen? Etc, etc...

InformationsquelleAutor Sham | 2011-04-20

Das problem ist, dass Ihr die Schleife durch jede Zeile einzeln, die Excel Weg ist, zu versuchen und zu arbeiten, mit der sich ganze Bereiche auf einmal, Wann immer möglich. Ich würde laden Sie die Bereiche in arrays, dann ändern Sie Ihre DECILE_RANK code zum arbeiten mit der Elemente im array.

Beachten Sie, dass die Variante arrays, Lesen reicht in 2-D.

Hier ist die voll funktionstüchtige code einschließlich meiner benutzerdefinierten VBA-array-slicer. Beachten Sie, dass es getestet wurde auf einem kleinen Datensatz:

Sub Bucketting()

Dim lastRow As Long
Dim bidArray As Variant
Dim offerArray As Variant

Sheets("Sheet1").Select 'Sheet selected

With ActiveSheet

  lastRow = .UsedRange.Rows.Count + 1

  bidArray = .Range("F2:F" & lastRow)
  offerArray = .Range("G2:G" & lastRow)

  Range("J2:J" & lastRow).Value = GetArraySlice2D(DECILE_RANK(bidArray), "column", 1, 1, 0)
  Range("K2:K" & lastRow).Value = GetArraySlice2D(DECILE_RANK(offerArray), "column", 1, 1, 0)

End With

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

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

End Sub

Function DECILE_RANK(DataRange As Variant) As Variant

' 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)

Dim DEC(0 To 10) As Variant
Dim i As Integer, j As Integer

'Using the percentile worksheet function calculate where the 10th, 20th etc percentile of the reference range are
DEC(0) = 0
For i = 1 To 9
  DEC(i) = Application.WorksheetFunction.Percentile(DataRange, 0.1 * i)
Next i
DEC(10) = Application.WorksheetFunction.Max(DataRange)

' Calculate the Decile rank that the reference cell value sits within
For i = 1 To UBound(DataRange, 1)
  For j = 1 To 10
    If ((DataRange(i, 1) > DEC(j - 1)) And (DataRange(i, 1) <= DEC(j))) Then
      DataRange(i, 1) = j
      Exit For
    End If
  Next j
Next i

DECILE_RANK = DataRange

End Function

Public Function GetArraySlice2D(Sarray As Variant, Stype As String, Sindex As Integer, Sstart As Integer, Sfinish As Integer) As Variant

' this function returns a slice of an array, Stype is either row or column
' Sstart is beginning of slice, Sfinish is end of slice (Sfinish = 0 means entire
' row or column is taken), Sindex is the row or column to be sliced (NOTE:
' 1 is always the first row or first column)
' an Sindex value of 0 means that the array is one dimensional 3/20/09 Lance Roberts

Dim vtemp() As Variant
Dim i As Integer

On Err GoTo ErrHandler

Select Case Sindex
    Case 0
        If Sfinish - Sstart = UBound(Sarray) - LBound(Sarray) Then
            vtemp = Sarray
        Else
            ReDim vtemp(1 To Sfinish - Sstart + 1)
            For i = 1 To Sfinish - Sstart + 1
                vtemp(i) = Sarray(i + Sstart - 1)
            Next i
        End If
    Case Else
        Select Case Stype
            Case "row"
                If Sfinish = 0 Or (Sstart = LBound(Sarray, 2) And Sfinish = UBound(Sarray, 2)) Then
                    vtemp = Application.WorksheetFunction.Index(Sarray, Sindex, 0)
                Else
                    ReDim vtemp(1 To Sfinish - Sstart + 1)
                    For i = 1 To Sfinish - Sstart + 1
                        vtemp(i) = Sarray(Sindex, i + Sstart - 1)
                    Next i
                End If
            Case "column"
                If Sfinish = 0 Or (Sstart = LBound(Sarray, 1) And Sfinish = UBound(Sarray, 1)) Then
                    vtemp = Application.WorksheetFunction.Index(Sarray, 0, Sindex)
                Else
                    ReDim vtemp(1 To Sfinish - Sstart + 1)
                    For i = 1 To Sfinish - Sstart + 1
                        vtemp(i) = Sarray(i + Sstart - 1, Sindex)
                    Next i
                End If
        End Select
End Select
GetArraySlice2D = vtemp
Exit Function

ErrHandler:
    Dim M As Integer
    M = MsgBox("Bad Array Input", vbOKOnly, "GetArraySlice2D")

End Function

Ill versuchen Sie dieses heraus.
Ich bearbeitete ihn ein paar mal, um sicherzustellen, dass Sie die parameter direkt für die array-slicing-routine. Ich werde prüfen, auf die Dinge morgen.
Im an dieser für eine Weile, und meine Unerfahrenheit ist wirklich zeigt. Ich kann nicht scheinen, um die Decile_Rank-Funktion fangen Sie die Referenz-Zelle, es taucht immer wieder leer. Und wenn ich Schritt für Schritt durch das Programm und auf deine array-slicing-routine, es besagt, dass es nicht in der Lage ist, um die Index-Eigenschaft des worksheetfunction-Klasse. Dies ist die einzige Partei, die meine gesamte Modell das ist, nicht funktioniert, habe ich es geschafft um zu überprüfen, was sonst, und Sie funktionieren sogar mit diesem riesigen Datensatz. Ich wirklich zu schätzen, all das, glauben Sie mir!!!
Ich checkte ein und sah dein Kommentar. Ich brauche mehr Klarheit über die Decile_Rank Funktion nicht 'fangen' der Referenz-Zelle. Ich bin mir nicht sicher, warum es Probleme mit der Index-Eigenschaft (obwohl Sie eine haben kann, es kommen direkt aus dem Application-Objekt glaube ich).
Nochmals vielen Dank für Ihre Zeit. Im Grunde das Problem ist, dass jede Zeile hat eine Größe bieten, was ich brauche, ist zu Eimer (Rang) diese bieten in Größen von 1-10, und der beste Weg, fand ich war mit der Quantil-Funktion und dann läuft ein ifstatement wie Sie sehen können. Aber wenn ich den code schrittweise Durchlaufen, und wenn es Zeit kommt, um die Rangfolge der bestimmten Zelle, die RefCell Staaten leer (wenn du mit der Maus drauf). Wie für die Index-Eigenschaft, es will einfach nicht für mich arbeiten, ich bekomme es nicht.

InformationsquelleAutor Lance Roberts

2

935,000 Linien ist viel für excel. Wie, wirklich viel. Abgesehen nämlich der Verwendung einer echten Datenbank, Wenn Ihre Anwendung ist buchstäblich setzen a =Quantil(...) in jeder Zelle, ich würde empfehlen, Versucht ein anderes tool. Vielleicht etwas in VBA selbst. Mehr in der Regel, verwenden Sie etwas, das außerhalb der Zelle - dann speichern Sie das Ergebnis mit dem Wert in der Zelle. Es ist eine Menge Aufwand in die Aufrechterhaltung der Formeln, die in Abhängigkeit 935k Zeilen von Daten.

Jody-Das Quantil ist nicht auf den 935k Zeilen, seinen Betrieb auf 290K Zeilen. Ist, dass immer noch viele Online?
Ich würde so sagen. Excel 2003 beschränkt Sie zu 65.000 Zeilen. Das ist eine ziemlich gute Zahl, um zu wechseln, um etwas mehr heavy-duty. Unter access könnte Ihnen ermöglichen, zu übertragen, was Sie jetzt tun, in excel-leicht mehr als der übergang zu einer vollwertigen Programmiersprache/DB.
Jody-Im mit excel 2010. Ich bin nicht vertraut mit access, Ill haben, um es zu installieren, aber Zugriff auf den ganzen code, der excel läuft?
Erwähnte ich 2003 als Bezugspunkt. Sie können mit VBA mit Access, aber der code modifiziert werden muss. Der Zugang muss nicht "Zellen", und "Arbeitsblätter" es hat Tabellen und Spalten und Datensätzen (oh my). Diese senden Sie eine lange Reise des Lernens, aber du bist definitiv der Umgang mit Menschen große Menge von Daten gibt.
Sie könnten in der Lage zu schreiben, ein Quantil-Funktion, die eine viel mehr effiziente. Sie brauchen nicht 290K Daten Punkte. Sie bauen könnten, es nur gut mit 1000 zufällig ausgewählten Punkten oder auch 100, es sei denn, Sie sind wirklich besorgt über Punkte Weg in die Schwänze der Verteilung.

InformationsquelleAutor Jody
0

Ich bin mir nicht sicher, ob dies direkt zu Adresse Ihre problem, aber haben Sie sich überlegt mit Application.ScreenUpdating = False? Vergessen Sie nicht, um es true zurück, sobald Ihre Daten verarbeitet hat.

InformationsquelleAutor Phil.Wheeler

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.