Arrayberechnung laaangsam mit Excel 2016

[rodlof]

Guten Tag

Wir haben bei uns neuerdings WIN10 und Excel 2016 installiert. Letztes Jahr lief eine relativ grosse Berechnung eines Arrays, das ich von Peter Haserodt   entlehnt habe, irrsinnig schnell. Nun wollte ich die Datei wieder verwenden (wie jedes Jahr neu) und die Berechnung dauert eeewig . Eventuell kann mir jemand helfen.

Das Schnipsel von Peter war folgendes:
        ' Dim i As Long, vx(1 To 10000, 1 To 1) As Variant
        ' Randomize Timer
        ' For i = 1 To 10000
        '    vx(i, 1) = Int(500 * Rnd + 1)
        ' Next i
        ' With Worksheets("Tabelle1")
        '   .Range(.Cells(1, 1), .Cells(10000, 1)) = vx
        ' End With

Ich verwende es (im Prinzip) so:
   Dim vx(1 To 999000, 1 To 1) As Variant      'Array für Formelvorbereitung
   For i = ErsteZeile To AnzZeilen + ErsteZeile - 2
     FormelDRP = "=$BG$" & i2                'Formel zusammensetzen für Spalte BG und
     vx(1 + i - ErsteZeile, 1) = FormelDRP   'in Array eintragen
   Next i
   With ActiveSheet
      .Range(.Cells(ErsteZeile, intK), _
      .Cells(AnzZeilen + ErsteZeile - 2, intK)) = vx       'Gesammeltes Array in Kolonne eintragen
   End With

Ich war damals wahnsinnig begeistert :19: . Die Berechnung für 200'000 Zeilen dauerte ca 10 sec. Nun dauert es für 70'000 mind 15 min .

Vielen Dank schon mal für eure Hilfe.

Gruss aus der verschneiten Schweiz.
Rodlof

[Elex]

Hi Rodlof,
 
Für mich reichen deine Vorgaben (Codeschnipsel) nicht um zu helfen.
Beschreibe am besten welche Ausgangssituation vorliegt und was am Ende dein Wunschergebnis ist.

 
Gruß Elex

[Klaus-Dieter]

Hallo,

hat sicher nichts mit deinem Problem zu tun, aber das:

  FormelDRP = "=$BG$" & i2                
     vx(1 + i - ErsteZeile, 1) = FormelDRP   

wäre ja irgendwie das gleiche wie das:

  vx(1 + i - ErsteZeile, 1) = "=$BG$" & i2               

[snb]

Woher stammt AnzZeilen

[Redoute]

Rodlof,

deine Version des Makros trägt doch nur Formeln bzw. Bezüge auf eine andere Spalte in das Arbeitsblatt ein, oder? Wenn relative Bezüge OK sind, reicht dafür ein Einzeiler:

Code:

Sub FormelEintragen(ErsteZeile As Long, AnzZeilen As Long, intK As Long)
    ActiveSheet.Cells(ErsteZeile, intK).Resize(AnzZeilen, 1).Formula = "=$BG" & ErsteZeile
End Sub

Falls du tatsächlich den Eintrag für jede Zeile individuell berechnen willst, zum Beispiel weil du absolute Bezüge brauchst, könnte das Gerüst so aussehen:

Code:

Sub ArrayRechnenUndEintragen(ErsteZeile As Long, AnzZeilen As Long, intK As Long)
    Dim vx() As Variant
    ReDim vx(1 To AnzZeilen)

    For i = 1 To AnzZeilen
        vx(i) = "=$BG$" & i + ErsteZeile - 1 ' ### Formel mit absolutem Bezug
        ' vx(i) = Range("$BG" & i + ErsteZeile - 1).Value ' ### alternativ: holt Werte aus dem Arbeitsblatt
    Next i
    ActiveSheet.Cells(ErsteZeile, intK).Resize(AnzZeilen, 1) = vx
End Sub

Redoute

[rodlof]

Hallo Redoute

Vielen Dank für deinen Code. Ich brauche tatsächlich absolute Bezüge. Der zweite mit den absoluten Bezügen sieht daher gut aus. Ich werde das mal probieren und gebe nochmals Bescheid.

Um das Umfeld noch besser zu erklären: Ich habe viele verschiedene Artikel mit einer ID und mit jeweils verschiedenen Eigenschaften, die in einer Spalte pro Zeile einzeln aufgeführt werden (unpivotiert). Wenn die ID ändert, setze ich in einer Hilfsspalte eine 1, sonst 0. Damit kann ich die "erste" Spalte definieren und die Anzahl Artikel zählen. Die Anzahl Zeilen (also alle Eigenschaften aller Artikel) habe ich vorher gezählt und abgelegt (= AnzZeilen). Auf jeder ersten Zeile eines Artikels soll der User nun aus einem Aufklappmenu eine Wahl treffen und diese wird dann auf alle folgenden Zeilen dieses Artikels übertragen. Daher brauche ich eine absolute Referenzierung auf die jeweils "erste" Zeile (i2). Danach zähle ich i weiter bis zur nächsten "ersten" Zeile und kopiere das Aufklappmenu (leer) rein und setze i2 = i. Dann geht das Spiel von vorne los.

Viele Grüsse
Rodlof

[Redoute]

Ach so, alle Formeln sollen auf die gleiche Zelle $BG$i2 verweisen? Dann braucht der Einzeiler ein Dollarzeichen mehr:

Code:

Sub FormelEintragen(ErsteZeile As Long, AnzZeilen As Long, intK As Long, i2 As Long)
    ActiveSheet.Cells(ErsteZeile, intK).Resize(AnzZeilen, 1).Formula = "=$BG$" & i2
End Sub

Nach deiner Beschreibung müssten i2 und ErsteZeile identisch sein?

Redoute

[rodlof]

Hallo Redoute

Das wird ja immer besser. Vielen Dank. Werde ich auch probieren. Im Moment kämpfe ich noch mit anderen Problemen...

Die ErsteZeile ist die allererste Zeile aller Einträge, sie beginnt nicht bei 1, da ich einen Header habe. Zwar ist ErsteZeile = i2 vom ersten Artikel, danach stimmt es nicht mehr. LetzteZeile ist die allerletzte Zeile der Liste und daher: ErsteZeile + AnzZeilen = LetzteZeile. LetzteZeile variiert zwischen 20'000 und 500'000. 

Lieber Gruss
Rodlof

[snb]

Warum keine 'Intelligente' Tabelle ?

[Elex]

Hi

Das Problem Geschwindigkeit beim eintragen von Formel per Code lässt sich so meistens verbessern.

Code:

Sub Makro()
    Application.Calculation = xlManual
    
    Dein Code
    
    Application.Calculation = xlAutomatic
End Sub

Gruß Elex