(C) 2009 - 2014 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“.

Diesen Artikel habe ich auch in meinem Blog bei www.software.maninweb.de
und im englischen Excel Ticker publiziert.

Mourad Louha

Über den Autor · Mourad Louha

Mourad arbeitet seit 2005 als selbständiger Softwareentwickler und Trainer und 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 in Zusammenarbeit mit Freunden und Partnern, wie das Forum zu Microsoft Office 365 und Office 2013, den Excel Soccer oder den Excel Formel Übersetzer. Für sein außergewöhnliches Engagement wurde er seit Januar 2011 jährlich von Microsoft als Microsoft Most Valuable Professional (MVP) ausgezeichnet.

 
Comments

schönes Tool, wär schon, wenn das Fenster nicht die ganze Zeit offen bleiben müsste.

Hallo Vlad,

Vielen Dank, auch für die Anregung. Momentan bin ich sehr eingespannt, aber sobald etwas Zeit ist, schaue ich mal, ob sich es realisieren lässt, dass das Fenster ausgeblendet werden kann.

Gruß :-)

Super! Genau das, was ich gesucht habe. Das Fenster lässt sich problemlos an den Rand schieben und stört zumindest mich nicht.

Hallo Chris

Danke schön, freut mich :-)

Viele Grüße

Vielen Dank für das Tool.
Funktioniert Prima :)

Hallo Mark – Vielen Dank :-)

zum Verstecken, das geht einfach…
Alt+F11 drücken
dann bei Formulare auf „MLF_Toolbar“ klicken
dann unten einen Doppelklick auf das Feld neben dem Kreuz und im Dialog Fenster
dann kommt Ihr in das Code Eingabefeld
Private Sub NUL_Buttons_Click()

End Sub

zu
Private Sub NUL_Buttons_Click()
MLF_Toolbar.Hide
End Sub
Ändern und Speichen, wenn Ihr nun auf das Feld neben dem Kreuz klickt, bleib das Kreuz aber der Dialog verschwindet, zum Ausschalten noch einmal auf den „Highlighter Button“ klicken, nun könnt Ihr den Dialog schließen.
Übrigens danke schönes Tool, hilft hier gut

Hallo Jürgen, Danke :-)

Prima Tool, vielen Dank!

Hallo Markus, vielen Dank. Gruß :-)

Hey,
tolles Tool, auch das Verstecken des Fensters funktioniert dank Anleitung vom Jürgen problemlos.
Leider nur funktioniert die Tastenkombination STRG+Z nicht mehr (rückgängig machen) – vermutlich weil hier ein Makro läuft, dessen Funktion man – wie auch bei anderen Makros – nicht rückgängig machen kann.
gibt es dafür auch eine Lösung?
Lieben gruß
Andreas

Hallo Andreas,

Danke. Stimmt! Das ist bislang noch niemanden aufgefallen, auch mir nicht. Zumal in Excel 2003 „einmal“ zurück geht. Ob’s eine Lösung dazu gibt, muss ich prüfen bzw. ich muss es mir anschauen; ist schon eine Weile her. Auf jeden Fall, herzlichen Dank für die Rückmeldung.

Gruß :-)

Hallo,
genau das Tool, das ich lange gesucht habe. Bin Lehrer und setze es in Notentabellen ein. Nun ist es viel einfacher zu sehen, welche Note zu welchem Schüler gehört.
Allerdings sind meine Tabellen voll mit komplizierten Funktionen (jedenfalls sind diese Befehle für mich kompliziert, in jedem Fall aber lang und unübersichtlich). Daher benutze ich diese Blätter nur im geschützten Modus. In diesem Zustand jedoch funktioniert das Tool nicht bzw. es lässt sich im ungeschützten Zustand zwar starten und in geschütztem Modus weiter verwenden. Nach Beendigung des Tools friert dann die letzte Markierung aber ein.
Wenn ich also jetzt mit dem Tool arbeiten will, muss ich zuerst das Blatt in den ungeschützten Modus versetzen, was mir immer ein wenig riskant vorkommt, zumal ich ja auch Ereignisse mit STRG+Z nicht rückgängig machen kann.
Ich fürchte, dass sich hier so leicht keine Verbesserung anbringen lässt. Wäre aber richtig gut!
Vielen Dank jedenfalls für deine gewinnbringende Arbeit!
Joachim

Hallo Joachim,

vielen Dank!

Das Tool fügt ein oder mehrere Autoformen in die Tabelle ein, um das Visualisieren zu ermöglichen. Und geht dabei dann von einem ungeschützen Blatt aus. Leider gibt es kein Ereignis in Excel, so dass ich das Schützen bzw. Aufheben des Schutzes (also wenn der User dies aktiv macht) abfangen könnte. Es bliebe hierzu ein Timer, der das Blatt ständig abfragt und prüft, ob ein Schutz gesetzt wurde. Ggf. wäre es einfacher, wenn beim Schließen des Dokuments nochmal gecheckt wird, ob die AutoFormen eingefügt wurden und dann eine Meldung ausgegeben wird. Insgesamt bedeutet dies aber schon ein Umnudelns des Tools. Werde mal genauer drüber nachdenken und dies auch ggf. implementieren. Kann aber wegen Zeitmangels etwas dauern. In jedem Fall, vielen Dank für Deine Anregungen.

Viele Grüße, Mourad

Hallo Mourad,
danke für deine Antwort. Nun habe ich mich doch noch ein wenig mit meinem Problem befasst und eine, wie ich meine, brauchbare Lösung gefunden.
Das Tool kann ich nur im ungeschützten Modus starten. Wenn ich gleich nach dem Start den Blattschutz wieder einstelle, funktioniert alles bestens. Erst wenn ich zwischenspeichere (Strg+S), ist das Tool nicht mehr aktiv und die letzte Position der Balken ist eingefroren. Ich habe mir nun ein Makro gebastelt, welches zunächst den Schutz aufhebt, dann das Speichern durchführt, kurz mal bei der Nachbarzelle „vorbeischaut“ (sonst sieht man nach der Speicheraktion die Balken erst bei der nächsten Cursorbewegung wieder) und den Blattschutz wieder einschaltet. Funktioniert bestens und ich muss meinen Blattschutz während der Arbeit nicht aufgeben. Hier der Code (der Makro heißt bei mir schlicht „Speichern“):

Sub Speichern()
ActiveCell.Select
ActiveSheet.Unprotect
ActiveWorkbook.Save
ActiveCell.Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Select
ActiveSheet.Protect
End Sub

Bei der ganzen Prozedur bleibt die zuletzt bearbeitete Zelle aktiv.
Was meinst du?
Viele Grüße, Joachim

Hallo Joachim,

schön, dass Du eine Lösung gefunden hast. Die Zeile ActiveCell.Select bräuchtest Du m.E. nicht, stört aber eigentlich nicht.

Viele Grüße, Mourad

Hallo,
aller guten Dinge sind drei…
Eine Frage noch: Wie kann ich den Highlighter mit einer bestimmten Farb- und Leistenlängekonfiguration per VBA starten bzw. ausschalten? Würde mich sehr freuen, wenn das funktionieren könnte!
Vielen Dank schon mal und schönes Wochenende!
Joachim

Hallo Joachim,

dazu könntest Du im Highlighter eine Prozedur (z.B. mlfpMainRunExternal) in einem Modul (z.B. MLP_Main) anlegen, die beispielsweise folgenden Inhalt hat:

Public Function mlfpMainRunExternal()
MLF_Toolbar.Show
MLF_Toolbar.CMB_Cols.ListIndex = 4
MLF_Toolbar.CMB_Rows.ListIndex = 4
MLF_Toolbar.RDB_Color_3.Value = True
End Function

Diese Prozedur kannst Du dann aus einem anderen Projekt heraus aufrufen. Entweder, indem Du einen Verweis auf das Highlighter-Projekt setzt (Extras -> Verweise) oder per Application.Run (siehe VBA-Hilfe)

Gruß :-)

Das Tool ist wirklich super – genau so etwas habe ich gesucht und ich kann es auch als PC-Dummie alleine anwenden! Danke dafür!!!!

Hallo Susanne,

Vielen Dank :-)

Gruß, Mourad

Hallo,
ich habe mir den Code in eine bestehende Tabelle eingefögt.
Funktioniert eineandfrei.
Bis auf das Toolbox- Element, welches sich leider nicht angezeigt wird.
Ist es möglich mit maximalem Offset, Rows und Colums bei einer fest definierten Farbe, z.B. rot diese Aplikation ohne die Eingabe im Hintergrund zu starten?
Jedes mal, wenn ich eine Tabelle wechsele kommt das Formular!?
Wenn das so funktionieren würde währe es toll.
Vielen Dank für dieses tolle Add-In
Grüße
Markus

Hallo Markus,

wenn Du den Code in Deine Mappe eingefügt hast, hast Du auch die UserForm und die Klasse in Deine Mappe übertragen? Falls Nein, dann das Tool öffnen und alle Module sowie UserFormen im VBA-Editor vom Highlighter per Ziehen und Ablegen in Dein Projekt übertragen (falls nicht schon vorhanden).

Um das Tool ohne die UserForm verwenden zu können, lege ein neues Modul an, z.B. mit dem Namen MLP_Direct. Dann füge den Code hier in das Modul ein, den ich allerdings nicht detailliert getestet habe. Also bitte vor dem Testen, offene Mappen sichern:

' Options...
  
  Option Explicit
  
' Highlighter...
  
  Private mlvhHighlight As MLC_Highlight
  
' No User Interface...
  
  Public Function mlfpDirectDelete() As Long
  
'   Errors...
    
    On Error Resume Next
    
'   Delete...
    
    mlvhHighlight.Delete mlcpMainShapeColumn, False
    mlvhHighlight.Delete mlcpMainShapeRow, False
    
'   Clear...
    
    Set mlvhHighlight.Parent = Nothing
    Set mlvhHighlight = Nothing
      
'   Return...
    
    mlfpDirectDelete = 0
    
  End Function
  
  Public Function mlfpDirectCreate() As Long
    
'   Errors...
    
    On Error Resume Next
    
'   Clear...
    
    If Not mlvhHighlight Is Nothing Then
    
      Set mlvhHighlight.Parent = Nothing
      Set mlvhHighlight = Nothing
    
    End If
    
'   Create...
    
    Set mlvhHighlight = New MLC_Highlight
    Set mlvhHighlight.Parent = Application
    
'   Create...
    
    mlvhHighlight.Create mlcpMainShapeColumn, 8, RGB(196, 64, 64)
    mlvhHighlight.Create mlcpMainShapeRow, 8, RGB(196, 64, 64)
    
'   Return...
    
    mlfpDirectCreate = 0
    
  End Function

Mit mlfpDirectCreate() erzeugst Du dann die Hervorhebungen. Mit mlfpDirectDelete() löschst Du diese. Du kannst diese Funktionen dann z.B. in den Codemodulen für die Tabellenblätter einbauen bzw. aufrufen. Das Synchronisieren wann die Hervorhebungen erscheinen sollen oder nicht, musst Du dann aber schon selber implementieren.

Viele Grüße, Mourad

Matthias Pester

Hallo, die Idee ist gut. Ich brauche aber eine 64 bit-Version. Wird es eine geben?

Mourad Louha

Hallo Matthias,

ungetestet, aber Du könntest folgendes tun: lösche das Modul „MLP_Api“ aus dem VBA-Projekt. Öffne anschließend das Codemodul zu der UserForm „MLF_Toolbar“ und lösche die Zeile mlfpApiMenueRemove Me.Caption, True, True. Anschließend müsste es auch unter Excel 64 Bit laufen

Gruß

Matthias Pester

Ja, das geht, und offenbar ohne dass man das in die PERSONL.xlsm einbettet (das hatte ich aber schon gemacht), weil die Funktion vom Offenen Fenster abhängt. Leider kann ich das aber nicht verwenden, weil die Rechtecke im Vordergrund sind und man so nichts mehr in einer Zeile markieren kann und nur die Rechtecke verschiebt. Es war einen Versuch wert, da ein anderes Add on mein System zu sehr verlangsamt hat.

Mourad Louha

Hallo,

Du kannst über das Tool die Rechtecke, die durch das Add-In erstellt werden, jederzeit über den Dialog ein- und ausblenden.

Gruß

Leave a Reply