Ausgabe Mai 2017

Der VBA-Kracher – Mai 2017

der VBA-Kracher entwickelt sich pr√§chtig und die Abonnentenanzahl steigt kontinuierlich an. Ich werde auch in Zukunft versuchen, interessante und anspruchsvolle L√∂sungen vorzustellen. Gerne arbeite ich dabei auch mal eine Frage aus den XING-Foren ein. Denn aus den dort gestellt Fragen, l√§sst sich immer ein direkter Bezug zur Praxis herstellen, der auch f√ľr andere Anwender von Interesse sein k√∂nnte.

F√ľr die Mai-Ausgabe des VBA-Krachers haben wir uns folgende Themen √ľberlegt:

¬∑¬†¬†¬†¬†¬†¬†¬†¬† Erstellung eines Kalenders mit VBA ‚Äď Schritt f√ľr Schritt

¬∑¬†¬†¬†¬†¬†¬†¬†¬† Kriterien f√ľr eine Mehrfachfilterung aus Zellen beziehen

¬∑¬†¬†¬†¬†¬†¬†¬†¬† Eintr√§ge aus einer benutzerdefinierten Liste in eine ComboBox f√ľllen

·         Bestimmte Zeichenfolge in einem Bereich entfernen

·         Mit versteckten Bereichsnamen arbeiten

 

Erstellung eines Kalenders mit VBA ‚Äď Schritt f√ľr Schritt

Im ersten Beispiel soll ein einfacher Kalender in Excel √ľber ein VBA-Makro erstellt werden. Dabei werden Funktionen in Excel wie beispielsweise das AutoAusf√ľllen, die bedingte Formatierung und Bereichsfunktionen zum Einsatz. Werfen Sie zun√§chst einen Blick auf das fertige Ergebnis.

 

Das Makro, welches diese Aufgabe erledigt sieht dabei wie folgt aus:

Sub KalenderMitWochentagenErstellen()
 Dim lngZeileMax As Long
 Dim rngBereich As Range

 With Tabelle1
  .Range("A1").Value = DateSerial(2017, 1, 1)
  .Range("A1").DataSeries Rowcol:=xlColumns, _

                Type:=xlChronological, _

              Date:=xlDay, Step:=1, _

               Stop:=DateSerial(2017, 12, 31)
   lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
  .Range("B:B").NumberFormat = "DDDD"
  .Range("B1:B" & lngZeileMax).Formula = "=A1"
  Set rngBereich = .Range("A1:B" & lngZeileMax)
  rngBereich.FormatConditions.Delete
  rngBereich.FormatConditions.Add Type:=xlExpression, _
  Formula1:="=WOCHENTAG($A1;2)>5"
  rngBereich.FormatConditions(1).Interior.ColorIndex = 6
  rngBereich.Resize(, 1).HorizontalAlignment = xlCenter
 End With

End Sub
Quelle: VBA-Tanker, ID8716

Geben Sie im ersten Schritt des Makros die Tabelle an, auf der der Kalender eingef√ľgt werden soll. Danach schreiben Sie den ersten Tag des Jahres in Zelle A1. Das Datum wird hierbei √ľber die Funktion DateSerial √ľbergeben. Diese Funktion erwartet das Datum in den drei Komponenten Jahr, Monat und Tag. Wenden Sie danach die Methode DataSeries an, um die restlichen Datumsangaben nach unten auszuf√ľllen. ¬†Damit das Ausf√ľllen der Datumsangaben von oben nach unten stattfindet, verwenden Sie im Parameter RowCol die Konstante xlColumns. Die Schrittweite, n√§mlich immer ein Tag geben Sie im Parameter Step an. Damit Excel wei√ü, wann das AutoAusf√ľllen aufh√∂ren soll, formulieren Sie im Parameter Stop das Enddatum des Jahres. √úbergeben Sie auch hierbei mit Hilfe der Funktion DateSerial das Datum in dieser ‚Äěaufgespalteten‚Äú Form.

Mit Hilfe der Eigenschaft End ermitteln Sie die letzte belegte Zelle in Spalte A. Das sollte dann der 31.12.2017 sein. Da in Spalte B der ausgeschriebene Tagesnamen auf Basis von Spalte A erscheinen soll, formatieren Sie die komplette Spalte mit dem benutzerdefinierten Format ‚ÄěDDDD‚Äú √ľber die Eigenschaft NumberFormat. Stellen Sie danach f√ľr die Zelle B1 die Formel her, die die Zelle A1 verkn√ľpft. Dazu verwenden Sie die Eigenschaft Formula. Damit stehen in Zelle A1 und B1 jeweils das gleiche Datum, aber eben anders formatiert.

Im n√§chsten Schritt bilden Sie mit Hilfe der Anweisung Set einen Bereich, der beide Spalten (A+B) umfasst. Diese Vorgehensweise erleichtert die anschlie√üende Einf√§rbung der Wochenenden mit Hilfe der bedingten Formatierung von Excel. Zun√§chst einmal wird √ľber die Methode Delete eine eventuell bereits vorher eingestellte bedingte Formatierung gel√∂scht. Die Methode Add bezogen auf das Auflistungsobjekt FormatConditions f√ľgt jetzt die ‚ÄěWochenendregel‚Äú per Formel √ľber den Parameter Formula1 ein. Im Falle, dass ein Wochenende ermittelt wird, wird mit Hilfe der Eigenschaft ColorIndex die entsprechenden Zellen der Spalten A und B automatisch gef√§rbt.

Am Ende des Makro wird ein Teil des vorher gebildeten Bereichs mittels der Methode Resize abgesprochen, indem die Inhalte der Spalte A horizontal √ľber den Einsatz der Eigenschaft HorizontalAlignment zentriert werden.

 

Kriterien f√ľr eine Mehrfachfilterung aus Zellen beziehen

Der Datenfilter von Excel ist eine feine Sache, um auf schnelle Art und Weise Daten auszuwerten. Dabei k√∂nnen in einem Filter auch mehrere Kriterien eingestellt werden. Soweit so gut. Bei der nachfolgenden Abbildung handelt es sich um eine Frage im XING-Forum ‚ÄěEffizienter Arbeiten mit MS-Office:VBA‚Äú.

 

 

 

 

 

 

 

 

 

 

Auf den ersten Blick erscheint die Aufgabe, die Filterkriterien f√ľr den Datenfilter in Zelle A3 aus den Zellen G1:G3 zu beziehen als relativ gut l√∂sbar. Allerdings bei der Umsetzung macht Excel Probleme. Damit die Aufgabe erfolgreich gel√∂st werden kann, m√ľssen dem Datenfilter die drei Zelleninhalte aus G1:G3 als Datenfeld, also als Datentyp Variant √ľbergeben werden.

Sub FilterAnwenden()
Dim Vardat As Variant

  With Tabelle1
    Vardat = Array(.Range("G1").Text, .Range("G2").Text, .Range("G3").Text)
   .Range("$A$3:$D$12").AutoFilter Field:=1, _
   Criteria1:=Vardat, Operator:=xlFilterValues
  End With

End Sub

Quelle: VBA-Tanker, ID8719

Schon beim √úbergeben der drei Zelleninhalte, bei denen man ganz zwangsl√§ufig an die Eigenschaft Value denkt, erleidet man Schiffsbruch. Die Zelleninhalte m√ľssen als Text also √ľber die Eigenschaft Text in das Datenfeld bef√∂rdert werden.

Mit Hilfe der Methode AutoFilter wird √ľber den Parameter Field die erste Spalte im vorher angegebenen Bereich angesprochen. Dort wird im Parameter Criteria1 der vorher gef√ľllte Array √ľbergeben. Im Parameter Operator geben Sie die Konstante xlFilterValues an.

  ­
­ ­

Eintr√§ge aus einer benutzerdefinierten Liste in eine ComboBox f√ľllen

Beim folgenden Beispiel sollen die Wochentage aus der benutzerdefinierten Liste von Excel gezogen und in einem Dropdown zur Auswahl angeboten werden.

 

 

 

 

 

 

 

 

 

Der Inhalt der sechsten Liste soll angezapft und in einer ComboBox zur Verf√ľgung gestellt werden. Setzen Sie dazu den folgenden Einzeiler ein.

Sub WochenTageInDropdown()

 Tabelle1.OLEObjects("Combobox1").Object.List = _

 Application.GetCustomListContents(6)

End Sub

Quelle: VBA-Tanker, ID8626

Bei dem hier verwendeten Steuerelement handelt es sich um das Steuerelement aus der Symbolleiste ActiveX-Steuerelemente. Mit Hilfe der Eigenschaft List k√∂nnen Sie das Kombinationsfeld bef√ľllen. Mit Hilfe der Methode GetCustomListContens mit der laufenden Nummer 6 greifen Sie dabei auf das sechste Element der benutzerdefinierten Listen zu.

Hinweis: Die ersten 8 angebotenen Listen werden von Excel standardmäßig angeboten und können weder entfernt, noch in ihrer Position geändert werden. Weitere Listen können jederzeit benutzerdefiniert angelegt werden.

 

Bestimmte Zeichenfolge in einem Bereich entfernen

Bei der nachfolgenden Aufgabenstellung sollen in einem bestimmten Bereich eine bestimmte Zeichenfolge ersatzlos entfernt werden. Dazu kann dieses Makro eingesetzt werden:

Sub ZeichenfolgeErsetzenInBereich()
  Dim rngBereich As Range

  Set rngBereich = Tabelle1.Range("A1:A100")
   rngBereich.Replace "XYZ", "", xlPart

End Sub

Quelle: VBA-Tanker, ID5929

Definieren Sie im ersten Schritt des Makros eine Objektvariable vom Typ Range und geben Sie danach an, wo der zu verarbeitende Bereich in der Tabelle liegt. Wenden Sie danach die Funktion Replace an, der Sie im ersten Argument die zu l√∂schende Zeichenfolge √ľbergeben. Im zweiten Argument √ľbergeben Sie eine leere Zeichenfolge, was eben bewirkt, dass die gesuchte Zeichenfolge durch eine leere Zeichenfolge ersetzt wird. Im nachfolgenden Argument geben Sie die Konstante xlPart an, damit teilqualifiziert gesucht wird.

Hinweis:

Die Funktion Replace kann √ľbriges auch mit Steuerzeichen wie beispielsweise dem Zeilenumbruch verwendet werden. So entfernt das folgende Makro alle Zeilenumbr√ľche in der ersten Zeile der aktiven Tabelle.

Sub ZeilenUmbruchEntfernenAusKompletterZeile()
 Rows("1:1").Replace What:=vbLf, Replacement:="", LookAt:=xlPart
End Sub
Quelle: VBA-Tanker, ID5977

 

Mit versteckten Bereichsnamen arbeiten

Gerne arbeite ich in Excel mit benannten Bereichen. Dies macht die Formeln sprechender und leichter verst√§ndlich. Allerdings mag ich es dann nicht, wenn die benannten Bereiche im Namens-Manager von jedermann angesehen und mit Leichtigkeit gel√∂scht werden k√∂nnen. Daher lege ich benannte Bereiche gerne mit einem Makro, versteckt f√ľr andere an.

Sub UnsichtbarenNamenVergeben()
 Dim lngZeileMax As Long

With Tabelle1

       lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row

       ThisWorkbook.Names.Add Name:="BWA", _

       RefersTo:=.Range("A2:D" & lngZeileMax), _

       Visible:=False

  End With

End Sub

Quelle: VBA-Tanker, ID6893

Zun√§chst wird dynamisch die letzte, belegte Zeile in Spalte A ermittelt. Danach kommt die Methode Add zum Einsatz, um einen benannten Bereich zu erstellen. Im Parameter Name wird der gew√ľnschte Namen festgelegt. Im Parameter RefersTo geben Sie an, auf welchen Bereich sich der Namen beziehen soll. Im Parameter Visible geben Sie an, ob der Namen versteckt oder im Namens-Manager von Excel angezeigt werden soll. Setzen Sie diesen Parameter auf den Wert False, um den Namen versteckt anzulegen.

Um zu testen, ob die Anlage des versteckten Namens erfolgreich war, können Sie die Zeile

?Range(„BWA“).Address

Im Direktfenster der Entwicklungsumgebung eingeben und mit Enter bestätigen.