Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel 2013 VBA für Profis

Tabellenfunktionen einsetzen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Die in Excel-Tabellen zur Verfügung stehenden Rechenfunktionen können Sie auch aus VBA heraus aufrufen.

Transkript

Eine Besonderheit in Excel VBA ist die Möglichkeit, die Tabellenfunktionen von Excel hier auch zu nutzen. Das ist allerdings recht gut versteckt, das kann man nicht anders sagen. Das mag das Objektmodell übersichtlicher halten, aber man muss erstmal drauf kommen, wo die stehen. Ich fange an mit einer einfachen Prozedur. Die soll einfach den Wert von Pi anzeigen. Pi gehört zu den Funktionen in Tabellen. Deswegen ist es hier das einfachste. Die anderen funktionieren technisch erstmal genauso. Mit Debug.Print will ich mir das Ergebnis unten im Direktfenster, im Direktbereich anzeigen lassen. Und diese Arbeitsblatt-Tabellenfunktionen, die verstecken sich unter Application.WorksheetFunction. Und nach dem Punkt stehen sie jetzt alle zur Verfügung. In diesem Fall ist Pi eine Funktion, die genau diese Zahl zurückgibt. Sie hat also keine Parameter. Und wenn ich das Ganze speichere und jetzt mit F5 starte, steht hier das, was ich mir bisher mühsam als Konstante bereitstellen musste, auch zur Verfügung. So weit, so einfach. Grundsätzlich also hinter Application.WorksheetFunction. Aber es gibt natürlich auch Funktionen, die ein bisschen komplizierter sind. Vielleicht kennen Sie die SVerweise. Ich habe hier schonmal ein kleines Beispiel aufgebaut, verschiedene deutsche Autokennzeichen und die jeweiligen Städte dazu. Damit das nachher vernünftig anzusprechen ist, werde ich diesen völlig beliebig gewählten Bereich mit einem Bereichsnamen versehen. Also zum Beispiel als berKennzeichen. Return. Damit habe ich jetzt einen Bereichsnamen vergeben. Der ist da oben auswählbar. Und kann damit jetzt arbeiten. Hier zum Beispiel ganz zu Fuß. Wenn ich rauskriegen will, das Kennzeichen, was "BI" heißt, zu welchem Ort gehört das? Würde ich also die Funktion SVerweis aufrufen, senkrechter Verweis, SVerweis. Das Suchkriterium ist der Wert oder die Zahl in der ersten Spalte. Die müssen übrigens sortiert sein, aber das ist hier einfach zu machen. Als zweites die Matrix, das ist der Bereich, wo der Verweis suchen soll. Das ist der Name des Bereichs. Sie können mit der F3-Taste sich den einfach anzeigen lassen. Also OK. Und als drittes, in der wievielten Spalte finde ich das Ergebnis? Denn die können länger sein, das muss nicht zweispaltig, kann drei-, vier-, fünf-, beliebig-spaltig sein. Also in der zweiten Spalte steht das Ergebnis. Und, Klammer zu, wenn ich das jetzt mit Return bestätige, dann ermittelt die Funktion, dass wenn ich in D6 suche, ich das Kennzeichen "Bielefeld" erhalte. Das ist natürlich schlauer, wenn ich in einem anderen Feld suche, also ich ändere das jetzt mal, dass ich nicht in D6, sondern hier in H5 suche. Und wenn Sie es dann bestätigen, werden Sie feststellen, funktioniert genauso. Ich kann einen beliebigen Wert eingeben und finde dazu jeweils den passenden Ort. Diese Funktion, nämlich der SVerweis ist so praktisch, den möchte ich nicht in VBA nachprogrammieren müssen. Aber es kann ja sein, dass meine Daten so in einer Excel-Tabelle stehen. Also nutze ich diese SVerweis-Funktion. Allerdings muss ich als Erstes rauskriegen, wie sie denn auf Englisch heißt. Das kann ich hier schonmal verraten. Das ist ein Lookup, und zwar ein Vertical Lookup. VLookup steht da also gleich. Dann gucken wir doch mal, wie das hinzukriegen ist. Also Sub FindeVerweis sagen wir ruhig mal. Und diese Funktion hat drei Argumente, sogar eigentlich noch ein viertes, optionales, haben Sie vielleicht eben gesehen. Sie müssen sagen, wo der Wert steht oder wie er heißt, wo es zu suchen ist, und schließlich in welcher Spalte von dieser Suchmatrix das Ergebnis zu finden ist. Das Problem, werden Sie gleich feststellen, ist, dass die Argumente eigentlich nur durchnummeriert sind. Die haben keine sprechenden Namen. Also wenn ich jetzt das erste Argument vergebe, dann sage ich hier schonmal Dim varSuchen As Variant. Dim varMatrix. Dann muss ich jetzt vorher wissen, was da reinzuschreiben ist. Das muss ich mir in der Hilfe oder in dem Aufruf angucken. Also die Matrix war das zweite Argument. Und Dim varSpalte As. Da dürfte ich sogar As Integer schreiben, das ist der dritte Wert. Aber Dim muss ich natürlich schreiben. Diese drei Argumente gibt es zu belegen. Und die kann ich jetzt schonmal versehen mit Werten. Das macht es jetzt vor allem übersichtlicher. varSuchen = . Ich will wissen, "AC", wer dieses Kennzeichen benutzt. Und suchen möchte ich in einer Matrix, die ich hier sogar genau angeben kann. Ich kann nämlich sagen, in dieser Application gibt es eine Auflistung von Bereichsnamen, Names. Achtung, erstens der Plural, Names, die Auflistung, nicht Name, der Name der Application. Das wäre nämlich Microsoft Excel. Also die Auflistung, Names. Und sie gehört interessanterweise, obwohl die Bereichsnamen ja an die Datei gebunden sind, zur Application. Sehr gewöhnungsbedürftig. Ich würde da fast das Wort falsch in den Mund nehmen, aber es ist einfach so. Jetzt müssen Sie allerdings zu Fuß wieder wissen, wie es hieß. Da muss ich selber nachgucken. Also für diesen Bereich, berKennzeichen hatte ich das genannt. Die F3-Taste funktioniert hier nicht, denn die ist ja natürlich für die Tabellen gedacht. Und von diesem Bereichsname habe ich jetzt nur das Namensobjekt in der Hand. Ich muss jetzt auch den Bereich dazu wissen. Das geschieht mit RefersToRange-Methode, die jetzt die Zelladressen und Inhalte zurückgibt. Und das dritte Argument, die varSpalte, ist schlicht die 2. Damit habe ich also alle drei Argumente zusammen. Und mit Debug.Print kann ich das jetzt aufrufen, nämlich Application. WorksheetFunction.VLookup. Und jetzt sehen Sie hier nämlich nach der Klammer, die Argumente heißen ganz schnöde Arg1, Arg2, Arg3. Das ist also minder hilfreich. Ich muss jetzt wissen, das erste Argument ist das, was bei mir varSuchen ist. Das zweite varMatrix. Und das dritte ist varSpalte. Und Klammer wieder zu. Und jetzt sollte alles so weit, sodass wir mit F5 uns das Ergebnis ausgeben können. Also zu "AC" gehört "Aachen", nur damit Sie sehen, dass es wirklich funktioniert. "BI", wenn ich das jetzt hier mal wegnehme, kommt entsprechend zu dem Ergebnis "Bielefeld". Also Sie sehen schon, die WorksheetFunctions, die einfachen sind direkt so zu benutzen, weil sie keine Parameter haben. Die anderen sind zwar ein bisschen mühsamer zu benutzen, aber vor allem sehr viel mühsamer wegen der unbenannten, durchnummerierten Argumente, sodass man sich am besten erstmal in der Tabelle ein Beispiel macht, damit man anhand dieses Assistent nachsehen kann, was denn da eigentlich gefordert wird, und das auch direkt testet. Aber ich gehe mal davon aus, dass Sie die komplizierten Funktionen erst dann einsetzen, wenn es selber programmiert noch komplizierter wäre. Und dann ist es schon eine erhebliche Erleichterung und natürlich schneller. Schneller als selbst programmiert sind die fertigen, integrierten WorksheetFunctions allemal.

Excel 2013 VBA für Profis

Nutzen Sie die Möglichkeiten der Programmiersprache VBA in Excel 2013, um eigene Dialoge zu erstellen, auf andere Arbeitsmappen zuzugreifen und wichtige Funktionen einzusetzen.

4 Std. 59 min (53 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!