Office: Makro ist brutal langsam

Hallo VBAler,
ich habe ein Makro, das jedes Sheet einer Datei anwählt, in einer festgelegten Range "#Ref!" durch "Tabelle1" ersetzt, zum nächsten Sheet springt, dort "#Ref!" ersetzt etc, bis alle Sheets durch sind und ich glücklich bin (naja, fast):
Code:

Ich habe jetzt zweierlei Problem. Einmal ist das Makro brutal langsam, legt sicherlich für 4 Minuten meinen Rechner lahm, da es 40 Sheets durchsuchen muss. Hat da jemand eine Idee, wie man das performanter gestalten könnte?
Der zweite Knackpunkt ist, dass das Makro nur bis zum vorletzten Sheet funktioniert, im letzten Sheet läuft es vor den Pöller: Anwendungs- oder objektdefinierter Fehler
Das passiert immer im letzten Sheet. Schuld ist diese Zeile:
Code:

Hat jemand eine Idee, damit es funktioniert und ein wenig flotter wird?
Schonmal Danke im Vorraus!

Office User

:)

 

Hallo,

zu Deinem ersten Problem (Geschwindigkeit):

Schalte zu Beginn des Makros die Berechnungsmethode auf "manuell" und am Ende wieder auf "automatisch". Das sollte einen erkennbaren Geschwindigkeitszuwachs bringen.

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

Grüße
Klaus

 

Moin, OfficeUser,

den Bereich der zu durchlaufenden Zellen einschränken (hier auf die fehlerhaften Formelzellen in den einzelnen Tabellen) - ungetestet:

Code:

 

Das Performance Problem kommt dadurch zustande, dass du nicht 40 mal "Suchen - Ersetzen" machst sondern einmal für jede Zelle in jedem Datenblatt.

Bei dem Bereich wo du angegeben hast sind das 100.000 + Rechenoperationen (bin gerade zu faul es genau auszurechen).

Du kannst im Excel (ohne VBA) einen einen Bereich markieren und da über die Standardfunktion Suchen/Ersetzen alles ersetzen und es geht super schnell.

Den Vorgang tät ich einfach in VBA automatisieren. Hab gerade mal mit dem Makrorecoder was aufgenommen.

Code:

Muss man natürlich noch ein bisschen anpassen aber sollte die Geschwindigkeit massiv erhöhen da es so nur 40 Rechenoperationen sind.

Gruß

Fairfax

 

Hallo zusammen,

erstmal Danke für die Antworten. Das Einarbeiten von Code:

ist schonmal eine gute Sache. Das kannte ich nicht.
@jinx: Erstmal Danke, leider funktioniert das Ganze nicht. In der Zeile:
Code:

läuft er mit der Meldung "Keine Zellen gefunden" vor den Baum. Leider erschließt sich mir nicht warum? Eine Idee?
@Fairfax: MIt dem makrorecorder habe ich zu Beginn auch gearbeitet. Das funktioniert allerdings nicht, wenn Du #Ref! ersetzen lassen willst. Excel macht es einfach nicht, warum weiß ich nicht. Google hat mir jedenfalls die Erkenntnis geliefert, dass auch andere das nicht mit dem Recorder hinbekommen haben.

Hat jemand denn noch eine Anregung, warum sich das Makro weigert, das letzte Sheet zu bearbeiten?

Grüße

OfficeUser

 

Moin, OfficeUser321,

wahrscheinlich erkennt Excel die Daten nicht als Fehlerwerte - entweder im Vorwege die Anzahl der Zellen durch .Cells.Count feststellen lassen und die Aktion nur starten, wenn die Anzahl größer als 0 ist. Ansonsten den Zusatz 16 weglassen, dann sollten alle Formelzellen in die Auswahl kommen.

Die Fehlermeldung taucht halt immer auf, wenn SpecialCells eine bestimmte Art von Zellen nicht finden kann. Übergehen kann man es wie oben beschrieben durch Zählen oder durch Verwendung von On Error Resume Next, wobei das nur die Meldung übergeht, aber keine Aktion ausführt.

Mich würde ein Ausschnitt Deiner Daten interessieren - ich war bisher der Meinung, man müsse anders an die Umwandlung von Fehlerwerten herangehen...

Wegen der Fehlermeldung auf der letzten Tabelle: Sieh Dir Deine Bedingungen für die Schleife an - auf der letzten Tabelle wird eine nicht vorhandene Tabelle (wks + 1) ausgewählt. Warum habe ich wohl eine "sichere" Schleife vorgestellt mit For Each wks in Worksheets... ?

 

Hi jinx,

ich habe jetzt, wie von Dir angeregt,einfach:
Code:

rausgenommen, somit sieht das komplette Ergebnis jetztso aus:
Code:

Das ganze funktioniert super, als I-Tüpfelchen wird jetzt auch das letzteSheet mitgenommen. Zum Verständnis: Könntest Du mir die Zeile :
Code:

noch erklären und warum die jetzt den Fehler verursacht hat? Danke!

Gruß

OfficeUser

 

Moin, OfficeUser321,

die Verwendung von wks.Range("B16:AE100").SpecialCells(xlCellTypeFormulas) würde alle Formelzellen der jeweiligen Tabelle ermitteln und nur diese durchlaufen. Die Einschränkung auf die 16 war eine Begrenzung der Formelzellen auf solche, die eine Fehlermeldung darstellen (wahrscheinlich aber nur diejenigem die den gesamten Ausdruck als Fehler darstellen).

Bei Deinem Makro fehlt meiner Meinung nach die Begrenzung auf Formelzellen bzw. die Prüfung auf Formeln in den Zellen... *wink.gif*

 

Hi jinx,

habe deinem Post zufolge wieder:
Code:

hinzugefügt. Das ganze Makro läuft jetzt in gut 30 Sekunden durch, damit ist mein Auftrag erfüllt ;-)
Vielen Dank!

 

Hallo OfficeUser321,

Warum geht Suchen&Ersetzen nicht?

Probier mal

Code:

Grüße
EarlFred

 

Hallo EarlFred,

habe Deinen Code jetzt nicht ausprobiert. Ich meinte folgendes:
Du nimmst einen Suchen&Ersetzen-Vorgang auf:
Code:

Dann strickt man das in sein Makro ein und ich gebe Dir Brief und Siegel, dass es nicht funktionieren wird. Warum, keine Ahnung. Wenn man einen "normalen" Formelbestandteil ändern will, kein Problem. #REF! mag er aber nicht. Wieso, keine Ahnung.
Officeuser

 

Hallo Officeuser,

dann hätte ich beides jetzt gerne per PM *wink.gif*

Beim Deutschen Excel muss man freilich #Bezug! durch #Ref! im Code ersetzen, aber gehen tut es - warum allerdings nur bei mir und bei allen anderen nicht, kann ich auch nicht beantworten, vermutlich ist mein Excel kaputt *wink.gif*

Aber für den unwahrscheinlichen Fall, dass es bei mir klappen sollte, wovon ich ausgehe, da ich es getestet habe, reicht mir bei rund 70 Tabellen und ca. 900 zu ersetzenden Einträgen pro Blatt rund eine Sekunde für einen gesamten Durchlauf.
OK, aber da es bei Dir bereits ungetestet nicht geht, musst Du mit 30 Sekunden leben. Schade. *wink.gif*

Grüße
EarlFred