Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
876to880
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
876to880
876to880
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Replace für gesamte Mappe ziemlich langsam

Replace für gesamte Mappe ziemlich langsam
08.06.2007 23:46:03
Sabine
Hallo Leute,
nochmal ne Frage zu Replace...ich hab ne Excel-Datei mit 31 Blättern. Sie ist 10 MB groß und enthält sowohl Einzeldaten als auch Formeln.
Ich muss eigentlich zwei relativ simple Ersetzungen an Formeln vornehmen.
Mache ich das manuell (Ersetzen, Gesamte Arbeitsmappe) so geht das innerhalb von 4 Sekunden (beide).
Mach ichs jedoch per VBA ist er 3 Minuten beschäftigt. Da per VBA nicht die gesamte Mappe auf einmal verarbeitet werden kann, gehe ich so vor:

Private Sub ReplaceCompleteWorkbook(strFileName As String, strWhat As String, strReplacement As  _
_
String)
Dim i As Integer
With Workbooks(strFileName)
For i = 1 To .Worksheets.Count
.Worksheets(i).Cells.Replace What:=strWhat, Replacement:=strReplacement, LookAt:=xlPart,   _
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
End With
End Sub


Hat jemand einen Ansatz?
Ach ja, ich hab im Aufruf-Makro vorher noch "Application.Calculation = xlManual" stehen, damit nix berechnet wird.
Es macht auch keinen Unterschied ob ich ScreenUpdating aktiviert habe oder nicht. Ebenso ob die zu ersetzende Mappe oder das Blatt aktiv ist oder eine ganz andere Mappe. Ebenso ob Events enabled oder disabled sind (in der Mappe sind keine Events vorhanden).
LG
Sabine

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Replace für gesamte Mappe ziemlich langsam
08.06.2007 23:53:01
Hubert
Hi,
warum machst du das überhaupt mit VBA? VBA ist nun mal Welten langsamer
als Excel.
mfg Hubert

Bin auf VBA angewiesen...
08.06.2007 23:58:30
Sabine
Hi,
würde es manuell reichen, wäre ich die Probleme los.
Doch es gehört zu einem Ablaufprogramm, das von nem Otto-Normal-User in der Firma verwendet wird. Ich hab an unterschiedlichen Stellen immer wieder solche Ersetzen-Klamotten drin und ohne geht es nicht (es werden Rohdaten verarbeitet, die ich nicht anders geliefert bekomme).
Vielleicht einen Tipp (außer manuell)?
Ich verstehe nich, weshalb das manuell unendlich schnell und per VBA tierisch langsam ist.
LG
Sabine

Anzeige
AW: Bin auf VBA angewiesen...
09.06.2007 00:01:38
Hubert
"Ich verstehe nich, weshalb das manuell unendlich schnell und per VBA tierisch langsam ist."
Weil Excel in C programmiert ist, das ca. 10.000 mal schneller ist als VBA.
mfg Hubert

AW: Bin auf VBA angewiesen...
09.06.2007 00:13:00
Sabine
Das weiß ich schon.
Sollte VBA nicht auf die in C geschriebene Funktion von Excel zugreifen?
Aber das hilft mir leider auch nicht weiter. Gibts ne Möglichkeit das Performanter zu gestalten?

AW: Bin auf VBA angewiesen...
09.06.2007 00:43:32
Daniel
Hallo
sollte schon, aber es könnte ja durchaus sein, daß der Code für die VBA-Replace-Funktion ein andererer ist, als der für die Excel-Funktion. (obwohl beide das gleiche machen)
und da kann es natürlich schon unterschiede in der effektivität des Codes geben (sollte natürlich nicht vorkommen, aber bei derartigen MEGA-Projekten ist es nicht auszuschließen)
Gruß, Daniel

Anzeige
AW: Replace für gesamte Mappe ziemlich langsam
09.06.2007 00:35:20
Daniel
Hallo Sabine
probier mal dieses Makro:

Private Sub RCW(strFileName As String, strWhat As String, strReplacement As String)
Dim i As Integer
Dim sp As Long, ze As Long
Dim arr
With Workbooks(strFileName)
For i = 1 To .Worksheets.Count
arr = .Worksheets(i).UsedRange.FormulaLocal
For sp = 1 To UBound(arr, 2)
For ze = 1 To UBound(arr, 1)
arr(ze, sp) = WorksheetFunction.Substitute(arr(ze, sp), strWhat,  _
strReplacement)
Next
Next
.Worksheets(i).UsedRange.Value = arr
Next
End With
End Sub


es hat die gleiche Funktionalität und die gleichen Parameter wie dein Makro, basiert aber darauf, daß die Zellewerte erst in eine Interne Array-Variable kopiert, dort verändert und dann zurückgeschrieben werden.
das ist bei mir im Test mit 260.000 Ersetzungen um den Faktor 10 schneller als die Methode mit Replace
und je mehr Daten es werden um so grösser ist der Geschwindigkeitsvorteil.
Gruß, Daniel

Anzeige
AW: Replace für gesamte Mappe ziemlich langsam
09.06.2007 02:10:54
Sabine
Hallo Daniel,
ist ja geil, das funktioniert richtig gut. Danke!
Wie kamst Du darauf?
LG
Sabine

AW: Replace für gesamte Mappe ziemlich langsam
09.06.2007 12:12:00
Sabine
Hallo Daniel,
ein Problem ergibt sich noch...mit der Routine werden Array-Formeln in normale Formeln umgewandelt. Gibts dafür auch ne Lösung?
LG
Sabine

AW: Bitte jemand anders helfen
09.06.2007 13:03:58
Daniel
bei Array-Formeln müsstest du die Zeilen:

arr = .Worksheets(i).UsedRange.FormulaLocal
und
.Worksheets(i).UsedRange.Value = arr


so verändern:


arr = .Worksheets(i).UsedRange.FormulaArray
und
.Worksheets(i).UsedRange.FormulaArray = arr


Allerdings werden dann auch deine normalen Formeln zu Array-Formeln
Wenn daß ein Problem gibt, weiß ich auch nicht mehr weiter.
Gruß, Daniel

Anzeige
AW: Bitte jemand anders helfen
09.06.2007 13:06:10
Sabine
Danke.
Mhm gibts ne Methode die Array-Formeln als Array-Formeln zu behandeln und die anderen Formeln als nicht-Array-Formeln zu behanteln?

AW: Bitte jemand anders helfen
09.06.2007 13:54:00
Daniel
Hallo
kommt jetzt darauf an, wie deine Formeln angeordnet sind.
wenns immer pro Spalte eindeutig ist, müsstest du das Makro so umschreiben, daß das EINLESEN - ERSETZEN - RÜCKSCHREIBEN für jede Spalte einzeln erfolgt, dann könntest du Fallweise unterscheiden, ob eine Array-Formel vorliegt oder nicht.
Prüfen, ob eine Zelle eine Array-Formel hat, kann man mit der Funktion:
= range("A2").HasArray
die dann eben TRUE oder FALSE zurückgibt.
wie es dann mit der Geschwindigkeit aussieht, weiß ich leider auch nicht
Gruß, Daniel

Anzeige
AW: Bitte jemand anders helfen
09.06.2007 13:57:01
Sabine
Mhm okay, ich schau mal was ich draus machen kann.
Danke.

Autom. Berechnung und -Aktualisierung ?
09.06.2007 13:54:00
ransi
HAllo Sabine
Ich glaube was da so auf die Bremse tritt ist die automatische Berechnung und die Bilschirmaktualisierung.
Teste mal dies hier:
Option Explicit

Dim AppCalc
Dim AppScreen
Dim AppEvents
Dim AppCursor

Public Sub ReplaceCompleteWorkbook(strFileName As String, strWhat As String, strReplacement As String)
Dim i As Integer
Call More_speed
On Error GoTo raus
With Workbooks(strFileName)
    For i = 1 To .Worksheets.Count
        .Worksheets(i).Cells.Replace What:=strWhat, Replacement:=strReplacement, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next i
End With
raus:
Call Meine_Einstellungen
End Sub


Public Sub More_speed()
With Application
    AppCalc = .Calculation
    AppScreen = .ScreenUpdating
    AppEvents = .EnableEvents
    AppCursor = .Cursor
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .Cursor = xlDefault
End With
End Sub

Public Sub Meine_Einstellungen()
With Application
    .Calculation = AppCalc
    .ScreenUpdating = AppScreen
    .EnableEvents = AppEvents
    .Cursor = AppCursor
End With
End Sub


ransi

Anzeige
AW: Autom. Berechnung und -Aktualisierung ?
09.06.2007 14:33:46
Sabine
Hallo ransi,
ist alles bereits gemacht. Daran liegts also nicht...
Zitat:

Ach ja, ich hab im Aufruf-Makro vorher noch "Application.Calculation = xlManual" stehen, damit  _
nix berechnet wird.
Es macht auch keinen Unterschied ob ich ScreenUpdating aktiviert habe oder nicht. Ebenso ob die zu ersetzende Mappe oder das Blatt aktiv ist oder eine ganz andere Mappe. Ebenso ob Events enabled oder disabled sind (in der Mappe sind keine Events vorhanden).


LG
Sabine

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige