Einige Excel-Dateien mit Datenverknüpfungen sind langsam

Problem

Wenn eine Excel-Datei Excel Datenverknüpfungen (siehe 21. Excel-Datenlinks) und zu viele Zellenformatvorlagen enthält, kann das Scrollen sehr langsam werden und Excel scheint nicht mehr zu reagieren.

Dieses Problem kann auftreten, wenn die Excel-Datei über ~3000 Zellenformatvorlagen enthält. Sie können die Anzahl der Zellenformatvorlagen mit dem folgenden VBA-Befehl im Direktfenster überprüfen:

print Activeworkbook.Styles.Count

Lösung: Betroffene Arbeitsmappen säubern

• Kopieren Sie Ihre Arbeitsblätter in eine neue Arbeitsmappe

  In Office 2013 und höher wird beim Kopieren von Arbeitsblättern zwischen Arbeitsmappen standardmäßig die Zusammenfassung von nicht verwendeten Zellenformatvorlagen unterbunden:

  • Wählen Sie alle Arbeitsblätter in Ihrer Arbeitsmappe aus: Halten Sie die UMSCHALTTASTE gedrückt und klicken Sie auf das erste und letzte Arbeitsblatt
  • Klicken Sie mit der rechten Maustaste auf die Auswahl: Verschieben oder kopieren … → Zur Mappe: (neue Arbeitsmappe) und aktivieren Sie Kopie erstellen → OK
  • Speichern Sie die neue Arbeitsmappe unter einem neuen Namen.

  In Office 2007 und 2010 werden beim Kopieren eines Arbeitsblatts in eine neue Arbeitsmappe auch alle nicht genutzten Zellenformatvorlagen kopiert. Um dies zu verhindern, aktivieren Sie zuvor bitte den folgenden Hotfix:

  Wenn Ihre Office-Version vollständig aktualisiert wurde, ist diesen Hotfix wahrscheinlich bereits installiert. Sie müssen dieses Fix entweder mithilfe der Lösung Fix it aktivieren, die von Microsoft bereitgestellt wird, oder in der Registrierung gemäß den Anweisungen in den folgenden Microsoft-Artikeln:

  • Microsoft Office 2007: KB2553085
  • Microsoft Office 2010: KB2598127, der im Update zu Office 2010 Update für April 2012 veröffentlicht wurde.

  Wenn das Aktivieren des Hotfix nicht funktioniert, wenden Sie das im Abschnitt Resolution in den Microsoft-Artikeln oben genannte Update an.

• Räumen Sie Ihre Arbeitsmappen mit einem Bereinigungstool auf

  Nutzen Sie das von Microsoft in KB213904 empfohlene Bereinigungstool. Wenn Sie das XLStyles-Tool verwenden, klicken Sie auf Excel-Datei aufrufen, aktivieren Sie die Option Alle Zellenformatvorlagen auf „Normal“ setzen, und klicken Sie dann auf Datei verarbeiten. Sobald Excel mit der gesäuberten Datei geöffnet wird, speichern Sie sie unter einem neuen Namen.

• Entfernen Sie die benutzerdefinierten Zellenformate mithilfe eines VBA-Makros aus Ihrer Arbeitsmappe

  Öffnen Sie den VBA-Editor durch Drücken von Alt+F11. Wählen Sie Ihre Arbeitsmappe aus und klicken Sie auf Einfügen → Modul. Fügen Sie anschließend das folgende Makro ein und führen Sie es aus, indem Sie F5 drücken:

  DeleteCustomStyles() entfernt alle benutzerdefinierten Zellformate aus Ihrer Arbeitsmappe.

  Sub DeleteCustomStyles()
  Dim st As Style
  Dim i, i_cust As Long
  i_cust = 0
  For i = (ActiveWorkbook.Styles.Count) To 1 Step -1
  	With ActiveWorkbook.Styles(i)
  		If .BuiltIn = False Then
  			On Error Resume Next
  			.Locked = False
  			On Error GoTo 0
  			If Not .Locked Then
  				.Delete
  				i_cust = i_cust + 1
  			End If
  		End If
  	End With
  	If (i Mod 100) = 0 Then Debug.Print i
  Next
  MsgBox "Styles deleted: " & i_cust & " styles" & Chr(13) & "Styles left: " & ActiveWorkbook.Styles.Count
  End Sub

  Remove_Styles() entfernt lediglich nicht verwendete Zellstile aus Ihrer Arbeitsmappe. Das Makro ähnelt dem Beispiel oben, die Ausführung kann jedoch viel Zeit in Anspruch nehmen. Es wurde vom MS Office-Support bereitgestellt.

  Option Explicit
  Dim st_array() As String
  Dim i_x As Long
  Sub Remove_Styles()
  Dim stname As String
  Dim ustname As String
  Dim uc As Range
  Dim retval As Boolean
  Dim ust As Style
  Dim sh As Worksheet
  i_x = 0
  For Each sh In ActiveWorkbook.Worksheets
      For Each uc In sh.UsedRange
          stname = uc.Style.Name
          retval = Check_Array(stname)
          If retval = False Then
          ReDim Preserve st_array(i_x)
          st_array(i_x) = stname
          i_x = i_x + 1
          End If
      Next uc
  Next sh
  For Each ust In ActiveWorkbook.Styles
      If ust.BuiltIn = False Then
          ustname = ust.Name
          retval = Delete_Styles(ustname)
          On Error Resume Next
          If retval = True Then ust.Delete
          On Error GoTo 0
      End If
  Next ust
  End Sub
  Function Delete_Styles(stylename As String) As Boolean
  Delete_Styles = True
  Dim i_y As Long
  For i_y = 0 To i_x - 1
      If st_array(i_y) = stylename Then Delete_Styles = False
  Next i_y
  End Function
  Function Check_Array(stylename As String) As Boolean
  Check_Array = False
  Dim i_y As Long
  For i_y = 0 To i_x - 1
      If st_array(i_y) = stylename Then Check_Array = True
  Next i_y
  End Function

Die beste Lösung wäre, das Einfügen nicht verwendeter Zellenformatvorlagen zu verhindern, aber dafür muss Microsoft einen Bug in Excel 2007 oder höher beheben. Bislang meint Microsoft, dass der Fehler nicht schwerwiegend genug ist, um eine Lösung zu entwickeln. Wenn Ihr Unternehmen mit Microsoft einen Servicevertrag für den Office-Support abgeschlossen hat und Sie sich wegen einer Lösung an Microsoft wenden möchten, geben Sie bitte folgende Microsoft-interne Fallnummer an: 113012810172527.

Erklärung

Einige XLSX-Arbeitsmappen enthalten aufgrund von Fehlern und eines problematischen Code-Designs in Excel eine große Zahl an Zellenformatvorlagen (einige Zehntausend). Ein weiterer Faktor können Berichterstellungstools sein, die manchmal unnötig viele Zellenformatvorlagen erzeugen.