2018 – Januar (VBA)

Ich hoffe, dass Sie gut ins neue Jahr gestartet sind! Das neue Jahr habe ich etwas ruhiger angehen lassen. Weniger Außentermine und doch schon einige interessante Programmierprojekte für zuhause. Nebenbei habe ich fast jeden Tag Newsletter gelöscht, die ich nie abonniert habe und die gerade zu Beginn des Jahres meinen Posteingang aus welchen Gründen auch immer überschwemmt haben. Der Begriff „Newsletter“ ist bei mir daher z.Z. etwas negativ belegt. Aus diesem Grund habe ich mich auch entschlossen, meinen eigenen „VBA-Kracher-Newsletter“ umzutaufen. Er heißt jetzt „VBA-Kracher-Rundschreiben“. Ich sehe dieses Rundschreiben als Online-VBA-Zeitschrift. Mit einem normalen Newsletter hat das nichts zu tun. Selbstverständlich freue ich mich aber schon, wenn sich durch dieses Medium etwas Geschäftliches für mich und mein Team ergibt. Der Werbeanteil in meinen Sendungen wird aber immer klein sein. Die ersten externen Anfragen nach Werbung von anderen Firmen in diesem Medium habe ich bereits abgelehnt und das wird auch so bleiben.

Im Januar-VBA-Kracher Rundschreiben werden die folgenden Themen behandelt:

  • Eine bestimmte Internetseite aufrufen und einen Link automatisch klicken
  • Einen automatischen Log-In auf einer Internetseite durchführen
  • Bilder in einer Userform automatisch anzeigen lassen
  • Das Erstellungsdatum / Datum der letzten Änderung einer Mappe abfragen
  • Chamäleon: Nach jedem Buchstaben Farbe tauschen
  • Nie wieder SVERWEIS: Daten aus einer anderen Tabelle „mappen“

Eine bestimmte Internetseite aufrufen und einen Link automatisch klicken

Beim ersten Beispiel soll eine bestimmte Internetseite (nämlich meine) aufgerufen werden und nach dem Ladevorgang dann ein bestimmter Link (mein Kursangebot) automatisch geklickt werden. Dazu wird die Bibliothek „Microsoft Internet Controls“ in der Entwicklungsumgebung von Excel im Menü Extras und dem Befehl Verweise eingebunden. Diese Bibliothek stellt dazu alle notwendigen Befehl zur Verfügung.

Das Makro zur Lösung dieser Aufgabenstellung sieht wie folgt aus.

Sub LinkAufInternetseiteKlicken()
  Dim iexpl As New InternetExplorer
  Dim ieDoc As Object

      With iexpl
          .Navigate "https://www.held-office.de"
          Do
          Loop Until Not .Busy
          .Visible = True
          Set ieDoc = iexpl.Document
          ieDoc.Links(8).Click
      End With

End Sub

‘*** Quelle: VBA-Tanker, ID 9152

Mit Hilfe der Methode Navigate wird auf eine Internetseite gesprungen. In der Do Until – Schleife wird solange gewartet, bis die Internetseite komplett geladen ist. Die Eigenschaft Visible wird auf den Wert True gesetzt, damit die Internet-Sitzung überhaupt angezeigt wird. Im Auflistungsobjekt Links befinden sich alle auf der Internetseite befindlichen Hyperlinks. Über das Ereignis Click wird in diesem Beispiel die achte Seite in dieser Auflistung automatisch geklickt.

Einen automatischen Log-In auf einer Internetseite durchführen

Bei der nächsten Aufgabenstellung wird exemplarisch auf der Internetseite www.newsletter2go.de ein automatischer Log-In durchgeführt. Dazu ist die automatische Angabe der E-Mail-Adresse sowie das Log-In Passwort notwendig. Diese Aufgabe kann über das folgende Makro automatisiert werden:

Sub AufInternertseiteEinloggen()
 Dim ie As New InternetExplorer

    ie.Visible = True
    ie.Navigate ("https://www.newsletter2go.de/login/")
    Do
        If ie.ReadyState = 4 Then
            Exit Do
        Else
            DoEvents
        End If
    Loop
  
    Debug.Print ie.Document.Forms(0)
    Debug.Print ie
   
    ie.Document.Forms(0).all("login_mail").Value = "b.Held@held-office.de"
    ie.Document.Forms(0).all("password").Value = "XXXX"
    ie.Document.all.Item("submit").Click

End Sub

‘*** Quelle: VBA-Tanker, ID 9155

Wie bereits im vorherigen Beispiel angesprochen, muss hierbei die Bibliothek „Microsoft Internet Controls vorab unter Extras/Verweise eingebunden werden. Danach wird die Internet-Sitzung sichtbar gemacht, indem der Eigenschaft Visible der Wert True übergeben wird. Mit Hilfe der Methode Navigate wird auf die entsprechende Internetseite verzweigt. In einer Do Loop-Schleife wird solange gewartet, bis die komplette Seite geladen ist. In diesem Fall liefert die Eigenschaft ReadyState die Rückmeldung 4. In diesem Fall wird über die Anweisung Exit Do aus der Schleife gesprungen.

Die einzelnen Formularsteuerelemente können über die Auflistung Forms und dem eigentlichen Namen des Eingabefeldes angesprochen werden. Wie die einzelnen Eingabefelder heißen können Sie direkt im Internet-Explorer erkunden, wenn Sie das entsprechende Eingabefeld mit der rechten Maustaste anklicken und den Befehl Element untersuchen klicken. Im HTML-Quelltext ist der Name des Eingabefeldes ersichtlich. Nach der automatischen Erfassung der E-Mail-Adresse und des Passwortes findet ein Klick auf die Schaltfläche Login statt. Auch der Name dieser Schaltfläche kann über den rechten Mausklick und dem Befehl Element untersuchen ermittelt werden.

Bilder in einer Userform dynamisch anzeigen lassen

Beim nachfolgenden Beispiel handelt es sich um eine Teilaufgabe in einem Programmierprojekt in diesem Monat. Dabei sollten einige Bilder dynamisch in einer Userform angezeigt werden. Standardmäßig würde man zu diesem Zweck die Anweisung LoadPicture einsetzen, die ein Bild direkt von der Festplatte holt und im Steuerelement Anzeige in der Userform anzeigt. Hierbei liegt die Problematik darin, dass eben die Bilder, die man in der Userform anzeigen möchte, eben in einem bestimmten Verzeichnis auf der Festplatte liegen müssen. Die Forderung war hier, die Bilder aus der aktuellen Arbeitsmappe, d.h. aus dem Excel-Tool selbst zu holen. Wie kann diese Forderung bewerkstelligt werden. Eine Vorgehensweise ist es, auf einer versteckten Steuertabelle einige leere Bild-Steuerelement einzufügen und die Bilder über das Eigenschaften-Fenster direkt in die Steuerelemente einzufügen. Diese gelingt prima über den Ribbon Entwicklertools.

Die hier gezeigten Bilder stammen vom jüngsten Hochwasser in unserem Dorf, was uns innerhalb 2 Wochen gleich zweimal heimgesucht hat. Als nächsten Schritt wird eine Userform mit einem Dropdown und einem Image-Steuerelement angelegt und folgender Quellcode direkt hinter der Userform platziert.

Private Sub UserForm_Initialize()

With Me.ComboBox1
 .AddItem "Bild1"
 .AddItem "Bild2"
 .AddItem "Bild3"
 .ListIndex = 0
 .Style = fmStyleDropDownList
End With

Me.Image1.PictureSizeMode = fmPictureSizeModeStretch

End Sub

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.Value
 Case "Bild1"
 Set Me.Image1.Picture = Tabelle1.Image1.Picture
 Case "Bild2"
 Set Me.Image1.Picture = Tabelle1.Image2.Picture
 Case "Bild3"
 Set Me.Image1.Picture = Tabelle1.Image3.Picture
 Case Else
 Me.Image1.Picture = LoadPicture("")
End Select
End Sub
'*** Quelle: VBA-Tanker, ID 9181

Mit Hilfe der Methode AddItem werden die Namen der drei Bilder in das Dropdown-Feld eingefügt. Damit wirklich nichts anderes im Dropdown eingegeben werden kann, weisen Sie der Eigenschaft Style die Konstante fmStyleDropDownList zu. Über die Eigenschaft PictureSizeMode legen Sie fest, dass das zu ladende Bild auf den zur Verfügung stehenden „Platz“ automatisch angepasst wird.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Danach muss auf eine Änderung in diesem Dropdown reagiert werden. Dazu wird das Ereignis Change eingesetzt. Weisen Sie in einer Select Case Anweisung je nach ausgewähltem Bild über den Einsatz der Eigenschaft Picture das jeweilige Bild zu. Dabei holen Sie das entsprechende Bild aus einem der drei Steuerelemente in der Tabelle und übertragen es in das eine Bild-Steuerelement in der Userform.

 

Das Erstellungsdatum / Datum der letzten Änderung abfragen

Beim nachfolgenden Beispiel soll das Erstellungsdatum der aktuell geöffneten Mappe bzw. die letzte Änderung an dieser Mappe ermittelt werden. Dazu gibt es exemplarisch zwei Ansätze:

  1. Abfrage über die Dokumenteigenschaft der Arbeitsmappe
  2. Abfrage über das FileSystemObject

Wenn Sie das Erstellungsdatum/Datum der letzten Änderung über die Dokumenteigenschaften abfragen möchten, dann setzen Sie die folgende Zeilen ein:

MsgBox ActiveWorkbook.BuiltinDocumentProperties(“creation date”)

MsgBox ActiveWorkbook.BuiltinDocumentProperties(“last save time”)

Alternativ können beide Informationen auch über das FileSystemObject abgefragt werden.

Sub LetzteÄnderungAbfragen()
Set FSO = CreateObject("Scripting.FileSystemObject")
Set F = FSO.GetFile(ThisWorkbook.FullName)
MsgBox F.DateLastModified
End Sub
'*** Quelle: VBA-Tanker, ID 9188

Sub DatumDerErstellungAbfragen()
Set FSO = CreateObject("Scripting.FileSystemObject")
Set F = FSO.GetFile(ThisWorkbook.FullName)

MsgBox F.Datecreated
End Sub
'*** Quelle: VBA-Tanker, ID 9189

Chamäleon: Nach jedem Buchstaben Farbe tauschen

Sieht auf der ersten Blick etwas verspielt aus – ist es ehrlich auch, aber egal. Schauen Sie sixch zunächst einmal die folgende Abbildung an.

Für die Zelle A1 wurde definiert, dass jeder Buchstabe in einer anderen Farbe formatiert werden soll. Bestimmte Farben wie Weiss und Gelb machen dabei zwecks der Lesbarkeit wenig Sinn. Also sollen bei dieser Aufgabe nur bestimmte Farben verwendet werden dürfen.

Sub JederBuchstabeInAndererFarbe()
Dim VarDat As Variant
Dim strFarben As String
Dim i As Integer
Dim x As Integer

strFarben = "3, 4, 5, 7, 8, 9, 10"
VarDat = Split(strFarben, ",")
With Tabelle1

For i = 1 To Len(.Range("A1").Value)
.Range("A1").Characters(i, 1).Font.ColorIndex = VarDat(x)
  If x = UBound(VarDat) Then
     x = 1
  Else
    x = x + 1
  End If
Next i

End With

End Sub
'*** Quelle: VBA-Tanker, ID 9185

In einer Variablen vom Typ String werden hierbei die Farbnummern der Farben hinterlegt, die gut lesbar sind. Dazu gehören eben die bekannten Farbnummer 3 und 4 (rot und grün) und diverse andere Farb-Nummern. Im vorliegenden Beispiel wurden 7 Farben ausgewählt, die im Wechsel angewendet werden sollen. Diese Farben werden mit Hilfe der Funktion Split in einen Array geschrieben. Als nächstes wird eine For Next-Schleife aufgesetzt, die solange Zeichen für Zeichen abarbeitet, bis das Ende der Eingabe erreicht ist. Innerhalb der Schleife wird dem jeweiligen Zeichen die entsprechende Farbe über die Eigenschaft ColorIndex zugewiesen. Wenn die letzte Farbe im Array (UBound) abgearbeitet wurde, dann wird wieder vorne bei Farbe 1 begonnen.

Nie wieder SVERWEIS: Daten aus einer anderen Tabelle „mappen“

Ein Klassiker schlechthin, der in fast jedem Projekt zur Anwendung kommt. Mit anderen Worten auch „Der Ersatz von SVERWEIS“. Sehen Sie sich zunächst einmal exemplarisch die folgende Abbildung an.

Die Spalte A soll mit der entsprechenden Region gefüllt werden, die aus einer anderen Tabelle kommt.

Das Makro, um das Regionen-Mapping umzusetzen, sieht wie folgt aus:

Sub ZuordnungHolen()
'Holt die Zuordnung aus einer anderen Tabelle der gleichen Mappe
Dim lngZeile As Long
Dim lngZeileMax As Long
Dim rngTreffer As Range

With tbl_Daten

lngZeileMax = .Range("B" & .Rows.Count).End(xlUp).Row

For lngZeile = 2 To lngZeileMax
Set rngTreffer = tbl_Mapping.Range("B:B").Find _
(what:=.Range("B" & lngZeile).Value, lookat:=xlWhole)

 If rngTreffer Is Nothing Then
  .Range("B" & lngZeile).Interior.ColorIndex = 3
 Else
  .Range("A" & lngZeile).Value = rngTreffer.Offset(0, 1).Value
  .Range("B" & lngZeile).Interior.ColorIndex = xlNone
 End If
Next lngZeile

End With

End Sub
'*** Quelle: VBA-Tanker, ID 9126

Arbeiten Sie die Tabelle tbl_Daten Zeile für Zeile mit einer For Next-Schleife ab. Im Innern der Schleife führen Sie eine Direktsuche über die Methode Find nach dem Land in der Tabelle tbl_Mapping in Spalte B durch. Wird das gesucht Land gefunden, dann übertragen Sie die Region, die eine Spalte weiter rechts in der Mapping-Tabelle steht in die Tabelle tbl_Daten. Für den Fall, dass die Mapping-Tabelle nicht aktuell gepflegt sein sollte, färben Sie die nicht gefundene Land in der Tabelle tbl_Daten mit der Hintergrundfarbe Rot.