Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

VBA Schleifen beschleunigen: Arrays?

Betrifft: VBA Schleifen beschleunigen: Arrays? von: Robert
Geschrieben am: 11.08.2014 15:08:21

Hallo zusammen,

nun ist es auch bei mir soweit, die Schleiferei dauert einfach zu lange.
Mit Arrays habe ich bisher kaum Kontakt gehabt, ich habe mir nur die Grundlagen auf diversen ExcelSeiten angelesen. Leider lange nicht genug, um das ganze in einem Praxisteil funktionierend umzusetzen.
Ich hoffe aber, dass ich Codevorschläge von euch nachvollziehen und daraus lernen kann.

Folgender Code läuft über 1500 Zeilen "Actual" und ca 17000 Zeilen "Forecast"
Damit benötigt er leider momentan ein paar Minuten Rechenzeit.

Option Explicit
Dim x, y, z As Long
Dim MonatName As String

Sub Testr()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = 2 To Tabelle1.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    For y = 17 To Workbooks("Actual.xlsx").Worksheets("Sales Download").UsedRange.SpecialCells( _
xlCellTypeLastCell).Row
        If Left(Tabelle1.Cells(x, 1), 12) = Left(Workbooks("Actual.xlsx").Worksheets("Sales  _
Download").Cells(y, 20), 12) Then
            MonatName = Workbooks("Actual.xlsx").Worksheets("Sales Download").Cells(y, 34)
            z = InStr("xxJanFebMarAprMayJunJulAugSepOctNovDec", Left(MonatName, 3)) / 3
            Tabelle1.Cells(x, 1 + z) = Workbooks("Actual.xlsx").Worksheets("Sales Download"). _
Cells(y, 28)
        End If
    Next y
    Debug.Print x & " of " & Tabelle1.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Next x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Für jede noch so kleine Optimierung bin ich Dankbar :)

Viele Grüße
Robert

  

Betrifft: AW: VBA Schleifen beschleunigen: Arrays? von: Daniel
Geschrieben am: 11.08.2014 15:18:31

Hi
kannst du mal beschreiben, was der Code machen soll?
Sich das allein aus dem Code zurück zu übersetzen ist etwas mühsam.
so wie es aussieht, ist es nicht unwahrscheinlich, dass sich das Problem auch durch eine geschickte Sortierung und einem SVerweis lösen lässt.

eine kleine Beispieldatei (gekürzt) würde auch nicht schaden, so dass man seine Überlegungen auch mal testen kann bevor man antwortet.

Gruß Daniel


  

Betrifft: AW: VBA Schleifen beschleunigen: Arrays? von: Robert
Geschrieben am: 11.08.2014 15:33:31

Hallo Daniel,

Im Grunde läuft es folgendermaßen:

Ich habe eine Liste mit Materialnummern, die in dem File in dem Der Code Liegt generiert wird (Tabelle1, Spalte1). Nun möchte ich von Spalte 2-13 für die 12 Monate des Jahres Preise aus einem anderen File ziehen (File "Actual", Blatt "Sales Download").
Um die Preise zuzuordnen müssen die ersten 12 Ziffern der Materialnummer stimmen.
Stimmt die Materialnummer, wird der Preis entsprechend dem Monatsnamen in die richtige Spalte im Output Sheet eingefügt.

Im Grunde ist es ein SVerweis mit Zusatzbedingung, nur will ich, da die Geschichte in eine größere Konsolidierung eingebunden werden soll, auf Formeln verzichten.

Ich hoffe das war irgendwie Verständlich.

Eigentlich möchte ich nur wissen, wie ich eine vergleichende Schleife durch Verwenden von Arrays beschleunigen kann.


  

Betrifft: AW: VBA Schleifen beschleunigen: Arrays? von: Daniel
Geschrieben am: 11.08.2014 15:46:47

Hi

dazu musst du die Werte der Zellen in ein Array kopieren, dann mit diesen Arrays arbeiten und das Ergebnis dann wieder zurück in die Zellen schreiben.
Das Ansprechen der einzelnen Werte im Array erfolgt dann immer 2-Dimensional über Zeilennummer und Spaltennummer ähnlich wie bei Zellen mit Cells(), allerdings beginnt im Array die Zählung immer mit 1, egal aus welchem Zellbereich du die Werte ins Array kopierst:

hier mal ein kleines Beispiel, hier werden die Werte aus Spalte A und B multipliziert und in C ausgegeben, das alles ab Zeile 10:

dim arrA
dim arrB
dim arrC
dim z

arrA = Range("A10:A20").Value
arrB = Range("B10:B20").Value
redim arrC(1 to Ubound(arrA, 1), 1 to 1)

for z = 1 to Ubound(ArrA, 1)
    arrC(z, 1) = arrA(z, 1) * arrB(z, 1)
Next

Range("C10").Resize(Ubound(arrC, 1), Ubound(arrC, 2)).value = arrC
gruß Daniel


  

Betrifft: Damit hast du ihm die klassische, aber ... von: Luc:-?
Geschrieben am: 11.08.2014 15:52:44

…uneleganteste und höchstwahr­scheinlich auch lang­samste Methode vorgestellt, Daniel! ;->
Luc :-?


  

Betrifft: AW: Damit hast du ihm die klassische, aber ... von: Daniel
Geschrieben am: 11.08.2014 15:59:04

Formeln waren nicht erwünscht, Umsortieren ebenso.
du kannst dich gerne mit einer bessern Lösung einbringen.

Gruß Daniel


  

Betrifft: Du solltest dich mal an die erste Anfrage ... von: Luc:-?
Geschrieben am: 11.08.2014 19:43:22

…erinnern, Daniel!
Warst du da nicht auch involviert? Von Fmln war da auch keine Rede und es ging ganz allein um die Schnelligkeit von PgmZyklen — über Ranges oder über Arrays.
Ansonsten steht alles, was noch zu sagen wäre, schon in meiner AW an ihn.
Luc :-?


  

Betrifft: AW: Du solltest dich mal an die erste Anfrage ... von: Daniel
Geschrieben am: 11.08.2014 20:07:23

in der ersten Anfrage nicht Luc!
dafür aber in seiner Antwort auf meinen ersten Beitrag.
Gruß Daniel


  

Betrifft: Aber darum ging's ihm ja damals wie auch ... von: Luc:-?
Geschrieben am: 11.08.2014 20:54:06

…jetzt (eigentlich), Daniel!
Nebenbei, „Krümel k…n“ kann ich notfalls auch, Quintessenzen scheinen hier doch aber wesentlicher zu sein, oder…?!
Luc :-?


  

Betrifft: AW: Aber darum ging's ihm ja damals wie auch ... von: Daniel
Geschrieben am: 11.08.2014 21:31:10

ich verstehe jetzt nicht so ganz, was du meinst.
In seinem Codebespiel verwendet er ja direkte Zellbezüge.
Ich habe ihm grundsätzlich gezeigt, wie man Zellinhalte in Arrays kopiert und dann mit diesen Arrays arbeiten kann, um, damit erstmal grundsätzich die Verarbeitunsgeschwindigkeit zu erhöhen.
ganz Quintesszens-mässig mit einem klaren, leichtversändlichen Beispiel.

Wenn der das auf seine Tabelle anwendet, wird deutlich schneller werden.
Das da dann vielleicht noch weitere Optimierungen möglich sind, zweifle ich ja nicht an.
Ich will nur nicht eine Antwort gleich mit zuvielen Inhalten übfrachten, damit der Fragesteller nicht die übersicht verliert und dann gar nichts mehr versteht.

Gruß Daniel


  

Betrifft: Er hatte dort aber zuletzt mich angesprochen, ... von: Luc:-?
Geschrieben am: 12.08.2014 03:30:15

…nur hat er dann nicht mehr reagiert, dafür quasi erneut danach gefragt!
Mir persönlich ist diese Art von Arrays zu starr, auch, wenn du mit (drohender) „Überfrachtung“ durchaus recht hast. Aber diese Art wird überall erwähnt, die andere eher selten bzw gar nicht.
Morrn, Luc :-?


  

Betrifft: AW: VBA Schleifen beschleunigen: Arrays? von: Robert
Geschrieben am: 12.08.2014 14:11:05

Hallo Daniel,

Vielen Dank für die Antwort...
Die Arrays werden also von der Größe automatisch festgelegt, wenn ich sie mit den Werten einer Range befülle?

Den Resize Befehl musst du mir aber bitte nochmal etwas näher bringen, das habe ich auch bei Luc`s Beispiel nicht verstanden.
Ich nehme an eine Range kann man nicht einfach an einer Stelle einfügen wie bei copy and paste, sondern muss einen Bereich auswählen, der exact dieselbe Größe hat wie das Array?

Viele Grüße
Robert


  

Betrifft: Ja, genau! Gruß owT von: Luc:-?
Geschrieben am: 14.08.2014 01:58:58

:-?


  

Betrifft: ...und warum neu, wenn du doch zu ... von: Luc:-?
Geschrieben am: 11.08.2014 15:46:50

…deinem „Eigentlich-Problem“ schon AWen erhalten hattest, Robert,
zB auch diese, auf die du noch nicht geantwortet hast? :-(
Luc :-?


  

Betrifft: AW: ...und warum neu, wenn du doch zu ... von: Robert
Geschrieben am: 12.08.2014 11:02:21

Hallo Luc:?,

Nichts für ungut, hatte nach einer Woche nicht mehr nachgeschaut ob eine Antwort gekommen ist, da du ja den selben Abend angekündigt hattest. Mein Fehler.
Vielen Dank für deine ausführliche Antwort, werde da nochmal nachlesen und entsprechend antworten.

Viele Grüße
Robert


  

Betrifft: AWen kannst du jetzt aber nur noch hier! Gruß owT von: Luc:-?
Geschrieben am: 12.08.2014 14:32:07

:-?


  

Betrifft: Dann hier: Verständnisfragen! von: Robert
Geschrieben am: 12.08.2014 14:40:15

Hallo Luc,

Ich konnte beim Dritten mal gleich doppelt antworten, allerdings ist das mit der Archivtechnik etwas umständlich! :D Also hier weiter:

Der Code ist mir leider um einiges zu komplex, und beim 2ten absatz deiner Erklärung habe ich leider nur Bahnhof verstanden (und es hat ein paar traumatische (:P) Erinnerungen an MathematikVorlesungen in der Uni geweckt).
Ich versuche mal zu rekapitulieren:

ZBereich: Bereich mit einer Liste für jede Zeile im Output. Zbereich(12) gibt mir also eine Liste mit allen Werten der Reihe 12. Der ZBereich wird befüllt mit allen nicht leeren Zeilen von Output.

Frage: Wenn ich das so richtig verstanden habe, warum ist dann der Zbereich "zu Groß" definiert. Zählt das Programm nicht nur bei vollen Zeilen eins hoch, d.h. wenn 50% der Zeilen leer sind, ist das Array nur bis zur Hälfte beschrieben und die zweite Hälfte ist leer?
UND: muss, damit die Erste Zeile korrekt geschrieben wird, nicht zu Beginn iz auf 0 (oder 1?) gesetzt werden?

QBereich(3) = Array mit 4 Feldern für jede Zeile der Mappingliste, Felder werden gefüllt mit den entsprechenden Werten der Spalten 20, 31, 33 und 49 aus dem Mappingfile

Soviel zu Bereichen...

Match:
Jetzt sucht er die Konstante (die ich in meinem Programm irgendwie durch pro Zeile variablen Zellbezug in "Output" ersetzen muss) in der Liste QBer(0), also in Output, Spalte 20
Findet er was, geht er die drei Optionen durch und schreibt den Wert in die Entsprechende Liste an die Stelle, die der Spalte in der Combobox entspricht.

Frage: Um jetzt den Suchwert für jede Zeile in Output zu verändern, packe ich diesen Codeabschnitt

On Error Resume Next: zR = .Match(txSuBeg, QBer(0), 0)
        On Error GoTo fx
        If CBool(zR) Then
            For iz = 1 To UBound(ZBer)
                For ix = 1 To 3
                    If CBox(ix) <> "" Then ZBer(iz)(CBox(ix)) = QBer(ix)(zR)
                Next ix
            Next iz
        End If

In eine Schleife mit x = 1 to UBound(ZBer)? Oder hab ich da nen knick in der Logik?

Weiter im Code:

Dann setzt er eine Range auf Zelle1 und löscht die Inhalte von Output.
Und bei der Letzten Zeile verließen sie ihn:
urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer

Du defnierst die Range neu, allerdings begreife ich Zber bzw Zber(0) nicht.
Hängt vermutlich mit einem Verständnisfehler weiter oben zusammen.

Bitte um weitere Hilfe! :)

viele Grüße und schonmal vielen vielen Dank
Robert


  

Betrifft: Code Angepasst: Debugging Hilfe! von: Robert
Geschrieben am: 12.08.2014 15:52:26

Hallo Luc,

Habe den Code jetzt korrekt angepasst (hoffe ich).
Problem 1: Typen unverträglich bei folgendem

If CBox(ix) <> "" Then

Logisch, Cbox(3) ist als Long definiert.... hab es durch <> 0 ersetzt.

So sieht meine Version aus:
'On Error GoTo fx
    ReDim ZBer(Output.UsedRange.Rows.Count - 1), QBer(3)
    With WorksheetFunction
        QBer(0) = .Transpose(Mapping.Columns(20))
        QBer(1) = .Transpose(Mapping.Columns(31))
        CBox(1) = Me.ColumnBox1 'Customer
        QBer(2) = .Transpose(Mapping.Columns(33))
        CBox(2) = Me.ColumnBox2 'Material
        QBer(3) = .Transpose(Mapping.Columns(49))
        CBox(3) = Me.ColumnBox3 'ProductFamily
        iz = 0
        For Each xR In Output.UsedRange.Rows
            If CBool(.CountA(xR)) Then
            ZBer(iz) = .Transpose(.Transpose(xR))
            iz = iz + 1
            End If
        Next xR
        For iz = 0 To UBound(ZBer)
            On Error Resume Next: zR = .Match(ZBer(iz)(ColumnBox6), QBer(0), 0)
'            On Error GoTo fx
            If CBool(zR) Then
                    For ix = 1 To 3
                        If Not CBox(ix) = 0 Then
                        ZBer(iz)(CBox(ix)) = QBer(ix)(zR)
                        End If
                    Next ix
            End If
        Next iz
    End With
    With Output.UsedRange
        Set urC1 = .Cells(1)
    End With
    urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer

fx: If CBool(Err.Number) Then MsgBox Err.Description, vbCritical, _
        "Interner Fehler " & Err.Number: Set xR = Nothing
ex: Set urC1 = Nothing
Problem: Das Einfügen des Arrays in die Zellen klappt nicht (Letzte Zeile)
 urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer

Wenn ich manuell (Direktfenster) den ZBer mit verschiedenen Argumenten Überprüfe stehen die korrekten Daten drin.
Eingefügt werden aber leere Zellen!
Z.B. Gibt mir ZBer(30)(1) den korrekten wert zurück, aber in der Tabelle am Schluss sind leere Zellen!

Viele Grüße
Robert


  

Betrifft: So, dann erst mal nur kurz, ... von: Luc:-?
Geschrieben am: 13.08.2014 04:12:50

…Robert,
evtl später bzw b.Bedarf mehr, denn ich hatte bereits eine längere AW in Arbeit als der Mist-Laptop meiner Frau wohl wg irgendwelcher dämlicher Software­Aktuali­sierungen runter gefahren ist. Und natürlich war alles weg (ich hasse solch blödsinnige Einstellungen, falls es nicht doch eine Tasten­Fehl­Inter­pretation der Grund war; das hat die Krücke nämlich auch drauf!)… :-(
1. If CBox(ix) <> 0 Then ist natürlich korrekt.
2. Wo kommt plötzlich ColumnBox6 her? Bei mir wdn mit CBox(ix) alle 3 CBoxes durchlaufen, nicht nur eine, die vorher gar nicht auftaucht. Dadurch wird immer der gleiche Wert im (Zeilen-)Element iz überschrieben.
3. Warum hast du On Error GoTo fx auskommentiert? Ab On Error Resume Next bekommst du dann überhpt keine FehlerMeldung mehr! Für Testzwecke entweder Ersteres durch On Error GoTo 0 ersetzen oder besser eine Zeile vor der SprungMarke ex: mit Stop: Resume Next einfügen (später wieder entfernen!). Oder willst du, dass ein VBA-unbeleckter Nutzer plötzlich im VBE-Debugger­Fenster landet?! ;-]
4. Ja, Ubound(ZBer) ist etwas ungeschickt, schadet hier aber nicht, da du ja ohnehin .ClearContents weggelassen hast. So kann man Inhalte überzähliger Zellen schließlich auch löschen (womit sich die berechtigte Frage nach dem „ZuGroß“ eigentl auch erledigt hätte, obwohl man ZBer natürlich auch mit iz - 1 redimensionieren könnte). ;-)
5. Variablen muss man bei Erstgebrauch nicht initialisieren. Das erledigt schon die Deklaration (und später ggf eine einfache For…Next-Schleife). Nur in einer Schleife separat mitlfdn bzw Einzel­Variablen muss man bei Zweit­Verwendung einen Anfangswert geben.
Morrn, Luc :-?


  

Betrifft: AW: So, dann erst mal nur kurz, ... von: Robert
Geschrieben am: 13.08.2014 08:45:59

Immer wieder diese Maschinen, die einem einen Strich durch die Rechnung machen :)

Die Errorbehandlung habe ich auskommentiert, damit ICH im Debug Fenster lande und evtl die Fehlerhafte Zeile angezeigt bekomme.
Wenn ich fertig bin kommt die natürlich wieder rein.

Columnbox6 ist lediglich die Spalte, in dem der Suchwert steht (für jede Zeile gleich), in numerischer Form z.B. 2
Ich möchte also nicht nach einer Konstanten in QBer(0) suchen, sondern für jede Zeile eben den Wert, der in Spalte 2 bzw. Columnbox6 steht.

Ich habe das Array jetzt redimensioniert mit iz-1, inkl. Preserve.
Das Einfügen der Zeilen klappt nach wie vor nicht!
Wenn ich eine Haltemarke auf die letzt Zeile setze, kann ich jedes Feld meines Arrays überprüfen, es stehen die richtigen Werte drin.
Wenn ich nun die letzte Zeile Ausführe werden alle Werte der Tabelle mit nichts überschrieben.
Es gibt mir keinen Fehler aus!

Ich habe keine Ahnung warum!

Viele Grüße
Robert


  

Betrifft: Warum kein Fehler kommen kann, hatte ich dir ... von: Luc:-?
Geschrieben am: 13.08.2014 11:35:13

…bereits mitgeteilt (lies nochmals 3. ganz genau!), Robert;
ansonsten ist es nicht ganz einfach, so mal eben den Fehler herauszufinden. Ich werde dazu wohl mal eine umgebungs­technisch vereinfachte Variante meines Original­Codes testen müssen. Das könnte dann bei der FehlerSuche helfen. Dauert aber noch etwas…
Gruß, Luc :-?


  

Betrifft: Fehlerfrei und trotzdem nix! von: Robert
Geschrieben am: 13.08.2014 11:58:30

Hallo Luc,

Auch mit Fehlerbehandlung läuft das Makro ohne Fehler durch!
Ich hab nach deinem Kommentar alles wieder einkommentiert, es läuft durch, fügt aber statt den richtigen Werten nur leere Felder ein!

Viele Grüße
Robert


  

Betrifft: Fehlerfrei? Also mein Original läuft im Test ... von: Luc:-?
Geschrieben am: 14.08.2014 01:57:22

…gleich in mehrere Fehler, Robert… ;-]
Hattest du die denn alle beseitigt oder die entsprd Passagen weggelassen (rhetorisch, habe das jetzt nicht kontrolliert)? Im Wesentlichen ist die Ursache das Array, das hier wohl doch nicht die gewünschte Form aus senkrechtem Haupt- und waagerechten ElementeVektoren angenommen hatte. Mit so etwas tut sich Xl dann schwer (mit Array(Array(…),…) klappt's aber idR), aber seine Fktt schaffen da glücklicherweise Abhilfe, weshalb ich jetzt ZBer noch 2× transponiert habe.
Ich lade dir hier mal ein fktionierendes TestBsp mit Mini-UF hoch. Dann fällt dir ggf die Anpassung leichter.
Falls du es weiterhin wünschst, kann ich dir später auch noch deine vorherigen Fragen beantworten.
Morrn, Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "VBA Schleifen beschleunigen: Arrays?"