Excel 2013 VBA Grundkurs

Bereich ohne Überschriften ermitteln

Testen Sie unsere 1951 Kurse

10 Tage kostenlos!

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

Damit ist alles vorbereitet, was ohne VBA funktioniert. Die Datumswerte stehen drin. Die bedingte Formatierung erkennt das Wochenende. Jetzt kommt der VBA-Teil. Sie haben sicherlich schon gesehen, dass die Datei gespeichert ist als .xlsm mit Makros. Sonst würde das ja sowieso nicht funktionieren. Mit Alt + F11 kommen wir jetzt in den VBA Editor und dann nehme ich erstmal ein paar Umbenennungen vor, z.B. die Datei selber. Die heißt standardmäßig VBAProject. Das finde ich jetzt nicht so hilfreich. Also, nenne ich das mal irgendwie so: ExcelVBA_Kalender. Wie auch immer Sie es nennen, das ist eigentlich erst interessant, dieser Code-Name, wenn Sie es mit anderen Dateien verlinken oder per VBA darauf zugreifen. Aber ich sehe all zu viele, sogar Add-Ins, die immer noch VBAProject heißen und irgendwann kommen die sich mal ins Gehege. Das zweite - die Tabelle, und zwar ihr Code-Name, nicht der, den der Benutzer sieht, sondern der interne, den werde ich auch umbenennen in tblKalender. So dass ich später sicher darauf zugreifen kann und unabhängig davon bin, ob den jemand umbenennt oder an eine andere Position verschiebt. Jetzt brauche ich noch ein neues Modul, da dieses Symbol hier schon steht, können wir direkt drauf klicken. Und auch das Modul heißt passend, nämlich modKalender. Viel mehr an Modulen wird es in dieser Datei nicht geben. Das reicht hier. Aber trotzdem sollte man es ordentlich benennen. Jetzt kommt die eigentliche Programmierung und zwar möchte ich zuerst rauskriegen, wo die Datumswerte stehen. Boshafterweise beginnen die nicht links oben und noch nicht mal links. Das heißt, ich kann mich auf nichts verlassen. Es gibt oberhalb zwei Leerzeilen, links eine leere Spalte. Und wenn ich die reinen Datumswerte sehen will, dann möchte ich nur diesen Bereich haben. Also, auch ohne die Überschriften. Anstatt mir jetzt nun zu merken, dass hier drei Zeilen drüber und eine Spalte links freizuhalten sind, mache ich hier eine Markierung rein. Diese Zelle benenne ich mit einem Bereichsnamen. Da kann ich ja da oben einfach reinklicken und ich werde die als "Neujahr" benennen. Denken Sie bitte daran, bei Bereichsnahmen, die müssen Sie mit Return bestätigen, sonst vergisst Excel die. Ich kann also ab jetzt sagen: der genutzte Bereich UsedRange wird vorgeben, wo das Datum steht, aber links oben fängt es erst bei Neujahr an. Und das muss ich erstmal rauskriegen. Also schreibe ich mir eine Function, ja, eine Function, die den Bereich nur mit dem Datum ermittelt, und deren Datentyp ist As Range. Natürlich können Functions auch Objekt-Datentypen annehmen, um nicht zu sagen, bei Excel mache ich das sehr häufig. Bisher hatten Sie die Functions nur mit den einfachen Datentypen: Long, Integer, Double, String oder sowas gesehen. Auch als Range geht es und zwar super. Und ich notiere mal als Erinnerung, dass es "alle Datumswerte liegen in diesem Bereich". Damit Sie schon mal sehen, dass es funktioniert, ich muss das sowieso gleich testen. Eine Sub findet Datumswerte, auch wenn die Function noch nicht wirklich funktioniert, kann ich jetzt schon schreiben BereichNurDatum.Select. Der Datentyp Range sagt dieser Function, welche Fähigkeiten das anschließen hat, und z.B. ein Select. Ich muss nur irgendwann dafür sorgen, dass diese Function es auch zurückgibt. Da der Name der Function ein bisschen zu lang ist, werde ich mir hier eine Kurzform zwischendurch erfinden: Dim rngX As Range. Und ganz zum Schluss sagen BereichNurDatum ist gleich rngx. Da das Objekt - Datentypen sind, muss ich natürlich ein Set davor schreiben. Und zwischendurch ermittle ich das. Und zwar in Häppchen, sodass wir mal prüfen können, wie das jetzt funktioniert. Fangen wir mit dem ersten Häppchen an: Set rngX ist gleich. Von dieser Tabelle, die hat ja jetzt einen vernünftigen Code-Namen, Punkt, der UsedRange. Das ist der erste Test. Nach dem Speichern natürlich. Und wenn ich diese Prozedur starte, diese nämlich mit F5, dann sehen Sie, hier ist jetzt der UsedRange markiert. Soweit, so gut, aber es geht natürlich noch besser. Ich muss das da alles abziehen. Ich hebe mal wieder die Markierung auf. Und dafür mache ich mir jetzt zwei Hilfsvariablen, nämlich Dim intLinks As Integer. Und Dim intOben As Integer. Das gibt an, wie viele Spalten links und Zeilen oben ich abziehen möchte. Die müssen wir jetzt erstmal ausrechnen und da kann ich jemanden fragen. Z.B. für intLinks kann ich jemanden fragen. Da habe ich nämlich einen Bereichsnamen vergeben, also diese merkwürdige Applications-Elternschaft, Punkt Names. Achten Sie drauf, die Auflistung Names. Der Bereichsname hieß "Neujahr". Nach dem Punkt muss ich erst wieder mit RefersToRange das zugehörige Zell-Objekt ermitteln, und kann dann seine Column abfragen. Im einfachsten Fall mache ich hier ein Debug.Print hinterher. intLinks antwortet jetzt mit 2, weil die Spalte, in der sich "Neujahr" befindet, 2 ist. Wir können es einfach mal eben laufen lassen mit F5. Da ist die "2". Das bedeutet, ich will einen weniger abziehen. Ich muss nachher 1 abziehen, damit ich in die Spalte 2 komme. Und das mehr oder weniger gleiche, mache ich mit intOben, auch da frage ich Application.Names, und Sie merken schon, zweimal das Gleiche geschrieben. Das lohnt sich nicht. Das werde ich hier abschneiden und mit der With-Konstruktion ausklammern, die beiden eingerückt. Da ein End With. Und jetzt geht es nämlich kürzer. Jetzt kann ich den direkt nach seiner Row fragen, seine Zeile. Und die verhält sich ähnlich, er gibt mir eine "4" an, aber ich will -1, natürlich nur 3 abziehen, später. Da können wir ruhig zwei Werte in einem aufgeben. Also da unten` wird gleich "2" Komma... Nee, inzwischen 1, weil ich schon eins abgezogen habe, erscheinen. Also mit F5 - "1, 3", weil "Neujahr" (ich kann das nochmal zeigen) in Spalte 2 und Zeile 4 steht. Und ich will aber eine Spalte abziehen und drei Zeilen. Das habe ich jetzt also ermittelt, damit ist das Debug.Print hier überflüssig. Ich nehme es nur mal als Kommentar raus, wie es mich zu viel Platz kostet. So, und jetzt kann ich anfangen, den Bereich, der im Moment so ein bisschen lieblos rngX heißt, zu verschieben. Also zuerst Set rngX ist gleich bisheriger rngx.Offset. Und zwar Rows (Zeilen) muss ich ihn jetzt um so viel nach unten verschieben, wie ich mit intOben als Lücke da oben ermittelt hatte. Komma, und die Columns, dafür ist der intLinks-Wert zuständig. Das können wir einfach mal direkt testen. Wenn Sie größere Programmänderungen gemacht haben, empfehle ich immer einmal Debuggen Kompilieren. Dann sieht man sofort, ob irgendwelche syntaktischen Fehler drin sind. Sieht nicht so aus. Einmal F5 und dann gucken wir mal, was jetzt markiert ist. Das sieht schon super aus, weil nämlich die linke obere Ecke richtig ist. Die Größe des Bereichs ist dabei verschoben worden. Das ist korrekt. Das habe ich auch nicht geändert, aber die Position stimmt. Wenn ich es hier um einen verschoben habe, muss ich es hier um einen verkürzen. Wenn ich es da um drei Zeilen verschoben habe, muss ich es um drei Zeilen kürzen. Also, Markierung wieder aufheben, Alt + F11. Jetzt kommt der zweite Teil. Das hätte man auch dranhängen können, macht es aber nicht übersichtlicher. Den jetzigen rngX mit Resize zu verkürzen. Die RowSize: soll werden wie die bisherige Länge. Ich muss jetzt also erstmal rausfinden, wie ist die bisherige Länge. Das frage ich einfach: rngX.Rows (Achtung: Plural).Count So viele Zeilen habe ich. Und davon ziehe ich intOben ab. Und dasselbe gilt für die Spalten, also rngX.Columns (Plural) .Count. Und auch davon Minus int, in dem Fall - Links abziehen. Und wenn das geklappt hat, haben wir jetzt den korrekten Bereich gefunden. Also einmal F5 und nachgucken. Das sieht doch schon super aus. Egal, wo ich jetzt diesen Kalender platziere, der lässt sich finden. Ich werde das jetzt boshaft provozieren, indem ich hier einfach eine Zeile mehr einfüge. Und dann teste mit F5, ob der immer noch gefunden wird und siehe da, das ist dem Kalender, bzw. dem Makro völlig wurscht. Ich kann die also aus Platzgründen hier wieder rausnehmen. Es funktioniert. Das Makro ist so stabil (und jetzt mache ich auch hier aus Platzgründen diese Zeile weg), dass es den Datumsbereich findet, weil der "Neujahr", dieser Bereichsname sich mitverschiebt.

Excel 2013 VBA Grundkurs

Holen Sie aus Excel 2013 mit der integrierten Programmiersprache VBA auch das letzte Quäntchen an Funktion heraus und lassen Sie sich dazu zeigen, was alles möglich ist.

6 Std. 18 min (63 Videos)
Christopher B.
Super Einstieg in die Welt der Makros
 

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!