If-Statement mit Platzhalter & Like-Operator (VBA)

Bei einer Suche - beispielsweise auf Google - kann mit Platzhaltern gearbeitet werden. Das Fragezeichen (?) ersetzt dabei ein einzelnes Zeichen, das Sternchen (*) ist ein Platzhalter/eine Wildcard für eine beliebige Zeichenfolge vor, innerhalb oder nach dem Suchbegriff. Verwendet man in VBA ein If-Statement, können ebenfalls solche Platzhalter zur Anwendung gebracht werden. Nehmen wir an, basierend auf einem User-Input im Format "Qx-JAHR" (bspw. Q2-2019) soll eine bestimmte Prozedur für eben dieses Quartal gesteuert werden. Normalerweise ist ein If-Statement oder besser gesagt ein If-Then-Else-Statement wie folgt aufgebaut:

If MeinBegriff = "Q2-2019" Then
Weiterer Code für diesen Fall

ElseIf Weitere Prüfungen
Weiterer Code

End If

Im obigen Fall wird Bei "MeinBegriff" handelt es sich um eine Variable, die vorhin in der Prozedur initialisiert wird. Sie kann das Resultat eines User-Inputs sein oder sich auf einen bestimmten Zellwert beziehen. Generell gilt jedoch: Da sich die Jahreszahl über die Dauer verändern wird, benötigen wir eine dynamische Variante mit Platzhaltern im Format "Q2-20*". Das Gleichheitszeichen (=) wird Operator genannt. Weitere Möglichkeiten sind "<>", ">" oder "<=" etc. Desweiteren gibt es den "Like"-Operator, den wir in diesem Fall verwenden.

Der Code für ein If-Statement mit "Like-Operator" lautet:

If MeinBegriff Like "Q1-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q2-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q3-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q4-20*" Then
Weiterer Code für diesen Fall

End If

Wenn der Wert der Variable "MeinBegriff" also ähnlich (like) ist, wird der entsprechende Fall ausgeübt.

Eigene Funktionen erstellen (Meter in Meilen & Celsius in Fahrenheit)

Eine Vielzahl an Funktionen und Formeln sind bereits standardmässig in Excel verfügbar. Dem Standarduser genügt dieses Angebot grundsätzlich, doch können auch da eigene Funktionen den Umgang mit Excel erleichtern. Dieser Blog zeigt auf, wie benutzerdefinierte Funktionen und Formeln angelegt werden können, die danach jederzeit verfügbar sind. Als Beispiel werden wir hier Meter in Meilen sowie Grad Celsius in Fahrenheit umwandeln.

Meter in Meilen

Die erste Funktion, die angelegt werden soll, ist die Umwandlung von Meter in Meilen (eine englische Meile beträgt 1'609.344 Meter). Öffne hierzu eine neue Arbeitsmappe und gehe in die VBA-Umgebung (Alt + F11 oder Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite befindet sich die Projekt-Übersicht - dort sind alle geöffneten Excel-Dateien zu sehen. Mittels Rechtsklick auf die neue Arbeitsmappe lässt sich ein Modul einfügen, wo die Funktion eingepflegt werden kann:

NeuesModulEinfuegen

Im angelegten Modul1 wird nun die Funktion eingetragen. Für die Umrechnung von Meter in Meilen gilt folgender Code:

Public Function MeterInMeilen(ByVal X As Double)

MeterInMeilen = X / 1609.344

End Function

Die Formel nennen wir "MeterInMeilen". Achte bei der Vergabe der Namen darauf, dass diese nicht bereits vorhanden sind. In unserer Excel-Datei kann alsdann mit der Umwandlung begonnen werden. Die Funktion findet sich unter sämtlichen anderen - gebe in Excel einfach "=M" ein und die Vorschläge erscheinen in einer Auswahlliste, dort ist auch "MeterInMeilen" aufgeführt:

EigeneFunktionenAuswahl

Um die Umwandlung der Massangaben zu veranschaulichen, habe ich eine kleine Tabelle angelegt:

MeterInMeilen

In Zelle "B3" sind 1000 Meter eingetragen, dieser Wert kann selbst gewählt werden. In Zelle "C3" kommt unsere benutzerdefinierte Funktion zum Einsatz. Füge da nun die folgende Formel ein:

=MeterInMeilen(B3)

Ein simpler Bezug auf die Zelle "B3" reicht bereits aus, um die Meterangabe in Meilen zu konvertieren. Uns interessiert zudem, wie wir die Rückkonvertierung vornehmen können. Dazu wird folgender Programmcode eingetragen, um die Funktion "MeilenInMeter" anzulegen:

Public Function MeilenInMeter(ByVal X As Double)

MeilenInMeter = X * 1609.344

End Function

Diese Zeilen können direkt unterhalb von "End Function" der ersten Funktion in Modul1 eingetragen werden. In der Excel-Beispieltabelle kann für die Meter-Rückkonvertierung die neue Formel eingegeben werden:

=MeilenInMeter(C3)

Das Resultat davon ist wiederum exakt 1'000 Meter.

Celsius in Fahrenheit

Analog der Konvertierung oben soll nun zudem Grad Celsius in Fahrenheit umgewandelt werden können. Folgender Code erfüllt diese Anforderung:

Public Function CelsiusInFahrenheit(ByVal X As Double)

CelsiusInFahrenheit= X * 9 / 5 + 32

End Function

Wiederum als Beispieltabelle in Excel wird in Spalte C die Berechnung von diversen Temperaturangaben vorgenommen.

CelsiusInFahrenheit

Die angegebenen Werte sind korrekt, man könnte zudem die Dezimalstellen einblenden (beispielsweise 22 Grad Celsius entsprechen 71.6 Grad Fahrenheit). Die angelegte Funktion kann damit ebenfalls umgehen, da der Variablentyp "Double" gewählt wurde.

Auf die Rückkonvertierung wird hier nicht eingegangen, wir lassen dies als mögliche "Übungsaufgabe" offen. Bei Fragen kann stets die Kommentar-Sektion verwendet werden.

Zuletzt ist anzumerken, dass neben komplett neuen Funktionen wie den oben gezeigten Umwandlungs-Formeln auch bestehende Standard-Formeln individuell optimiert werden könnten. Ein Beispiel ist der SVERWEIS, bei dem Benutzer je nach dem immer wieder Probleme bekunden - bei dem Verweis gibt es diverse Fehlerursachen. Mit einer eigenen Funktion (z.B. analog der INDEX-VERGLEICH-Kombination) kann der SVERWEIS benutzerfreundlicher gestaltet werden und zugleich auch Daten links von dem Suchkriterium ausgeben.

Eigene Icons/Symbole zum Menüband hinzufügen

Wie man das Menüband individuell gestalten und um eigene Register und Sektionen erweitert kann, wurde vor einiger Zeit im Beitrag "Makro als Add-In einbetten" illustriert. Für die gewünschten Funktionen gibt es diverse Standard-Symbole, die man verwenden kann. Dies ist eine Übersicht:

StandardIcons

Je nach Anwendungsfall lässt sich jedoch kein passendes Bild finden - oder man strebt grundsätzlich eine komplett eigene Lösung an. Hier wird dargestellt, wie ein selbst erstelltes Symbol eingefügt werden kann: Die Bilddatei sollte im Format .png und quadratisch sein. Da es kleine Icons sind, sollten die Masse nicht über 256x256 Pixel sein. Ich habe ein Bild in Photoshop mit den Massen 100x100 Pixel erstellt und lokal abgespeichert:

ArrowPlus10

Die Funktion, die über dieses Symbol abgerufen werden soll, ist eine Erhöhung des selektierten Wertes um 10%. Um dieses Beispiel zu illustrieren, habe ich eine simple Umsatzplanung für das nächste Jahr erstellt:

Umsatzplanung2020

Es soll über die angesprochene Funktion entweder die aktuelle Menge oder der jetzige Preis um 10% erhöht werden, da sich der Umsatz ohnehin aus Preis * Menge zusammensetzt. Um dies zu bewerkstelligen, legst Du folgendes Makro an:

Sub IncreaseBy10Percent()

Dim CurrentValue As Double
Dim NewValue As Double

CurrentValue = ActiveCell.Value
NewValue = CurrentValue * 1.1
ActiveCell = NewValue

End Sub

(Beachte: Es sind keinerlei Kontrollen und Sicherheiten eingebaut. Es wird nicht überprüft, welcher Wert in der selektierte Zelle steht oder ob gar ein Bereich ausgewählt wurde - das kann zu Fehlermeldungen führen!)

Office RibbonX Editor

Um das Custom-Icon einzufügen, verwenden wir die Software "Office RibbonX Editor" (analog "Custom UI Editor"). Es handelt sich dabei nicht um ein Microsoft Produkt. Fernando Andreu hat das Tool auf GitHub zur Verfügung gestellt (hier geht es zur Release-Seite).

Nach dem Öffnen der Datei ist das gewünschte Excel-File auszuwählen (File → Open). Daraufhin fügen wir - je nach Excel-Version - einen "Custom UI Part" ein (ich verwende "Office 2010+"). Dies erfolgt via Rechtsklick auf die Excel-Datei im Editor:

InsertCustomUiPart

Als nächstes ist die erstellte Grafik einzufügen. Gehe dazu auf "Insert Icons". Wie im Screenshot unten ersichtlich ist das Icon ebenfalls innerhalb des Editors auf der linken Seite (Navigation) aufgeführt. Der fertige Code, um das entsprechende Icon ("ArrowPlus10.png") in ein neues Menüband-Register "Eigene Makros" und der Sektion "Umsatzplanung" einzubetten, ist unten dargestellt. Am Ende dieses Beitrags ist zudem der Code als "Zitat" zu finden - für Copy+Paste-Zwecke. Nach einer Anpassung in dem Editor kannst Du jederzeit auf "Validate" klicken, um den Code zu testen. Speichere die Änderungen im Editor ab ("Save") und öffne das Excel-File erneut.

RibbonXCode

Die obigen Bezeichnungen wie "Menüband-Register" oder "Sektion" können individuell ausgestaltet werden. Auch die Anordnung/Reihenfolge des Menübands kann hier beeinflusst werden (in Zeile 4: "insertAfterMso="). Im fertigen Screenshot unten habe ich unser eigenes Register hinter "TabHome" eingefügt, damit es auf der Abbildung besser ersichtlich ist:

RibbonSolution

Ein Klick auf diesen individuell gestalteten "Button" erhöht daraufhin den selektierten Wert wie gewünscht um 10%.

Dies ist der Code für den "Custom UI Part":

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Eigene Makros" insertAfterMso="TabHelp">

<group id="customGroup" label="Umsatzplanung">
<button id="customButton1" label="Erhöhe Wert" size="large" onAction="IncreaseBy10Percent" image="ArrowPlus10" />

</group>
</tab>
</tabs>
</ribbon>
</customUI>