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 Grundkurs

Bereich der Datumswerte ermitteln

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Damit nicht jedes Mal längerer VBA-Code nötig ist, übernimmt eine zentrale Funktion die Ermittlung des Bereichs, in dem sich die Datumswerte befinden.

Transkript

Jetzt erst geht es darum, diesen Kalender überhaupt mit VBA zu bearbeiten. Das heißt ich wechsle mit ALT + F11 in den VBA-Editor. Da gibt es ja schon eine Funktion. in einem eigenen Modul. Da ist auch hier schon dieser Name umbenannt worden. Das ist hier dieser interne Code-Name. Und auch die Tabelle habe ich hier schon mal vernünftig benannt, als "tblDatum" oder "tblKalender", oder auf wie auch immer Sie wollen. Wichtig ist an dieser Stelle, dass Sie nicht die Bezeichnung benutzen, die der Benutzer sieht, - die könnte er nämlich ändern - sondern sich darauf verlassen. Damit sich das hier - das ist ja so ein bisschen ein Ausnahmefall - nicht ins Gehege kommt, werde ich ein neues Modul anlegen, Wenn Sie das Symbol da nicht sehen, dann müssen Sie ausdrücklich hier drauf klicken. Und das werde ich dann entsprechend als "modKalender" bezeichnen. Da können wir ein bisschen kleiner machen. Und die erste wesentliche Aufgabe, die ich habe, herauszufinden wo denn die eigentlichen Daten sich befinden. Die Datumswerte müsste ich genauer sagen. Das heißt, diesen Bereich will ich ausdrücklich erwischen. Und damit ich es nicht zu einfach habe, der steht nämlich ganz links, werde ich jetzt - mit STRG "+" eine Spalte hinzufügen - werde ich diesen Kalender irgendwo zufällig hin verschieben. Nachher können wir es aus Platzgründen wieder wegnehmen. Aber ich möchte nicht unterstellen, dass es in der ersten Spalte ist, oder auch nur in der ersten Zeile, sondern er muss erkannt werden, egal wo er ist. Und dafür brauche ich eine Funktion, - ja, ich schreibe eine Funktion - eine Function, die genau das zurückliefert, nämlich den Bereich nur mit Datum. Und deren Rückgabetyp ist "as Range". Das ist bisher eher ungewohnt, weil wir so die einfachen Datentypen hatten, Integer, Long oder sonst was, meistens String. Auch Objektdatentypen sind möglich, um nicht zu sagen, die sind sogar sehr häufig. Deswegen kommentiere ich das mal gleich dahinter: "Alle Datumswerte" "liegen in diesem Beriech". Und damit muss ich irgendwas ermitteln, um die zu finden, aber technisch, syntaktisch, ist die schon in Ordnung. Und damit ich die testen kann, brauche ich sowieso nachher eine Prozedur. Die nenne ich dann "FindeDatumswerte". Und dort kann ich bereits diese Function benutzen. Die verhält sich wie ein Range, deswegen darf ich jetzt direkt schreiben: "BereichNurDatum.Select". Da muss ich gar keinen großen Aufwand betreiben, es ist ein Range, ein Range hat die Methode "Select" und damit ist alles gut. Der Range selber ist leider noch nicht zu ermitteln, der ist leer, aber technisch weiß VBA schon, was der zukünftig kann. Deswegen mache ich hier ein Range-Datentyp. Da dieses Wort "BereichNurDatum" zwischendurch immer ein bisschen lästig lang ist, werde ich hier zwischendurch einen Range "X" deklarieren, auch "as Range". Und das letzte, was natürlich die Function dann machen muss, ist den wieder an den Namen zurückzugeben. Objektdatentp also mit "Set" "BereichNurDatum" gleich" "RangeX". Und dazwischen muss irgendwas passieren. Zum Beispiel kann ich erstmal gucken, wo liegen denn überhaupt Daten, also mit "Set RangeX" gleich - Achtung, jetzt muss ich den internen Namen hier nehmen - "TableDatum" Punkt "UsedRange". Sie sehen schon, mit diesem internen, mit diesem Code-Namen geht das viel kürzer, als wenn Sie diese Worksheet-Auflistung nehmen. Die ist immer so unentschieden, dass sie ihre Eigenschaften und Methoden nicht kennt. Ich hätte jetzt also erstmal den UsedRange, und das will ich erstmal testen. Es ist also ein Range drin. Und mit F5 starte ich jetzt dieses hier und gucke mit ALT + F11 mal eben nach. Es ist alles markiert, der UsedRange, - hier oben stehen ja Daten - ist im Moment noch zu viel. Ich werde boshaft auch mal hier oben ein "X" reinschreiben, dann gilt der nämlich auch als used, als genutzt. Und mit F5 nochmal ausführen. Dann mache ich es mit sozusagen extra schwer. Es muss trotzdem funktionieren. Egal was drum rum um den Kalender als Notizen steht, will ich nur diese Daten haben. Und der Trick, wie ich das hier finde, besteht darin, dass ich hier einen Bereichsnamen hinterlasse. Ich mache das da mal ein klein bisschen breiter. Hier kann ich einen neuen Bereichsnamen eintragen, den nenne ich mal einfach "Neujahr". Unbedingt mit Return bestätigen, sonst wird er vergessen. Wenn Sie das prüfen wollen, irgendwo anders hin klicken, Ausklappen, Anklicken, dann ist alles in Ordnung. Ich weiß also... Das könnte ich zur Not hier mit Sylvester auch noch machen. Das spare ich mir jetzt mal, damit dahinter nicht mehr noch so viel steht. Ich könnte also dann formulieren: Ich will alles zwischen Neujahr und Sylvester sehen. Im Moment formuliere ich es so: UsedRange ist in Ordnung, der lag ja hier, aber er beginnt bitte bei Neujahr. Und das bedeutet, ich muss jetzt hier rauskriegen, in der wievielten Spalte, in der wievielten Zeile liegt Neujahr, nämlich vierte Zeile, zweite Spalte. Und muss dann vier minus eins, drei abziehen in den Zeilen, oder zwei minus eins, einen in der Spalte. Deswegen werde ich zwei - Integer wird reichen bis 32.000 Zeilen - zwei Integer-Variablen bereitstellen, die sich das merken. Also "dim intLinks", - Was muss ich links abziehen? - "as Integer" und "dim intOben as Integer", wieviel muss ich oben abziehen? Und die kann ich jetzt ermitteln. Zum Beispiell "intLinks". Das ist das, was ich in "ThisWorkbook.Names"-Auflistung sehe. Achtung: "Names". Und dort den Namen "Neujahr" abfrage. Und ganz wichtig, nicht vergessen, "RefersToRange", dann erst habe ich den Bereich. Und der hat eine Column-Eigenschaft. "Column" im Singular. Das ist nämlich die Spalte, in der er steht. Da würde er also mit einer "2" antworten. Ich will aber immer nur eins abziehen, nämlich die Differenz. In der zweiten Spalte will ich einen abziehen, weil ich eine Spalte weniger habe. also muss ich davon etwas abziehen. Das käme jetzt praktisch identisch für "intOben", nur dass es nicht "Column" sondern "Row" heißt. Und das bedeutet, dieser Bandwurm hier wird zweimal auftauchen. Das geht kürzer, indem ich ihn ausschneide und in die "With"-Konstruktion ergänze. Entsprechend hier ein "End With". Das heißt das Objekt, was hinter dem "With" genannt ist würde ja vor dem führenden Punkt eingesetzt. Und jetzt kann ich entsprechend "intOben" ist dasselbe für die "Row". Auch da Singular, es ist eine Zahl minus eins. Minus eins immer deswegen weil ich ja hier eine zweite Spalte, eine vierte Zeile bekomme, aber ich will nur die drei davor, beziehungsweise die eine davor abziehen. Und das können wir ja erstmal testen. Also, hier würde ich jetzt ganz banal ein "debug.Print" dahinter schreiben. Und da steht dann "intLinks", verkettet mit ein bisschen Abstandshalter, verkettet mit "intOben". Und da gucken wir mal einfach nach dem Speichern, wenn ich den hier aufrufe mit F5. Da steht also eine eins und eine drei. Wunderbar. Ich kann das vorübergehend kommentieren, aber langfristig wird das rausfliegen. Jetzt, das sehen Sie aber beim "Select", muss ich dafür sorgen, dass diese "Selection" sich um das verschiebt, also um drei nach unten und um eins nach rechts. Das ist der "Offset"-Befehl, also ich muss jetzt sagen, der "Rangex" - Objekt, deswegen mit "Set" davor - ist der bisherige "Rangex" mit einem zusätzlichen Offset. Und zwar "Rows", das ist der von oben, "intOben", und "Columns", das ist der von "intLinks". Dann gucken wir mal, was dabei rauskommt. Ich hebe die "-1" hier schon abgezogen, Also mit F5 ist das perfekt verschoben. Die linke obere Ecke liegt jetzt auf Neujahr. Nur der Bereich ist natürlich noch zu groß. Wenn Sie sich das hier angucken, der ist genau um die drei Zeilen und eine Spalte zu groß, die ich ihn verschoben habe. Also kommt jetzt der zweite Teil. Das könnte man hier direkt dranschreiben, mit Resize kein Problem. Macht es aber nicht übersichtlicher, schon gar nicht wenn man es testen möchte. Deswegen schreibe ich es einfach nochmal. Anstatt das dranzuhängen, schreibt man dann eben "Set RangeX=RangeX" Deswegen hier die kurze Fassung, die ich so bevorzuge, und nicht die lange Schreibweise. Und da kommt jetzt das "Resize" dran. Und das Resize gibt nicht die Differenz an, sondern die Ergebnisse. Ich muss also die Rows erstmal zählen, das bedeutet ich muss jemanden fragen, der was davon versteht, das "RangeX" selber. hat eine "Rows", jetzt der Plural, ".Count", das ist die Anzahl insgesamt. und davon ziehe ich "intOben" ab. Und dasselbe für die Spalten. Also, "RangeX" hat eine Columns-Auflistung, die hat eine Count-Eigenschaft, minus "intLinks". Klammer wieder zu. Und wenn ich das jetzt mit F5 teste, sind wir am Ergebnis. Also, ich kann das mal ein bisschen tiefer schieben. Damit ist exakt der Datumsbereich gefunden, egal wo er sich befindet. Ich werde mal die Markierung aufheben, die Spalte "A" wieder mit STRG "-" rausnehmen, und auch hier die Zeile mit STRG "-" entfernen. Und dann prüfen wir mal, ob er die Datumswert immer noch findet. - mit F5, ALT + F11 - egal wo dieser Bereich sich befindet. Außer wenn Sie noch anfangen, hier draußen noch Zahlen oder Texte zu schreiben, dann müssten wir auch Sylvester noch setzen. Aber Sie haben das Muster gesehen. Das ginge dann entsprechend einfach. Also wird dieser Datumsbereich korrekt erkannt und ich muss mich in anderen Funktionen, oder Prozeduren überhaupt, nicht mehr damit beschäftigen, - das können wir rausschmeißen - sondern sage dann einfach, "BereichNurDatum". Das ist der, den ich haben will, mit dem kann ich weiterarbeiten. Das erleichtert die übrigen Funktionen sehr, weil Sie dort sich darauf verlassen können, dass Sie nur noch mit Datumswert arbeiten.

Excel 2016 VBA Grundkurs

Lassen Sie sich systematisch in die Excel-Programmierung mit Visual Basic for Applications (VBA) einführen.

7 Std. 25 min (66 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Exklusiv für Abo-Kunden
Ihr(e) Trainer:
Erscheinungsdatum:30.08.2016
Laufzeit:7 Std. 25 min (66 Videos)

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!