2018 – Januar

VBA-Kracher Januar 2018

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 "http://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.