Autor: Roman

Makro im Hintergrund abspielen (Application.ScreenUpdating)

Roman VBA (Makros) , , , , , ,

Sobald ein Makro andere Tabellen öffnen und viele Befehle ausführen soll, eignet es sich, dieses im Hintergrund abzuspielen. Dadurch sieht der Benutzer nur das fertige Resultat, nicht jedoch die Einzelschritte im Schnelldurchlauf, was vorwiegend wie ein "Flackern" des Bildschirms aussieht. Um diese Hintergrundverarbeitung zu aktivieren, benötigt man lediglich zwei Codezeilen: Zu Beginn schaltet man die Sichtbarkeit aus [False], am Ende des Makros oder eines beliebigen Code-Abschnitts aktiviert man sie wieder [True]:

Sub MeinMakro()

Application.ScreenUpdating = False

...

...

Application.ScreenUpdating = True

End Sub

Dadurch, dass das Makro im Hintergrund verarbeitet wird und man die einzelnen Verarbeitungsschritte nicht sieht, ist das Programm grundsätzlich auch schneller fertig ausgeführt.

Verweis (SVERWEIS) mit mehreren Suchkriterien erstellen

Roman Formeln , , , , , , , , , , , ,

Wir hatten schon einmal über den "SVERWEIS" nach links, respektive den "WVERWEIS" nach oben gesprochen. Den Beitrag dazu findest Du unter diesem Link. Mittels den dort verwendeten Formeln können wir auch einen Verweis mit mehreren Suchkriterien erstellen. Das eignet sich insbesondere bei dynamischen Berichten. Schauen wir uns ein Beispiel und die zu verwendende Tabelle an:

WechselkursTabelle1

Unser Verweis soll uns in der Zelle "E3" einen Kurswert ausgeben, der zwei Kriterien entspricht: Der Währung und dem Monat; Entsprechend dieser beiden Selektionen soll in der Matrix unterhalb der Wechselkurs ermittelt werden. Die beispielhaft verwendeten Kurse zum Schweizer Franken stammen aus einem originellen Tool von www.oanda.com.

Als Grundgerüst verwenden wir die eingangs verlinkte Formel "=VERWEIS" und "=VERGLEICH" in Kombination. Die komplette Formel, um unseren oben definierten Wechselkurs ausgeben zu können, lautet:

=INDEX(C6:G8;VERGLEICH(C3;B6:B8;0);VERGLEICH(D3;C5:G5;0))

... und nun zur Formel selbst und deren Bestandteile: Die erste Angabe (C6:G8) ist unserer Bereich mit den Wechselkursen - dieser beinhaltet alle möglichen Resultate unserer Suche. Das heisst, ein Wert aus diesem Bereich wird schlussendlich von unserer Formel in "E3" ausgegeben. Die Index-Formel verlangt daraufhin nach der "Zeile", also in welcher Zeile des Bereichs/Arrays sich der Wert befindet. Diese müssen wir allerdings erst noch ermitteln - es könnte ja einer von drei Währungen sein. Deshalb stellen wir einen "VERGLEICH" von "C3" gegenüber den Möglichkeiten innerhalb von "B6:B8" her. Ein zweiter Vergleich wird für die Monate durchgeführt, womit die korrekte Spalte ermittelt wird. Der Suchablauf verläuft wie folgt:

WechselkursTabelleVorgehen1

Strukturieren wir unsere Basis-Tabelle etwas um, kommen wir mit der obigen Formel leider nicht ganz ans Ziel. Dies ist die neue Ausgangslage:

WechselkursTabelle2

Unsere beiden Suchkriterien bleiben zwar dieselben, sie stehen nun aber nicht mehr in Spalte und Zeile, sondern nur noch in nebeneinanderliegenden Spalten. Unser Verweis muss deshalb so abgeändert werden, dass zuerst nach der korrekten Währung und dann zudem nach dem entsprechenden Monat gesucht wird. In der VERGLEICH-Formel werden dann die einzelnen Suchkriterien mittels Kaufmännischem-Und (&) aneinandergereiht ("C3&D3"). Dasselbe wird daraufhin für jeden entsprechenden Suchbereich gemacht ("B6:B20&C6:C20"). Die Reihenfolge der Suchkriterien spielt keine Rolle, sie muss nicht zwingend der Sequenz unserer obigen Definition entsprechen. Dies ist die komplette Formel in Zelle "E3":

{=INDEX(D6:D20;VERGLEICH(C3&D3;B6:B20&C6:C20;0))}

Fällt Dir etwas auf? Genau! Die geschweiften Klammern weisen auf eine sogenannte Matrixformel hin. Speziell daran ist, dass diese nicht selbst eingeben werden, sondern die Formel (ohne geschweifgten Klammern) wird mittels CTRL+Shift+Enter bestätigt! Der Suchvorgang in Matrixformel entspricht dem Iterationsverfahren - es wird zuerst das erste Kriterium (USD) überprüft, dann wird geschaut, ob auf der dazugehörigen Zeile auch der korrekte Monat steht. Falls nein, wird weiter in der ersten Suchspalte nach "USD" gesucht, usw. Das nachfolgende Bild veranschaulicht diesen Ablauf:

WechselkursTabelleVorgehen2

Je nach Datenstruktur kann also eine unterschiedliche Formel zum Zuge kommen - es lohnt sich auf jeden Fall, beide dieser Arten zu verstehen. (P.S.: Zum selben Ergebnis wie mit der vorgestellten Matrixformel würdest Du in diesem Fall auch mittels "=SUMMEWENNS" gelangen. Die komplette Formel lautet: "=SUMMEWENNS(D6:D20;B6:B20;C3;C6:C20;D3)")

Tastenkombinationen in VBA

Roman Allgemein, VBA (Makros) , , , ,

Tastenkombinationen zur Navigation in Excel haben wir bereits behandelt. Dieser Beitrag befasst sich nun ausschliesslich mit Tastatur-Kürzel in der Makro-Umgebung (VBA).

  • Den VBA-Editor öffnest Du ganz allgemein mittels Alt + F11 aus Excel (auch beispielsweise in Microsoft Word).
  • Während Du auf einer Code-Zeile einen Befehl eingibst, kannst Du diesen mittels CTRL + Leertaste vervollständigen. Als Beispiel: Appl wird als Application ausgeschrieben und vervollständigt. Du kannst Dir diese Methoden/Befehle auch via CTRL + J aufrufen, ein Wort tippen oder mit den Pfeiltasten suchen und mittels Enter oder Tabulator für Deinen Code verwenden.
  • Mittels F2 rufst Du den vollumfänglichen Objektkatalog auf, mit F7 gelangst Du wieder zu Deinem Code zurück.
  • Für die oben eingefügten Befehle und Objekte kannst Du mittels CTRL + I die Kurzinfo ("gelbe Beschreibung") wieder einblenden lassen.
  • Deinen Code kannst Du mit F8 Schritt für Schritt ausführen lassen.
  • Mittels F9 kannst Du bei der aktuellen Cursor-Position einen Haltepunkt einfügen - beim Ausführen des Programms (mit F5) wird an jener spezifischen Position unterbrochen, damit Du zum Beispiel den aktuellen Stand der Dinge überprüfen und analysieren kannst. Drückst Du erneut F9 wird der Haltepunkt wiederum entfernt.
  • Anstelle von Haltepunkten kannst Du auch CTRL+F8 drücken, dann wird das Makro bis zur aktuellen Cursor-Position ausgeführt. (Ich empfehle jedoch, da es deutlich übersichtlicher ist, mit Haltepunkten zu arbeiten!)
  • Den Einzug kannst Du mittels Tabulator vergrössern. Du kannst so auch zuerst ganze Blöcke selektieren und dann Tabulator drücken, um mehrere Zeilen gleichzeitig einzuziehen.
  • Mit Shift+Tabulator hingegen verkleinerst Du den Einzug des Selektionsbereiches.
  • Wie auch in Excel kannst Du mit der Kombination CTRL+F nach bestimmten Begriffen suchen; mit CTRL+H kannst Du einzelne Befehle oder ganzen Code suchen und ersetzen.