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: Eigene Funktionen mit VBA erstellen

Beispiele zu Funktionen mit einem Parameter

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Sehen Sie anhand einiger Beispiele, wie Sie Funktionen definieren, bei denen ein Parameter angegeben wird, um ein bestimmtes Ergebnis zu erhalten.
11:54

Transkript

In diesem Video zeige ich Ihnen, wie Sie Funktionen erstellen können, die ein Argument erhalten und ein Argument wieder zurückgeben. Um das zu realisieren bin ich nach VBA gegangen, habe in eine Modul eine neue Funktion geschrieben, die natürlich mit function beginnt und dann den Namen der Funktion trägt. Ich möchte als Beispiel um zu zeigen, wie so was funktioniert, eine Quersumme berechnen, das heißt, ich nenne die Funktion Quersumme. Die Quersumme soll von einer eingegebenen Zahl sämtliche Ziffern addieren und eine Zahl mir zurückgeben, die diese Summe, diese einzeln Ziffer ist. Quersummen werden manchmal in technischen oder kaufmännischen Zusammenhängen gebraucht um festzustellen, ob die Personalnummer, Artikelnummer, ID und so weiter den Anforderungen unserer Firma genügt. Also die Quersumme erhält, Klammer auf, eine Zahl. Die Zahl ist vom Typ as long, als Ganzzahl. Aber Achtung, long hat hier doch nur 10 Ziffern. Machen wir es ein bisschen besser, gehen wir gleich as double auf einen double-Wert. Eigentlich kann double ja Kommazahlen speichern, wir brauchen keine Dezimalstellen, aber bei double haben wir umgekehrt 300 Stellen vor dem Komma zur Verfügung und das reicht fürs erste, Klammer zu, As und das Ergebnis dürfte eigentlich eine long-Zahl sein, aber hier ist ein bisschen über das Ziel raus, machen wir mal eine double-Zahl, da sind wir auf der sicheren Seite. So. Meine Funktion soll jede einzelne der Ziffern von der eingegebenen Zahl sich rausgreifen, das heißt, ich brauche hier eine Schleife, eine Zähler-Schleife, die bekommt natürlich den intZähler, eine Zähler-Variable, as integer. Da wir bis zu 300 Stellen haben, reicht die 32000 von intZähler dicke aus. Außerdem verwende ich noch dim, zum Beispiel dblQ as Double, eine weitere Funktion, in der ich das Zwischenergebnis der Quersumme, der zwischen berechneten Quersumme schon mal speichere. So meine Schleife läuft nun for intZähler = 1 to wie viele Ziffern haben wir? Antwort: Die Funktion len ermittelt mir von der Zahl die Anzahl der Ziffern. Ganz genau ich sollte die Zahl vorher mit einem Convert to String erst noch in einen Text umwandeln, damit wir von diesem Text, von diesem Zahlentext jetzt genau die Anzahl der Stellen ermitteln können. Enter, Enter. Next ist das Ende von For, oder Next intZähler. So die Funktion mid holt mir nun aus der Zahl, oder noch besser aus cstr (zahl) an der Position einmal die erste, die zweite, die dritte, also an der Position intZähler, Komma, jeweils immer nur eine Ziffer raus, das heißt die Länge beträgt 1, Klammer zu. Die Funktion mid übergeben wir an die Zwischenvariable dblq = und da dblq natürlich alle Ziffern einsammeln soll, muss dblq berechnet werden als dblq + das Ergebnis der Funktion mid. Prima! Guter Programmierstil ist es vor der Schleife natürlich dblq zu initialisieren, dblq auf den Wert 0 zu setzen. Schlampiger Programmierstil würde hier auch funktionieren, das heißt, wenn Sie das nicht machen, vergibt VBA automatisch den Wert 0, weil die Variable deklariert wurde, aber nicht initialisiert. Ok, das ist guter, gut lesbarer Code, prima, so gefällt er mir. Am Ende der Schleife weiß ich auch, wie groß die Zahl dblq ist, also weiß das Ergebnis meiner Quersumme, und das kann ich natürlich, quersumme =, übergeben an den Funktionsnamen dblq, schreibe von rechts nach links den Wert rüber. Testen wir das einmal aus, ich wechsele nach Excel rüber. Ich schreibe in eine beliebige Zelle einen Wert, zum Beispiel 1234. Das kann ich noch im Kopf, ist natürlich 10. Wenn ich jetzt teste =Quersumme von dieser Zahl, dann erhalte ich als Ergebnis das was ich, wie gesagt, im Kopf noch berechnen kann, nämlich das Ergebnis 10. Schauen wir es nochmal kurz an. Die Funktion Quersumme bekommt einen Wert, liefert einen Wert. Dass hier Double und Double vom gleichen Typ ist, ist hier Zufall, muss nicht sein, kann natürlich sein, und wenn ich das Ganze über den Funktions-Assistent starten würde, also hier über Formeln Funktions-Assistent, aus der Kategorie benutzerdefiniert meine Funktion Quersumme Ok, dann sehen Sie hier einerseits den Namen des Parameters, den Namen des Arguments, das ich auf der VBA-Seite vergeben habe, andererseits sehen Sie, hier die Quersumme bekommt nur eine Zahl, zum Beispiel diese Zahl, und liefert mir hier gleich im Funtkions-Assistent sichtbar das Ergebnis, Ok. So ich zeige Ihnen noch ein paar weitere Funktionen, die ich schon vorbereitet habe. Gehen wir ein bisschen weiter runter, die habe ich ein bisschen versteckt. Ich habe die Leerzeilen eingefügt, die lösche ich jetzt hier raus. Zum Beispiel eine Funktion Zahlentext. Zahlentext ist eine Funktion, die eine Zahl erhält und die mir einen Text zurückgibt. Oder ganz genau, Sie sehen es bereits hier unten, die einzelnen Ziffern werden in die Texte umgewandelt, das heißt, aus der Ziffer 0 macht er die Buchstabenfolge null, aus 1 macht er eins und so weiter. Hierzu lose ich natürlich, genau wie oben, mit der Funktion mid aus der Zahl die erste, zweite, dritte Ziffer heraus und schreibe das in dem Fall in eine string-Variable rüber und mit meiner If-Verzweigung entscheide ich, was ich hier haben will. Oh ein Komma schafft er auch. Und das Ergebnis baue ich dann wieder zusammen, Zifferntext nimm bitte das, was du schon hast und ein Leerzeichen und den Ziffenrtext. Probieren wir diese Funktion Zahlentext auch mal in Excel aus. Ich wechsele nach Excel rüber, mache irgendwo in einer leeren Zelle =Zahlentext, gehe auf diese Zahl los und er liefert mir, Enter, natürlich die Zeichenkette eins zwei drei vier. Wenn Sie ganz genau hinschauen, sehen Sie am Anfang noch ein Leerzeichen. Das hätte ich vielleicht noch sauberer wegprogramieren sollen, aber das überlasse ich Ihnen hier am Ende das Leerzeichen, das zu viel ist, raus zu löschen. Eine weitere Funktion, Endquersumme, macht folgendes: Die verwendet nun so lange die Quersumme, bis ich als Ergebnis eine ein ziffrige Zahl habe. Das heißt, Do While Loop, so lange das Ergebnis meiner internen Variable dblq größer oder gleich 10, wird nochmal diese Funktion, die ich hier intern verwende, aufgerufen. So lange bleibt er in der Schleife drin, so lange berechnet er die Endquersumme, bis ich hier fertig bin. Schauen wir uns das nochmal an. Das heißt, die Quersumme von 1234 wäre 10, die Endquersumme von dieser Zahl wäre natürlich 10, nochmal berechnet die Quersumme, 1+0 ist 1, ergibt dann eben 1. Noch ein kleines Beispielchen. AnzahlDerTeiler würde mir mit einer Schleife so viele Teiler liefern oder eine Zahl liefern, die eben, wie der Name sagt, die Anzahl der Teiler ermittelt. Das heißt, beispielsweise die Primzahl 3 hat natürlich nur zwei Teiler. Die Zahl 9 hat als Teiler die 1, die 3, die 9, das heißt, hier würde er eine 3 liefern. Gehen wir auch hier mal mit der Funktion AnzahlDerTeiler auf unsere Zahl los und =AnzahlDerTeiler von dieser Zahl liefert erstaunlicherweise nur... Ups! Das ich ein Fehler gemacht habe! Wo ist der Fehler? Schauen wir den an. Klar, das ist schnell gefunden. Sehen Sie es? Hier habe ich eine Variable dblWurzel verwendet, das passiert beim Kopieren, ich habe natürlich vorher eine Sub gemacht und habe es hier verändert. Sollte ich gut testen. Sie sehen hier, schönes Beispiel für gut Testen. Wenn die Wurzel der Zahl ist gleich die Zahl selbst, das heißt, die abgerundete Zahl, dann mach mir einen Wert weniger, weiter geht es, und meine Funktion würde jetzt 4 liefern, das heißt, die Anzahl der Teiler von dieser recht großen Zahl ist erstaunlicherweise nur 4. Noch eine kleine Funktion. Prüfziffer. Im Jahr 2005 wurden die Prüfziffern von Büchern von der ISBN 10 auf die ISBN 13 umgestellt. Ich habe hier nun zwei Prüfziffern-Funktionen geschrieben, die jeweils die alte Prüffzifer- Nummern und die neue verwenden. Bei allen Büchern, die ich heute immer noch kaufe, ist sowohl die alte Prüfziffer, also die zehn ziffrige Prüfziffer, als auch die neue drauf. In dieser Prüfziffer wird einerseits der Landercode, der Verlagscode, der Buchcode und die letzte Ziffer gibt eine Prüfziffer eben die Zahl so zusammengebaut. Diese alte Prüfziffernfolge, Sie finden im Internet die genau Beschreibung davon, geht folgendermaßen vor: Die multipliziert nacheinander die einzelnen Ziffern mit den Werten 10, 9, 8, 7 und so weiter, addiert dann die einzelnen Werte zusammen. Wenn das Ergebnis der Prüfziffer eine Zahl ist, natürlich ist es eine Zahl, dann wird von dieser Zahl Modulo 11 gerechnet, das heißt, die Zahl durch 11 geteilt und der Rest wird angeschaut. Beträgt der Rest selbst hier 11, dann ist das Ergebnis der Prüfziffer eine 0. Beträgt der Rest 10, dann kann ich natürlich keine zwei ziffrige Prüfziffer nehmen, deshalb nimmt man ein x, ansonsten nimmt das Ergebnis. Das müssen Sie auf die schnelle nicht verstehen, wie gesagt, das kann man Im Internet nachlesen. Genauso die ISBN 13 multipliziert alle Zahlen abwechselnd mit dem Faktor 3-1, 3-1, 3-1, zählt sie zusammen und berechnet dann Modulo 10, ein klein bisschen anders natürlich, die Prüfziffer. Auch das will ich einmal ganz kurz zeigen. Ich habe hier zwei Buchnummern schon vorbereitet, sind Büchern, die ich mal erstellt habe, und wenn ich jetzt hier einmal =Prüfziffer_ISBN10, das sind die alten, die letzte Ziffer fehlt noch, ich habe übrigens die ISBN-Nummer natürlich ohne Minus geschrieben, dann liefert er mir hier die Zahl 6. Oder hier nochmal =Prüfziffer_ISBN10, liefert von diesem Buch die Prüfziffer in dem Fall x. Deshalb habe ich hier in VBA auch das Ergebnis bei der oberen As String zurückgegeben. Die Prüfziffer ISBN13 liefert immer eine Ziffer, da kommt keine x vor, deshalb können wir hier as integer. Auch hier =Prüfziffer nehmen wir die letzte, und auch hier liefert er eine Zahl, das müssen Sie mir jetzt glauben. Oder wenn Sie ganz neugierig sind, schauen Sie ins Internet nach, da finden Sie solche Buchziffern, Buchnummern und können damit ISBN10- bzw. 13-Zahl überprüfen.

Excel: Eigene Funktionen mit VBA erstellen

Schreiben Sie Ihre eigenen Excel-Funktionen. Als erfahrener Excel-Anwender lernen Sie, wie Sie mithilfe von VBA Berechnungen mittels selbst erstellter Funktionen automatisieren.

3 Std. 4 min (33 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Exklusiv für Abo-Kunden
Ihr(e) Trainer:
Erscheinungsdatum:21.01.2015
Laufzeit:3 Std. 4 min (33 Videos)

Im Training wird Excel 2013 benutzt. Die Vorgehensweisen sind aber für alle Windows-Versionen von Excel identisch und leicht übertragbar.

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!