Excel 2016 VBA Grundkurs

Cells

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Sie können mehrere Zellen eines Bereichs mit einer For-Each-Schleife untersuchen oder direkt mit einer SpecialCells-Funktion markieren.

Transkript

Sie werden es in der Excel VBA-Programmierung oft nicht nur mit einer einzigen Zelle zu tun haben, sondern mit vielen. Deswegen möchte ich Ihnen zeigen, wie Sie mit vielen Zellen leicht umgehen. Und am leichtesten immer mit einer "For Each"-Schleife. Ich mache das hier mal als Beispiel: Ich habe in einer Tabelle schon testweise völlig beliebige Daten eingegeben. Unwesentlich formatiert. Das ist irgendein beliebiger Datenblock. Den will ich mir angucken. Also das Einzige, was ich weiß, ist dass ich in ActiveSheet drin bin. und deswegen werde ich das entsprechend berücksichtigen. Also möchte ich davon alle Zellenadressen beispielsweise sehen: "Sub AlleZelladressen" Wenn ich die habe, könnte ich auch mit der Zelle selber arbeiten. inhaltlich auslesen, farbig verändern oder so was. Dazu brauche ich erst mal ein WKS-Objekt. Nicht zwingend, aber es ist netter, also "wksDieses as Worksheet". Und dann ein Range-Objekt. Also auch "dim Range". Nennen wir das ruhig Zelle, dann ist das deutlicher, "as Range" und kann dann sehr schön erstmal zuweisen, allerdings vor der Schleife. mit "set wksDieses" gleich "ActiveWorksheet"... Nein, nicht "ActiveWorksheet", sondern "ActiveSheet" heißt es nur. Das ist genau das, warum ich ndas umspeichere. ActiveSheet könnte nämlich auch ein Diagramm sein. Ich verlasse mich jetzt also einfach darauf, dass es ein Worksheet ist, sonst würde es hier einen Laufzeitfehler geben. Und deswegen ist ActiveSheet so unspezifisch und sagt Ihnen nichts über seine Eigenschaften, weil die sich zwischen einem Worksheet und einem Diagramm eben unterscheiden. Also, "wksDieses" weiß ich jetzt sicher, ist ein Worksheet, sonst hätte es einen Laufzeitfehler gegeben, wenn ich das starte. Und jetzt kann ich nämlich sehr bequem "for each RangeZelle", "in wksDieses", und zwar in dem "UsedRange". Er liefert eine Cells-Auflistung, einen Range zurück. Und damit habe ich jetzt jede Zelle einzeln in der Hand. Ich kann also mit "debug.Print" einfach mal "RangeZelle" mal nach "AddressLocal" fragen, und wenn wir schon dabei sind, auch nach Ihrem Inhalt, also "RangeZelle.Value" Das braucht unten ein klein bisschen mehr Platz. Und dann gucken wir nochmal eben, es ist nichts markiert, das ist übrigens auch Absicht. Ein Makro wird nicht besser, wenn Sie dauernd was markieren. Dann wir der Benutzer nur wahnsinnig, wenn er hier markiert, markiert, markiert, während des Makroablaufs immer solche Blitze sieht. Im Prinzip Markierung an, Markierung wieder aus. Also, den "Select"-Befehl, wenn es irgendwie geht vermeiden, den habe ich nur zu Testzwecken. Der Benutzer behält seine Markierung hier, und trotzdem kann ich hier alles auslesen. Wenn ich das jetzt starte, mit DebuggenKompilieren einmal checken: Alles syntaktisch richtig. Und mit F5 sehen Sie, dass das hier alles ausgelesen ist. "B5" ist die Zelle oben links, die leer ist, hier diese Ecke. Und dann sehen Sie geht es hier zeilenweise weiter Januar, Februar, März, April, das ist völlig beliebig, und ich kann alle Sachen auslesen. Soweit, so einfach. Das lösche ich jetzt wieder, - mit STRG + "A" markieren, mit Entfernen wegnehmen - denn ich möchte ein kleines bisschen mehr. Dafür muss ich vorher ein bisschen manipulieren. Ich werde also hier diese Zellen, - die habe ich jetzt mit gedrückter STRG-Taste mehrfach markiert - werde ich entfernen, völlig zufällig. Und ich möchte die unterscheiden können von den übrigen. Also sagen wir mal, alle die nicht leer sind nur noch auflisten. Und das bedeutet, ich mache erstmal fast das Gleiche Also mit gedrückter STRG-Taste runterkopieren. Und natürlich hier den Namen ändern, sonst wir der Editor schon bei dem Versuch IntelliSense auszuführen scheitern. Und jetzt will ich hier unterscheiden, zwischen Zellen, die was drin stehen haben und anderen. Das heißt ich muss nur hier ein "If" einbauen. Ich kann zum Beispiel einfach prüfen, ob da eine Zahl ist, oder viel bequemer, ob da überhaupt was drin steht, Also "If " von "RangeZelle" natürlich ".Value", ob da ungleich leer, also etwas drin steht, nur dann mache ich meinen "debug.Print", und da kommt das "End If" Das fühlt sich immer noch wie guter Code an. Es ist ja nur eine Zeile Änderung. Jetzt muss ich hier mal gucken, dass ich genug Platz finde. Und wir können das einfach mit F5 starten. Und allein die Tatsache, dass unten in jeder Zeile ein Wert steht, beweist mir, dass es geklappt hat. Zum Beispiel, wenn ich mal prüfe: Die Zelle "D7" ist leer, die darf da unten nicht vorkommen. Hier ist "D6", da ist einen darunter, "D8" sowieso nicht, die ist nämlich auch leer. Müssen wir prüfen, genau. Hat also geklappt. Ich sehe es auch schon daran, dass unten überall Inhalte stehen. Und da lehnt man sich dann entspannt zurück und denkt: Alles super. Und an der Stelle muss ich dann leider sagen, - es geht ja hier um kleine Datenmengen - das ist Beschäftigungstherapie. Das geht schneller, und zwar in jeder Hinsicht. Erstens mit weniger Zeilen und Zweitens auch im Ablauf. Unheimlich viel schneller. Diese "For Each"-Schleifen sind schön, da gibt es auch nichts zu meckern. Die sind wunderbar, wenn Sie die brauchen. aber Excel ist besser. Wenn Sie also eine Excel-Fähigkeit finden, die das gleiche kann, dann ist die um Dimensionen besser. Kann ich Ihnen garantiert versprechen. Und vor allem um Dimensionen schneller. Und diese Fähigkeit gibt es. Ich klicke jetzt irgendwo hier rein. Ich rufe das selber immer mit der F5-Taste auf, denn ich bin ja nicht im VBA-Editor, F5- Ausführen, sondern F5 - Gehe zu. Offiziell steht das hier drin. Das Tastenkürzel steht leider nicht mehr da drin. Also, F5 oder diesen Befehl, dann kommen Sie in den Gehe zu-Dialog. Der an sich ist tendenziell ein bisschen langweilig, aber der hat hier noch eine Schaltfläche für Inhalte. Und die ist spannend. Die ermöglicht konkret, bestimmte Objekte zu markieren. Innerhalb dieses Blockes zum Beispiel. Der ist nicht besonders intuitiv, das kann man nicht behaupten. Dass die Formeln hier die Checkboxen berücksichtigen, das kann man ja noch einsehen. Aber das die Konstanten das auch tun, kann man leider nicht wirklich verstehen, das ist eher zufällig. Sie merken es daran, dass es hier grau wird. Aber den hier zu überspringen, um die zu berücksichtigen, ist ein bisschen gewagt. Und ich möchte alle Konstanten markieren, die im Prinzip alle Checkboxen hier haben. Fehler- und Wahrheitswerte habe ich nicht, aber Texte und Zahlen. Kurz gesagt: Ich möchte alle Konstanten markieren, zum Beispiel keine Leerzellen. Und nach dem OK ist genau das passiert. Alles was ich eben mühsam in der "For Each"-Schleife gesucht habe, lässt sich mit einem einzigen Befehl erzeugen. Und das ist das, was ich jetzt auch nutzen will. Ich mache die Markierung mal wieder weg mit ALT + F11. Ich werde jetzt also, anstatt da mit einer "For Each"-Schleife durchzugehen, diesen "SpecialCells"-Befehl nutzen, der hinter dem Dialog steckt. Also, machen wir ruhig das Gleiche, "Sub AlleZellenMitInhaltMarkieren". Und dann brauche ich mal diese drei Zeilen. Eigentlich nur, damit es ordentliche Programmierung ist. Man kann das theoretisch in einer einzigen Zeile machen, aber das macht es nicht übersichtlicher. Und jetzt kann ich hier "Set Range", -naja, "Zelle" ist falsch - sagen wir "Genutzt". Also "Set RangeGenutzt" ist gleich nämlich "wksDieses.UsedRange". Und jetzt kommt der eigentliche Befehl, also mit "RangeGenutzt". der kennt einen "SpecialCells"-Befehl. Der wird auf Ranges angewandt und, Klammer auf, dort gibt es einen "xlCellTypeConstants". Da sind relativ viele drin. Wo haben wir ihn? Da unten, Doppelklick. Und wenn ich im zweiten Parameter - da muss man am besten aufzeichnen, was dieser Dialog macht - im zweiten Parameter alle Checkboxen angeklickt haben möchte, dann kommt dort die Zahl "23" raus. Das kriegen Sie beim Aufzeichnen diese Dialogs angezeigt und das will ich jetzt der Einfachheit halber mit "Select" auswählen. Sie könnten das - das will ich Ihnen alternativ eben zeigen - in einer Zeile machen, aber dann müssen Sie ganz viel selber schreiben. Dann würden Sie nämlich "ActiveSheet." und da ist es schon vorbei mit Automatik. Müssten jetzt wissen, es gibt einen "UsedRange". Immer noch keine Automatik. Müssten "SpecialCells" richtig schreiben, "xlCellTypeConstants", - ich kann da jetzt oben abgucken, und vertippe mich hoffentlich nicht - ".Select". Aha, ich habe mich schon vertippt, da ist ein "x" falsch. Ich sehe das daran, dass die Großbuchstaben fehlen. "xlCellTypeConstants". Im einfachsten Fall kopiere ich das jetzt runter, und hoffe, dass es dann alles richtig ist. Ich nehme es jetzt wieder in den Kommentar, es wird also nicht ausgeführt. Sie merken, alleine schon, das zu schreiben und die Schreibfehler zu finden, dauert viel länger, als sich ein klein bisschen mehr Mühe zu geben, und das Objekten zuzuweisen. Dann werden alle diese Funktionsnamen, oder die Konstanten freiwillig erscheinen. Und das ist genau das, was der Dialog gerade macht, der macht sozusagen in der einen Zeile... Hier ist es ein bisschen ordentlicher formuliert. Mit F5 ist jetzt nichts anderes passiert, als genau diese Zellen zu markieren.. Und die könnte ich jetzt beispielsweise gleichzeitig bunt färben, oder was immer ich damit machen möchte. Ich könnte auch mit einer "For Each"-Schleife wieder über diese Zellen gehen. Auch das geht. Ich könnte also, wenn ich das tatsächlich das weiter nutzen wollte... Das baue ich einmal kurz ein, mit dem "RangeInhalte", nenne ich das mal, "as Range". Ich kopiere mal die Zeile. "Set RangeInhalte =", und zwar natürlich ohne das "Select", dann habe ich jetzt in "RangeInhalte" selber alle Zellen, die davon betroffen sind, und könnte jetzt eine "For Each"-Schleife machen und dann steht an dieser Stelle "RangeInhalte". Da haben Sie also sehr viel Freiheit, vor allem, es geht schneller mit diesem "SpecialCells"-Befehl, als mit einer "For Each"-Schleife. Die brauchen Sie immer dann, wenn Excel Ihnen nichts Fertiges bietet.

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
Erscheinungsdatum:30.08.2016

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!