Wenn Sie in Excel VBA Makros u.a. Pivottabellen bearbeiten, kann es zu versionsabhängigen Fehlern in der Skriptausführung kommen, falls Sie Datenfelder via PivotFields explizit mit Ihrem Datenfeld-Namen ansprechen. Ältere Skripte von 2007 oder 2010 laufen z.B. auf der neueren Excel Version 2013 dann nicht mehr.
Das folgende Beispiel stellen wir Ihnen hier zum Download bereit.
In dem Beispiel haben eine Tabelle mit ‚Name‘, ‚Wert‘ und ‚Datum‘. Sie erstellen z.B. manuell eine Pivottabelle, die wir ‚MeinePivotTabelle‘ nennen und verwenden ‚Wert’ als Datenfeld mit der Summenfunktion. ‚Name‘ und ‚Datum‘ sind in unserem Beispiel Spalten- bzw. Zeilenfelder.
Das Datenfeld, das aus Wert gebildet wird, heißt dann in Excel 2013 automatisch ‚Summe von Wert‘, da die Funktion Summe initial ausgewählt wird, bis Sie etwas anderes Angeben. Anmerkung: Das ist allerdings Sprachabhängig, bei Einstellung ‚English‘ würde das Feld automatisch von Excel ‚Sum of Wert‘ genannt werden.
Wenn Sie nun per Excel VBA Makro die Funktionszuweisung z.B. von ‚Summe‘ auf ‚Anzahl‘ umstellen wollen, geht man manchmal recht einfach so vor:
ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Summe von Wert").Function = xlCount
Da dieses Datenfeld manuell mit diesem Namen angelegt wurde, existiert es zunäcsht unabhängig von der vorliegenden Excel Version. Durch die Funktionszuweisung xlCount wird das Datenfeld allerdings abhängig von der Excel Version gleichzeitig umbenannt:
- Excel 2013 macht daraus: „Anzahl von Wert“
- Excel 2010 belässt es bei „Summe von Wert“
- Excel 2007 benennt ebenfalls um, jedoch „Anzahl – Wert“
Wollen Sie also an einer späteren Stelle im Skript nochmals auf diesen Datensatz zugreifen, müssten Sie eine Fallunterscheidung nach der verwendeten Excel Version durchführen. Wenn Sie z.B. an anderer Stelle doch noch einmal die Summe benötigen, wäre nun folgende Funktionszuweisung nötig:
- Excel 2013:
ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Anzahl von Wert").Function = xlSum
- Excel 2010:
ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Summe von Wert").Function = xlSum
- Excel 2007:
ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Anzahl - Wert").Function = xlSum
Das ursprüngliche Datenfeld "Summe von Wert" heißt nach beiden Operationen, die bzgl. der Berechnung wieder zum Ausgangspunkt zurückführen, dann allerdings so:
- Excel 2013: „Summe von Wert“
- Excel 2010: „Summe von Wert“
- Excel 2007: „Summe – Wert“
Diese Auto-Umbenennung führt mit diesem Vorgehen zu einigen Folgeprobleme, da Sie im Makro an den entsprechenden Stellen die Versionsunterschiede in der Benennung berücksichtigen müssen und bei häufigerem hin- und her schalten der Funktion recht schnell den Überblick verlieren können.
Leider haben Sie in Excel VBA nicht die Möglichkeit die Existenz eines PivotFields direkt abzufragen, z.B.
If ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Summe von Wert") is nothing then …
führt zu Makroabbruch und Fehlermeldung.
Sie können allerdings eine eigene Exists Funktion bauen, z.B.
Public Function PivotFieldExists(ByVal pt As PivotTable, ByVal PivotFieldName As Variant) As Boolean
On Error Resume Next
PivotFieldExists = Not pt.PivotFields(PivotFieldName) Is Nothing
End Function
Damit können Sie dann zumindest ohne Makro-Fehlerabbrüche die Existenz abfragen und dann darauf entsprechend zugreifen, z.B. mit
If PivotFieldExists(ActiveSheet.PivotTables("MeinePivotTabelle"), „Summe von Wert“) Then
ActiveSheet.PivotTables("MeinePivotTabelle").PivotFields("Summe von Wert").Function = xlCount
End if
Eine versionsunabhängige Lösung, die diese Probleme umgehen möchte, muss entsprechend ohne explizite Angabe des Datenfeld-Names auskommen und nur auf den ursprünglichen Quellnamen zugreifen, z.B. so:
For Each pf In ActiveSheet.PivotTables("MeinePivotTabelle").DataFields
If pf.SourceName = "Wert" Then pf.Function = xlSum
Next pf
Noch etwas eleganter finde ich persönlich folgendes Vorgehen:
Dim pf As PivotField
Set pf = GetPivotDataField(ActiveSheet.PivotTables("MeinePivotTabelle"), "Wert")
If Not pf Is Nothing Then pf.Function = xlSum
Oder verkürzt ohne Sicherheitsabfrage einfach:
GetPivotDataField(ActiveSheet.PivotTables("MeinePivotTabelle"), "Wert").Function = xlSum
Wobei diese Funktion GetPivotDataField die Lokalisierung des richtigen Datenfeldes kapselt:
Public Function GetPivotDataField (ByVal pt As PivotTable, ByVal SourceName As Variant) As PivotField
Dim pf As PivotField
For Each pf In ActiveSheet.PivotTables("MeinePivotTabelle").DataFields
If pf.SourceName = SourceName Then Exit For
Next pf
Set GetPivotDataField = pf
End Function
Ein weiterer Aspekt sollte noch berücksichtigt werden: Wenn Sie den gleichen Datensatz mehrfach in einer Pivottabelle als Datenfeld verwenden, entsteht beim manuellen Erstellen im Excel 2013 folgendes:
- „Summe von Wert“ und „Summe von Wert2“.
Nach manueller Umstellung der Funktion des zweiten Datensatzes haben Sie dann
- „Summe von Wert“ und „Anzahl von Wert2“
Im Excel Skript müssten Sie hernach die korrekte Identifikation des Datenfelds nicht nur die Versionsabhängigkeit der Auto-Funktionsbenennung betrachten, sondern darüber hinaus noch die Ergänzung des Namens um eine fortlaufende Nummer.
In der vorgestellten Lösung per SourceName wird über die Reihenfolge der Verwendung oder die Feststellung der gewählten Funktion differenziert, versionsUNabhängig und unkompliziert.
Wir freuen uns über Kommentare / Rückmeldungen oder alternative Ideen dazu. Das Beispiel haben wir für Sie unter Download bereitgestellt.
Falls Sie bei Ihrem Excel Makro Unterstützung brauchen, stehen wir Ihnen als Dienstleister ebenfalls gern zu Verfügung. Sprechen Sie uns einfach an.