Performance von VBA-Code unter Excel 2013/2016

Question

Hallo,

ich habe unter Excel 2003 eine Reihe von professionellen VBA-Anwendungen entwickelt, die bei meinen Kunden erfolgreich laufen.

Nun sind einige meiner Kunden auf Excel 2013 umgestiegen und bemängeln, dass die Programm unerträglich langsam laufen.

Ich hab mir nun auch Excel 2013 zugelegt und musste feststellen, dass typische Codesequencen meiner VBA-Programme (Daten laden, auswerten, berechnen, ausgeben und graphisch als Excel-Diagramm darstellen) unter Excel 2013 um mehr als den Faktor 30 langsamer laufen als unter Excel 2003. Um das genauer abzuklären, hab ich die Programme bei einem Kollegen unter Excel 2010 und mit der Testversion von Excel 2016 getestet.

Ergebnis:

Mit Excel 2010 ist die Performance noch voll in Ordnung, Excel 2016 ist fast genauso langsam wie Excel 2013.

Fragen:

• Gibt es eine Liste der VBA-Kommandos, die unter Excel 2013/2016 so viel langsam laufen und die man meiden sollte?
• Gebt es Informationen zu möglichen Codeoptimierungen unter Windows 2013/2016?

Zur Info:

Alle mir bekannten Optimierungen bezüglich des Vermeidens von Select-Befehlen, der Abschaltung von Berechnungen und der Abschaltung des Screenupdatings sind bereits standardmäßig umgesetzt. Nicht verzichten kann ich hingegen auf Events. Aber auch hier ist über Flags (Globale Boolsche Variablen) sichergestellt, dass Eventcode nur abgearbeitet wird, wenn er gebraucht wird.

Herzliche Grüße

Bernd Glauner

Answer 1

Kann ich das Programm nicht optimieren?

Das Problem ist nicht (wirklich) das Programm, es ist Deine Datei.

Nur allein durch Zurücksetzen des benutzen Bereiches konnte ich die Performance um mehrere 100% steigern...

Andreas.

Answer 2

Muss natürlich lauten:

... Codeoptimierungen unter Excel 2013/2016?

Answer 3

Was kann man da noch reduzieren? 

Reduzieren heißt nicht das der Code schneller läuft. ;-) Oftmals laufen längere Code schneller als kürzere... aber egal.

Die Frage ist eigentlich die: Ist der Code generell langsam, sprich sind alle Codeteile mehr oder weniger gleich schnell, oder gibt es einen Flaschenhals der das ganze ausbremst.

Bei Dir ist 2tes der Fall, es ist offensichtlich der Autofilter... wobei der Autofilter in Excel wirklich schnell ist... d.h. heißt es liegt an Deiner Datei. Ich wette die ist schön bunt, mit vielen Formeln und ggf. sogar mit vielen bedingten Formatierungen gespickt. Und da ist das Problem.

Auch das Konstrukt

  MIN_Punkt = Replace(Sheets("Main").Cells(Erste_Zeile_bunte_Tabelle + 1, i).Value, ",", ".")

läßt vermuten das die Daten gar nicht oder schlecht aufbereitet sind..

Die älteren Officeversionen hatten eben noch nicht diese ganzen Eyecatcher, die man überall hinzufügen kann, etc.

Mach mal einen Test: Kopiere die Daten ohne Formeln und ohne Formate in eine neue Datei und dann lass Deinen Code da mal laufen. Ich wette Du wirst überrascht sein wie flott das geht. Wenn nicht: Datei hochladen und den Download-Link hier posten, dann kuck ich mal.

Andreas.

Answer 4

Hi Andreas, ich kann meinen Code zeigen:

Unter 2010 lief es noch jetzt kann ich mir eine Kaffee holen.

Die Funktion soll aus der Ziele über dem Filter die Werte die in der Zeile mit einem F gekennzeichnet sind in den Filter übernehmen.

Was kann man da noch reduzieren? 

Sub Setze_Filter()

    Erste_Zeile_bunte_Tabelle = 191

    Zweite_Zeile_bunte_Tabelle = 193

    Dritte_Zeile_bunte_Tabelle = 195

    Letzte_Spalte_bunte_Tabelle = 227

  Application.EnableEvents = False

  Application.Calculation = xlCalculationManual

  Application.ScreenUpdating = False

  Application.AskToUpdateLinks = False

On Error GoTo NachFehler

    ActiveSheet.ShowAllData

NachFehler:

 For i = 1 To Letzte_Spalte_bunte_Tabelle

    If (Sheets("Main").Cells(190, i).Value = "F") Then

    MIN_Punkt = Replace(Sheets("Main").Cells(Erste_Zeile_bunte_Tabelle + 1, i).Value, ",", ".")

    MAX_Punkt = Replace(Sheets("Main").Cells(Zweite_Zeile_bunte_Tabelle + 1, i).Value, ",", ".")

'    Sheets("Main").Range("$A$198:$HY$9391").AutoFilter Field:=i, Criteria1:=">=" & MIN_Punkt

    Sheets("Main").Cells(181, 1).AutoFilter Field:=i, Criteria1:=">=" & MIN_Punkt _

           , Operator:=xlAnd, Criteria2:="<" & MAX_Punkt

    End If

 Next

  Application.Calculation = xlCalculationAutomatic

  Application.ScreenUpdating = True

  Application.AskToUpdateLinks = True

  Application.EnableEvents = True

End Sub

Beste Grüße,

Michael

Answer 5

Fragen:

• Gibt es eine Liste der VBA-Kommandos, die unter Excel 2013/2016 so viel langsam laufen und die man meiden sollte?
• Gebt es Informationen zu möglichen Codeoptimierungen unter Windows 2013/2016?

Hey, das ist doch mein Thema wieder. :-) Also nein und nein.

Ich lese diese Meldungen häufiger, oftmals wird auch bemängelt das in einem deutschen XL der gleiche Vorgang langsamer ist als in einem englischen...

Je lauter die Leute schreien, desto weniger sind sie bereit Ihren Code zu zeigen. Und wenn doch, dann findet man oft viel SchnickSchnack, bunte Zellen, bedingte Formatierungen, Formeln die temporär zur Berechnung von irgendwas benutzt werden, naja, so'n Zeugs halt.

Manchmal juckt es mich in den Fingern und ich mach das gleiche mal ohne SchnickSchnack und idR läuft das ganze dann irgendwas zwischen 100 und 1000 mal schneller... als es vorher unter XL 2010 lief! Und dann zeigt der Versuch mit 2013/2016 meistens das es nicht oder nur unwesentlich länger braucht.

Hier ist eine gute Seite die zeigt was langsam ist und was nicht:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Der Burner schlechthin in Excel sind Arrays, es gibt nichts schnelleres. Wenn Du bunte Zellen und Diagramme per Code erzeugst... watt mutt datt mutt, da läßt sich nix machen... es sei denn ein Vorlage benutzen.

Andreas.