VBA-Syntax & Beispiele

Achtung “ “ werden hier falsch angezeigt, sie müssen immer oben stehen!!!! 

1. Grundsätzliches

In VBA (sowie in vielen anderen Programmiersprachen) wird erst das Objekt (zum Beispiel eine Zelle) genannt mit dem etwas passieren soll, dann die Eigenschaft (zum Beispiel Farbe) und dann die konkrete Ausprägung (zum Beispiel pink).

Beispiel
Range(„E5“).Font.Color = 1500
oder
Cells(6, 5).Value = „Dies ist eine VBA-Übung“

Inhalt von Zellen & Formatierung

Einzelne Zellen können mit den Befehlen

  • Range(„E5“) -> Spalte E Zeile 5
    oder mit
  • Cells(3,5)  -> 3. Zeile, 5. Spalte (E)
    angesprochen werden.

Mit können allerdings auch Bereiche festgelegt werden:

Bsp: Range(„B3:E10“).value = „xxx“ 
schreibt in jede Zelle (also in 32 Zellen!!) des Bereiches xxx rein.

Eigenschaften von Zellen

Grundsätzlich können dies alle Eigenschaften sein, die Du in Excel festlegen kannst. Dies sind zum Beispiel:

Schriftfarbe: Font.Color  = VbColor Name 

  • Range("a1").Font.Color = vbRed

    Bildschirmfoto 2023-09-03 um 18.37.48

    • vbBlack (schwarz)
    • vbBlue
    • vbCyan (türkis)
    • vbGreen
    • vbMagenta (pink)
    • vbRed
    • Ivbwhite
    • vbYellow

oder:

Range("a1").Font.Color = RGB(255,255,0)
Cells(zeile, spalte).Font.Color = RGB(0,5,100)

RGB steht für rot, grün, blau. Die Zahlen bewegen sich jeweils zwischen 0 und 256. Welche Zahlen eine Farbe ergeben kannst Du hier nachlesen:
https://www.farb-tabelle.de/de/farbtabelle.htm

 Hintergrundfarbe der Zelle Interior.ColorIndex.

  • Beispiele:
    Range("a1").Font.ColorIndex = 36
    Cells(zeile, spalte).Interior.ColorIndex = 45

Der Colorindex ist eine Zahl zwischen 1 und 56. Welche Hintergrundfarbe sich ergibt kannst Du ausprobieren.

Hintergrundfarben-Übersicht

Umrandungen

Löschen

Inhalt & Formatierung löschen: clear

Bsp.: Range(Cells(1, 1), Cells(5, 4)).Clear
-> In diesem  Bereich werden sowohl Inhalt als auch Formatierung gelöscht.

Sonstiges

  • Fett: .Font.Bold = True nicht mehr fett: .Font.Bold = False
  • Kursiv: .Font.Italic = True nicht mehr kursiv: .Font.Italic = False
  • Schriftgröße: Font.Size 
  • Schriftart: Font.Name
  • Zeilenhöhe festlegen: Rows(„1:1“).RowHeight = 30
  • Spaltenbreite setzen: Columns(„A“).ColumnWidth = 30 oder  Columns(„A:G“).ColumnWidth = 15 oder Columns(„A:B“).Autofit

Beispiele

  • Range("a1").Font.Bold = True
  • Range("a1").Font.italic = True (kursiv)
  • Range("a1"). Font.Size = 26
  • Range("a1").Font.Name ="Arial"

Zellen zentrieren und ausrichten

Erst werden die Zellen selektiert:

Range(„a15:F16“).Select

Dann werden die selektierten Zellen ausgerichtet zum Beispiel:

  With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With

Im Anschluss werden die selektierten Zellen verbunden:
Selection.Merge

Tabellenblätter benennen

Das aktive Tabellenblatt wird mit ActiveSheet. angesprochen. Mit ActiveSheet.Name = “ Mein Name“ wird dann der Name festgelegt.

Rechnen in VBA

Wie Du sicher weißt, geeignet  Excel (und somit auch VBA) sehr gut zum rechnen. Dazu musst Du lediglich einer Zelle den Wert (Ergebnis) zuweisen.

Addition:

Beispiel: In D10 soll nun das Ergebnis der Addition von B10 + C10
Cells(10,4).value = Cells(10,3).value + Cells(10,2).value
Cells(10,4) = Cells(10,3) + Cells(10,2) funktioniert auch.

Subtraktion:

Beispiel: In D10 soll nun das Ergebnis der Addition von B10 – C10
Cells(10,4) = Cells(10,3) – Cells(10,2)

Multiplikation/Division funktioniert mit den Rechenzeichen * bzw. /.

Potenzieren funktioniert mit den Rechenzeichen ^ ;
Bsp.: 2^3 = 8   (2 hoch 3)

Wurzel ziehen funktioniert mit den Rechenzeichen ^ ;
Bsp.: 8^(1/3) = 2  -> hier die 3. Wurzel aus 8;
-> ^ 1/n-> n-te Wurzel

Eingabe der Werte durch eine Inputbox

Durch den Befehl InputBox(„bitte geben Sie die erste Zahl ein“) erscheint diese Box.

Bildschirmfoto 2023-08-27 um 17.31.38

 Durch den Befehl cells(10,2) = InputBox(„bitte geben Sie die erste Zahl ein“) wird die Benutzereingabe (also die eingegebene Zahl) zusätzlich in die Zelle B10 geschrieben.

Ausgabe der Werte durch eine Messagebox

 Durch den Befehl MsgBox „Das Ergebnis lautet “ & Cells(10, 4)  wird der Wert der Zelle D10 in einer Messagebox ausgegeben.

Bildschirmfoto 2023-08-27 um 17.37.55

MsgBox „Heute ist der “ & Date“
– > Es wird eine Messagebox mit den heutigen Datum erzeugt

Deklaration (=Festlegen) von Variablen

Bisher haben zu immer wenn wir Daten, die der Benutzer eingegeben hat (zum Bsp.: Schriftgröße, Schriftfarbe, etc. ) verarbeiten wollten, in eine Zelle geschrieben und haben dann durch den Cells bzw. Range-Befehlt darauf zugegriffen.

Dies geschieht in der Informatik i.d.R. einfacher. Es wird eine Variable definiert, in die dieser Wert dann reingeschrieben wird.

VBA-Syntax:  Dim VariablenName as <Datentyp>

Beispiele:
Dim schriftgroesse  as integer ->Variable wird definiert
schriftgroesse= InputBox(„bitte geben Sie die Schriftgröße ein!“) 
->Schriftgröße wird durch den Benutzer eingegeben und in die Variable geschrieben
cells(1,1).Font.Size = schriftgroesse
-> Die Schrift in der Zelle A1 wird auf diese Größe gesetzt.

Zur Festlegung (Deklaration) von Variablen muss der Name und der Datentyp festgelegt werden.

Gängige Datentypen sind u.a. Integer (ganze Zahl), Double ( Zahl mit Nachkommastellen), Date (im Format MM/TT/YYYY) und String (Text, d.h. beliebige Zeichen

Beispiele:
Dim KerstinsZahl as integer
Dim KerstinsName as String
Dim KerstinsNachkommaZahl as Double
Dim KerstinsGeburtsdatum as Date

Zuordnen von Werten in Zellen

Dim laufzeit as Integer
laufzeit = InputBox(„Wie lange möchten Sie den Kredit abbezahlen?“)
Range(„c5“).Value = laufzeit
-> Es wird durch eine Inputbox die laufzeit abgefragt, in der Variablen laufzeit gespeichert und in die Zelle C5 geschrieben.

… oder mit Variablen
Dim Zeile, Spalte as integer
Zeile = 5
Spalte=3
Cells(Zeile, Spalte ).Value = „Laufzeit“
-> in C5 wird der Wert reingeschrieben.

Zuordnen von Werten in Variablen

Spalte = Spalte +1
-> Die Variable Spalte wird um eins hochgezählt.

Jahr=Year(Date)
-> Die Variable Jahr wird auf das Jahr des aktuellen Datums gesetzt.

Dim ergebnis, zahl As Double
Dim exponent As Integer
exponent = 3
zahl =2
ergebnis = zahl ^exponent
-> In der Variablen ergebnis steht nun 8 (2 hoch 3)

Selektieren von Zellen / Bereichen

Oft müssen Zellen bzw. Bereiche selektiert (=markiert) werden, um sie zu formatieren:

Beispiele:

Range(„C6“).Select
Cells(2,2).Select

Beispiel für das Selektieren von Bereichen mit Variablen:

Dim zeileA, zeileE, spalteA, spalteE As Integer
zeileA = 5
zeileE = 10
spalteA = 2
spalteE = 4
Range(Cells(zeileA, spalteA), Cells(zeileE, spalteE)).Select
-> Der Bereich von B5 bis D10 wird selektiert

Formatieren – Beispiele

infohttps://www.excel-inside.de/beispiele_vba/vba-formatierung/270-parameter-fuer-den-vba-befehl-qformatq

Mit dem Befehl Format können Felder formatiert werden.
Parameter für den VBA-Befehl „Format“

Syntax: FORMAT(wert, Format)

Beispiele:

Zahlenformate

dim wert as double
(wert ist hier eine Variable, in der eine Zahl steht)

  • FORMAT(wert, „0.00“)-> Zahl mit 2 Nachkommastellen
  • FORMAT(wert, „#,##0.00“) ->  4.711,00; # Platzhalter für eine Ziffer. Diese wird aber nur angezeigt, wenn sich an dieser Stelle auch wirklich eine Ziffer befindet.
  • FORMAT(wert, „currency“) -> Liefert das voreingestellte Währungsformat 4.711,00 €
  • FORMAT(wert, „0.0 %“) -> % Wandelt die Zahl in Prozent um, indem es die Zahl mit 100 multipliziert und ein %-Zeichen anhängt 471,1 % =

Eine andere Möglichkeit Zellen zu formatieren besteht darin, sie zu selektieren und ihnen dann Formatierungen zu zuweisen.

Beispiele

Range(Cells(zeileA, spalteA), Cells(zeileE, spalteE)).Select
Selection.Font.Bold = True
Selection.NumberFormat = „0_ ;-0 „

-> Es wird ein Bereich selektiert und dann fett  und als Zahl ohne Nachkommastellen formatiert.

oder Bereich selektieren, fett und zentriert
Range(„B9:F9“).Select
Selection.Font.Bold = True
With Selection .HorizontalAlignment = xlCenter
End With
… als Euro-Wert:
Cells(zeile, spalte).Select I selektieren des Bereichs
Selection.NumberFormat = _ „_-* #,##0.00 [$€-de-DE]_-;-* #,##0.00 [$€-de-DE]_-;_-* „“-„“?? [$€-de-DE]_-;_-@_-“ 

oder als %-Wert 
Range(„C6“).Select
Selection.Style = „Percent“
Selection.NumberFormat = „0.00%“
oder als ganze Zahl ohne Tausenderpunkt
Selection.NumberFormat = „0_ ;-0 „

_VBA-syntax-Info_________________________________________________

Bedingungsprüfungen (If…)

Es werden 2 Fälle unterschieden:

If Cells(1 , 1 ).Value < 0
Then Cells(1 , 1 ).Interior.ColorIndex = 38 (pink)
Else Cells(1 , 1 ).Interior.ColorIndex = 48 (grau)
End If
–> In Abhängigkeit des Wertes der Zelle A1 wird der Hintergrund pink oder grau.

Bei mehr Fällen:

Syntax:

If condition
[ Then ] [ statements ]
[ ElseIf elseifcondition [ Then ]
[ elseifstatements ] ]
[ Else [ elsestatements ] ]
End If

Beispiele:
In Abhängigkeit der Zahl in der Zelle A1, wird ihre Hintergrundfarbe gesetzt:
If Cells(1 , 1 ).Value < 0 Then
Cells(1 , 1 ).Interior.ColorIndex = 38 (pink)
elseif Cells(1 , 1 ).Value <10 Then
Cells(1 , 1 ).Interior.ColorIndex = 50 (grün)
Else
Cells(1 , 1 ).Interior.ColorIndex = 48 (grau)
End If

If performance = 1 Then
Bonus = salary * 0.1
ElseIf performance = 2 Then
Bonus = salary * 0.09
ElseIf performance = 3 Then
Bonus = salary * 0.07
 Else
Bonus = 0
End If

-> Die If-Bedingung (entspricht der WENN-Funktion) kann viele Fälle enthalten.

https://docs.microsoft.com/de-de/dotnet/visual-basic/language-reference/statements/if-then-else-statement

Schleifen

For-Schleife …

Syntax:
For counter [ As datatype ] = start To end [ Step step ] [ statements ] [ Continue For ] [ statements ] [ Exit For ] [ statements ] Next [ counter ]
https://docs.microsoft.com/de-de/dotnet/visual-basic/language-reference/statements/for-next-statement

Beispiel
Dim zeile, spalte, ende, zahl As Integer
zeile = 5
spalte = 2
ende = 10
For zahl = 0 To ende
Cells(zeile, spalte).Value = zahl
Cells(zeile, spalte).Interior.ColorIndex = 1 + zahl
zeile = zeile + 1
Next
-> Von Zeile 5 bis 10 in Spalte B wird der Zellenhintergrundverändert. 

Beispiel:10x10-Farbe

Sub farbe10x10()
Range(„B3“, „Z54“).clear
Dim z, sp, zae1, zae2, farbe As Integer
sp = 2
For zae1 = 1 To 10
z = 3
For zae2 = 1 To 10
farbe = Round(Rnd * 56, 0)
Cells(z, sp).Interior.ColorIndex = farbe
Cells(z, sp).Value = farbe
z = z + 1
Next
sp = sp + 1
Next

 

 

 

While-Schleife

Syntax:
While condition [ statements ] [ Continue While ] [ statements ] [ Exit While ] [ statements ] End While

https://docs.microsoft.com/de-de/dotnet/visual-basic/language-reference/statements/while-end-while-statement

Do While-Loop

Syntax:
Do While condition
Statements
Loop

Beispiel: 

Dim zeile, spalte, ende, zahl As Integer
zeile = 5
spalte = 2
zahl = 1
ende = 20

Do While zahl < ende.     (Bedingung)
Cells(zeile+zahl, spalte).Value =zahl  (Statements)
zahl = zahl +1
Loop 

Die Zahl wird von 1 bis 19 hochgezählt und in die Spalte B geschrieben.

x=0
Do
x = x + 1
Cells(x, 1).Value = x
Loop Until x > 5

Userforms

Öffnen einer Userform: <userform-Name>.show
Bsp.: KerstinsUF.show

Aufrufen von Subs

Mit
Call Subname
können innerhalb einer Routine andere aufgerufen werden. Dies bietet sich insbesondere dann an, wenn diese Routine häufiger gebraucht werden.

K. Fröhlig