VBA - Befehle

In dieser Rubrik führe ich VBA - Befehle auf, mit denen diverse Dinge erreicht bzw. ermittelt werden können und die immer mal wieder schnell benötigt werden. Ich bin bemüht, diese Sammlung stetig zu erweitern.

Zum Übernehmen der Befehlszeile(n) diese markieren, kopieren - mit der rechten Maustaste anklicken und dann "Kopieren" auswählen oder mit der Tastenkombination STRG C - und in Ihr VBA Projekt - mit der rechten Maustaste in Ihr VBA Projekt klicken und dann "Einfügen" auswählen oder mit der Tastenkombination STRG V - einfügen.

Ich verzichte hier absichtlich auf genauere Erklärungen zu den einzelnen VBA Strings, da die Auflistung nur einen schnellen Überblick einzelner Anweisungen aufzeigen soll. Für eine ausführliche Hilfe sollte man die Excel-VBA-Hilfe nutzen, in dem man nach dem Kopieren der Anweisung in Ihr VBA-Projekt diese markiert und dann die F1 Taste betätigt.


Letzte beschriebene Spalte im gesamten Tabellenblatt finden.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column 
 


Letzte beschriebene Zeile in einer bestimmten Spalte - hier Spalte A - finden.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


Range("A65536").End(xlUp).Row

 


Letzte beschriebene Spalte in einer bestimmten Zeile - hier Zeile 1 - finden.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


Range("IV1").End(xlToLeft).Column - 1

 


Erste freie Zeile in einer bestimmten Spalte - hier Spalte A - finden.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


Range("A65536").End(xlUp).Offset(1, 0).Row

 


Leerzeilen in einem Tabellenblatt über eine Schleife finden.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


For i = 1 To 65536
'i = Zählervariable einer Schleife
If 
WorksheetFunction.CountBlank(Rows(i)) = 256 Then
'Hier dann z.B. Leerzeilennummer in Variable schreiben lassen
End If
Next


 


Leerzeile löschen, wenn Zelle einer bestimmten Spalte - hier Spalte A - leer ist.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


Range("A1:A65536").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 


Position, an der eine bestimmte Zeichenfolge - hier in Zelle A1 das Minuszeichen ( - ) -auftritt, ermitteln.

Beispiel: Text = excel-beispiele, Ergebnis = 6

Gibt einen Wert des Typs Integer, bzw. Long zurück.


InStr(Range("A1"), "-") 

 


Pfad der Datei , aus der das aktuelle Makro ausgeführt wird, ermitteln.

Gibt einen Wert des Typs String zurück.


ThisWorkbook.Path
 


Pfad der aktiven Datei, also der, die sich im Vordergrund befindet, ermitteln.

Gibt einen Wert des Typs String zurück.


 
ActiveWorkbook.Path
 


Teil eines Wertes einer Zelle - hier Wert in Zelle A1 ab dem ersten Buchstaben für die Länge von 5 Buchstaben - darstellen.

Beispiel: Text = excelbeispiele, Ergebnis = excel

Gibt einen Wert des Typs String zurück.


Mid(Range("A1"),1,5)
 


Länge eines Textes/ Wertes - hier Textlänge aus Zelle A1 - ermitteln.

Beispiel: Text = excelbeispiele, Ergebnis = 14

Gibt einen Wert des Typs Integer, bzw. Long zurück.


 
Len(Range("A1"))
 


Gibt den Wert, angefangen von rechts für eine bestimmte Anzahl von Buchstaben - hier Wert aus Zelle A1 für 9 Buchstaben -, zurück.

Beispiel: Text = excelbeispiele, Ergebnis = beispiele

Gibt einen Wert des Typs String zurück.


Right(Range("A1"), 9)
 


Gibt den Wert, angefangen von links für eine bestimmte Anzahl von Buchstaben - hier Wert aus Zelle A1 für 5 Buchstaben -, zurück.

Beispiel: Text = excelbeispiele, Ergebnis = excel

Gibt einen Wert des Typs String zurück.


Left(Range("A1"), 5)
 


Aktive Zelle ermitteln.

Gibt einen Wert des Typs String zurück.


ActiveCell.Address
 


Aktive Zeile ermitteln.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


ActiveCell.Row
 


Aktive Spalte ermitteln.

Gibt einen Wert des Typs Integer, bzw. Long zurück.


ActiveCell.Column
 


Bildschirmmeldung am Bildschirm - hier mit dem Text "Ich bin eine Bildschirmmeldung!" - anzeigen.


MsgBox "Ich bin eine Bildschirmmeldung!", vbInformation, "Meldung..."

 


Eingabefenster am Bildschirm - hier mit der Eingabeaufforderung "Bitte Text eingeben!" - anzeigen, deren Eingabe dann in Variable "Eingabevariable" geschrieben wird.


Eingabevariable = InputBox("Bitte Text eingeben!", "Eingabeaufforderung")

 


On Error GoTo Sprungmarke - Anweisung regelt, das die Prozedur bei einem Fehler ab einer definierten Sprungmarke - Hier die Sprungmarke mit dem Namen "Fehlerbehandlung" - fortgesetzt wird


On Error GoTo Fehlerbehandlung
.
.
.
'Sprungmarke "Fehlerbehandlung
Fehlerbehandlung:
 


On Error Resume Next - Anweisung regelt, dass eine Prozedur ab der Anweisung, die nach der Fehlerhaften folgt, fortgesetzt wird.


On Error Resume Next
 


On Error goto 0 - Anweisung deaktiviert in der aktuellen Prozedur alle vorangegangenen Fehlerroutinen.


On Error 
GoTo 0
 


Application.EnableEvents - Eigenschaft deaktiviert die Ereignisanzeige. Sie muss, nachdem sie deaktiviert  wurde, am Ende eines VBA Codes wieder aktiviert werden. Eingesetzt verhindert es z.B. dass ein Ereignis, das durch einen Automatismus ungewollt mehrfach ausgelöst wird, mehrfach ausgeführt wird

Einstellung/ Zustand:
True
= aktivieren
False
= deaktivieren


Application.EnableEvents = False
 


Application.ScreenUpdating - Eigenschaft deaktiviert die Bildschirmanzeige und verhindert so das die Einzelschritte eines Codes am Bildschirm wahrgenommen werden können. Sie muss nicht, nachdem sie deaktiviert  wurde, am Ende eines VBA Codes wieder aktiviert werden, da diese automatisch sich wieder aktiviert.

Einstellung/ Zustand:
True
= aktivieren
False
= deaktivieren


Application.ScreenUpdating = False
 


Application.DisplayAlerts - Eigenschaft deaktiviert die Meldungsanzeige und verhindert so das Warn- bzw. Fehlermeldungen am Bildschirm ausgegeben werden. Diese Eigenschaft muss spätestens am Ende, besser ist jedoch früher, eines VBA Code wieder aktiviert werden.

Einstellung/ Zustand:
True = aktivieren
False
= deaktivieren


Application.DisplayAlerts = False
 


Formel über ein Makro einfügen - hier in Zelle C1 die Formel A1+B1 -. Durch die FormulaLocal - Eigenschaft wird die Formel immer in der Versionssprache eingefügt.


Range("C1").FormulaLocal = "=A1+B1"
 


Arbeitsblattfunktionen in VBA - hier die Funktion ZÄHLENWENN mit den Argumenten Bereich A1:A10 und dem Kriterium "x" -. Es stehen fast alle Arbeitsblattfunktionen auch in VBA zur Verfügung. Allerdings lauten die Funktionen etwas anders. Hierfür habe ich eine Datei erstellt, in der man die Funktion unter dem gewohnten Namen suchen lassen kann und der Funktionsname für die Application.WorksheetFunction einschließlich des kompletten Syntx für das VBA Projekt ausgegeben wird.

Gegenüberstellung Arbeitsblattfunktion <-> Application.WorksheetFunction  herunterladen


Application.WorksheetFunction.CountIf(Range("A1:A10"), "x")
 


Die Cells-Eigenschaft - Beispielbefehl zum Ansprechen der Zelle A1 - wird überall dort benutzt, wo u. a. nicht mit den Spaltenbezeichnungen in Form des Buchstabens, sondern mit der Spaltenindexnummer (ColumnIndex) gearbeitet werden kann/ muss. Die Eigenschaft setzt sich aus dem Ausdruck Cells und dem RowIndex für die Zeilennummer, sowie dem ColumnIndex für die Spaltenindexnummer zusammen. Die Zeilennummer kann für den Bereich 1 bis 65536 vergeben werden. Mit der Spaltenindexnummer für einen Bereich 1 bis 256 und verhält es sich wie folgt: Die Zahl 1 steht für Spalte A, eine 2 steht für die Spalte B, eine 3 für Spalte C usw. Die Spaltenindexnummer kann man sich auch im aktuellen Blatt anzeigen lassen. Dazu in der Menüleiste Extras => Optionen auswählen und in den Optionen auf die Registerkarte Allgemein wechseln um in den Einstellungen den Punkt Z1S1-Bezugsart zu aktivieren. Nach einem Klick auf OK werden anstelle der Spaltenbuchstaben die Spaltenindexnummern angezeigt.


Cells(1, 1)
 


Werte aus geschlossener Exceldatei auslesen - hier im Pfad "C:\" aus Datei "Dateiname.xls" aus Tabellenblatt "Blattname" aus Zelle "A1" -.
Die Zellenangabe (A1) muss in Z1S1-Zeichenfolge (R1C1) erfolgen. Diese sieht man, wenn man in der Menüleiste auf Extras => Optionen auswählt, dann auf die Registerkarte "Allgemein" wechselt und dann unter "Einstellungen" den Punkt "Z1S1-Bezugsart" markiert. Nun haben die Spaltenbezeichnungen keine Buchstaben mehr, sondern Zahlen, die Spaltenindexzahlen.


ExecuteExcel4Macro("'C:\[Dateiname.xls]Blattname'!R1C1")
 


Zeilenvorschub mit der Chr(10)- Funktion oder vbLf -Konstante - Mit beiden Befehlen wird ein Zeilenvorschub in Texten, z.B. in einer Messagebox, erzeugt. Der Text, der hinter Chr(10) bzw. vbLf steht, wird eine Zeile tiefer dargestellt.
In diesem Beispiel

   Ich bin eine                                                           
   Messagebox mit Chr(10)-Funktion  

   Ich bin eine
    Messagebox mit vbLf-Konstante


MsgBox "Ich bin eine" & Chr(10) & "Messagebox mit Chr(10)-Funktion"
'oder
MsgBox "Ich bin eine" & vbLf & "Messagebox mit vbLf-Konstante"
 


Wagenrücklauf mit der Chr(13)- Funktion oder vbCr-Konstante - Mit beiden Befehlen wird ein Wagenrücklauf in Texten, z.B. in einer Messagebox, erzeugt. Der Text, der hinter Chr(13) bzw. vbCr steht, wird eine Zeile tiefer dargestellt.
In diesem Beispiel

   Ich bin eine
   Messagebox mit Chr(13)-Funktion  

   Ich bin eine
   Messagebox mit vbCr-Konstante


MsgBox "Ich bin eine" & Chr(13) & "Messagebox mit Chr(13)-Funktion"
'oder
MsgBox "Ich bin eine" & vbCr & "Messagebox mit vbCr-Konstante"
 


Autofiltermodus mit der FilterMode-Eigenschaft ermitteln - Mit dieser Eigenschaft kann ermittelt werden, in welchem Modus, also ob Werte gefiltert oder nicht gefiltert wurden, sich der Autofilter befindet.

Einstellung/ Zustand:
True
= Werte gefiltert
False
= Werte nicht nicht gefiltert


MsgBox ActiveSheet.FilterMode
 


Status des Autofilters mit der AutoFilterMode-Eigenschaft ermitteln - Mit dieser Eigenschaft kann ermittelt werden, ob in einem Tabellenblatt ein Autofilter aktiv ist.

Einstellung/ Zustand:
True
= aktiv
False
= nicht aktiv


MsgBox ActiveSheet.AutoFilterMode
 


Alles in Großbuchstaben wandeln durch die UCase-Funktion - Mit dieser Funktion wird ein Text, der aus Groß und/ oder Kleinbuchstaben besteht, komplett in Großbuchstaben gewandelt.

Beispiel: In Zelle A1 steht:                 Ich lerne VBA
            Zurückgegebener Wert:        ICH LERNE VBA

Gibt einen Wert des Typs String zurück,


MsgBox UCase(Range("A1"))
 


Alles in Kleinbuchstaben wandeln durch die LCase-Funktion - Mit dieser Funktion wird ein Text, der aus Groß und/ oder Kleinbuchstaben besteht, komplett in Kleinbuchstaben gewandelt.

Beispiel: In Zelle A1 steht:             ICH LERNE VBA
            Zurückgegebener Wert:    
ich lerne vba

Gibt einen Wert des Typs String zurück,


MsgBox LCase(Range("A1"))
 


Blattindexnummer ermitteln - Mit dieser Eigenschaft erhält man u.a. die Blattindexnummer eines Tabellenblatts oder eines anderen Objekts

Gibt einen Wert des Typs Long zurück


MsgBox Sheets("Blattname").Index
 


Vergleich von 2 Zeichenfolgen - Mit diesem Operator können 2 Zeichenfolgen miteinander verglichen werden.
Die Zeichenfolge bestehen aus einem Zeichenfolgeausdruck (z.B. einem Rangeobjekt) und einem Muster

Einstellung/ Zustand:
True
= Muster kommt in der Zeichenfolge vor
False
=
Muster kommt nicht in der Zeichenfolge vor


Zeichenfolgeausdruck Like Muster
'Beispiel:
MsgBox "Constante" Like "Const*"
 


Blendet alle gefilterten Daten ein. Bei der Funktion Autofilter wird die Einstellung "Alle" aktiviert.

 


Sheets("Blattname").ShowAllData
 


Öffnet unsichtbar eine Exceldatei.

 


GetObject ("kompletter Dateipfad & Dateiname.xls")
 


Blendet eine unsichtbare Exceldatei ein bzw. aus.

Einstellung/ Zustand:
True
= Blendet Datei EIN
False
=
Blendet Datei AUS


Windows("Dateiname.xls").visible = True
 


Sie sollten, sofern Sie Visual Basic Code einsetzen, zum Abschluss noch die Sicherheitseinstellung, die beim Öffnen der Datei abgefragt wird, kontrollieren. Klicken Sie dazu nacheinander in der Menüleiste wieder auf Extras => Makro und dann auf Sicherheit.... Gehen Sie, falls nicht schon angezeigt, auf die Registerkarte Sicherheitsstufe und schauen Sie dort nach, ob die Einstellung Mittel aktiviert ist. Wenn nicht, aktivieren Sie diese Einstellung, da sonst der Code nicht ausgeführt werden kann. Beim nächsten Öffnen Ihrer Datei kommt eine Abfrage, ob Makros aktiviert oder deaktiviert werden sollen. Klicken Sie auf aktivieren und der Code wird ausgeführt


Besucherstatistik

 
  A B C D E F G H I J K
1                      
2   Besucher gesamt:    
3    
4                      
5  

Besucher heute: 37

 

Besucher gestern: 123

 
6                      
7  

Max. Besucher pro Tag: 708

 

Max. Besucher pro Tag am: 03.01.2008

 
8                      
9  

gerade online: 1

  max. gleichzeitig online: 68  
10                    
11  

Seitenaufrufe heute: 79

 

Seitenaufrufe gestern: 333

 
12                      
13  

Max. Seitenaufrufe pro Tag: 14.818

 

Max. Seitenaufrufe pro Tag am: 15.11.2015

 
14                      
15   Diese Seite wurde bereits 1.584.292 x aufgerufen  
16                      
17  

Die hier angezeigte Besucherstatistik wurde am 06.01.2008 neu eingebunden und gestartet.
Aus der alten Besuchererfassung wurden lediglich die Gesamtbesucherzahl und die maximale Besucheranzahl eines Tages seit Bestehen der Homepage eingepflegt.

 
18    
19                      

 
Kontakt:

letzte Aktualisierung am 14.08.2011

© 2004 - Copyright Oliver Scheckelhoff,
All Rights Reserved