VBA-Befehle

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		Besucher gesamt:
4
5		Besucher heute: 57					Besucher gestern: 101
6
7		Max. Besucher pro Tag: 708					Max. Besucher pro Tag am: 03.01.2008
8
9		gerade online: 3					max. gleichzeitig online: 68
10
11		Seitenaufrufe heute: 180					Seitenaufrufe gestern: 261
12
13		Max. Seitenaufrufe pro Tag: 14.818					Max. Seitenaufrufe pro Tag am: 15.11.2015
14
15		Diese Seite wurde bereits 1.736.506 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