If-Then

Live-Suche in der Excel-Suchmaschine

Roman VBA (Makros) , , , , , , , , , , ,

Wie Du in Excel eine simple Suchmaschine programmieren und einrichten kannst, wurde in einem älteren Beitrag (siehe Suchmaschine programmieren) illustriert. Hier wird nun ergänzt, wie Du eine Live-Suche etablierst, damit direkt nach Eingabe eines Suchkriteriums die Liste auf die entsprechenden Einträge gefiltert wird.

Nehmen wir nach wie vor an, dass in der Zelle "B1" das Suchkriterium steht. Falls sich also diese Zelle ändert, hat die Suchmaschine direkt zu reagieren. Das Makro aus dem Beitrag "Suchmaschine programmieren" ist hierzu nicht in einem Modul abzulegen, sondern direkt im entsprechenden Tabellenblatt (=Tabelle1 (Suchmaschine))!

vbaprojekttabelle

Zudem ist der Code leicht anzupassen, respektive  um nachfolgende zusätzliche Angaben zu ergänzen. Der vollständige Code wird dann weiter unten aufgeführt.

Anstelle von "Sub Suchmaschine()" zu Beginn hat die erste Codezeile zu lauten:

Private Sub worksheet_change(ByVal target As Range)

Zudem ist eine Wenn-Dann-Kondition einzubauen, die auf die Zelle "B1" verweist - und eben beim Anpassen dieser Zelle das Makro automatisch auslöst.

If target.Row = 1 And target.Column = 2 Then

Darauf folgt der gesamte Code der Suchmaschine, abgeschlossen (vor "End Sub") mit dem Befehl:

End If

Der vollständige Code ...

... (nicht in Modul1 sondern im Tabellenblatt direkt, wie oben erwähnt) lautet demnach:

Private Sub worksheet_change(ByVal target As Range)

If target.Row = 1 And target.Column = 2 Then

Range("D2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=4, Criteria1:="<>0"

AnzahlTreffer = Application.WorksheetFunction.Subtotal(3, Range("A:A")) - 2

MsgBox ("Insgesamt " & AnzahlTreffer & " Treffer gefunden.")

End If

End Sub

Die Eingabe des Platzhalters/Sternchens (*) erübrigen

Beim Programmieren der Suchmaschine habe ich Dir aufgezeigt, dass das Suchkriterium jeweils mit einem Sternchen vor- und nachher versehen werden sollte - beispielsweise "*Pop*". Um diesem vorzubeugen (es ist also nur noch der Begriff einzugeben, ohne Platzhalter-Symbol), ist die WVERWEIS-Formel minim anzupassen. Wir kombinieren jeweils den Text "*" mit dem Zellbezug auf das Suchkriterium in "B1". Für die Zeile 3 lautet die Formel wie folgt (und kann auf sämtliche weiteren Zeilen herunterkopiert werden):

=WENNFEHLER(WVERWEIS("*"&$B$1&"*";A3:C3;1;FALSCH);0)

GoTo VBA-Befehl

Roman VBA (Makros) , , , , , , , , , ,

Mittels dem VBA-Befehl "GoTo" kann in einen beliebigen Bereich des Codes gesprungen werden. Dieser Beitrag zeigt ein Beispiel auf, wie dies funktioniert.

An einer Stelle im Code erfolgt der Befehl "GoTo Zielbereich" und an einem beliebigen anderen Ort steht "Zielbereich:" gefolgt von den entsprechend auszuführenden Befehlen:

Sub MeinGoToMakro()
...
GoTo Zielbereich
...
Zielbereich:
Hier auszuführender Code
...
End Sub

Schauen wir uns ein konkretes Beispiel an: Angenommen das Programm soll zwei mögliche Prozeduren vornehmen - entweder die Befehle in Sektion1 oder diejenigen in Sektion2. Je nach dem, welcher Zelleninhalt in "B1" ist, soll Sektion1 oder Sektion2 ausgeführt werden. In diesem Fall wird das Programm einfach die entsprechende Sektion mit ".Select" auswählen. Meine Exceltabelle sieht wie folgt aus:

GoToSheet

Und der vollständige Code in VBA dazu lautet:

Sub GoToMacro()

If Range("B1") = 1 Then
Range("A5").Select
GoTo Ende
Else: GoTo Sektion2
End If

Sektion2:
Range("A9").Select

Ende:
End Sub

Es gibt zwei Aspekte, die ich genauer hervorheben möchte: Der Doppelpunkt hinter der jeweiligen Sektion ("Sektion2:" und "Ende:") ist zwingend notwendig. Zudem ist der Sprung zu eben diesem "Ende:" in unserem obigen Programm wichtig, damit die Befehle in Sektion2 im Falle von B1 = 1 gar nicht erst ausgeführt werden. Da ganze Teile im Code je nach Szenario übersprungen werden, wird die Ausführungszeit des Makros im Generellen beschleunigt.

"For"-Loop in VBA

Roman VBA (Makros) , , , , , , , , , , ,

Bei Makros kann man jeden Befehl einzeln programmieren oder aufzeichnen. In einer ersten Einführung in die VBA-Umgebung habe ich Dir gezeigt, wie Du mit dem Makro-Rekorder arbeiten kannst und schnell zu mehr Code kommst. Sich wiederholende Befehle kann man selbstverständlich alle einzeln in Code schreiben. Es ist jedoch deutlich übersichtlicher, wenn Du Wiederholungen mit sogenannten Loops (Schleifen) programmierst. In diesem Beitrag möchte ich Dir die "For"-Schleife zeigen.

Mit diesem Loop kannst Du einen Befehl x-beliebig ausführen lassen. Mit dem nachfolgenden Code wird im Bereich A1:A10 jeweils "Mein erster Loop" geschrieben:

For x = 1 To 10
Cells(x, 1) = "Mein erster Loop"
Next

Betrachten wir die einzelnen Bestandteile genauer: Der Loop beginnt mit "For". Danach verwenden wir X als Variable, beginnend bei 1 bis 10. In der zweiten Code-Zeile verwenden wir "Cells" - also Zelle. In Zelle "x, 1" soll unser Text geschrieben werden. Wichtig zu beachten ist, dass in "Cells" die Reihenfolge gegenüber Excel umgekehrt ist - es beginnt hier mit der Zeile (Row), danach wird erst nach der Spalte (Column) gefragt. In Excel ist zuerst die Spalte und dann die Zeile anzugeben (beispielsweise A1). Der Befehl "Next" erweitert das X um eins und die Schlaufe wird erneut durchlaufen. Probier es mal aus und führe Dein Makro mit F8 Schritt für Schritt aus. Du kannst mit dem Cursor auf X bleiben, dann zeigt es Dir jeweils den aktuellen Wert der Variable an. Zu Beginn ist dies "x = Leer", danach nimmt es die Werte 1 bis 10 an.

Weitere Anwendungsbereiche

Abhängig von Deinem spezifischen Makro kann diese For-Schleife in unterschiedlicher Form zur Anwendung kommen. Im ersten Beispiel oben hast Du Dich auf die Zeile bezogen. Dasselbe funktioniert natürlich auch mit der Spalte [Cells(1, x)]. Eine weitere Möglichkeit besteht darin, eine Zelle auf dessen Wert zu überprüfen. In der nachfolgenden Datenbank soll das Geschlecht jeweils ausgeschrieben werden (W = Weiblich; M = Männlich).

For-Schleife

Dazu schreiben wir folgenden Code:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Cells(x, 2) = "Männlich"
End If
If Cells(x, 1) = "W" Then
Cells(x, 2) = "Weiblich"
End If
Next

Die For-Schleife beginnt diesmal bei X = 2. Danach findet eine Überprüfung mittels "If-Then" statt. Pro Durchlauf habe ich jeweils nach "M" und anschliessend nach "W" gesucht - einfacher wäre die Verwendung von "If-Then-Else", also "Wenn-Dann-Sonst". Ich decke diesen Fall nicht in diesem Beitrag ab, Du kannst das aber gerne selbst ausprobieren!

Den letzten Fall, den ich noch aufzeigen möchte, ist das Entfernen von Zeilen. Nehmen wir an, wir möchten nur die weiblichen Personen angezeigt haben. Unsere Überprüfung soll also nach "M" suchen und in dem Fall die gesamte Zeile entfernen. Der Code dazu lautet wie folgt:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Rows(Cells(x, 1).Row).Delete
x = x - 1
End If
Next

Der Check in den ersten beiden Zeilen bleibt gleich wie im oberen Beispiel. Danach wird die Zeile der Zelle "x, 1" entfernt. Da sämtliche unteren Zeilen nach oben rücken, müssen wir in dem Fall x unbedingt wieder um 1 zurücksetzen, ansonsten werden einzelne Zeilen ungewollt übersprungen. Je nach dem, wie Deine Daten angeordnet sind, kannst Du mit "x - 1" oder "x + y" y-beliebig viele Zeilen überspringen, bevor Dein Loop wieder zur Anwendung kommt. Deine Variable in der For-Schleife bezieht sich dann nicht auf die Zeile selbst, sondern auf den Fall Nummer x. Dein Loop verarbeitet somit x-beliebige Fälle - und den Code dazu brauchst Du nur ein einziges Mal zu erfassen.

Natürlich könnten sämtliche dieser Befehle leicht in Excel mittels Formel "WENN", dem Setzen eines Filters und dem Löschen der jeweiligen Zeilen vorgenommen werden. Falls es sich aber um eine grosse Tabelle handelt, oder ein wiederkehrender Prozess vorliegt, kann das Makro abgespeichert und jederzeit aufgerufen werden - es lediglich ein initialer Aufwand zur Programmierung notwendig, danach entstehen erhebliche Zeitgewinne und das Fehlerpotenzial wird minimiert (sofern alles korrekt programmiert wurde).