(C) 2009 - 2020 by Mourad Louha · Alle Rechte vorbehalten

Add-In zum Hervorheben von Zeilen und Spalten mit VBA in Excel

Seit geraumer Zeit werte ich meine Webserver Logfiles auch mit Excel 2010 aus. Klar, es gibt eine Menge Produkte zum detaillierten Auswerten von Logfiles, von welchen auch einige bei mir zum Einsatz kommen. Jedoch bietet mir Excel 2010 die Möglichkeit, die Rohdaten relativ schnell und einfach zu importieren und über benutzerdefinierte Filter spezielle Kennzahlen genauer anzuschauen. Leider ist es aber so, dass ich oft bei der Menge an Daten während des Lesens die Zeile am Bildschirm „verliere“. Nun kann man beispielsweise den Zoom höher setzen oder auch einen Rahmen um die Zellen legen; lieber ist mir jedoch ein Tool welches die Zeilen hervorhebt, sobald eine Zelle in der Tabelle angeklickt wird. Da Excel leider eine solche Funktionalität nicht zur Verfügung stellt, habe ich ein eigenes Add-In in VBA entwickelt. Folgend eine Abbildung des Tools:

VBA Highlighter

Das Excel VBA Add-In ist übrigens kostenlos und auch im Quellcode verfügbar; einen Downloadlink finden Sie am Ende des Artikels. Im folgenden werde ich kurz den Aufbau des Add-Ins beschreiben und ein paar Erläuterungen zum Code geben. Beachten Sie jedoch bitte, dass das Tool zu meinem privaten Zweck entwickelt wurde, ich also auf professionelle Testreihen unter verschiedenen Betriebssystem und Excel Versionen verzichtet habe. Auf meinem Windows 7 Rechner lief das Tool in Excel 2003, 2007 und 2010 ohne Probleme. Ausserdem ist anzumerken, dass allein schon auf Grund fehlender Ereignisshandler in Excel nicht alle Fälle abgefangen werden können.

Zunächst habe ich mir überlegt, welche Funktionen ich überhaupt brauche; folgende Features erschienen mir sinnvoll:

  • Das Tool soll eine Auswahlmöglichkeit bieten, ob Zeilen, Spalten oder beide hervorgehoben werden sollen.
  • Es sollte eine Möglichkeit vorhanden sein, um die Anzahl der Zellen einzustellen, die links, rechts, über und unter der aktiven Zelle hervorgehoben werden. Ein Hervorheben der gesamten aktiven Zeile und/oder Spalte ist allein schon aus Performancegründen nicht sinnvoll.
  • Schön wäre es, wenn eine Farbe zum Hervorheben ausgesucht werden könnte.

Anschließend habe ich mir einen Prototypen in Form einer Userform erstellt und hierbei passende Steuerelemente verwendet, folgend ein Screenshot der Userform:

VBA Highlighter

Für die Farben habe ich einfache statische Steuerelemente vom Typ „Label“ verwendet und diese mit einer Hintergrundfarbe eingefärbt. Bei der Initialisierung der Userform wird entsprechend folgender Code ausgeführt.

Sie sehen, im Code wird zunächst die Funktion „mlfpApiMenueRemove()“ aufgerufen, diese stützt sich auf Windows API Funktionen und entfernt zur Laufzeit das Schließen Kreuz am oberen rechten Rand der Userform. Sie finden diese Funktion im Modul „MLP_Api“. Anschließend wird eine Variable „mlvhHighlight“ instanziiert; dazu später mehr. Das Add-In speichert einige seiner aktuellen Einstellungen in einer Tabelle der Mappe. Die Initialisierung der Steuerelemente sowie das Einlesen der gespeicherten Werte findet ebenfalls in „UserForm_Initialize()“ statt.

Soweit so gut; im Laufe der Entwicklung des Tools stellt sich die Frage, wie denn am besten das Hervorheben der Zeilen und Spalten erreicht werden könnte. Eine Möglichkeit wäre gewesen, die betroffenen Zeilen und Spalten mit einer Hintergrundfarbe einzufärben. Das wäre jedoch verhältnismäßig sehr aufwendig geworden, denn man müsste sich ja immer den vorherigen Zustand der Zellen merken. Einfacher ist es, für das Hervorheben Rechtecke zu verwenden, die sich entsprechend an die Größe der Zellen anpassen. Hier sollte aber sichergestellt werden, dass die Rechtecke nicht mit einer Farbe gefüllt sind, denn sonst kann logischerweise nicht mehr auf die Zellen hinter dem Rechteck geklickt werden.

Mein Ziel war es ja, dass die Zellen immer in der aktuelle gerade aktiven Mappe in Excel hervorgehoben werden. Die bedeutet, dass ich dann abfangen muss, welche Mappe bzw. Tabelle aktiv ist. Und dies wiederum lässt sich nur mit einer Klasse realisieren, die auf globaler Ebene die gesendeten Ereignisse abfängt.

Insofern habe ich die Klasse „MLC_Highlight“ angelegt, die einerseits das Application Objekt referenziert und die Ereignisprozeduren implementiert.

In den Ereignissen „Class_Initialize()“ und „Class_Terminate()“ werden Startaufgaben und Aufräumarbeiten jeweils beim Anlegen und Beenden eines Objektes zur Klasse durchgeführt. Die weiteren Funktionen fangen die Ereignisse des Application Objektes ab. Das Ganz soll dann wie folgt funktionieren:

  • Wenn der User in eine andere Tabelle derselben Mappe wechselt, sollen eventuell vorhandene Rechtecke zum Hervorheben der Zeilen und Spalten aus der vorherigen Tabelle entfernt werden und neue in die aktive Tabelle eingefügt werden.
  • Wechselt der User zu einer anderen Mappe, werden auch hier die Rechtecke aus der zuvor aktiven Mappe entfernt und in der neuen Mappe angelegt.
  • Schließt oder speichert der User die Mappe, sollen auch hier wieder die Rechtecke entfernt werden.
  • Klickt der User auf eine Zelle in der aktiven Tabelle, sollen die Rechtecke zum Hervorheben automatisch verschoben werden.

In den jeweiligen Funktionen wird immer die Funktion „mlfhAction(…)“ aufgerufen, die nur der besseren Übersichtlichkeit halber erstellt haben. Folgend der Code dieser Funktion:

Sie sehen, je nachdem, welchen Wert „Action“ hat, werden weitere Aktionen durchgeführt, wie beispielsweise das Erstellen der Rechtecke in der Funktion „Create(…)“ oder Löschen in der Funktion „Delete(…)“. Wie Sie sicherlich schon festgestellt haben, sind meine beiden Rechtecke (horizontal und vertical) im Array „mlvhShapes()“ abgelegt. Dieses Array enthält zwei Elemente eines benutzerdefinierten Typs, in welchem auch die Variable „Recreate“ abgelegt ist, die bestimmt, ob die Rechtecke nach dem Löschen neu erstellt werden sollen. Ausserdem enthält der Typ Variablen zur Referenzierung eine Shape-Objektes, zur Farbe, zur Anzahl der Zellen, die umrandet werden sollen und zu den Namen der Mappe und der Tabelle. Schauen wir uns nun die Funktion „Create(…)“ etwas genauer an:

Zunächst werden einige Parameter eines Array Elementes von „mlvhShapes()“ gefüllt und anschließend abgefragt, ob die Tabelle, wo das Rechteck eingefügt werden soll, schreibgeschützt ist. Wenn ja, wird die Funktion wieder verlassen. Ansonsten werden die Startkoordinaten, Höhe und Breite anhand der jeweiligen Funktionen „mlfhLeft(…)“, „mlfhTop(…)“, „mlfhHeight(…)“ und „mlfhWidth(…)“ des Rechtecks ermittelt und gesetzt. Zudem werden ein paar Eigenschaften, wie Linienbreite oder Transparenz des Rechtecks gesetzt. Bei der Höhen- und Breitenberechnung ist auch zu berücksichtigen, wieviele Zellen in die jeweilige Richtung noch verfügbar sind. Und die verschiedenen Excel Versionen sind ebenfalls zu berücksichtigen, denn bekanntermassen stellen Excel 2007 und 2010 mehr Spalten und Zeilen zur Verfügung als Excel 2003. Schauen wir uns beispielhaft die Funktion „mlfhWidth(…)“ an:

Ja nachdem, ob es sich um das Rechteck handelt, welches horizontal oder vertikal dargestellt wird, wird eine andere Berechnungsmethode aufgerufen. In vertikaler Richtung ist das ganz einfach; die Breite entspricht der Breite der aktiven Zelle. In horizontaler Richtung wird zunächst die Zelle ermittelt, die „Offset“ Spalten rechts neben der aktiven Zelle liegt. Dabei werden ausgeblendete Spalten übersprungen; an dem Loop zu sehen. Ausserdem wird geprüft, ob ein Fehler auftritt, wenn ja, dann ist das Ende der verfügbaren Spalten erreicht.

Jetzt zu einem ganz interessanter Teil des Codes; nämlich die Funktion zum Löschen eines Rechtecks.

Hier wird geprüft, ob überhaupt ein Rechteck vorhanden ist und dieses anschließend gelöscht. Das funktionierte auch soweit ganz gut, bis auf den Fall wo ein Rechteck in einer Mappe gelöscht werden sollte, die nicht der aktiven Mappe entspricht und die aktive Tabelle schreibgeschützt war. Dann tauchte der Fehler 1004 auf. Glücklicherweise konnte aber „ransi“ aus meinem Lieblingsforum „Office-Lösung“ weiterhelfen. Statt „Shape(…)
.Delete“ aufzurufen, kann auf die undokumentierte Funktion „DrawingObjects(…).Delete“ verwendet werden. Dies funktioniert einwandfrei; Danke schön ransi.

Bleibt nun noch den Code mit der Userform zu verbinden, folgend beispielhaft der Code, wenn die Checkbox für Spalten angeklickt wird:

Je nachdem, ob das Häkchen gesetzt wird oder nicht, wird das entsprechende Rechteck über „Create(…)“ erzeugt oder über „Delete(…)“ entfernt. Die weiteren Steuerelemente sind in ähnlicher Art angebunden.

Abschließend sei noch zu erwähnen, dass das Add-In beispielsweise eine neue Symbolleiste unter Excel 2003 erstellt; in Excel 2007 oder Excel 2010 finden Sie das Symbol im Reiter „Add-Ins“ der Multifunktionsleiste bzw. des Menübandes. Das Add-In speichert sich nach jedem Schließen des Dialogfenster zudem selbst. Sie können das Add-In unter folgendem Link herunterladen:

» Excel Add-In zum Hervorheben von Zeilen und Spalten

Beachten Sie bitte, dass ich für das Add-In keinen Support leisten kann und auch keinerlei Garantie oder sonstige Gewährleistungen jeglicher Art übernehmen kann. Wichtige Dokumente sollten beispielsweise beim Testen auf Ihrem Rechner natürlich zuvor geschlossen werden. Gerne freue ich mich aber über Ihr Feedback, Anregungen oder falls Sie einen Bug finden sollten, über eine Nachricht. Folgend noch der Link zum Forum mit dem Beitrag von „ransi“.

Mourad Louha

Über den Autor · Mourad Louha

Mourad ist seit 2005 als unabhängiger Softwareentwickler tätig und spezialisiert auf Excel VBA-Anwendungen. Er lebt in Aachen, gelegen am Dreiländereck Belgien, Deutschland und Niederlande. In seiner Freizeit engagiert er sich in Online Communities rund um Office, schreibt beim Excel Ticker Artikel zu Office & Co. und betreibt weitere Community Projekte, wie sein Lieblingsprojekt zum Excel Formel Übersetzer. Für sein außergewöhnliches Engagement wurde er von Microsoft über viele Jahre hinweg als Microsoft Most Valuable Professional (MVP) ausgezeichnet.

 
Comments

Hallo Matthias,

ich habe Dir hier mal eine Version hier zum Download abgelegt. Das Zip-Archiv enthält beide Dateitypen, eine XLA und eine XLAM. Ich musste die Datei erst in ein XLSM konvertieren und konnte dann raus wieder ein XLAM machen. Probiere es bitte aus, ob es jetzt funktioniert.

Viele Grüße,
Mourad

Leave a Reply