Dezember

VBA-Kracher Dezember 2017

Ein langes Jahr geht langsam zu Ende und es kommen jetzt ruhigere Tage, an denen man √ľber das vergangene Jahr nachdenken und neue Pl√§ne f√ľr das Folgejahr schmieden kann. Unter anderen habe ich mich dazu entschlossen, ab Januar 2018 einen Excel-Tipps / Tricks Newsletter aufzusetzen. Der kostenloste Excel-Newsletter kann unter der URL:

http://held-office.de/excel-held-tippstricks/

abonniert werden.

Der vorliegende VBA-Newsletter, der nun bereits 8 Ausgaben hat, wird auch in 2018 flei√üig weiter geschrieben werden. Ich m√∂chte mich an dieser Stelle f√ľr das zahlreiche Feedback bedanken. Einige Themenw√ľnsche und Anregungen der Leserschaft¬† flossen in die vorherigen Ausgaben ein.

Die letzten drei Wochen waren gepr√§gt von interessanten Inhouse-Schulungen und VBA-Workshops, bei denen zahlreiche Ideen und W√ľnsche der Teilnehmer direkt vor Ort besprochen und umgesetzt wurden. Viele Makros aus diesen Terminen landeten bereits in meiner VBA-Datenbank ‚ÄěVBA-Tanker‚Äú. ¬†Heute m√∂chte ich Ihnen einige n√ľtzliche Makros der letzten drei Wochen vorstellen.

Im Dezember-VBA-Kracher Newsletter werden die folgenden Themen behandelt:

  • √Ąnderungen von Zellen automatisch in Kommentaren festhalten
  • Exportieren einer mehrspaltigen ListBox in eine Tabelle in einem Aufwasch
  • Eine Mehrfachsuche f√ľr eine Tabelle dynamisch und anwenderfreundlich programmieren
  • Color versus ColorIndex ‚Äď ‚ÄěDas Missverst√§ndnis des Monats‚Äú
  • In gesperrten Tabellen trotzdem √Ąnderungen per Makro durchf√ľhren d√ľrfen
  • TIPP: Autovervollst√§ndigen per Tastenkombination

 

√Ąnderungen in Zellen automatisch in Kommentaren festhalten

Die Dokumentation alter Zellenwerte in Excel kann man auf mehrere Art und Weisen erledigen. So k√∂nnen alle √Ąnderungen in einer Tabelle beispielsweise in einer versteckten Tabelle dokumentiert werden. Eine alternative M√∂glichkeit, alte Zellenwerte zu dokumentieren, bietet das Kommentarfenster an. So k√∂nnen alle √Ąnderungen einer Zelle l√ľckenlos aufzeichnet werden. Bei der folgenden L√∂sung werden Ver√§nderungen in der Spalte A dokumentiert. Dabei werden der Anwendernamen, das Datum und der neu erfasste Zellenwert festgehalten.

 

 

 

 

 

 

 

 

 

 

Das Ereignismakro Worksheet_Change wird direkt hinter der Tabelle eingepflanzt und sieht wie folgt aus.

Private Sub Worksheet_Change(ByVal Target As Range)
'schlägt an, wenn eine Zelle geschrieben wird
 Dim cmt As Comment

 If Target.Column <> 1 Then Exit Sub

 'Die Kommentaranzeige einstellen
 Application.DisplayCommentIndicator = xlCommentAndIndicator

¬†'√Ąnderungen farbig hervorheben
 Target.Interior.ColorIndex = 4

 'Kommentar anlegen bzw. Update des Kommentars
 If Target.Comment Is Nothing Then
   Set cmt = Target.AddComment(Environ("username") & "|" & Now & "|" & _
              Target.Value)
 Else
   Set cmt = Target.Comment
   cmt.Text cmt.Text & vbLf & _
   Environ("username") & "|" & Now & "|" & Target.Value

 End If

 'Automatisch das Kommentarfester in der Größe anpassen
  cmt.Shape.TextFrame.AutoSize = True

End Sub

√úber das Objekt Target kann gepr√ľft werden, in welchem Bereich der Tabelle eine √Ąnderung stattgefunden hat. Indem Sie die Eigenschaft Column nutzen k√∂nnen Sie die Spaltennummer der gerade ge√§nderten Zelle ermitteln. Ob Sie die dokumentierten √Ąnderungen im Kommentarfenster anzeigen lassen oder verbergen m√∂chten, bleibt Ihnen √ľberlassen. Wenn Sie der Eigenschaft DisplayCommentIndicator die Konstante xlCommentAnIndicator zuweisen, dann werden die √Ąnderungen in den Kommentaren f√ľr jedermann sichtbar am Bildschirm angezeigt. Weisen Sie der Eigenschaft die Konstante xlNoIndicator zu, dann findet die Dokumentation der √Ąnderungen unsichtbar im Hintergrund statt. Wenn Sie die zweite Option verwenden, dann sollte gepr√ľft werden, ob dieses Vorhaben auch ‚ÄěBetriebsratstechnisch‚Äú abgedeckt werden kann.

Jede √Ąnderung in Spalte A wird automatisch mit einer gr√ľnen Hintergrundf√§rbung belegt. Zus√§tzlich wird gepr√ľft, ob bereits eine Zellenkommentar in der Zelle vorhanden ist. Wenn nicht, dann wenden Sie die Methode AddComment an, um einen neuen Kommentar der Zelle hinzuzuf√ľgen. ¬†Mit Hilfe der Funktion Environ, der Sie die Konstante Username zuweisen, k√∂nnen Sie den Windows-Anmeldnamen des Anwenders ermitteln. Zudem erfassen Sie das Datum der √Ąnderung sowie den gerade erfassten Wert in der Zelle. Befindet sich bereits ein Kommentar in der Zelle, dann muss ein Update des Kommentars vorgenommen werden. In diesem Fall wird dem aktuellen Inhalt des Kommentars ein neuer Eintrag hinzugef√ľgt. √úber die Konstante vbLf wird dabei ein Zeilenumbruch eingestellt. Passen Sie am Ende des Makros das Kommentarfester automatisch der ben√∂tigten Gr√∂√üe an, indem Sie die Eigenschaft AutoSize verwenden.

Exportieren einer mehrspaltigen ListBox in eine Tabelle in einem Aufwasch

Bei der folgenden Lösung werden aus einer Tabelle unikate Länder und Kategorien in einer Userform in zwei Kombinationsfeldlisten geschrieben.

 

 

 

 

 

 

 

 

 

 

 

 

Mit einem Klick auf die Schaltfl√§che Daten exportieren sollen alle Eintr√§ge aus dem Listenfeld in eine Ergebnistabelle geschrieben werden. Auf welche Art und Weise kann dies jetzt geschehen? Entweder Zeile f√ľr Zeile oder besser den kompletten Inhalt des Listenfelds in einem einzigen Block.

Private Sub cmd_Export_Click()
Dim lngZeile As Long
Dim i As Integer

tbl_Ziel.UsedRange.Clear
tbl_Ziel.Rows(1).Value = tbl_Daten.Rows(1).Value
lngZeile = 2

' *** Var. 1 Zeile f√ľr Zeile ***
'For i = 0 To Me.ListBox_Ergebnisse.ListCount - 1
'
' tbl_Ziel.Range("A" & lngZeile).Value = Me.ListBox_Ergebnisse.Column(0, i)
' tbl_Ziel.Range("B" & lngZeile).Value = Me.ListBox_Ergebnisse.Column(1, i)
' tbl_Ziel.Range("C" & lngZeile).Value = Me.ListBox_Ergebnisse.Column(2, i)
' tbl_Ziel.Range("D" & lngZeile).Value = Me.ListBox_Ergebnisse.Column(3, i)
' lngZeile = lngZeile + 1
'
'Next i

'*** Var. 2 √ľber Resize **
'  tbl_Ziel.Cells(2, 1).Resize(Me.ListBox_Ergebnisse.ListCount, _
'                              Me.ListBox_Ergebnisse.ColumnCount).Value = _
'                              Me.ListBox_Ergebnisse.List

'oder
'*** Var. 3 √ľber Range ***
'Der Zielbereich muss in der Tabelle vorgehalten werden. (muss genauso groß ' sein, wie der Inhalt der Listbox)
'Die Gr√∂√üe der Listboxdaten kann √ľber Listcount (Anzahl der Zeile) und 'ColumnCount (Anzahl der Spalten) gemessen werden
'Die ermittelte Größe muss im Range bekannt gegeben werden.

With tbl_Ziel
.Range(.Cells(2, 1), _
.Cells(Me.ListBox_Ergebnisse.ListCount + 1, _
 Me.ListBox_Ergebnisse.ColumnCount)) = _
Me.ListBox_Ergebnisse.List
End With
Unload Me

End Sub
' +++ Quelle: VBA-Tanker, ID 9124 +++

Die schnellste Methode, den Inhalt des Listenfelds in eine Tabelle zu bef√∂rdern bieten die im Makro vorgestellten Varianten 2 und 3. Auf die dritte Variante m√∂chte in an dieser Stelle n√§her eingehen. Um den kompletten Inhalt des Listenfeldes in die Tabelle tbl_Ziel zu schreiben, muss der darin in ben√∂tigte Platz ‚Äěvorreserviert‚Äú werden. Das ist kein Problem, da Sie die Anzahl der Zeilen √ľber die Eigenschaft ListCount auslesen k√∂nnen. Die Anzahl der Spalten bekommen Sie √ľber die Eigenschaft ColumnCount gebacken. Da der erste Eintrag im Listenfeld mit dem Index 0 beginnt, m√ľssen Sie zur ermittelten Zeilenanzahl noch den Wert 1 addieren ‚Äď sonst fehlt nachher ein Satz in der Ergebnistabelle. Den kompletten Inhalt des Listenfeldes k√∂nnen Sie √ľber die Eigenschaft List abgreifen und direkt √ľber das Range-Objekt in die Tabelle tbl_Ziel schreiben.

Eine Mehrfachsuche f√ľr eine Tabelle dynamisch und anwenderfreundlich programmieren

Bei der folgenden Aufgabenstellung soll √ľber eine Userform eine Mehrfachsuche m√∂glichst dynamisch durchgef√ľhrt werden. Sehen Sie sich zun√§chst einmal die folgende Abbildung an:

 

 

 

 

 

 

 

 

 

 

 

 

In einer Listbox können mehrere Länder markiert werden. In einer Kombinationsfeldliste kann eine Kategorie eingestellt werden. Die Ergebnisse der Suche soll im darunter liegenden Listenfeld ausgegeben werden. Wie können Sie eine solche Suche möglichst anwenderfreundlich und dynamisch erstellen?

Um die Kombinationen aus den markierten Ländern und der dazu gehörigen Kategorie aus dem Datenbestand zu suchen, wird bei diesem Beispiel wie folgt vorgegangen:

  1. In einer Schleife wird die L√§nder-ListBox Zeile f√ľr Zeile durchlaufen. Die markierten L√§nder werden dabei in einer String Variable zusammengekettet: Deutschland, Frankreich, Italien
  2. In einer Schleife wird die hinterlegte Tabelle durchlaufen. Mit Hilfe der Funktion Instr wird gepr√ľft, ob das jeweilige Land in dem zusammengesetzten String vorkommt.
  3. Wenn ja, dann wird die Zeile in das Listefeld mittels der Methode AddItem
  4. Die restlichen Spalten werden √ľber die Eigenschaft Column aufgef√ľllt.

Der Quellcode dazu könnte dabei wie folgt aussehen:

Private Sub cmd_Suche_Click()
Dim lngZeile As Long
Dim lngZeileMax As Long
Dim lngZZ As Long
Dim i As Integer
Dim strLänder As String

'Zuerst einmal alle markierten Länder sammeln
For i = 0 To Me.ListBox_Land.ListCount - 1

If Me.ListBox_Land.Selected(i) = True Then
strLänder = strLänder & Me.ListBox_Land.List(i) & ","
End If

Next i

tbl_Daten.Range("M1").Value = strLänder
tbl_Daten.Range("M2").Value = Me.Combo_Kategorie.Value

Me.ListBox_Ergebnisse.Clear
With tbl_Daten
lngZeileMax = .Range("A" & .Rows.Count).End(xlUp).Row
For lngZeile = 2 To lngZeileMax
If InStr(strLänder, .Range("B" & lngZeile).Value) > 0 And _
Me.Combo_Kategorie.Value = .Range("C" & lngZeile).Value Then
Me.ListBox_Ergebnisse.AddItem .Range("A" & lngZeile).Value
Me.ListBox_Ergebnisse.Column(1, lngZZ) = .Range("B" & lngZeile).Value
Me.ListBox_Ergebnisse.Column(2, lngZZ) = .Range("C" & lngZeile).Value
Me.ListBox_Ergebnisse.Column(3, lngZZ) = .Range("D" & lngZeile).Value
lngZZ = lngZZ + 1
End If
Next lngZeile
End With
Me.Label1.Caption = "Anzahl gefundener Sätze: " & _
 Me.ListBox_Ergebnisse.ListCount

End Sub
' +++ Quelle: VBA-Tanker, ID 9124 +++

 

Color versus ColorIndex ‚Äď ‚ÄěDas Missverst√§ndnis des Monats‚Äú

W√§hrend einer Inhouse-Schulung in sch√∂nen Salzburg bekam ich die Aufgabe eine Excel-Mappe √ľber ein Makro zu √∂ffnen und alle Tabellen weiterzuverarbeiten, die die Registerfarbe Hellgelb haben. Die hellgr√ľnen Tabellen durften dabei nicht verarbeitet werden. Oh Schreck, mein Makro st√ľrzte ab. Nach einer kleineren Suchaktion wurde der Fehler gefunden. Beide Registerfarben hatten den gleichen Colorindex. Ist ja irgendwie auch gut m√∂glich, wenn √ľber eine Million verf√ľgbare Farben auf die 56 Farben-Palette heruntergebrochen werden. Dabei ist es ganz normal, wenn einige Farben dann der gleiche Colorindex zugeordnet wird.

 

 

 

 

 

 

 

 

Mit dem folgenden Makro k√∂nnen Sie pr√ľfen, welche Farbnummern der Registerkarten bei Hellgelb und hellgr√ľn ausgegeben werden.

Sub FarbenRegisterKartenAusgeben()
Dim wksBlatt As Worksheet

For Each wksBlatt In ThisWorkbook.Worksheets

Debug.Print "Colorindex: " & wksBlatt.Name & ": " & _
 wksBlatt.Tab.ColorIndex & vbLf & _
"Color:      " & wksBlatt.Name & ": " &  _
 wksBlatt.Tab.Color & vbLf

Next wksBlatt

End Sub
' +++ Quelle: VBA-Tanker, ID 9080 +++

Die Eigenschaft Colorindex liefert f√ľr die beiden unterschiedlichen Farben den gleichen Farbindex. Daher ist es besser, hier die Eigenschaft Color einzusetzen, die eine eindeutige Farbnummer liefert.

In gesperrten Tabellen trotzdem √Ąnderungen per Makro durchf√ľhren d√ľrfen

Im letzten Programmierbeispiel des Newsletters m√∂chte ich Ihnen noch eine Technik vorstellen, die es Ihnen erlaubt, auch in eine gesperrte Tabelle, per Makro Daten zu √§ndern, ohne dabei den Blattschutz aufheben zu m√ľssen.

Sub TabelleSperrenUndTrotzdemSchreiben()

Tabelle1.Protect Password:="test", UserInterFaceOnly:=True
Tabelle1.Range("A1").Value = Date

End Sub
' +++ Quelle: VBA-Tanker, ID 7327 +++

Wenn Sie die Methode Protect mit dem Parameter UserInterFaceOnly verwenden, k√∂nnen Sie per Makro auch in eine gesch√ľtzte Tabelle schreiben.

Autovervollständigen per Tastenkombination

Am Ende dieses Newsletters gibt es noch einen kleinen Trick, mit dem Sie sich viel Schreibarbeit sparen k√∂nnen. Wenn Sie beispielsweise einen Teil eines VBA-Befehls eingeben, z.B. Die Buchstaben ‚ÄěThis‚Äú und dann die Tastenkombination Strg + Leertaste dr√ľcken, dann vervollst√§ndigt Excel diesen Befehl zu Thisworkbook. Dies macht Excel auch bei Tabellennamen und anderen VBA-Befehlen. Ist der Befehl bekannt, wird der augenblicklich angeboten. Ist der Befehl nicht eindeutig, dann wird ein Zellendropdown angeboten.

 

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 7.298 Prozeduren enthalten, die √ľber ausgefeilt Suchfunktionen gefunden werden k√∂nnen. Zu den meisten VBA-Codes gibt es Bespieldateien, 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.

 

Feedback zu meinem ersten Newsletter ist erw√ľnscht. Sie erreichen mich per E-Mail unter b.Held@held-office.de

Ich w√ľnsche Ihnen und Ihren Familien sch√∂ne Weihnachten und einen guten Rutsch ins neue Jahr!

Viele Gr√ľ√üe

Bernd Held