MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 22.04.2009, 19:42   #1
OfficeUser321
Neuer Benutzer
Neuer Benutzer
Standard VBA - 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:

Sub ersetzen()
Application.ScreenUpdating = False
Dim wks As Double

sheets(1).Range("A1") = Worksheets.Count
wks = 0
Do Until wks = Worksheets.Count
Worksheets(wks + 1).Activate
raus = "#REF!"
rein = "Tabelle1!"
    For Each Cell In Selection
        Range("B16:AE100").Select
        If Cell.HasFormula = True Then
            Cell.Formula = Application.WorksheetFunction.Substitute(Cell.Formula, raus, rein)
        End If
    Next Cell
wks = wks + 1
Loop

End Sub
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:

Cell.Formula = Application.WorksheetFunction.Substitute
Hat jemand eine Idee, damit es funktioniert und ein wenig flotter wird?
Schonmal Danke im Vorraus!

Office User
OfficeUser321 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 22.04.2009, 20:58   #2
nicht registriert
Standard

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
 
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 22.04.2009, 21:11   #3
jinx
MOF Guru
MOF Guru
Standard

Moin, OfficeUser,

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

Code:

Sub ersetzen()
Dim wks As Worksheet
Dim rngCell As Range
Dim raus As String
Dim rein As String

With Application
   .ScreenUpdating = False
   .Calculation = xlCalculationManual
   .EnableEvents = False
End With

On Error GoTo exit_here

For Each wks In Worksheets
raus = "#REF!"
rein = "Tabelle1!"
   For Each rngCell In wks.Range("B16:AE100").SpecialCells(xlCellTypeFormulas, 16)
     rngCell.Formula = Application.WorksheetFunction.Substitute(rngCell.Formula, raus, rein)
   Next rngCell
Next wks

exit_here:
With Application
   .ScreenUpdating = True
   .Calculation = xlCalculationAutomatic
   .EnableEvents = True
End With
End Sub

__________________

cu
jinx


per 31.12.2010 ausgeschiedener User und ehemaliger Excel-

Folgende Tools werden zur optischen Aufbereitung eingesetzt: <a *****"http://www.ms-office-forum.net/forum/codeconverter.php" target="_blank">Code Converter</a>; <a *****"http://www.haserodt.de/ejh_do/ex_jean_info.htm" target="_blank">Excel Jeanie Html</a>
Für die allgemeinen Hinweise: <a *****"http://www.ms-office-forum.net/forum/netiquette.php" target="_blank">Netiquette</a>; <a *****"http://www.excel-vba.de/frageninforen.htm" target="_blank">Fragen und Antworten in Foren</a>

eingesetzte Betriebssysteme: XP Home, XP Media Center Edition, XP Professional, Vista Ultimate, 7/Seven
verwendete Programme: Excel97 SR-2, Office 2000 SP-3, Office2002/XP SP-3, Office 2003 Professional SP-3, Office 2007 Home & Student SP-3, Office 2007 Professional SP-3
jinx ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 22.04.2009, 21:25   #4
Fairfax
Neuer Benutzer
Neuer Benutzer
Standard

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:

Range("E7:I20").Select
Selection.Replace What:="1", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

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

Gruß

Fairfax
Fairfax ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 23.04.2009, 09:25   #5
OfficeUser321
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo zusammen,

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

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

   For Each rngCell In wks.Range("B16:AE100").SpecialCells(xlCellTypeFormulas, 16)
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
OfficeUser321 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 23.04.2009, 09:41   #6
jinx
MOF Guru
MOF Guru
Standard

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... ?

__________________

cu
jinx


per 31.12.2010 ausgeschiedener User und ehemaliger Excel-

Folgende Tools werden zur optischen Aufbereitung eingesetzt: <a *****"http://www.ms-office-forum.net/forum/codeconverter.php" target="_blank">Code Converter</a>; <a *****"http://www.haserodt.de/ejh_do/ex_jean_info.htm" target="_blank">Excel Jeanie Html</a>
Für die allgemeinen Hinweise: <a *****"http://www.ms-office-forum.net/forum/netiquette.php" target="_blank">Netiquette</a>; <a *****"http://www.excel-vba.de/frageninforen.htm" target="_blank">Fragen und Antworten in Foren</a>

eingesetzte Betriebssysteme: XP Home, XP Media Center Edition, XP Professional, Vista Ultimate, 7/Seven
verwendete Programme: Excel97 SR-2, Office 2000 SP-3, Office2002/XP SP-3, Office 2003 Professional SP-3, Office 2007 Home & Student SP-3, Office 2007 Professional SP-3

Geändert von jinx (23.04.2009 um 09:45 Uhr).
jinx ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 23.04.2009, 11:47   #7
EarlFred
MOF Guru
MOF Guru
Standard

Hallo OfficeUser321,

Zitat:

@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.

Warum geht Suchen&Ersetzen nicht?

Probier mal

Code:

Sub RefRaus()
Dim wks As Worksheet
Dim raus As String
Dim rein As String

raus = "#REF!"
rein = "Tabelle1!"

    With Application
       .ScreenUpdating = False
       .Calculation = xlCalculationManual
       .EnableEvents = False
    End With

    For Each wks In Worksheets
        wks.Cells.Replace raus, rein
    Next wks
    
    With Application
       .ScreenUpdating = True
       .Calculation = xlCalculationAutomatic
       .EnableEvents = True
    End With
End Sub
Grüße
EarlFred

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 8 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,054% per 13.08.2019) - eine tolle Geste!
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 23.04.2009, 12:12   #8
OfficeUser321
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo EarlFred,

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

    Range("J25:P42").Select
    Selection.Replace What:="#REF!", Replacement:="Tabelle1!", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
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
OfficeUser321 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 23.04.2009, 12:25   #9
EarlFred
MOF Guru
MOF Guru
Standard

Hallo Officeuser,

Zitat:

und ich gebe Dir Brief und Siegel

dann hätte ich beides jetzt gerne per PM

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

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.

Grüße
EarlFred

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 8 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,054% per 13.08.2019) - eine tolle Geste!
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 13:01 Uhr.



Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.