Ich bin neu in VBA. Ich habe Arbeit in meiner Hand, um die Leistung von VBA-Code zu verbessern. Um die Leistung des Codes zu verbessern, muss ich die gesamte Zeile lesen und sie mit einer anderen Zeile vergleichen. Gibt es eine Möglichkeit, dies in VBA zu tun?
Pseudocode:
%Vor%Was ist los?
a ist nur eine Abkürzung für Application , um den unten stehenden Code leichter lesbar zu machen
ActiveSheet.Rows(1).Value gibt ein 2D-Array mit Dimensionen (1 bis 1, 1 bis {Anzahl der Spalten in einem Arbeitsblatt}) Join() zu einem einzigen Wert zusammenfassen, damit wir es mit einem anderen Array aus der zweiten Zeile vergleichen können. Join () funktioniert jedoch nur bei 1-D-Arrays. Daher führen wir das Array zweimal durch Application.Transpose() aus. Hinweis: Wenn Sie Spalten anstelle von Zeilen vergleichen, benötigen Sie nur einen Durchgang durch Transpose (). Join() auf das Array angewendet wird, erhalten wir eine einzelne Zeichenfolge, bei der die ursprünglichen Zellenwerte durch ein "Nullzeichen" ( Chr(0) ) getrennt sind: Wir wählen dies aus, da es unwahrscheinlich ist, dass es in einem der Zellenwerte selbst vorhanden ist . Hinweis: Wie von Reafidy in den Kommentaren erwähnt, kann Transpose() nicht mit Arrays mit mehr als ca. 65.000 Elemente, so dass Sie diesen Ansatz nicht verwenden können, um zwei ganze Spalten in Versionen von Excel zu vergleichen, in denen Blätter mehr als diese Anzahl von Zeilen haben (d. H. Jede nicht alte Version).
Hinweis 2: Diese Methode hat eine ziemlich schlechte Leistung im Vergleich zu einer Schleife, die für ein variantes Array von Daten verwendet wird, die aus dem Arbeitsblatt gelesen werden. Wenn Sie einen zeilenweisen Vergleich durchführen eine große Anzahl von Zeilen, dann wird der obige Ansatz viel langsamer sein.
Für Ihr konkretes Beispiel gibt es zwei Möglichkeiten ...
Groß- / Kleinschreibung beachten:
%Vor%Groß- und Kleinschreibung beachten:
%Vor%...
Im Folgenden finden Sie verallgemeinerte Funktionen zum Vergleichen zweier benachbarter Bereiche.
Groß- / Kleinschreibung beachten:
%Vor%Groß- und Kleinschreibung beachten:
%Vor% OK, das sollte ziemlich schnell sein: minimale Interaktion zwischen Excel UI und VBA (wo viel von der Langsamkeit lebt). Angenommen, Arbeitsblätter haben ähnliche Layouts von $A und wir werden nur versuchen, den gemeinsamen Bereich von UsedRange s für die beiden Blätter anzupassen:
Hier ist ein bisschen Code, der zwei Vektorbereiche ausführt. Sie können es gegen zwei Zeilen, zwei Spalten ausführen.
Denken Sie nicht, dass es so schnell ist wie die x2-Transpose-Methode, aber es ist flexibler. Der Spaltenaufruf dauert etwas länger, da 1M Elemente zum Vergleichen vorhanden sind!
%Vor%Ich werde hier eine Antwort von Vorschlaghammer auf Ratschlag geben, um die Vollständigkeit zu prüfen, denn die Frage "Sind diese beiden Bereiche identisch?" stellt sich als eine ungeprüfte Komponente aller anderen heraus, vergleiche meine Reihen und mache dann diese komplizierte Sache ... Fragen.
Ihre Frage ist eine einfache Frage über kleine Bereiche. Meine Antwort ist für große; aber die Frage ist eine gute und ein guter Platz für eine allgemeinere Antwort, weil sie einfach und klar ist: und 'Unterscheiden sich diese Bereiche?' und 'Hat jemand meine Daten manipuliert? ? ' sind für die meisten kommerziellen Excel-Benutzer relevant.
Die meisten Antworten auf die typischen Fragen zum Vergleichen meiner Zeilen sind zellenweise Lesevorgänge und Vergleiche in VBA. Die Einfachheit dieser Antworten ist lobenswert, aber dieser Ansatz funktioniert bei großen Datenmengen sehr langsam, weil:
var = Range("A1") aufnimmt, um den gesamten Bereich auf einmal mit var = Range("A1:Z1024") zu erfassen ...
... Und jede Interaktion mit dem Blatt dauert viermal so lange wie ein String-Vergleich in VBA und zwanzigmal länger als ein Vergleich zwischen Gleitpunkt-Dezimalzahlen; und das wiederum ist dreimal länger als ein ganzzahliger Vergleich.
Ihr Code wird also wahrscheinlich viermal schneller und möglicherweise hundertmal schneller sein, wenn Sie den gesamten Bereich auf einmal lesen und am Array Range.Value2 in VBA arbeiten.
Das ist in Office 2010 und 2013 (ich habe sie getestet); Bei einer älteren Excel-Version sehen Sie die angegebenen Zeiten zwischen 1/50 th und 1/500 th einer Sekunde für jede VBA-Interaktion mit einer Zelle oder einem Bereich von Zellen. Das wird viel langsamer sein, da die VBA-Aktionen sowohl in alten als auch in neuen Versionen von Excel immer noch im einstelligen Bereich von Mikrosekunden liegen: Ihr Code wird mindestens hundert Mal schneller ausgeführt wahrscheinlich tausende Male schneller, wenn Sie zellenweise Lesevorgänge aus dem Blatt in älteren Versionen von Excel vermeiden.
%Vor% Sie werden feststellen, dass dieses Codebeispiel generisch ist, und zwar für zwei Bereiche der gleichen Größe, die von überall her aufgenommen werden - sogar von separaten Arbeitsmappen. Wenn Sie zwei benachbarte Spalten vergleichen, wird das Laden eines einzelnen Arrays aus zwei Spalten und das Vergleichen von IF arrX(i, 1) <> arrX(i,2) Then die Laufzeit halbieren.
Ihre nächste Herausforderung ist nur relevant, wenn Sie Zehntausende von Werten aus großen Bereichen erfassen. Diese kleinere Antwort bietet keinen Leistungsgewinn für etwas, das kleiner ist.
Was wir machen ist:
Die Idee ist sehr einfach, obwohl die zugrundeliegende Mathematik für Nicht-Mathematiker ziemlich herausfordernd ist: Anstatt einen Wert nach dem anderen zu vergleichen, führen wir eine mathematische Funktion aus, die die Werte für einen einfachen Vergleich in eine kurze Kennung "hasht" / p>
Wenn Sie Bereiche wiederholt mit einer Referenzkopie vergleichen, können Sie den Referenzhash speichern, was die Arbeitslast halbiert.
Es gibt einige schnelle und zuverlässige Hashfunktionen, die in Windows als Teil der Sicherheits- und Kryptografie-API zur Verfügung stehen. Es gibt ein kleines Problem darin, dass sie auf Strings laufen, und wir haben ein Array, an dem wir arbeiten können; Sie können jedoch schnell eine 'Join2D'-Funktion finden, die eine Zeichenfolge aus den 2D-Arrays abruft, die von der .Value2 -Eigenschaft eines Bereichs zurückgegeben werden.
Eine schnelle Vergleichsfunktion für zwei große Bereiche sieht dann so aus:
%pr_e%
Ich habe den Windows System.Security MD5-Hash in dieser VBA-Funktion verpackt: %Code% Es gibt noch andere VBA-Implementierungen, aber niemand scheint etwas über das Byte Array / String-Wortspiel zu wissen - sie sind nicht äquivalent , sie sind identisch - also ist jeder Code unnötig Typ Konvertierungen.
Eine schnelle und einfache Join2D-Funktion war von Dick Kusleika bei Daily Dose of Excel gepostet im Jahr 2015:
%Vor%Wenn Sie vor dem Vergleich leere Zeilen entfernen möchten, benötigen Sie die Join2D-Funktion, die ich 2012 in StackOverflow gepostet habe .
Die gebräuchlichste Anwendung dieser Art von Hash-Vergleich ist die Kontrolle der Tabellenkalkulation - Änderungsüberwachung - und Sie sehen %pr_e% anstelle von Range1.Formula : Ihre Frage bezieht sich jedoch auf den Vergleich von Werten keine Formeln.
Fußnote: Ich habe eine sehr ähnliche Antwort an anderer Stelle gepostet. Ich hätte es hier zuerst gepostet, wenn ich diese Frage früher gesehen hätte.
Excel 2016 hat eine eingebaute Funktion namens TEXTJOIN
Betrachte @Tim Williams und benutze diese neue Funktion (die nicht das Zeilenlimit 65536 hat):
%Vor%Als Funktion geschrieben:
%Vor%