VBA für Faule: Excel-Makros und M-Abfragen sinnvoll kombiniert

2. Oktober 2018

Auor: Ignaz Schels jun.

Die Makro-Programmiersprache VBA ist besonders in Kombination mit Microsoft Excel ein mächtiges Werkzeug, um langwierige Aktionen zu automatisieren und bei komplexen Aufgaben viel Zeit zu sparen. Umso zeitaufwändiger kann dagegen aber das Erstellen des Makros sein. Doch auch hier kann man Zeit sparen: Seit einiger Zeit unterstützt Excel hocheffiziente Abfrage-Funktionen, die Sie auch in Ihren Makros nutzen können. Damit wird der Code nicht nur kürzer sondern auch effizienter.

Schon für Excel 2010 und 2013 gibt es das Add-In „Power Query“, mit dessen Hilfe Sie eine Vielzahl von Datenquellen „anzapfen“ und die enthaltenen Daten in eine Excel-Tabelle importieren können. Diese Funktionen sind bei Excel 2016 fest integriert und finden sich auf der Registerkarte „Daten“ unter der Rubrik „Daten abrufen und transformieren“. Wenn Sie sich noch nicht genauer mit diesen Werkzeugen auseinandergesetzt haben, ist es höchste Zeit: Sie können nicht nur einfache Abfragen auf externe Datenquellen erstellen, sondern dabei auch aufwendige Transformationen wie z.B. das Filtern, Sortieren oder Ersetzen bestimmter Bereiche vornehmen. Eine einmal erstellte Abfrage kann immer wieder verwendet werden. Auf diese Weise können typische Aufgaben wie das Öffnen einer Excel-Datei und das Kopieren eines Abschnitts einer Tabelle so automatisiert werden, dass sie mit nur einem Klick abgearbeitet werden – ganz so, als ob Sie ein VBA-Makro dafür geschrieben hätten.

Automatisierung auch ohne VBA

Tatsächlich kann man mit Abfragen viele Probleme lösen, für die man früher VBA verwendet hätte. Dabei ist eine Abfrage meistens auch noch deutlich schneller erstellt als ein Makro.

Ein Beispiel: Stellen Sie sich vor, in einer Excel-Datei befindet sich eine Liste mit Bestelldaten. Sie möchten die Bestellungen des aktuellen Monats eines bestimmten Kunden in einer eigenen Excel-Tabelle ausgeben. Keine schwere Aufgabe, dennoch müsste ein VBA-Makro einiges leisten: Das Öffnen und Schließen der Quelldatei, das Ermitteln der Zeilenanzahl, das Prüfen aller Zeilen auf Filter-Kriterien und das Kopieren der gewünschten Zeilen in die Ziel-Tabelle. Das Ganze muss natürlich von den üblichen Sicherheitsvorkehrungen begleitet werden: Was ist, wenn die Quelldatei bereits geöffnet ist, was wenn sie leer ist, was, wenn unerwartete Datentypen auftreten, usw., usw.…

Abbildung 1: Die Abfrage-Tools in Excel 2016

Anstatt ein VBA-Makro zu schreiben, können Sie in der neuesten Excel-Version dasselbe Ziel mit wenigen Klicks erreichen:

• Klicken Sie im Register „Daten“ auf die Schaltfläche „Daten abrufen“ und wählen Sie „Aus Datei – Aus Arbeitsmappe“.
• Wählen Sie die gewünschte Quelldatei und das Tabellenblatt
• Wenn keine Anpassungen an der Tabelle nötig sind, können Sie nun bereits auf „Laden“ klicken und sind schon fertig. Ansonsten klicken Sie auf „Bearbeiten“.
• Im Abfrage-Editor (bzw. Power Query-Editor) haben Sie jede Menge Werkzeuge zur Verfügung, um die Quell-Tabelle zu bearbeiten. Das Sortieren und Filtern erledigt zum Beispiel das kleine Pfeil-Symbol neben den Spalten-Überschriften.
  Das Besondere dabei: Jede Änderung, die Sie hier vornehmen, wird als sogenannter Transformationsschritt aufgezeichnet. Die aufgezeichneten Schritte werden beim nächsten Aktualisieren der Abfrage erneut abgearbeitet – auch bei geänderten Quelldaten.
• Klicken Sie „Schließen & laden“ um die Bearbeitung zu beenden und die Abfrage zu speichern.

Abbildung 2: Mit dem Power Query-Editor zeichnen Sie Transformations-Schritte auf, um die Tabelle automatisch anzupassen.

Das Ergebnis ist eine Tabelle, die die abgerufenen Daten enthält. Sie können sie jederzeit aktualisieren, wenn sich die Quelldaten geändert haben. Die aufgezeichneten Transformationsschritte, also z.B. das Filtern bestimmter Zeilen, werden dabei erneut ausgeführt.

Abfragen als Ergänzung zu VBA

Die Benutzeroberfläche des Abfrage-Editors bietet eine Vielzahl an Funktionen, um die Tabelle anzupassen. Vom Entfernen bestimmter Zeilen und Spalten über das Ersetzen von Werten bis hin zum Gruppieren, Pivotieren und Entpivotieren der ganzen Tabelle sind die gängigsten Problemstellungen abgedeckt. Für speziellere Anforderungen steht eine umfangreiche Abfragesprache zur Verfügung, die unter dem Kürzel „M“ bekannt ist. Hiermit ist fast jede erdenkliche Transformation der Datenbasis möglich.

Der Datenimport ist dabei keineswegs auf Excel-Dateien beschränkt. Auch Textdateien, Datenbanken, Cloud-Dienste und sogar Webseiten können als Datenquelle verwendet werden.

Trotz all dieser Features wäre es aber verfehlt, Power Query bzw. M als gleichwertigen Ersatz von VBA zu verstehen. Power Query ist sehr effektiv beim Einlesen und Verarbeiten von Daten, doch es stößt schnell an seine Grenzen, wenn es um die Benutzersteuerung oder die Visualisierung von Ergebnissen geht. Der Königsweg liegt daher in einer Kombination von M und VBA: Nutzen Sie beispielsweise eine Abfrage für das Einlesen von Daten. Mit Hilfe des Power Query-Editors können Sie die Datenmenge anpassen oder auf das Wesentliche reduzieren. Das Resultat der Abfrage ist stets eine einfache Tabelle. Diese können Sie im Anschluss per VBA einlesen, um die Ergebnisse anschaulicher zu präsentieren oder anderweitig umzusetzen.

Vergessen Sie jedoch nicht, in Ihrem VBA-Code die Abfrage zu aktualisieren, bevor auf die Tabelle zugegriffen wird. Nutzen Sie hierfür den folgenden Befehl:

Hiermit wird die erste Tabelle auf dem Blatt „Blatt1“ aktualisiert. Setzen Sie den Parameter „BackgroundQuery“ dabei auf „False“, um sicherzustellen, dass der VBA-Code erst fortgesetzt wird, sobald die Aktualisierung der Abfrage abgeschlossen ist. Andernfalls könnte es passieren, dass ihr VBA-Code auf veraltete Daten zugreift.

M-Code per VBA anpassen

Wer noch flexibler sein will, passt den starren M-Code direkt mit VBA an die jeweiligen Bedürfnisse an. Jede Abfrage, auch wenn Sie über die Schaltflächen des Abfrage-Editors erstellt wurde, basiert auf einem M-Code. Er ist als String in der Formula-Eigenschaft des Queries-Objekts hinterlegt. Der folgende VBA-Code gibt Ihnen beispielsweise den M-Code einer Abfrage aus:

Weitere Abfragen können Sie über die Anpassung des Index in Klammern ansteuern. Alternativ können Sie auch den Namen der Abfrage in den Klammern angeben:

Selbstverständlich kann der M-Code über die Formula-Eigenschaft nicht nur ausgelesen, sondern auch angepasst werden. Dies eröffnet viele Möglichkeiten. Da Abfragen in der Regel auf eine bestimmte Datenquelle verweisen, können Sie beispielsweise ganz einfach Adressen oder Dateipfade per VBA ersetzen. Auch eine flexible Steuerung einzelner Abfrage-Parameter durch den Benutzer ist denkbar.

Übrigens: Die Kombination von M und VBA erleichtert Ihnen nicht nur die Programmierarbeit, sondern macht Ihre Programme auch deutlich effizienter. M ist optimiert für den Zugriff auf externe Datenquellen. Daher können ohne viel Aufwand verschiedene Dateien, Datenbanken oder Web-Dienste auslesen und miteinander in Beziehung setzen. Die Ergebnisse erscheinen in der Regel in Sekundenschnelle. VBA kann dagegen für die Benutzersteuerung sowie die Ausgabe oder Umsetzung der Ergebnisse verwendet werden. Der Königsweg liegt also darin, die Stärken der jeweiligen Technologien für die erforderlichen Prozesse optimal zu nutzen.