2018 – März (VBA)

In diesem Monat habe ich mich nach der Internet-Masse in München intensiv mit meiner Homepage beschäftigt. So habe ich seit Beginn des Monats jetzt einen eigenen Web-Shop, einen Veranstaltungsplaner und sogar eine Chat-Funktion. Auch die ab Mai 2018 gültige Datenschutzverordnung macht erheblich Mühe. Über Feedback zu meiner Homepage freue ich mich in der Regel (-;

Seit Ende Januar ist mein aktuelles VBA-Buch beim Rheinwerk-Verlag mit 995 Seiten auf dem Markt- Es erfreut sich allgemeiner Beliebtheit und ich habe bereits einige nette Rückmeldungen zu diesem Werk erhalten.

https://www.rheinwerk-verlag.de/vba-mit-excel_4560/?GPP=held

Der Verlag hatte für letzte Ausgabe des VBA-Rundschreibens 5 Exemplare versprochen, die ich unter den Lesern des Rundschreibens verlosen darf. Anstatt des Losverfahrens habe ich aber hier im Rundschreiben einen Aufruf gestartet und die Leserschaft gebeten, mit etwas VBA-Input zur Verfügung zu stellen. Die eingehenden Antworten waren beachtlich und es fällt mir sehr schwer aus den ganzen Antworten 5 zu nominieren. Aber die Entscheidung ist gefallen. Die 5 Bücher gehen an Isabelle Seiler, Cyrill Reinl, Klaus Bader, Andreas Banse und an Lars Janes. Ich bitte die Gewinner sich bei zu melden und mir die Adresse bekanntzugeben, damit der Verlag die Bücher rausschicken kann.
Vielen Dank an alle, die an der Aktion teilgenommen haben. Die eingesendeten Lösungen werden in dieser oder in einer der nächsten Ausgaben nach und nach vorgestellt.

Im März-VBA-Kracher Rundschreiben werden die folgenden Themen behandelt:

  • Eine Word-Tabelle nach doppelten Texten durchsuchen
  • Einen Wert in mehreren Spalten finden
  • Massendaten auf eine Matrix verteilen (Buchgewinner: Andreas Banse)
  • Vorschau auf die VBA-Entwicklertage 2018

Eine Word-Tabelle nach doppelten Texten durchsuchen

Neulich erhielt ich von einem meiner VBA-Tanker-Kunden eine interessante Aufgabe gestellt. Herr Schreiner fragte mich nach einer Möglichkeit, in einer Word-Tabelle in einer bestimmten Spalte Dubletten automatisch zu kennzeichnen. Da mir eine fertige Word-Funktion zu diesem Thema nicht bekannt war, fing ich also an, so eine Funktionalität direkt in Word-VBA zu programmieren. Nun, ich muss dazu sagen, so ein Word-VBA-Indianer bin ich auch nicht, aber wir haben es tatsächlich hinbekommen. Das Ausgangsdokument bei dieser Aufgabenstellung sah dabei wie folgt aus:

 

Die zweite Spalte sollte in diesem Dokument ausgewertet werden. In der Abbildung sehen Sie bereits das fertige Ergebnis. Die Dubletten werden unabhängig ihrer Position im Dokument gelb eingefärbt. Etwas hinderlich bei der Programmierung warten die verbundenen hellblauen Zeilen, die hin- und wieder quer durch die Tabelle gingen.

Das Makro für die Umsetzung dieser Aufgabenstellung sieht dabei wie folgt aus:

Sub DoppelteWerte2()
  Dim tbl As Table
  Dim dc As Object
  Dim lngZ As Long
  Dim strZeile As Long
  Dim lngZeile As Long
  Dim i As Integer

  
  Set tbl = ActiveDocument.Tables(1)
  Set dc = CreateObject("Scripting.Dictionary")

  With tbl
   For lngZeile = 1 To .Rows.Count
      If .Rows(lngZeile).Cells.Count = 4 Then
        If Not dc.Exists(.Cell(lngZeile, 2).Range.Text) Then
          dc.Add .Cell(lngZeile, 2).Range.Text, i
          i = i + 1
        Else
         .Cell(strZeile, 2).Range.HighlightColorIndex = wdYellow
        End If
       End If
    Next lngZeile

  End With
 MsgBox "fertig!"
End Sub
 
 '*** Quelle: VBA-Tanker: ID 9224 ***

Deklarieren Sie im ersten Schritt eine Objektvariable vom Typ Table. Danach geben Sie mit der Anweisung Set bekannt, wo sich diese Tabelle befindet. Tables(1) bedeutet hierbei, dass es sich um die erste Tabelle im aktuellen Dokument handelt. Danach erstellen Sie mit Hilfe der Anweisung CreateObject ein sogenanntes Dictionary-Objekt. Mit Hilfe dieses Objektes möchte ich ermitteln, ob doppelte Texte in der Tabelle vorliegen. Das bedeutet, dass ich erst einmal die unikaten Texte in diesem Objekt sammle und beim ersten Auftreten einer Dublette den entsprechenden Text direkt im Dokument einfärbe. Ob der jeweilige Eintrag sich bereits im Dictionary-Objekt befindet, können Sie mit Hilfe der Funktion Exists herausfinden. Wenn nicht, dann fügen dem Dictionary-Objekt über die Methode Add den Inhalt der zweiten Spalte dem Dictionary-Objekt hinzu. Da die Tabelle jedoch durch verbundene Überschriftenzeilen durchzogen ist, müssen Sie diese Zeile von der Verarbeitung ausnehmen. Dies gelingt Ihnen, indem Sie die Anzahl der Zellen einer Zeile über die Funktion Count abfragen. Nur wenn es pro Zeile 4 Zellen gibt, dass ist es eine relevante Zeile für die weitere Verarbeitung. Wird also ermittelt, dass eine Text in der Tabelle bereits schon im Dictionary-Objekt ist, dann wird die die betreffende Zelle in der Tabelle über die Eigenschaft HighlightColorIndex gelb eingefärbt, indem dieser Eigenschaft die Word-Konstante wdYellow zugewiesen wird.

Einen Wert in mehreren Spalten finden

Bei der nachfolgenden Aufgabenstellung geht es um das Thema, wie man eine Mehrfachsuche in einer Excel-Tabelle programmieren kann. Schauen Sie sich zunächst einmal das Endergebnis aus der nächsten Abbildung an.

 

 

 

 

 

 

 

 

 

 

Über eine InputBox soll der gewünschte Wert eingeben und in den beiden Spalten A und B gefunden und gekennzeichnet werden. Das dafür verwendete Makro sieht dabei wie folgt aus:

Sub SpaltenDurchsuchen()
  Dim rngBereich As Range
  Dim rngTreffer As Range
  Dim strSuche As String
  Dim strFundstelle As String

 
  strSuche = InputBox("Zahl zwischen 1 und 10 eingeben", _
  "Zahl eingeben", 1)
 
  Set rngBereich = Tabelle1.Range("A:B")
  rngBereich.Interior.ColorIndex = xlColorIndexNone
  
  Set rngTreffer = rngBereich.Find(what:=strSuche, _
                                   lookat:=xlWhole)
  
  If rngTreffer Is Nothing Then
      Exit Sub
  Else
     strFundstelle = rngTreffer.Address
    Do
     rngTreffer.Interior.ColorIndex = 4
     Set rngTreffer = rngBereich.FindNext(rngTreffer)
    Loop While Not rngTreffer Is Nothing And _
         rngTreffer.Address <> strFundstelle
  End If 

End Sub

'*** Quelle: VBA-Tanker: ID 9225 ***

In einer Inputbox wird ein Wert zwischen 1 und 10 gefordert. Wenn kein Wert erfasst wird, dann wird der Standardwert 1 angenommen. Geben Sie danach über die Anweisung Set in einer Objektvariablen vom Typ Range an, wo sich der zu durchsuchende Bereich in der Tabelle befindet. Dieser Bereich wird zu Beginn des Makro entfärbt, indem der Eigenschaft ColorIndex die Konstante xlColorIndexNone zugewiesen wird. Mit Hilfe der Methode Find wird danach nach dem ersten Auftreten der Zahl im vorher angegebenen Bereich gesucht. Wird nichts gefunden, sorgt die Anweisung Exit Sub dafür, dass direkt aus der Prozedur gesprungen wird. Hier könnte auch noch eine Meldung spendiert werden, dass die Suche erfolglos war. Die aktuelle Fundstelle des ersten Suchergebnisses wird in der String Variablen strFundstelle zwischengespeichert. In einer anschließenden Do Loop Schleife wird solange über die Methode FindNext nach weiteren „Treffern“ gesucht, bis eben die aktuell gefundene Zelle mit der Koordinate des ersten Suchtreffers übereinstimmt. In diesem Fall wird die Schleife beendet. Mit anderen Worten verhindern Sie über diese Technik eine Endlosschleife, da Excel wieder von vorne mit der Suche beginnen würde, nachdem der letzte Treffer erzielt wurde. Färben Sie jede einzelne gefundene Zelle, indem Sie der Eigenschaft Colorindex den Wert 4 zuweisen.

Massendaten auf eine Matrix verteilen

Bei der nachfolgenden Lösung handelt es sich um einen Beitrag eines Buchgewinners. Andreas Banse ist Senior Consultant bei Best Advice Consulting und im Bankenumfeld tätig. Dort ist er unter anderem mit Aufgaben der Anwendungs-, Prozess- und Testautomatisierung beschäftigt. Er hat sich einem meiner Lieblingsthemen angenommen. In einer Tabelle liegen 500.000 Datensätze nach folgendem Vorbild vor:

Diese Massendaten sollen so schnell wie möglich in die folgende Matrix befördert werden.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Das Makro von Andreas Banse sieht dabei wie folgt aus:

Sub Daten_verdichten_Banse()
On Error GoTo errFehler
' Laufzeit beschleunigen ein

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .DisplayStatusBar = True
    .StatusBar = "Warten zu müssen ist eine freundliche Einladung zu einer kleinen Meditation."
End With
' Timer starten
Dim secs1 As Single
Dim secs2 As Single
secs1 = Timer()
' *** Start - Quelldaten als Tabellenbereich formatieren
With Sheets("tbl_Daten")
    If .ListObjects.Count = 0 Then
        .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Daten"
    Else
        .ListObjects(1).Name = "Daten"
    End If
End With
Dim varInput As Variant ' Input Array
' Inputdaten einlesen (hier als benannte Tabelle "Daten" formatiert)
varInput = Range("Daten[#all]")
Dim varOutput As Variant ' Output Array
Dim DicDaten As Object ' Verweis auf das Dictionary Object setzen
Set DicDaten = CreateObject("Scripting.Dictionary")
Dim varDaten As Variant
Dim lngCount As Long
Dim lngMax As Long
Dim lngZeile As Long
Dim lngSpalte As Long

 
' Schleife über die Input Array Zeilen
For lngCount = 2 To UBound(varInput)
    ' Prüfung auf doppelte Werte in Spalte 1
    ' Prüfung ob Wert bereits in Liste DicDaten
    If Not DicDaten.exists(varInput(lngCount, 1)) Then
        ' Wert noch nicht in Liste DicDaten, dann aufnehmen
        Call DicDaten.Add(varInput(lngCount, 1), Empty)
    End If
Next lngCount

' Werte der Liste DicDaten in das Array varDaten übergeben
varDaten = DicDaten.Keys
lngMax = DicDaten.Count
ReDim varOutput(1 To lngMax + 1, 1 To 13) ' Größe festlegen
' Monate
For lngCount = 2 To 13
    varOutput(1, lngCount) = lngCount - 1
Next lngCount
' Kostenstelle
For lngCount = 2 To UBound(varDaten) + 2
    varOutput(lngCount, 1) = varDaten(lngCount - 2)
Next lngCount
 
' Schleife über die Input Array Zeilen
For lngCount = 2 To UBound(varInput)
    ' Schleife über die Kostenstellen
    For lngZeile = 2 To UBound(varDaten) + 2
        ' Abgleich der Kostenstellen
        If varInput(lngCount, 1) = varOutput(lngZeile, 1) Then
            ' Schleife über die Monate
            For lngSpalte = 2 To 13
                ' Abgleich der Monate
                If varInput(lngCount, 2) = varOutput(1, lngSpalte) Then
                    ' kummulieren der Kosten
                    varOutput(lngZeile, lngSpalte) = _
                    varOutput(lngZeile, lngSpalte) + varInput(lngCount, 3)
                End If    
            Next lngSpalte
        End If
    Next lngZeile
Next lngCount
With Sheets("tbl_Ergebnis")
    .Rows.Delete ' bestehende Daten löschen
    .Range("B1").Resize(UBound(varOutput), 13) = varOutput ' Daten einfügen
    .Range("C2:N" & UBound(varDaten) + 2).NumberFormat = "0,00" ' Zahlenformat
    .Columns.AutoFit ' Spaltenbreite anpassen
    ' sortieren
    .Activate
    .Range("B2:N" & UBound(varDaten) + 2).Sort Key1:=Range("B2"), _
    Order1:=xlAscending, Header:=xlNo
End With
' Laufzeit beschleunigen aus
With Application
    .Goto Reference:=Sheets("tbl_Ergebnis").Cells(1, 1), scroll:=True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .StatusBar = ""
End With
' Timer beenden
secs2 = Timer()
' Hinweis zum Prüfen der Ergebnisse und Ausgebe des Timer
MsgBox ("+ + + Die generierten Daten prüfen + + +" & vbNewLine & vbNewLine & "Time taken to run code:" & vbNewLine & secs2 - secs1 & " seconds") ' Fehlerhandling
Exit Sub

' Fehlerhandling
Exit Sub
errFehler:
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .StatusBar = ""
End With
' Ausgabe Fehlermeldung
MsgBox "Es ist ein Fehler aufgetreten, die Daten konnten nicht erfolgreich generiert werden!"
End Sub

Im Makro werden die Quelldaten als ListObject definiert. Ein Dictionary-Object selektiert im Anschluss die eindeutigen Kostenstellen in einer Schleife aus der ersten Spalte des ListObjects. Die so generierten Daten werden in ein 1-dimensionales Array überführt und als Zeilenbeschriftungen in ein mehrdimensionales Array geschrieben. Die Spaltenbeschriftungen des mehrdimensionalen Arrays bilden die Monate 1 bis 12 ab. Die so entstandene Matrix wird über eine weitere Schleife über das ListObject gefüllt. Dabei wird jeweils die Kostenstelle aus dem ListObject in dem mehrdimensionalen Array gesucht und die Zeilennummer zurückgegeben. Mit der gleichen Logik wird die Spaltennummer des entsprechenden Monats bestimmt. Aus der Kombination von Zeile und Spalte ist eine genaue Zuordnung der Kosten innerhalb der Matrix gegeben. Auf dieser Position werden die Kosten aufaddiert. Im Anschluss wird das mehrdimensionale Array ausgegeben und formatiert.

Die Datengrundlage bezieht sich auf Bernd Helds Vortrag „Massendaten gekonnt konsolidieren mit VBA“ von den Entwicklertagen 2017.

Vorschau auf die VBA-Entwicklertage 2018

Am Ende des VBA-Rundschreibens möchte ich Sie auf unsere VBA-Veranstaltung im November in Masserberg /Thüringen aufmerksam machen.

Unsere diesjährigen Entwicklertage finden in der Zeit vom 13.11. bis 14.11.18 in Masserberg / Hotel Rennsteig statt.

Infos zu den VBA-Entwicklertagen

Bei den VBA-Entwicklertagen werden unter anderem die folgenden Themen behandelt:

  • Daten aus Access in Word-Vorlagen übertragen: Dirk Hornauer zeigt einen Ausschnitt aus einem Praxisbeispiel, in dem Daten aus einer Access-Datenbank in Word-Vorlagen transferiert werden. Hierbei kommt VBA-Code zum Ansprechen von Textmarken, Seriendruckfeldern und Tabellenfeldern in Word zum Einsatz. Weiterhin wird eine Möglichkeit zur Erstellung umfangreicher Word-Dokumente (z. B. von Angeboten) durch Zusammenstellen standardisierter Textbausteine über ein Auswahlmenü aufgezeigt.
  • Installieren mit Inno-Setup​: Um seine mit VBA erstellten Tools oder Anwendungen an andere Benutzer weiterzugeben, ist eine Installationsdatei hilfreich. Sie platziert die Datei(en) im richtigen Verzeichnis (z. B. bei Word- oder Excel-Add-Ins), macht Voreinstellungen für das Programm in der Registry (z. B. den Pfad zur Backend-Datenbank), erstellt Verknüpfungen im Startmenü oder auf dem Desktop und deinstalliert auch alles auf Knopfdruck wieder. Der Vortag von Raphael Hein zeigt, wie man mit dem OpenSource-Tool Inno Setup ganz einfach seine ersten eigenen Setup-Dateien erstellen kann.
  • Organigramm mit VBA erstellen: Automatisierte Darstellung der hierarchischen Firmenstruktur in Excel. Dieser Vortrag hält Iakov Loxine.
  • Best practices in VBA (dos and don’ts): Wer beim sich beim Programmieren an bewährte Methoden hält, erspart sich viel Verdruss. Welche Fallen lauern beim Deklarieren von Variablen und Konstanten – und wie macht man’s richtig? Welche Standardeinstellungen im VBA-Editor sollten geändert werden, um das Programmieren zu vereinfachen? Was ist bei der Verwendung von On Error unbedingt zu beachten? Warum ändern Prozeduren Variablen, obwohl sie ByVal übergeben werden? Antworten auf diese und weitere Fragen liefert Dominik Petri in seinem Vortrag.
  • Filter, Suche und Auswahl in Access-Endlosformularen: Dieser Vortrag von Raphael Hein demonstriert, wie man in Endlosformularen Daten filtert (auch bei 1:n-Beziehungen), Texte sucht und einzelne oder viele Datensätze auswählt. Die ausgewählten Datensätze lassen sich dann ganz einfach bearbeiten oder exportieren. Mit etwas VBA und SQL gelingen dynamische Filter und Selektionen, die mit Access-Bordmitteln nicht möglich sind. Themen sind u. a.: Formularfilter und -sortierung setzen, Datensatzquelle ändern, nicht aktualisierbare Abfragen trotzdem updaten, zwei Formulare gleichzeitig durchblättern (synchronisieren).
  • Einen dynamischen Excel-Bericht in mehrere PowerPoint-Präsentationen aufteilen: In diesem Beispiel aus einer Kundenanforderung stellt Dirk Hornauer den VBA-Code zur Berichtsauswahl und dem anschließenden Übertrag von Texten, Tabellen, Diagrammen und Bildern in mehrere PowerPoint-Präsentationen vor. Hierbei werden die wesentlichen Elemente des PowerPoint-Objektmodells angesprochen.
  • PDF-Dateien manipulieren mit PDFtk: Jürgen Vierus zeigt, wie Sie VBA-gesteuert PDF-Dateien ohne kostenpflichtigen (meist teuren) PDF-Editor verarbeiten können. Das ermöglicht das kostenlos verfügbare Kommandozeilen-Tool PDFtk. PDF-Formularfelder auslesen, PDF-Formulare befüllen, Dateien zusammenfügen, Logo und Seitenzahl ergänzen sind nur einige der Anwendungsmöglichkeiten.
  • Daten aus verschiedenen Quelle einlesen und weiterverarbeiten: Bernd Held zeigt in seinem Vortrag, wie man mit VBA auch gänzlich unterschiedliche Excel-Mappen normieren, einlesen und weiterverarbeiten kann.
  • Farben wechseln in Excel: Gerade sind alle Diagramme auf die Hausfarben des Unternehmens angepasst, da ändern sich die Farbvorgaben. Der Vortrag von Jürgen Vierus erklärt, wie die notwendig werdenden Änderungen per VBA automatisiert werden können, geht dabei auf die Zusammenhänge verschiedener Gestaltungsmöglichkeiten ein und zeigt auf, wie sich künftige Änderungen bereits im Vorfeld vereinfachen lassen.
  • Regular Expressions: Immer wieder stolpert Mirko Wege über Anwendungsmöglichkeiten für RegEx. Diese mag er dem erlauchten Publikum vorstellen, eine kleine Einführung geben und weitere Anwendungsmöglichkeiten aufzeigen.
  • Einsatz von Enumerationen: Enumerationen verbessern die Lesbarkeit des Codes. Markus Schlafmann zeigt wie und wo kann ich diese einsetzen kann: z.B. bei Gültigkeitsprüfung für Benutzereingaben, Verzweigungen im Code
  • Toolbox mit ListObject-Funktionen: Mirko Wege zeigt uns seine Toolbox mit Functions, die auf ListObjects aufbauen. Da sind im wesentlichen Such- und Verweisfunktionen enthalten, die die Arbeit erleichtern sollen. Die Toolbox schenkt er als AddIn den Teilnehmern.
  • Neuerungen im Datenmodell von Excel 2019: Im Herbst kommt Excel 2019 auf den Markt. Dominik Petri stellt uns die Neuerungen im Objektmodell vor und zeigt uns, welche neuen Möglichkeiten sich damit ergeben.
  • Array-Programmierung in Excel: Bernd Held zeigt in einigen praktischen Beispielen den Einsatz von Arrays im Zusammenspiel mit Excel. Als Zugabe obendrauf gibt es ein VBA-Skript mit 105 Seiten genau zu diesem Thema für jeden Teilenehmer.
  • Über den Tellerrand blicken: Welche Möglichkeiten der Automatisierung gibt’s in Excel/Office außer VBA? Um wiederkehrende manuelle Tätigkeiten zu automatisieren, gibt’s es in Office nicht nur VBA. Auch mit anderen Technologien lassen sich Routinearbeiten automatisieren. Zwei verschiedene Ansätze, die keinerlei Programmierkenntnisse erfordern, zeigt Dominik Petri in seinem Vortrag.
  • Aufspitten von Texten: Häufig befinden sich in Excel-Adresslisten der Vorname und Nachname einer Person in einer Zelle. Eine Trennung mittels Excel-Funktionen ist einfach, wenn es sich um einen Vornamen und einen Nachnamen handelt. Doch wie sieht es aus bei mehrfachen Vornamen, doppelten Nachnamen, akademischen Titeln, Namenszusätzen und fremdsprachlichen Namen? Aufbauend auf der einfachsten Möglichkeit geht es Schritt für Schritt mit Peter Stähler zu komplexen und komplizierten Namen und zu einer mächtigen Funktion … die fast alle denkbaren Fälle abdeckt.

 

Feedback zu meinem VBA-Kracher-Rundschreiben ist erwünscht. Sie erreichen mich per E-Mail unter b.Held@held-office.de.

Bis zum April alles Gute, genießen Sie die Sonne.

Viele Grüße

Bernd Held