August 2017

Der VBA-Kracher ‚Äď August 2017

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

  • Die erste / letzte Zelle in einem benannten Bereich auslesen
  • Signal bei Wert√ľberschreitung in Zelle ausgeben
  • Alle Kommentarfenster auf Originalposition zur√ľcksetzen
  • Den Codenamen einer Tabelle als Quelle f√ľr ein Listenfeld verwenden
  • Eine Zelle als Schaltfl√§che nutzen
  • Die Multifunktionsleiste ein- und ausblenden
  • Pr√ľfen, ob eine bestimmte Schriftart verf√ľgbar ist

Die erste / letzte Zelle in einem benannten Bereich auslesen

Bei der ersten Aufgabenstellung wurde in der Tabelle1 ein Bereich (C4:G13) benannt. Dieser Bereich beinhaltet Daten.

 

 

 

 

 

 

 

 

 

 

 

 

Wie kann man nun die erste und letzte Zelle in diesem Bereich ansprechen? Dazu können Sie die beiden folgenden Makros einsetzen.

Sub ErsteZelleAusBenanntemBereichExtrahieren()

  Dim rngZelle As Range


  Set rngZelle = Tabelle1.Range("Daten").Cells(1, 1)
  MsgBox rngZelle.Address & " --> " & rngZelle.Value

End Sub


Sub LetzteZelleAusBenanntemBereichExtrahieren()
  Dim rngZelle As Range
  Dim lngZeileMax As Long
  Dim lngSpalteMax As Long

  With Tabelle1.Range("Daten")

    lngZeileMax = .Rows.Count
    lngSpalteMax = .Columns.Count
    Set rngZelle = .Cells(lngZeileMax, lngSpalteMax)
    MsgBox rngZelle.Address & " --> " & rngZelle.Value

  End With

End Sub

Mit der Cells-Auflistung und den Koordinaten 1,1 sprechen Sie automatisch die erste Zeile sowie die erste Spalte im benannten Bereich ‚ÄěDaten‚Äú an. Diese erste Zelle weisen Sie der Objektvariablen rngZelle zu. √úber die Eigenschaft Address k√∂nnen Sie die dahinter steckende Koordinate der Zelle auslesen. Mit Hilfe der Eigenschaft Value extrahieren Sie den Wert, der in dieser Zelle steht.

Um die letzte Zelle im benannten Bereich auszulesen, m√ľssen Sie vorher wissen, wie viele Zeilen und Spalten sich in diesem Bereich befinden. Dazu wenden Sie die Funktion Count auf die Auflistungen Rows und Columns an. Die letzte Zelle ergibt sich dann automatisch aus dem Schnittpunkt dieser beiden ‚ÄěErgebnisse‚Äú.

Signal bei Wert√ľberschreitung in Zelle ausgeben

Bei der folgenden L√∂sung soll in Tabelle2 die Zelle A1 √ľberwacht werden. Wird in diese Zelle ein Wert > 100 erfasst, soll Excel mit einem Signalton reagieren. Dazu klicken Sie den Tabellenreiter unten mit der rechten Maustaste an und w√§hlen den Befehl Code anzeigen aus dem Kontextmen√ľ. Erfassen Sie dann das Ereignis Worksheet_Change wie folgt:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer

    If Target.Address = "$A$1" Then 
        If Target.Value > 100 Then
            For i = 1 To 3
                Beep
               Application.Wait Now + TimeValue("00:00:01")
            Next i
        End If

    End If

End Sub

Das Ereignis Worksheet_Change reagiert auf jede √Ąnderung in den Zellen, die durch eine direkte Eingabe verursacht werden. √Ąnderungen des Formats und √Ąnderungen durch Formeln interessiert dieses Ereignis nicht. Damit das Ereignis nicht bei allen Zellen reagiert, grenzen Sie die √úberwachung ein, indem Sie mit Hilfe der Eigenschaft Address die Koordinate der gerade ge√§nderten Zelle (=Target) abfragen. Entspricht der eingegebene Wert in dieser Zelle einem Wert gr√∂√üer 100, dann rufen Sie mit Hilfe der Anweisung Beep das Piepssignal Ihres Computers auf. √úber die Methode Wait haben Sie die M√∂glichkeit eine kurze Pause vor dem n√§chsten Piepton einzulegen. Die L√§nge der Pausendauer ergibt sich aus der aktuellen Uhrzeit (Now) und einem Zeitversatz von einer Sekunde, die Sie √ľber die Funktion TimeValue einsteuern k√∂nnen.

Alle Kommentarfenster auf Originalposition zur√ľcksetzen

Standardm√§√üig werden Notizen recht der jeweiligen Zelle etwas versetzt angezeigt. Durch bestimme Aktionen in Excel kann diese Position bewusst bzw. unbewusst ver√§ndert werden. Das folgende Makro stellt die urspr√ľngliche Position der Kommentarfenster wieder her.

Sub KommentarReset()
  Dim cmt As Comment

  For Each cmt In Tabelle3.Comments
   cmt.Shape.Top = cmt.Parent.Top
   cmt.Shape.Left = cmt.Parent.Offset(0, 1).Left + 5
   cmt.Shape.TextFrame.AutoSize = True

  Next cmt

End Sub

Deklarieren Sie im ersten Schritt des Makros eine Objektvariable vom Typ Comment. √úber eine For Each Next-Schleife durchlaufen Sie alle Kommentare in Tabelle3. Innerhalb der Schleife weisen Sie dem jeweiligen Kommentar √ľber die Eigenschaften Left und Top die linke, obere Ecke zu. Diese ergibt sich zur jeweils hinterliegenden Zelle (Parent). √úber die Eigenschaft Offset k√∂nnen Sie eine Spalte nach rechts versetzt und etwa 5 Punkte zus√§tzlich die neue Position des Kommentarfensters festlegen. Mit Hilfe der Methode AutoSize bestimmen Sie die Gr√∂√üe des Kommentarfensters je nach hinterlegtem Kommentarinhalt.

Den Codenamen einer Tabelle als Quelle f√ľr ein Listenfeld verwenden

Es gibt einige Varianten, um ein Listenfeld mit Daten aus einer Tabelle zu bef√ľllen. Im folgenden Beispiel soll die Eigenschaft RowSource verwendet werden, um ein Listenfeld zu f√ľllen. Das Problem dabei ist, dass auf manuelle Art und Weise lediglich der ‚Äěnormale‚Äú Tabellenname bei dieser Eigenschaft angegeben werden kann.

Wie Sie sehen unterscheidet sich der Codenamen der Tabelle (Tabelle4) vom ‚Äěnormalen‚Äú Namen der Tabelle (Listenfeld). Wenn Sie die Eigenschaft RowSource im Eigenschaften-Fenster bestr√ľcken m√∂chten, dann w√ľrde das nur √ľber die Zeile Listenfeld!A1:A10 funktionieren. Das Problem hierbei ist, wenn der Tabellenname ge√§ndert wird. Dann wird diese Ver√§nderung nicht nachgezogen. Daher wird der folgende Ansatz √ľber den Codenamen der Tabelle gew√§hlt.

Private Sub UserForm_Initialize()
 Dim lngZeileMax As Long

 With Tabelle4

  lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
  Me.ListBox1.RowSource = .Name & "!A1:A" & lngZeileMax   

 End With

End Sub

Laufzeit der Prozedur zugewiesen.¬† Das Ereignis UserForm_Initalize wird automatisch ausgef√ľhrt, noch bevor die Userform am Bildschirm angezeigt wird.

Der Codenamen der Tabelle Tabelle4 ist konstant vorgegeben. Von diesem Namen abgeleitet wird der normale Tabellenamen √ľber die Eigenschaft Name abgefragt und der Eigenschaft RowSource zur

Eine Zelle als Schaltfläche nutzen

Beim folgenden Beispiel soll eine bestimmte Zelle als Schaltfl√§che benutzt werden, um ein Makro auszuf√ľhren. Dazu kann das Ereignis Worksheet_SelectionChange eingesetzt werden, welches jede Bewegung in der Tabelle aufzeichnet. Sobald in diesem Beispiel die Zelle D4 selektiert wird, wird das hinterlegte Makro automatisch ausgef√ľhrt.

Direkt hinter der Tabelle4 wird das folgende Ereignis hinterlegt.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$D$4" Then Makrostart
End Sub

Sub Makrostart()
    MsgBox "Es ist jetzt genau " & Time & " Uhr", vbInformation
End Sub

Mit Hilfe der Eigenschaft Address kann die selektierte Zelle abgefragt werden. Wenn die Zelle D4 selektiert bzw. geklickt wird, dann wir die Prozedur Makrostart gestartet.

Die Multifunktionsleiste ein- und ausblenden

Soll das Men√ľband von Excel ein- und ausgeblendet werden, dann gen√ľgt es die Tastenkombination Strg + F1 zu dr√ľcken. Damit steht mehr Platz f√ľr eine Bearbeitung zur Verf√ľgung. Soll jedoch die Multifunktionsleiste komplett versteckt und wieder eingeblendet werden, dann hilft ausgerechnet ein Excel-4 Makro weiter.

Sub MultifunktionsleisteVerstecken()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


Sub MultiFunktionsleisteAnzeigen()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

Mit Hilfe der Methode ExceuteExcel4Macro können Sie ein Excel4-Uralt-Makro aufrufen.

Pr√ľfen, ob eine bestimmte Schriftart verf√ľgbar ist

Bei der letzten Aufgabenstellung in diesem VBA-Newsletter soll gepr√ľft werden, ob eine bestimmte Schriftart (Helvetica) verf√ľgbar ist. Dazu wird eine Word-Sitzung gestartet und die einzelnen Schriften in eine Excel-Tabelle gespielt. Danach wird in der Excel-Tabelle nach der gew√ľnschten Schrift gesucht.

Sub SchriftartPr√ľfen()
  Dim fnt As Variant
  Dim treffer As Range
  Dim WordObj As Object
  Dim i As Integer

  With Tabelle6

   .Cells.ClearContents

    Set WordObj = CreateObject("Word.application")

    i = 1

    For Each fnt In WordObj.FontNames

      .Cells(i, 1).Value = fnt

      i = i + 1

    Next fnt

    WordObj.Quit

   
    Set treffer = .Range("A:A").Find(what:="Helvetica", lookat:=xlWhole)

     If treffer Is Nothing Then

       MsgBox "Schriftart nicht installiert", vbCritical

     Else

       MsgBox "Schriftart installiert", vbInformation

     End If

  End With

End Sub

√úber die Funktion CreateObject wird eine Word-Sitzung direkt aus Excel gestartet. √úber eine Schleife werden die einzelnen Schriften mit Hilfe der FontNames-Auflistung ausgelesen. Danach wird die gew√ľnschte Schrift √ľber die Direktsuche von Excel gesucht.