[Excel 2007] VBA Skript zum setzen von Kopf- und Fußzeilen dauert sehr lange

[DarthShader]

Hallo zusammen,

ich habe folgendes kleines VBA Skript, um bei einer Änderung in einer meiner Tabellen in meiner Arbeitsmappe die Kopf- und Fußzeilen aller Tabellen automatisch zu aktualisieren:

    Application.ScreenUpdating = False
    Worksheets("Tabelle1").Activate
    
    sheetCount = Worksheets.Count
    For i = 1 To sheetCount
        With Worksheets(i).PageSetup
            .LeftHeader = "LeftHeader"
            .CenterHeader = "CenterHeader"
            .RightHeader = "RightHeader"
            .LeftFooter = "LeftFooter"
            .CenterFooter = "CenterFooter"
            .RightFooter = "RightFooter"
        End With
    Next i
    
    Application.ScreenUpdating = True

(Die Werte die ich setze sind natürlich für dieses Beispiel Strings, in dem echten Dokument werden andere Werte verwendet)

Eigentlich also keine Sache, die so viel Rechenaufwand benötigt - dennoch, bei 8 Tabellen/Reitern dauert die Schleife schon fast 10 Sekunden (auf einem sehr aktuellen Rechner). Das Dokument wird sich auch noch vergrößern um weitere Tabellen, die Laufzeit steigt linear dazu.

Mache ich irgendwas falsch, gibt es eine Optimierung die ich durchführen kann? Warum braucht Excel dafür so extrem lange?


Über Eure Hilfe würde ich mich sehr freuen


Vielen Dank!

 

[Stoffelchen]

Für mich sieht das ok aus. Ich wüsste nicht, wo da ein Fehler sein sollte.

Allerdings habe ich (aktuell) die Erfahrung gemacht, dass die Netzwerkverbindung den Ablauf stark beeinflussen kann. Solltest du das als z.B. für die Arbeit machen und dein Account im Firmennetzwerk sein, würde ich einfach mal kurz die Netzwerkverbindung stilllegen (Netzwerkkarte dekativieren), um zu sehen, obs nicht vllt daran liegt.

Als kleines Beispiel: Für das Bearbeiten einzelner Excel-Dokumente hat mein Tool mit Netzwerkverbindung rund 1min für 3 Dokumente gebraucht. Ohne Netzwerkverbindung habe ich in 4min 200 durchgekriegt (liegt aber zu einem gewissen Teil auch daran, dass ich ca. 7000mi von meinem eigentlichen Domänenstandort entfernt bin ^^)

Gruss Stoffelchen

 

[Drogist]

Moin DarthShader,

hmmm, woran erinnert mich bloß dein Nick? Schade, ich kann mich gerade nicht konzentrieren, im Hintergrund läuft bei mir (wirklich) auf Pro7 StarWars .

Aber zum Thema. Wie Stoffelchen schon geschrieben hat, der Code ist OK. Ich habe mal versucht, das eine oder andere zu optimieren. Die Zeitvorteile sind absolut minimal. Hier der Test-Code:

Sub Hugo()
    Dim z As Integer
    Dim Zeit1 As Single, Zeit2 As Single
    Dim Ws As Worksheet
    Dim kl As String, km As String, kr As String
    Dim fl As String, fm As String, fr As String
    
    kl = "Kopfzeile links"
    km = "Kopfzeile Mitte"
    kr = "Kopfzeile rechts"
    fl = "Fußzeile links"
    fm = "Fußzeile Mitte"
    fr = "Fußzeile rechts"
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Zeit1 = Timer()
    For z = 1 To 100
        For Each Ws In Worksheets
            With Ws.PageSetup
                .LeftHeader = kl & Format(k, "000")
                .CenterHeader = km
                .RightHeader = kr
                .LeftFooter = fl
                .CenterFooter = fm
                .RightFooter = fr
            End With
        Next Ws
    Next z
    Zeit2 = Timer
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    MsgBox Zeit2 - Zeit1 & " Sekunden"
End Sub

Dass hier 100 Mal die Kopf- und Fußzeile neu geschrieben wird, dient nur der besseren Zeitauswertung. Aber auch ich bin erstaunt, wie lange Excel dazu braucht!

 

[DarthShader]

Hallo,

vielen Dank für Eure Antworten.

Allerdings habe ich (aktuell) die Erfahrung gemacht, dass die Netzwerkverbindung den Ablauf stark beeinflussen kann. Solltest du das als z.B. für die Arbeit machen und dein Account im Firmennetzwerk sein, würde ich einfach mal kurz die Netzwerkverbindung stilllegen (Netzwerkkarte dekativieren), um zu sehen, obs nicht vllt daran liegt.

Das ist ein interessanter Tip, den ich heute abend einmal ausprobieren werde.

hmmm, woran erinnert mich bloß dein Nick? Schade, ich kann mich gerade nicht konzentrieren, im Hintergrund läuft bei mir (wirklich) auf Pro7 StarWars .

Ach das ist so ein alter Name für mich bei Tutorials.de, damals habe ich mich noch stark mit Grafikprogrammierung beschäftigt, deshalb das "Shader", und ich glaube, damals lief auch grad Star Wars

Aber zum Thema. Wie Stoffelchen schon geschrieben hat, der Code ist OK. Ich habe mal versucht, das eine oder andere zu optimieren. Die Zeitvorteile sind absolut minimal. Hier der Test-Code:

[...]

Dass hier 100 Mal die Kopf- und Fußzeile neu geschrieben wird, dient nur der besseren Zeitauswertung. Aber auch ich bin erstaunt, wie lange Excel dazu braucht!

Ja, mich wundert es auch sehr. Ich habe die Vermutung, dass es an der reinen Ausführung des VBA Codes gar nicht liegt, sondern eher indirekt etwas mit z.B. den Druckern/Druckertreibern zu tun hat. Wird die Seiteneinrichtung geändert, könnte ich mir vorstellen, dass Excel dort irgendwas an dem Layout prüft, Formate berechnet oder sonstwas - das Thema Druck bzw. Seitenlayout ist ja nicht soo trivial. Möglicherweise ist dies einfach ungeschickt implementiert, und so werden da immer Berechnungen angestellt, die z.B. beim Setzen der Kopfzeile gar nicht durchgeführt werden müssten.

Bei der Recherche im Internet bin ich öfters auf die Frage gestoßen, warum das Ändern des PageSetups via VBA so lange dauert, und nirgens gab es eine hilfreiche Antwort.

Ich werde es jetzt mit einem Workaround lösen - das Ändern meiner Kopf- und Fußzeilen mach ich nun auf Bedarf, und nicht bei jeder Tabellenänderung. Ändert natürlich nur subjektiv was an der Geschwindigkeit, aber reicht für mich erstmal.

Abgesehen davon, würden mich die Gründe für dieses Verhalten dennoch interessieren. Mal sehen, vielleicht gehe ich damit demnächst mal in einer Microsoft Newsgroup, und wenn die dort nichts wissen, versuch ich mein Glück mal beim Microsoft Support

 

[Drogist]

Moin DarthShader,

wo wir gerade bei WalkArounds sind: Die einfachste Lösung wäre, wenn du dir für diese Dateien einfach eine *.xlt (das gleiche wie eine *.dot) anlegst, also eine leere Excel-Vorlage und alle neuen entsprechenden Spreadsheets hier erstellst. Natürlich geht es auch per Makro, die alten Mappen in diese Vorlage zu kopieren. Welcher Aufwand nun größer ist, vermag ich nicht zu sagen. - Ich würde bei den alten Files das Makro laufen lassen und die neuen Mappen auf der Grundlage der Vorlage mit Kopf-Fußzeile laufen lassen.

Ach ja, als kleiner Vergleichswert: Dein Makro brauchte bei 100 Durchläufen und drei leeren Arbeitsblättern zwischen 65 und 85 Sekunden, meines war etwa 5% schneller, also auch nicht berauschend.

 

[DarthShader]

wo wir gerade bei WalkArounds sind: Die einfachste Lösung wäre, wenn du dir für diese Dateien einfach eine *.xlt (das gleiche wie eine *.dot) anlegst, also eine leere Excel-Vorlage und alle neuen entsprechenden Spreadsheets hier erstellst. Natürlich geht es auch per Makro, die alten Mappen in diese Vorlage zu kopieren. Welcher Aufwand nun größer ist, vermag ich nicht zu sagen. - Ich würde bei den alten Files das Makro laufen lassen und die neuen Mappen auf der Grundlage der Vorlage mit Kopf-Fußzeile laufen lassen.

Ehrlich gesagt, wirklich einfacher klingt das nicht, als dass ich mein Skript einfach auf einen Button lege (also auf Bedarf die Aktualisierung durchführe)

Allerdings verstehe ich deinen Workaround auch nicht wirklich, es sei denn Du hast nicht bedacht, dass die Inhalte meiner Kopf- und Fußzeilen dynamischer Natur sind, ich diese also nicht statisch in einer Vorlage ablegen kann (was ich zugegebenermaßen in meinem ersten Post aber auch nur angedeutet, nicht konkret gesagt habe).

 

[Thomas Ramel]

Grüezi zusammen

Der Page-Setup-Dialog ist leider etwas vom langsamsten und trägsten was es in Excel gibt.

Viel flotter geht das mit den alten Excel4-Makros wie hier:

Sub xl4PageSetup()
Dim Foot_L As String, Foot_C As String, Foot_R As String
Dim head As String, foot As String, pLeft As String, pRight As String, Top As String, _
Bot As String, hdng As String, grid As String, h_cntr As String, v_cntr As String, _
orient As String, paper_size As String, pscale As String, pg_num As String, _
pg_order As String, bw_cells As String, quality As String, head_margin As String, _
foot_margin As String, Notes As String, Draft As String
Dim pSetUp As String, ws As Worksheet
   With ActiveSheet.PageSetup
      Foot_L = .LeftFooter
      Foot_C = .CenterFooter
      Foot_R = .RightFooter
   End With

   'head = """"""
   foot = """&L&8&F, &A, &D, &T&C" & Foot_C & "&R" & Foot_R & """"
   pLeft = "0.69"
   pRight = "0.38"
   Top = "0.47"
   Bot = "0.47"
   hdng = "False"
   grid = "False"
   h_cntr = "True"
   v_cntr = "False"
   orient = 1
   paper_size = 9
   pscale = "True"
   pg_num = ""
   pg_order = ""
   bw_cells = ""
   quality = ""
   head_margin = "0.37"
   foot_margin = "0.27"
   Notes = "False"
   Draft = "False"

   pSetUp = "Page.Setup(" & head & "," & foot & "," & pLeft & ","
   pSetUp = pSetUp & pRight & "," & Top & "," & Bot & "," & hdng & ","
   pSetUp = pSetUp & grid & "," & h_cntr & "," & v_cntr & ","
   pSetUp = pSetUp & orient & "," & paper_size & "," & pscale & ","
   pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & ","
   pSetUp = pSetUp & quality & "," & head_margin & ","
   pSetUp = pSetUp & foot_margin & "," & Notes & "," & Draft & ")"

   Set ws = ActiveSheet

   Application.ExecuteExcel4Macro pSetUp

End Sub

 

[DarthShader]

Hallo Thomas,

da baust Du also ein Excel4 Makro als String zusammen und führst es via "Application.ExecuteExcel4Macro pSetUp" aus? Das ist sicherlich eine funktionierende Lösung, ich probiere das heute Abend einmal aus. Sieht für mich aber auch nach einem recht "schlimmen" Workaround aus

Mich würde interessieren, warum denn dieses Excel4 Makro zu viel schneller ist.

 

[Thomas Ramel]

Grüezi DarthShader

Ja, genau so ist es - wenn ein 'schlimmer' Workaround dafür sauschnell ist, dann lebe ich gerne damit

Im ersten Teil werden die bestehenden Inhalte der Kopf- und Fusszeilen ausgelesen und im zweiten Teil dann entsprechend gesetzt. Dann wird das Ganze in einem Rutsch an das Page-Setup übergeben.
Und genau das ist meines Erachtens der grosse Unterschied. Beim 'normalen' zuweisen wird jedesmal einzeln pro Zuweisung auf das PageSetup zugegriffen - beim Excel4Makro nur einmal und es werden komplett alle Werte zugewiesen.

Das Ganze entstand mal in Zusammenarbeit mit einer Firma, die das seither einsetzen und einen enorme Zeit-Verbesserung damit erzielen.

 

[DarthShader]

Hallo,


vielen Dank für Eure Antworten. Das Excel4 Skript werde ich demnächst ausprobieren, ich habe es bisher aus zeitlichen Gründen nicht geschafft.