März 2017

Der VBA-Kracher ‚Äď M√§rz 2017

In der ersten Ausgabe des VBA-Krachers werden die folgenden Themen behandelt:

  • Automatische Formatierung von Diagrammen auf Knopfdruck
  • Suchen nach bestimmten Namen via Wildcards
  • Leere Zeilen in einer Tabelle automatisch entfernen
  • Zwei ‚Äěidentische‚Äú Bereiche miteinander vergleichen
  • Suche nach einem Begriff unter Ber√ľcksichtigung der Formatierung

Diagramme automatisch formatieren

Die Bearbeitung von Diagrammen mit Hilfe von VBA ist eine lohnende Sache. Erfahren Sie , wie Sie die Farben von einem gestapelten Säulendiagramm blitzschnell mit vorher definierten Farben beispielsweise nach Firmen-CI einfärben können. Sehen Sie sich zunächst einmal die folgenden beiden Abbildungen an.

Die Kategorien des Säulendiagramms befinden sich in den Zellen A24:A27. Über diesen Namen der Kategorie wird auf der Tabelle tbl_Farben die entsprechende Kategorie in Spalte A gesucht und die dazu gehörige Farbe ermittelt.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Die Einstellung, welche Farbe f√ľr die Kategorien angewendet werden soll, wird in den Zellen Dropdowns B1:B4 der Tabelle tbl_Farben angegeben.¬†Die dort eingestellten Farben werden in Spalte G gesucht und die dazu geh√∂rigen Farbwerte aus den Spalten H bis J entnommen.¬†Das Makro, welches diese Aufgabe erledigt sieht dabei wie folgt aus:

Sub FarbenAnwenden()
 Dim ch As ChartObject
 Dim i As Integer
 Dim strKategorie As String
 Dim rngTreffer As Range
 Dim rngTrefferRGB As Range

 Set ch = Tabelle2.ChartObjects(1)

 For i = 1 To ch.Chart.SeriesCollection.Count

 strKategorie = ch.Chart.SeriesCollection(i).Name 

 Set rngTreffer = tbl_Farben.Range("A:A").Find _
  (what:=strKategorie, lookat:=xlWhole)

 If Not rngTreffer Is Nothing Then

 Set rngTrefferRGB = tbl_Farben.Range("G:G").Find _
 (what:=tbl_Farben.Cells(rngTreffer.Row, "B").Value, lookat:=xlWhole)

  If Not rngTrefferRGB Is Nothing Then

   ch.Chart.SeriesCollection(i).Format.Fill.ForeColor.RGB = _ 
   RGB(tbl_Farben.Cells(rngTrefferRGB.Row, "H"), _
   tbl_Farben.Cells(rngTrefferRGB.Row, "I"), _
   tbl_Farben.Cells(rngTrefferRGB.Row, "J"))

  End If
 Else
 Debug.Print strKategorie & " nicht gefunden!"
 End If

 Next i

End Sub
Quelle:VBA-Tanker, ID8513

Geben Sie zun√§chst √ľber die Anweisung Set an, auf welcher Tabelle das Diagrammobjekt sich befindet.¬†Danach setzen Sie eine For Next Schleife auf, √ľber die Sie die Datenreihen des Diagramms nacheinander verarbeiten.¬†Innerhalb der Schleife f√ľhren Sie eine Suche aus, bei der Sie den jeweiligen Reihennamen (strKategorie) in der Tabelle¬†tbl_Farben in Spalte A suchen.
Wird die Kategorie in der Steuertabelle gefunden, dann greifen Sie auf die daneben angegebene Farbe zu und suchen diese¬†im „Farbkatalog“ in Spalte G.¬†Wird die Farbe gefunden, dann √ľbergeben Sie der Funktion RGB die ben√∂tigten Farbkomponenten,¬†die Sie aus den Spalten H bis J entnehmen.Auf √§hnliche Art und Weise k√∂nnen beispielsweise auch die Anordnung der S√§ulen im¬†Diagramm oder Bez√ľge auf Tabellen und Bereiche festgelegt werden.

Suchen nach bestimmten Namen via Wildcards

Beim folgenden Beispiel sollen alle Namen in einem Bereich automatisch gef√§rbt werden, die mit dem Buchstaben ‚ÄěM‚Äú beginnen. Setzen Sie dazu das folgende Makro ein:Suchen nach bestimmten Namen via Wildcards

Sub SucheNachMuster()

 Dim rngZelle As Range

  For Each rngZelle In Range("A1:A20")

   If rngZelle Like "M*" Then

    rngZelle.Interior.ColorIndex = 4

   Else

    rngZelle.Interior.ColorIndex = xlColorIndexNone

   End If

  Next rngZelle

End Sub
Quelle: VBA-Tanker, ID8541

In einer For each Next Schleife durchlaufen Sie Zelle f√ľr Zelle im Bereich A1:A20.¬†Innerhalb der Schleife wenden Sie den Operator Like in Verbindung mit der WildCard * an,¬†um alle Namen zu ermitteln, die mit dem Buchstaben ‚ÄěM‚Äú beginnen.¬† F√§rben Sie die so ermittelten¬†Zellen Gr√ľn, indem Sie das Objekt Interior √ľber die Eigenschaften ColorIndex die Nummer 4 zuweisen.

 

 

 

 

 

 

 

 

 

 

 

 

Leere Zeilen in einer Tabelle entfernen

Bei der folgenden Aufgabenstellung geht es darum aus einer Tabelle alle Zeile zu entfernen, die in der Spalte A keinen Eintrag haben.¬†Normalerweise k√∂nnte man diese Aufgabe ganz gut mit einer Schleife l√∂sen, die am End der der Liste beginnt und sich Zeile f√ľr Zeile¬†nach oben durch die Liste arbeitet. Gerade aber bei gr√∂√üeren Datenbest√§nden kann man dann aber getrost einen Kaffee trinken gehen,¬†um die Laufzeit des Makros zu √ľberbr√ľcken. Sie wollen diese Aufgabe aber schneller l√∂sen.
Daher wird der Ansatz gew√§hlt, die leeren Zeilen zu filtern und in einem Aufwasch zu entfernen.¬†Das Makro f√ľr diese Aufgabe entnehmen Sie dem folgenden Listing.

Sub LeereZeilenInTabelleEntfernen()

Application.DisplayAlerts = False

 With Tabelle1

  .Range("A1:D" & .UsedRange.Rows.Count).AutoFilter Field:=1,   Criteria1:=""

  .Rows(1).Hidden = True

  .UsedRange.SpecialCells(xlCellTypeVisible).Delete

  .Rows(1).Hidden = False

  .AutoFilterMode = False

  End With

  Application.DisplayAlerts = True

End Sub
Quelle: VBA-Tanker, ID8507

Schalten Sie zu Beginn des Makros Warnmeldungen in Excel tempor√§r aus, indem Sie der Eigenschaft¬†DisplayAlerts den Wert False zuweisen. ¬†Danach wenden Sie die Methode AutoFilter an, um den¬†Datenfilter in Excel zu einzusetzen. Im Parameter Field geben Sie die Spaltennummer an, die Sie filtern m√∂chten.¬†Im Parameter Criteria1 geben Sie eine leere Zeichenfolge an. Danach blenden Sie zus√§tzlich die erste Zeile (√úberschrift) aus,¬†damit diese nicht mit gel√∂scht wird. Dazu weisen Sie der Eigenschaft Hidden f√ľr die erste Zeile der Tabelle den Wert True zu.
√úber die Methode SpecialCells und der Konstanten xlCellTypeVisible lassen sich nun alle sichtbaren Zellen identifizieren,¬†die Sie direkt im Anschluss √ľber die Methode Delete l√∂schen. Blenden Sie danach die √úberschrift wieder ein und weisen der Eigenschaft¬†AutoFilterMode den Wert False zu, um den Datenfilter wieder auszublenden und somit auch alle Zeilen wieder anzuzeigen.

Zwei identische Bereiche miteinander vergleichen

Bei der folgenden Angelegenheit soll uns ein Makro helfen, Unterschiede zwischen zwei identisch aufgebauten Tabellen zu erkennen.
Dabei sollen Veränderungen in den Zellen mit der Farbe Rot automatisch gefärbt werden.

Das Makro f√ľr diese Aufgabe lautet

Sub √ĄnderungenKennzeichnen()

Dim rngzelle As Range


With tbl_Wichtig1

  For Each rngzelle In .UsedRange

  If rngzelle.Value <> _

   tbl_Wichtig.Range(rngzelle.Address).Value Then

    rngzelle.Interior.Color = vbRed

  Else

    rngzelle.Interior.Color = vbYellow

   End If

  Next rngzelle

End With

End Sub
Quelle: VBA-Tanker, ID8546

Als Basis f√ľr das Einf√§rben wird die Tabelle tbl_Wichtig1 verwendet. In einer For Each Next-Schleife werden¬†alle Zellen im verwendeten Bereich nach einander verarbeitet. Innerhalb der Schleife wird die jeweilige Zelle mit¬†der gleichen Zelle auf der Original-Tabelle tbl_Wichtig vergleichen. Bei einer Abweichung wird die Zelle in der¬†Tabelle tbl_Wichtig1 √ľber die Eigenschaft Color und der Visual Basic-Konstanten vbRed rot eingef√§rbt.¬†Unver√§nderte Zellen werden bei diesem Beispiel mit der Farbe Gelb √ľber die Konstante vbYellow hinterlegt.

Suche nach einem Begriff unter Ber√ľcksichtigung der Formatierung

Beim letzten Beispiel in dieser Ausgabe des VBA-Kracher-Newsletters soll in einer Tabelle nach einem bestimmten Namen gesucht werden. Dabei soll aber nur der Name gefunden werden, der zusätzlich auch Fett formatiert ist. Sehen Sie sich das Ergebnis davon aus der folgenden Abbildung an.

 

 

 

 

 

 

 

 

 

 

 

 

Der Suchbegriff, der in der ersten Spalte gefunden werden soll, befindet sich in Zelle D1. Wie Sie erkennen k√∂nnen,¬†w√ľrde der Begriff aber auch schon in Zelle A6 gefunden werden k√∂nnen. Wie k√∂nnen Sie jetzt verhindern, dass diese Zelle¬†gefunden wird? Ziel soll sein, dass die Zelle A11 gefunden wird. Diese Zelle wurde daher mit dem Schriftschnitt Fett formatiert.¬†Sehen Sie sich dazu das folgende Makro an. Es findet die richtige Zelle und f√§rbt diese Gr√ľn ein.

Sub SucheNachFormatUndInhalt()

Dim rngTreffer As Range

With Tabelle1

 Application.FindFormat.Clear

 Application.FindFormat.Font.Bold = True

 Set rngTreffer = .Range("A1:B20").Find _
 (what:=.Range("D1").Value, LookIn:=xlValues, lookat:=xlWhole, _
  SearchFormat:=True)

   If Not rngTreffer Is Nothing Then

    rngTreffer.Interior.ColorIndex = 4

   Else

    MsgBox "Der Name wurde nicht gefunden!"

   End If

 End With

End Sub
Quelle: VBA-Tanker, ID8536

√úber die Eigenschaft FindFormat k√∂nnen f√ľr eine anschlie√üende Suche auch Formate angegeben werden,¬†die ber√ľcksichtigt werden sollen. Sicherheitshalber sollten eventuell bereits vorher get√§tigte Einstellungen¬†mit Hilfe der Methode Clear entfernt werden. Legen Sie im Anschluss daran die gew√ľnschte Formatierung fest.¬†In diesem Beispiel wurde der Schriftschnitt Fett √ľber die Eigenschaft Bold eingestellt.¬†In der anschlie√üenden Suche √ľber die Methode Find geben Sie unter anderem als Parameter das SearchFormat an.¬†Damit wird neben der eigentlichen Suche auf noch der Schriftschnitt Fett mit einbezogen. Im Parameter What wird¬†der Suchbegriff angegeben. Dieser wird direkt aus der Zelle D1 bezogen. Im Parameter Lookat legen Sie fest, dass die
Suche vollqualifiziert stattfinden soll, d.h. es muss eine 100% Übereinstimmung des Suchbegriffs mit der gefundenen Zelle zutreffen. Im Parameter LookIn können Sie festlegen, dass in den Werten der Tabelle gesucht werden soll. Die Reihenfolge der Parameter ist egal, solange Sie jeweils den Parameternamen mit angeben.

Schlusswort

Alle hier vorgestellten L√∂sungen stammen aus meinem VBA-Tanker, den ich w√∂chentlich bis zu drei Mal aktualisiere¬†und meinen Kunden zur Verf√ľgung stelle. In dieser VBA-Datenbank sind derzeit 6.740 Prozeduren enthalten, die √ľber¬†ausgefeilte Suchfunktionen gefunden werden k√∂nnen. Zu den meisten VBA-Codes gibt es Beispieldateien, die zum Download
auf Knopfdruck bereitstehen und frei verwendet werden d√ľrfen.¬†Mehr Informationen zu meinem Produkt k√∂nnen Sie auf der Internetseite http://held-office.de/vba-repository/ erhalten.