Microsoft Excel – AdvancedFilter in VBA

Wer wie wir regelmäßig hochkomplexe und tiefgreifende VBA-Entwicklungen für Kunden durchführt, wird gerade bei Versionswechseln von Microsoft Office regelmäßig mit den Unzulänglichkeiten einiger Änderungen konfrontiert, die einen meist nur noch fragend zurücklassen.

So ist es uns erst in dieser Woche bei einem unserer Office-basierten Finanzprogramme geschehen, das einer unserer Kunden seit 20 Jahren intern einsetzt und das wir um einige Funktionen ergänzt haben.

In diesem speziellen Fall handelt es sich um den sogenannten “Spezialfilter” von Microsoft Excel, der es erlaubt, eine Liste nach bestimmten Suchkriterien zu filtern und die Ergebnisse – sofern gewünscht – in eine andere Tabelle zu schreiben.
Der Spezialfilter wird über “Daten – Sortieren und filtern – Erweitert” aufgerufen und besitzt folgendes Dialogfeld:

Details zu dieser Funktion sowie deren Nutzung liefert Microsoft unter “Filtern mit erweiterten Kriterien”.

Gerade beim Kriterien-Vergleich mit Datumswerten beweist bewies Microsoft Excel bislang seine Qualitäten.

Hierfür legt man im sog. Kriterienbereich einen Zellbereich fest, in dem die bestimmten Kriterien festgelegt sind, die zur Filterung der Liste herangezogen werden sollen.

Kurzes Beispiel zur Veranschaulichung

Angenommen, wir haben eine Liste von mehreren Mitarbeitern mit deren Geburtstagen und weiteren Angaben wie Vorname, Name etc..
Jetzt möchte man aus dieser Liste jene Mitarbeiter filtern, deren Geburtstag vor oder gleich dem 02.12.1980 ist und daraus eine neue Liste erstellen, weil man diese beispielsweise mittels einer Serien-E-Mail anschreiben möchte.

Zugegeben, das kann man mit anderen Funktionen unter Microsoft Excel viel einfacher lösen, aber für die Veranschaulichung des Problems wollen wir es hierbei belassen.

Definiert man im Kriterienbereich in der Spalte “Geburtstag” das korrekt formulierte Kriterium “=”<=02.12.1980”” und führt die Funktion über den oben beschriebenen Funktionsaufruf manuell aus, so wird – unabhängig von der Microsoft Excel Version – bis heute das korrekte Ergebnis in einer neuen Liste geliefert.
Super!

Tja, aber nur bei manueller Ausführung

Bis zur Version Microsoft Office 2007 funktionierte diese Variante auch absolut zuverlässig unter VBA (Visual Basic for Applications), bis …. ja bis…. Microsoft Office 2010 erschien.
Denn – um es vorweg zu nehmen – seit dieser Version ist es nicht mehr möglich, deutsche Datumswerte im Kriterienbereich zu verwenden (wohlgemerkt, wir sprechen ab jetzt von der VBA Programmierung!).

Zeichnet man die manuelle Ausführung unter Microsoft Excel als Makro auf, so wird einem auch die korrekte Funktion als Makro geliefert (hier “AdvancedFilter” am Beispiel von Office 2013).
Führt man die soeben aufgezeichnete Funktion dann erneut als Makro aus, so liefert der bislang noch erfolgreich manuell durchgeführte Listenauszug plötzlich keine Ergebnisse mehr.
Selbst eine Fehlermeldung oder einen Hinweis verweigert Microsoft Office.

Der Fehler liegt am deutschen Datumsformat

Das Problem stellen die deutschen Datumswerte im Kriterienbereich dar, mit denen Microsoft Excel offensichtlich ab Version 2007 nichts mehr anfangen kann.
Dieser Umstand ist sehr widersprüchlich, zumal andere Funktionen unter Microsoft Excel – auch auf VBA Programmier-Ebene – deutsche Datumsformaten problemlos verarbeiten kann (logisch, denn nahezu alle User in Deutschland verwenden auch deutsche Datumsformate).

Doch es gibt eine Lösung!

Um die Funktion “AdvancedFilter” korrekt unter VBA ausführen zu können, müssen lediglich sämtliche Datumswerte im Kriterienbereich (und nur dort!) in das us-amerikanische Datumsformat umgewandelt werden.

[bctt tweet="Um den AdvancedFilter in Microsoft Excel per VBA \nkorrekt auszuführen, bedarf es eines Tricks." username="hdcnews"]

Die Datumswerte im Listenbereich (also in unserem Beispiel die ursprüngliche Liste der Mitarbeiter) dürfen hingegen im deutschen Datumsformat bestehen bleiben.

Bitte beachten:
Wenn ihr die Datumswerte im Kriterienbereich ebenfalls über VBA generiert, nützt es nichts, die Datumswerte mittels FORMAT(datwert, “MM/DD/YYYY”) zu formatieren!
Microsoft Excel wandelt diesen Datumswert intern wieder in ein deutsches Datumsformat zurück.
Ihr müsst also zwingend den Datumswert in einen String in Form eines us-amerikanischen Datumsformats umwandeln.

Warum dieser (offensichtliche) Fehler bislang nicht – auch nicht unter Microsoft Office 2013 – behoben wurde, entzieht sich unserer Kenntnis.
Er bedarf aber einer dringenden Behebung, denn er liefert bislang eindeutig falsche Ergebnisse – denn eine leere Liste aufgrund dieses Fehlers ist defakto falsch.
Erst recht, wenn keine Fehler- bzw. keine Hinweismeldung auf diesen Umstand hinweist.

Ich hoffe, diese Beschreibung unterstützt Euch bei der Fehlersuche in Eurer VBA-Programmierung und reduziert Eure Verzweiflung – zumindest in diesem Fall – auf ein akzeptables Niveau.

Viel Spaß Euch allen “da draußen”,
Eric