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 2016 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

Es wäre in Excel VBA durchaus praktisch, wenn Sie manchmal auch auf die Funktionen zurückgreifen könnten, die in Tabellen implementiert sind, wo Sie also die Summenfunktion oder den S-Verweis o. ä. nutzen können. Das geht auch, ist aber ein klein bisschen versteckt. Ich will Ihnen das zuerst zeigen mit einer Funktion, die nenne ich mal einfach "Sub ZeigePi", die nichts anderes macht, als die Zahl Pi anzuzeigen, aber die kommt aus einer Tabellenfunktion. Wie immer mit "Debug.Print", denn das geht in einer Zeile. Und es beginnt bei "Application". Und dort gibt es ein Objekt "WorksheetFunction", interessanterweise im Singular, und diesem untergeordnet sind alle Tabellenfunktionen, auf die Sie sonst auch in einer Tabelle zugreifen können, allerdings natürlich in Englisch. Das ist bei Pi jetzt noch nicht so überraschend, wie das dann heißt, aber bei anderen muss man dann einfach ein bisschen recherchieren, wie denn die deutsche Funktion auf Englisch heißt. Hier ist es noch relativ einfach, also mit "Speichern", "Debuggen" "Kompilieren". Pi hat keine Argumente. Man könnte korrekterweise da die Klammern dahinter setzen. Und mit F5 sehen Sie, da kommt das Ergebnis zurück, so wie es auch in einer Tabelle zurückkommen würde. Es gibt andere Funktionen, die sind ein bisschen komplexer, aber, ich sage mal, unersetzlich. Bevor Sie versuchen, so was wie einen S-Verweis nach zu programmieren, sollten Sie ihn lieber so als originale Tabellenfunktion aufrufen, auch wenn es, ich sage mal, nicht wirklich kompliziert, sondern vor allem lästig ist. Lästig ist, dass Sie erstmal rausfinden müssen, wie er heißt. Der S-Verweis, den ich gleich benutzen will, heißt "VLookup", Vertical Lookup. Und bevor ich jetzt hier ahnen muss, wie seine Argumente zu benutzen sind, die nämlich, gelinde gesagt, unscharf benannt sind, mache ich es lieber erstmal in einer Tabelle. Mit Alt+F11 gehe ich also rüber und habe mir in einem Tabellenblatt schon so eine typische Nachschlage-Tabelle für einen S-Verweis vorbereitet. Hier sind nur völlig beispielhaft Autokennzeichen und hier die jeweiligen Orte dazu. Diesen ganzen Bereich benenne ich. Also, ich habe ihn markiert und werde hier oben einfach als "Kennzeichen", Achtung, mit Return abschließen, bezeichnen. Wenn man das ein bisschen breiter zieht, sehen Sie hier, dieser Bereich heißt jetzt "Kennzeichen". Und ich mache das mal gelb hinterlegt. Jetzt gebe ich hier ein beliebiges Kennzeichen vor und will an dieser Stelle dann, machen wir das vielleicht hellgrün, rauskriegen, wie der Ort heißt. Also, mit dem S-Verweis, den schreibe ich schneller als ich da einen Assistenten starte. Der sagt mir jetzt ganz deutlich, zuerst das Suchkriterium, da will ich suchen, Semikolon. Dann, in welcher Matrix, da würde ich jetzt die F3-Taste nutzen, um zu gucken, was ich alles so an Matrixnamen habe oder überhaupt an Bereichsnamen. "Kennzeichen" hatte ich das eben benannt, nach dem "OK", die Matrix, nämlich den Suchbereich "Kennzeichen". Nach dem nächsten Semikolon der Spaltenindex. Ich habe jetzt sozusagen die Zeile gefunden. Und jetzt muss ich sagen, die wievielte Spalte, nämlich die mit der Nummer 2. Und dann gibt es hinten nach dem Semikolon noch so ein optionales viertes Argument, was Sie eigentlich immer auf "falsch" stellen sollten, was entgegen der Erwartung genaue Übereinstimmung fordert. Also, falsch ist sozusagen richtig. Und dann Klammer zu und fertig. Dann erscheint hier "Düsseldorf". Wenn ich jetzt "AC" mit Return bestätige, erscheint da "Aachen", KO, Koblenz, funktioniert wunderbar. Und diese Fähigkeit brauche ich jetzt im VBA. Ich habe irgendwo so eine Matrix, eine Nachschlage-Tabelle und muss deren Werte jetzt möglichst effizient auslesen. Und dann lohnt es sich, diesen S-Verweis in VBA zu benutzen. Ich werde ihn jetzt, erstmal mit Alt+F11 wieder rüber wechseln, sozusagen, überperfekt nutzen. Also mit "Sub FindeKennzeichen" werde ich mir eine Reihe von Variablen vorbesetzen. Das ginge alles kürzer, gar keine Frage, aber dann sehen Sie deutlicher, was ich wo wie mache. Und ich könnte, wenn ich gar nichts weiß, diese alle als "Variant" deklarieren. Das ist die sicherste Methode sozusagen. Ich weiß aber jetzt schon, da kommt ein String an, dann kann ich das auch erstmal deutlich so formulieren. Das ist ein "Range", der Matrixbereich, aber wenn Sie es nicht wissen, machen Sie einfach alles auf "Variant", das ist immer gut. Und "Dim Int", da würde sogar Byte reichen, "As Integer". Und dann kann ich die jetzt nämlich einzeln vorbesetzen, also "strSuchen =", da nehme ich jetzt völlig beliebig irgendeinen, also das AC-Kennzeichen. Dann kommt "rngMatrix" und da sehe ich schon, weil das ein Range, eine Objektvariable, ist, da muss ich es mit "Z" zuweisen natürlich. Und dann kann ich jetzt mal fragen, "ThisWorkbook" besitzt eine "NamesAuflistung". Achtung, im Plural! Dort hatte ich den Namen "Kennzeichen" vergeben und die muss mit "RefersToRange" ihren Bereich finden. Das ist sozusagen die sauberste Fassung. Dann kann ich "intSpalte" noch angeben, die ist die zweite. Und damit habe ich alles vorbesetzt. Ich könnte diese Daten auch direkt gleich übergeben, aber so kann man einzeln prüfen, ob die richtig ankommen. Und jetzt kommt die eigentliche Funktion, also "Debug.Print". Ich rufe wieder mit "Application.WorksheetFunction" auf, dass ich jetzt einen Namens-Vertical-Lookup, "VLookup", brauche. Da sehen Sie jetzt das eigentliche Problem mit der Klammer auf. Die Argumente sind völlig lieblos als "Arg1-3" bzw. bis 4 benannt. Das hilft einem überhaupt nicht. Also, es ist immer einfacher, das vorher in der Tabelle zu testen, dort die Argumente auch wirklich zu sehen, und dann erst hier noch mal zu wiederholen. Jetzt kann ich sagen, "strSuchen" ist das erste Argument, "rngMatrix" das zweite und "intSpalte" das dritte. Und das vierte kann ich jetzt direkt hier auf "false" setzen. Und nach dem Speichern in "Debuggen" "Kompilieren" können wir das mit F5 laufen lassen. Und da kommt korrekt "Aachen" raus. Damit Sie sehen, dass es auch funktioniert, setze ich jetzt "KO" ein und erhalte erwartungsgemäß "Koblenz". Das sollte also eigentlich vor allem zeigen, mit relativ viel Aufwand, das gebe ich zu, man hätte das auch hier direkt schreiben können, dass erstens die englischsprachigen Funktionen aus den Worksheet Functions, also die Tabellenfunktionen, eigentlich sehr leicht zu benutzen wären, wenn es einem nicht durch diese "Arg1-4"-Benennung schwer gemacht würde, zu raten, was da eigentlich gefordert ist. Aber mit ein bisschen Hilfe aus der Originalfunktion und zur Not ein bisschen recherchieren, wie das Wort denn diesmal auf Englisch heißt, ist es doch wiederum relativ einfach möglich, die zu benutzen. Und ich sage mal ganz ehrlich, wenn Sie so einen S-Verweis zu Fuß nachprogrammieren wollten, dann ist das nicht nur erheblich aufwendiger, sondern garantiert auch langsamer.

Excel 2016 VBA für Profis

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

5 Std. 52 min (55 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:25.01.2017

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!