Unsere Datenschutzrichtlinie wird in Kürze aktualisiert. Bitte sehen Sie sich die Vorschau an.

Excel 2016 VBA Grundkurs

Offset

Testen Sie unsere 2015 Kurse

10 Tage kostenlos!

Jetzt testen Alle Abonnements anzeigen
Mit dem Offset-Befehl lässt sich in VBA ein beliebiger Bereich in jede Richtung verschieben. So lassen sich auch aufgezeichnete Makros deutlich verbessern.

Transkript

Erinnern Sie sich noch, an das aufgezeichnete Makro "RechtsRot"? Welches mit STRG + "R" die Zelle rechts davon rot machen konnte. Ich lasse es einfach nochmal gerade ausführen. Hier steht mein Mauszeiger und da wird es rot werden, wenn ich jetzt STRG + "R" drücke. Fühlt sich doch ganz ordentlich an. Jetzt mache ich es mal ein bisschen anders. Ich markiere mal etwas mit gedrückter STRG-Taste. Als Benutzer habe ich ganz was kompliziertes vorbereitet. und jetzt drücke ich STRG + "R". Weil rechts von diesem Zell-Zeiger von der ActiveCell, es rot werden soll. Und gucken Sie sich mal den Rest der Markierung an. STRG + "R" hat geklappt, aber die ganze mühsame Markierung ist weg. Da machen Sie sich sehr unbeliebt. wenn Sie versehentlich oder mangels besseren Wissens die Markierung aufheben. Und es ist gar nicht nötig. Wenn ich mit ALT + F11 wieder zurückwandere, und ich habe hier schon ein modOffset vorbereitet, wo diese Prozedur genau drin steht, nur eben als Kopie. dann liegt das daran, dass beim Aufzeichnen immer diese unsägliche Kombination erzeugt wird, aus einer Zeile, die mit "Select" endet - die macht nämlich die Markierung kaputt - und in der nächsten Zeile geht es mit "Selection" weiter. Und weil sich die Selection ändert, mussten wir zum Schluss auch wieder in die Originalzelle. Immer dann, wenn hier ein "Select" und da ein "Selection" steht, können Sie einfach das Objekt davor nehmen, ausschneiden und hier reinschreiben. Und die Zeile kann wegfallen. Also, anstatt zu markieren und sich auf Selection zu beziehen, schreiben Sie doch lieber gleich rein, was Sie meinen. Und noch viel besser: Diese Zeile ist dann auch überflüssig, weil sich die Selection gar nicht geändert hat. Wenn Sie sich das nochmal angucken wollen... Ich muss jetzt hier wieder eine komplizierte Markierung machen, mit gedrückter Maustaste, Und da ist meine ActiveCell, ich muss nur gucken, dass daneben eben noch was frei ist. Und jetzt kann ich es nicht mit STRG + "R" starten, sondern ich muss das hier mit F5 starten. Und wenn ich jetzt drüben nachgucke, ist hier rot gefäbt und trotzdem die Markierung erhalten. Es wird nicht nur das Makro kürzer, sondern vor allem verlieren Sie nicht die Selection. Und das macht unter anderem dieser "Offset"-Befehl. Ich kann mich, egal wo ich stehe, auf eine Nachbarzelle beziehen, ohne da hin gehen zu müssen. Und dieser "Offset"-Befehl ist sehr unscheinbar, aber sehr wichtig. Häufig müssen Sie nämlich in der Nachbarzelle, daneben, darüber, darunter, oder wo auch immer arbeiten. Und diese speziellen will ich Ihnen zeigen. Und zwar einfach, indem ich das bunt mache. Dafür gehe ich mal lieber in eine ganz beliebige andere Tabelle hier. Da will ich diese vier Nachbarzellen unterschiedlich färben. Ich stehe mittendrin und fange jetzt also an, mit "Sub AlleVierBunt" nenne ich das mal, und werde entsprechend einen "Range deklarieren. Also einen "dim RangeRechts as Range". Dasselbe für Links. Das ist jetzt sehr viel Schreibarbeit, aber dadurch wird deutlicher erkennbar, was wo passiert. "dim RangeOben as Range" Soviel Schreibarbeit ist es nun auch wieder nicht. "as Unten as Range". Falls Sie übrigens mehrere hintereinander schreiben wollten. Ich mache das normalerweise nicht, aber ich sehe das häufig, nämlich durch Komma getrennt. Machen wir das mal beispielhaft für den hier. Dann muss das so aussehen. Dann wird das zwar in der Zeilenanzahl kürzer, aber die werden sehr breit. Das ist die korrekte Deklaration für vier Range-Objekte. Was ich aber oft sehe, - also ich mache mal eine Kopie, damit ich es dahinter schreiben darf - wie entsetzlich das ist, ist diese Fassung, weil das ja so schön bequem ist. Und das ist deswegen falsch, weil diese drei Variablen nur Variant sind, und nur der ist Range. Also wenn es denn unbedingt mal nebeneinander und nicht untereinander sein soll, was ich persönlich, untereinander übersichtlicher finde, dann bitte jeden einzelnen mit Datentyp, ansonsten ist er nur Variant. Und da sparen wir aber nicht wirklich viel, wir brauchen sie nämlich jetzt doch untereinander. Jetzt erkläre ich jedem Einzelnen, wo er sich befindet. Also sagen wir "RangeRechts", das ist von "ActiveCell" aus gesehen, per "Offset", Klammer auf, in eine bestimmte Richtung. Sie sehen hier jetzt ein "RowOffset" und ein "ColumnOffset". "Row", die Zeile, gibt jetzt an, positiv nach unten, negativ nach oben, aber ich will mich in der Zeile gar nicht bewegen, also "0". Komma, "ColumnOffset", positiv nach rechts, negativ nach links. Ich will nach rechts und zwar ein Stück, eine Zelle weiter. Das ist die Nachbarzelle rechts. Wird einfach durch "0,1" angegeben. Und Sie sehen schon, durch das Kopieren, da wird sich sehr viel ähnlich sein. Also, links ist das Gleiche einfach mit "-1". Das "ColumnOffset", die Spalten werden nach links einen verschoben. Dann gibt es ein "Oben". Da ist es hier andersrum. Da wird minus eine Zeile nach oben gegangen, Rows "-1" und Column bleibt. Und entsprechend hier für "Unten", da geht's um eine Zeile nach unten. Aber da muss ich hier natürlich auch ergänzen. Also das sind die vier Deklarationen, für die Zellen jeweils links, rechts, oben , unten. Und mit denen kann ich was machen. Dass Offset-Objekt gibt nämlich direkt ein Objekt, ein Cells-, ein Range-Objekt zurück. Deswegen funktioniert das so wunderbar. Und jetzt kann ich zum Beispiel sagen, von der Zelle rechts davon hätte ich gerne "Interior.Color", also die Innenfarbe, die vom Füllbereich, sagen wir auf "Green". Und von "Links" hätte ich gerne "Interior.Color" auf "vbRed". Das sind Konstanten, die die Farbnummern schon enthalten. "RangeOben.Interior.Color", dann sehen wir sehr schön, was passiert. ist gleich "vbBlue". "Blue" sollte das aber werden. Und "RangeUnten.Interior.Color" ist gleich "vbYellow". Ich mache das gerne so ausführlich. Grundsätzlich können Sie natürlich dieses Stückchen hier auch direkt da hinten dran hängen. Allerdings dann nicht mehr einem einzelnen Range zuweisen. Also wenn Sie es wirklich so schreiben wollten, dann müsste es so aussehen. Aber das macht es nicht unbedingt übersichtlicher, da muss man nämlich immer drüber nachdenken, ob "0,1" jetzt nach rechts oder nach oben oder nach unten ist. Deswegen gönne ich mir schon hier einzelne Ranges, die das dann im Namen enthalten. Wenn ich einen Fehler habe muss ich ihn nur hier suchen. Hier kann ich erwarten, dass der Range sich an der richtigen Stelle befindet. Also wenn ich das jetzt DebuggenKompilieren, checken lasse ist alles in Ordnung. Einmal nochmal gucken, wo ich stehe. An der Zelle. Und wenn ich jetzt mit F5 starte, dann sehen Sie drum rum alle leuchtenden Farben, ohne dass ich meinen Zell-Zeiger irgendwie verschoben hätte. Ich kann das gerne in einer anderen Tabellen noch mal machen, mit einer sehr komplizierten Markierung. Und zum Schluss habe ich diese Zelle angeklickt. Und wenn ich das jetzt wieder mit F5 laufen lasse, dann sehen Sie: Die komplizierte Markierung ist immer noch da, und diese Zelle hat aber ihre Farben gekriegt. Also sie sind sowohl sprechender, - das bringt immer eine Menge, sich seine Range-Bereiche auch vernünftig zu benennen - als auch vor allem effektiver. Sie müssen nicht dauernd den Zell-Zeiger bewegen, und Sie verärgern auch nicht Ihren Benutzer, der nämlich irgendeine Markierung, die er sich vorher angelegt hat, bei der Gelegenheit unfreiwillig verliert.

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!