Office: (Office 2007) Per VBA in Zellen schreiben = sehr langsam?

Hallo, ich habe einen kurzen VBA code geschrieben, der die Werte etlicher Zellen neu setzt. Dies geht allerdings relativ langsam. An den Berechnungen liegt es nicht, denn selbst wenn ich eine Matrix mit lauter 1en fülle, ist es nicht schneller.
Ich dachte, dass es daran liegt, dass Excel nach jeder Änderung wieder etliche Berechnungen durchführen muss (viele andere Berechnungen basieren auf den Werten in der Matrix). Ich habe die Berechnungen daher mittels "Application.Calculation = xlCalculationManual" ausgeschaltet. Dies hat auch tatsächlich eine Verbesserung bewirkt, allerdings dauert es bei ~1000 zellen immer noch ~2 sekunden.

Wirklich schlimm ist das nicht, aber ich wollte mal nachfragen, ob ich irgendetwas falsch mache bzw. ob ich das ständige Aktualisieren der GUI auch ausschalten kann --> das Skript soll durchlaufen und das Arbeitsblatt soll sich erst danach aktualisieren.

mfg frank

:)

 

Mon, Frank,

die Matrix direkt in den Zellbereich schreiben ist der absolut schnellste Weg , Application.ScreenUpdating für das Aus- und Einschalten der Aktualisierung nutzen...

 

_oT = "ohne Text"

 

danke, das funktioniert sehr gut! (etwas schneller würde ich es mir immer noch erwarten, aber prinzipiell passt es jetzt.)

bei der gelegenheit hätte ich ein paar fragen zur performance (allerdings nur aus interesse, ist nicht wirklich wichtig):
kann ich bei simplen mathematischen operationen, bei schleifen oder auch bei stringvergleichen/operationen eigentlich eine ähnliche geschwindigkeit wie etwa bei java erwarten oder ist VBA (deutlich) langsamer?
ist ein zellzugriff (schreiben/lesen) sehr teuer oder ist ein solcher - wenn berechnungen und screen updates ausgeschaltet sind - fast wie ein zugriff auf ein element eines array?
kann excel formeln wie "=WENN(SUMME(A1:Z1)>0;SUMME(A1:Z1);"")" optimieren oder muss die summe zwei mal berechnet werden?

mfg frank

 

hier ein einfaches beispiel:

Code:

 

Hallo Frank,

das dachte ich mir : Du verwendest keine Variablenmatrix zum Verändern der Werte sondern nur die Bereichsmatrix A1:CV100 (=100 Zeilen, 100 Spalten).

Schneller (und das meinte jinx wohl) geht es mit einer Variablenmatrix, in die temporär der Inhalt der Bereichsmatrix kopiert wird. Innerhalb der Variablenmatrix funktioniert die Wertänderung wesentlich schneller. Am Ende wird die Variablenmatrix wieder in den Bereich zurückkopiert.
Ich habe hier mal beide Versionen mit Zeitangabe erstellt :
Code:

Auf meinem PC waren das (gerundet) 3 Sekunden für "Test" und 1 Sekunden für "Test2".
Alelrdings gibt es bei "Test2" einen grossen Wermutstropfen : Da der Bereich in die Variablenmatrix kopiert wird, benötigt diese evtl. seeehr viel speicher und es kann schnell zu einem "Überlauf" oder "Out of memory"-Fehler kommen !
Teste doch mal aus, ob das Deinen Vorstellungen entspricht und gibt hier kurz Rückmeldung...*Smilie

 

Moin, Frank,

1 Sekunde ohne Bildschirmaktualisierung ausschalten auf meinem System mit Excle97 halte ich für nicht so ganz schlecht *wink.gif*

0,94 Sekunden werden es mit folgender Änderung:

Code:

VBA muss auch noch dem Betriebssystem und dem Host (Excel) einige Zugeständnisse machen, daher dürfte die Geschwindigkeit (gerade von doppelten Schleifen, die mit zunehmender Größe die Geschwindigkeit gegen Null tendieren lassen) immer niedriger sein.

Teuer in der Sicht von langwierig: ja, wenn man eine Schleife verwendet:
Code:

Ich habe leider keinen Einblick in das, was Excel hinter den "Kulissen" berechnet - eingebaute Funktionen werden im Vergleich zu deren VBA-Pendants um ein Vielfaches schneller berechnet...

 

also bei deiner zweiten variante gehts auch bei mir in 1 sekunde. danke für den tipp! gibt es eine einfache (in excel eingebaute) möglichkeit, x/y werte in eine range wie A1:C3 umzuwandeln? ich bräuchte das, wenn die matrixgröße erst dynamisch ermittelt wird.
EDIT: habs gerade in jinx' posting gesehen. @jinx: danke, ich werde das demnächst mal probieren!

möchte aber noch anmerken, dass dein anderes ergebnis (3 sekunden) mich ja gar nicht dazu bewogen hätte, hier zu posten: ich brauche in meinem excelsheet 43 sekunden! in einer völlig neuen arbeitsmappe dauert es aber nur noch 13s. wenn ich dann noch das screenupdate ausschalte, gehts runter auf 8 sekunden (und berechnungen ausschalten bringt nochmal 1s, also 7).
hast du einen sehr schnellen rechner?

mfg frank

 

Moin, Frank,

versuch mal folgenden Code (benötigt 0,2 Sekunden bei mir, diesmal Excel2007 SP-1):

Code:

 

wow! ...

0,13 sekunden
0,18 sekunden mit screenupdate

ich denke, ich werde meinen code etwas umschreiben *wink.gif*

 

Hi,
ein bis zwei hundertstel sind noch drin ;-):
Code:

 

Moin, Styrkar,

stimmt - ich glaube, ich werde mir EVALUATE mal genauer ansehen... *wink.gif*

 

Hi zusammen,

macht immer wieder spass hier *Smilie
Hab auch mal eure Codes ausprobiert, aber der von jinx war auf meinem Rechner der Schnellste.

Aber hierzu habe ich noch eine kleine Randfrage zu folgender Zeile "varArr(arr1, arr2) = varArr(arr1, arr2) & appendedText" in dieser Routine:

Sub bspArray()
Dim varArr As Variant
Dim arr1, arr2
Dim dteStart As Date, dteEnde As Date
Dim appendedText As String

appendedText = "123"

Application.ScreenUpdating = False
dteStart = Timer
varArr = Range(Cells(1, 1), Cells(100, 100))

For arr1 = 1 To 100
For arr2 = 1 To 100
varArr(arr1, arr2) = varArr(arr1, arr2) & appendedText
Next arr2
Next arr1

Range(Cells(1, 1), Cells(100, 100)) = varArr

dteEnde = Timer
Application.ScreenUpdating = True

Debug.Print "Bearbeitungszeit: " & Format _
(dteEnde - dteStart, "0.00") & " Sekunden..."

End Sub


Habe die Zeile mal in "varArr(arr1, arr2) = appendedText" anstatt "varArr(arr1, arr2) = varArr(arr1, arr2) & appendedText" umgewandelt und es lief nochmal einen Tick schneller, mit dem gleichen Ergebnis.

Sprich 0.03 statt 0.06 Sekunden.

Hat die Schreibweise "varArr(arr1, arr2) = varArr(arr1, arr2) & appendedText"
einen besonderen Hintergrund?

Gruß,
Eagle

 

Hallo Eagle,

ich springe mal für jinx ein *wink.gif* :
Mit varArr(arr1, arr2) = varArr(arr1, arr2) & appendedText hängst Du jeweils den Inhalt von appendedText (im Beispiel also "123") an den bestehenden Zellinhalt an. Steht in einer Zelle VOR der Makroausführung z.B. "abc", dann steht NACH der Makroausführung "abc123" in der Zelle.
Mit Deiner Variante schreibst Du einfach "123" in alle Zellen des ARRAYS - unabhängig davon, was zuvor in diesen Zellen stand ! Wenn Du das möchtes, benötigst Du weder eine ARRAY-Variable noch eine Schleife, das kannst Du auch mit einem VBA-Einzeiler lösen :
Code: