Microsoft Excel

Herbers Excel/VBA-Archiv

Excel extrem langsam durch VBA

Betrifft: Excel extrem langsam durch VBA von: Sparrow
Geschrieben am: 23.09.2014 14:26:58

Hallo!
Mein Excel ist leider extrem langsam, vermutlich durch eine komplizierte VBA Programmierung - diese lautet wie folgt:

Tabelle 1:





Private Sub Worksheet_Deactivate()

 Application.ScreenUpdating = False

 ActiveWorkbook.Worksheets("Ereignisse").ListObjects("Ereignisse").Sort. _
 SortFields.Clear
 ActiveWorkbook.Worksheets("Ereignisse").ListObjects("Ereignisse").Sort. _
 SortFields.Add Key:=Range("Ereignisse[Name]"), SortOn:=xlSortOnValues, _
 Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Ereignisse").ListObjects("Ereignisse").Sort
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With

 Application.ScreenUpdating = True
 End Sub

Tabelle 2:





Private Sub Worksheet_Deactivate()

 Application.ScreenUpdating = False

 ActiveWorkbook.Worksheets("Basisdaten").ListObjects("Basisdaten").Sort. _
 SortFields.Clear
 ActiveWorkbook.Worksheets("Basisdaten").ListObjects("Basisdaten").Sort. _
 SortFields.Add Key:=Range("Basisdaten[Nachname]"), SortOn:=xlSortOnValues, _
 Order:=xlAscending, DataOption:=xlSortNormal
 ActiveWorkbook.Worksheets("Basisdaten").ListObjects("Basisdaten").Sort. _
 SortFields.Add Key:=Range("Basisdaten[Vorname]"), SortOn:=xlSortOnValues, _
 Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Basisdaten").ListObjects("Basisdaten").Sort
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With

 Application.ScreenUpdating = True

 End Sub
Hat jemand eine Idee wie ich diesen Code vereinfachen könnte? Oder braucht ihr mehr Infos? Vielen Dank im Voraus!
Sascha

  

Betrifft: AW: Excel extrem langsam durch VBA von: Klaus M.vdT.
Geschrieben am: 23.09.2014 14:47:33

Hallo Sascha,
das ist keine komplizierte VBA-Programmierung, das ist eine simple Sortierroutine aus dem Makrorekorder. Die kann eigentlich nicht Schuld an einem langsamen Excel sein ... trotzdem mal eine Verkürzung und hoffentlich Verschnellerung:

Private Sub Worksheet_Deactivate() 'in Tabelle1

 GetMoreSpeed (True)

 With Worksheets("Ereignisse").ListObjects("Ereignisse")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("Ereignisse[Name]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
 End With

 GetMoreSpeed (False)
 End Sub


Private Sub Worksheet_Deactivate() 'in Tabelle2

 GetMoreSpeed (True)

 With Worksheets("Basisdaten").ListObjects("Basisdaten")
    .Sort.SortFields.Clear
    .Sort.Add Key:=.Range("Basisdaten[Nachname]"), SortOn:=xlSortOnValues, Order:=xlAscending,  _
DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("Basisdaten[Vorname]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
 End With

 GetMoreSpeed (False)

 End Sub


'das hier in ein Modul kopieren!
Public Static Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
Dim intCalculation As Integer
    If Modus = True Then intCalculation = Application.Calculation
    With Application
        .ScreenUpdating = Not Modus
        .EnableEvents = Not Modus
        .Calculation = IIf(Modus = True, xlManual, intCalculation)
        .Cursor = IIf(Modus = True, 2, -4143)
    End With
End Sub
Grüße,
Klaus M.vdT.


  

Betrifft: AW: Excel extrem langsam durch VBA von: Sparrow
Geschrieben am: 23.09.2014 15:19:08

Hallo!
Erst mal vielen Dank für die schnelle Antwort! Leider gibt VBA eine ungültige Prozedur der folgenden Zeile an:

.Sort.SortFields.Add Key:=.Range("Ereignisse[Name]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal

Wo könnte der Fehler liegen? Vielen Dank im Voraus!

Sascha


  

Betrifft: AW: Excel extrem langsam durch VBA von: Sparrow
Geschrieben am: 23.09.2014 15:30:52

Habe den Fehler gefunden - allerdings ist es immer noch so langsam - entferne ich den VBA Code ist die Mappe fix.. keine Ahnung woran es noch liegen könnte


  

Betrifft: AW: Excel extrem langsam durch VBA von: Sparrow
Geschrieben am: 24.09.2014 15:15:10

Hallo!
Kann mir jemand erklären was dieser Code genau beim Sortieren beschleunigt ?
Public Static

Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
  Dim intCalculation As Integer
      If Modus = True Then intCalculation = Application.Calculation
      With Application
          .ScreenUpdating = Not Modus
          .EnableEvents = Not Modus
          .Calculation = IIf(Modus = True, xlManual, intCalculation)
          .Cursor = IIf(Modus = True, 2, -4143)
      End With
  End Sub
Vielen Dank im Voraus!

Beste Grüße
Sascha


  

Betrifft: 'Modus = True' ist 1 überflüssiger Doppel-Vgl, ... von: Luc:-?
Geschrieben am: 25.09.2014 02:18:47

…Sascha,
If Modus Then reicht hier völlig. Ansonsten stellt die Fkt die entsprd Xl-Eigenschaften auf den Wert des ihr übergebenen Arguments, falls diese Eigenschaften einen Wahrheitswert verlangen, bzw einen diesem analogen Wert. Mit Argument True (oder ohne Argument) schaltet man die von diesen Eigenschaften gesteuerten Aktivitäten ab (wg Not Modus!), mit False wieder ein. Das betrifft hier die HptBremsen Bildschirmaktualisierung, Reaktion auf Ereignisse und automatische Berechnung (die sonst nach jeder Änderung auch schon einer einzigen Zelle erfolgen kann und dann uU viel Zeit frisst). Außerdem wird das Cursor-Symbol ausgetauscht.
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: 'Modus = True' ist 1 überflüssiger von: Klaus M.vdT.
Geschrieben am: 25.09.2014 10:30:15

Hallo Luc,
deine Exkursion in die Aussagenlogik ist hier vielleicht nicht zielführend, sondern eher ablenkend? Aber du hast recht, "mein" Code (ich erinnere mich nicht mehr, von wem ich den damals geklaut habe, darum ist es jetzt meiner :-) ) ist 7 Zeichen und damit 7 Byte zu lang. Ich werde das sofort ändern, um Festplattenplatz zu sparen :-)

Hallo Sascha,
während ein Makro läuft braucht der User üblicherweise nichts zu machen. Darum kann man auch die Bildschirmaktualisierung und den Cursor abschalten.
Manche Makros machen viele viele kleine Änderungen, die dann jeweils eine Neuberechnung auslösen. Da der User während der Makrolaufzeit aber eh nicht eingreift, reicht (meistens!!!) eine einzelne Neuberechnung am Makroende. Gleiches gilt für die Events.
Ergo: man schaltet Events, Screenupdating, Neuberechnung und den Cursor einfach ab, während das Makro läuft. Damit man den Code dazu nicht jedesmal neu schreiben muss, ist er in eine eigene Sub ausgelagert (so vergisst man auch nichts).
Die Zeile (für Luc gekürzt!)
If Modus Then intCalculation = Application.Calculation
Ist nur dafür da, falls die automatischen Berechnungen beim Makrostart bereits abgeschaltet waren, dann sollen sie nicht von selbst angestellt werden.

Dieses "Beschleunigungs"-Makro stelle ich eigentlich vor jedes Makro, dass mehr als 5 Operationen ausführt. Im besten Fall läuft es dann schneller, im schlechtesten Fall hat es auch nicht geschadet.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Excel extrem langsam durch VBA von: Rudi Maintaire
Geschrieben am: 23.09.2014 15:32:41

Hallo,
wenn an deinen Tabellen ein Haufen Berechnungen hängt, ist es kein Wunder, dass die Mappe lahm wird, da die Berechnungen nach der Sortierung neu gemacht werden müssen.

Gruß
Rudi


 

Beiträge aus den Excel-Beispielen zum Thema "Excel extrem langsam durch VBA "