Blog

Eingebaute Funktionen

Einführung

Eingebaute VBA Funktionen

In VBA gibt es mehr als 100 eingebaute Funktionen aus folgenden 11 Kategorien:

  • Nachrichtenfunktionen
  • Textfunktionen
  • Informationsfunktionen
  • Fehlerbehandlungsfunktionen
  • Programmablauffunktionen
  • Konvertierungsfunktionen
  • Datum & Zeit Funktionen
  • Mathematische und Trigonometrische Funktionen
  • Finanzmathematik
  • Funktionen für Arrays
  • Dateiverwaltungsfunktionen

Im folgenden werden die Funktionen und eine kurze Beschreibung aufgeführt. Nicht alle dieser Funktionen sind in allen Excel Versionen verfügbar. Auf Nachrichtenfunktionen, Textfunktionen und Datum & Zeit Funktionen gehe ich in weiteren Beiträgen genauer ein.

Eingebaute Excel Funktionen

Neben den eingebauten VBA Funktionen können auch die meisten der mehr als 380 Excel Funktionen in VBA genutzt werden, in dem man „Application.WorksheetFunktion.“ voranstellt, z. B.
      Application.WorksheetFunction.Min
für die Excel-Funktion „Min“.
Im Grunde genommen sind die Excel-Funktionen Methoden des Application.WorksheetFunction Objekts.
Leider werden in Excel deutsche Funktionsnamen verwendet während man in VBA den korrespondierenden englischen Funktionsnamen verwenden muss.

Eingebaute VBA Funktionen

Nachrichtenfunktionen

Es gibt zwei eingebaute VBA Nachrichtenfunktionen. Auf diese Funktionen gehe ich im Beitrag Dialogfenster genauer ein.

Zeichenfolgenfunktionen

Es gibt 18 eingebaute VBA Zeichenfolgenfunktionen. Auf diese Funktionen gehe ich im Beitrag Zeichenfolgenfunktionen genauer ein.

Informationsfunktionen

Es gibt 8 eingebaute VBA Informationsfunktionen.

Fehlerbehandlungsfunktionen

Es gibt 2 eingebaute VBA Fehlerbehandlungsfunktionen.

Programmablauffunktionen

Es gibt 3 eingebaute VBA Programmablauffunktionen.

Konvertierungsfunktionen

Es gibt 21 eingebaute VBA Konvertierungsfunktionen.

Datum & Zeit Funktionen

Es gibt 20 eingebaute VBA Datum und Zeitfunktionen. Auf diese Funktionen gehe ich im Beitrag Datums- und Zeitfunktionen genauer ein.

Mathematische und Trigonometrische Funktionen

Es gibt 13 eingebaute Mathematische und Trigonometrische VBA Funktionen.

 

Finanzmathematische Funktionen

Es gibt 13 eingebaute Finanzmathematische VBA Funktionen.

Datenfeldfunktionen

Es gibt 6 eingebaute VBA Datenfeldfunktionen.

Dateiverwaltungsfunktionen

Es gibt 6 eingebaute VBA Dateiverwaltungsfunktionen.

Eingebaute Excel Funktionen

Neben den eingebauten VBA Funktionen können auch die meisten der mehr als 380 Excel Funktionen in VBA genutzt werden, in dem man „Application.WorksheetFunktion.“ voranstellt, z. B.
    Application.WorksheetFunction.Min
für die Excel-Funktion „Min“.
In VBA werden die Excel-Funktionen als Methoden des Application.WorksheetFunction Objekts verwendet.
Leider werden in der deutschen Excel Version deutsche Funktionsnamen verwendet während in VBA für die Methoden des Application.WorksheetFunction Objekts die korrespondierenden englischen Funktionsnamen verwendet werden. Wenn man im VBA einen Punkt hinter das WorksheetFunction Schlüsselwort setzt, werden die Methoden des Application.WorksheetFunction Objekts angezeigt.

Excel Datei mit deutschen und englischen Excel Funktionsnamen

Um die Suche nach den englischen Excel Funktionsnamen zu erleichtern sind in der Excel Datei WorksheetFunctions

  • die Methoden des Worksheetfunction Objekts
  • die korrespondierenden deutschen Excel Funktionsnamen
  • die korrespondierenden englischen Excel Funktionsnamen

enthalten. Außerdem sind Links und Literatur für weitere Informationen enthalten. Für die deutschen Funktionsnamen sind die Kategorien für die Funktionen angegeben, die von Excel beim Einfügen der Funktionen wie in der Abbildung gezeigt unter „Kategorie auswählen“ angezeigt werden. Dazu müssen folgende Schritte ausgeführt werden

  1. Zelle auswählen, in die die Funktion eingefügt werden soll
  2. Auf das Symbol fx klicken

Dann wird das folgende Pop-Up Menü angezeigt.

Wählt man die gewünschte Funktion aus und klickt man „Hilfe für diese Funktion“ (links unten) wird die Microsoft Hilfe für diese Funktion aufgerufen mit Informationen zu dieser Funktion.

Trick bei der Anwendung von Excel WorksheetFunctions

Bei der Anwendung von Excel WorksheetFunctions gibt es einen Trick, auf den ich erst vor Kurzem gestossen bin. Ich möchte ihn am Beispiel der Funktion Application.WorksheetFunction.Min erklären. Bisher bin ich davon ausgegangen, dass man dieser Funktion nur einen Bereich als Argument übergeben kann wie in folgendem Beispiel gezeigt

Sub Minimalwert_in_einem_Zellbereich_ermitteln()
Dim ws As Worksheet, Ergebnis As Double
Dim meinBereich As Range
'
' Die beiden folgenden Zeilen muessen angepasst werden
Set ws = Worksheets("Tabelle1")
Set meinBereich = ws.Range("E1:E10")
'
Ergebnis = Application.WorksheetFunction.Min(meinBereich)
Debug.Print Ergebnis

Set ws = Nothing
End Sub

Vor Kurzem habe ich jedoch festgestellt, dass man der Funktion Application.WorksheetFunction.Min auch ein Datenfeld als Argument übergeben kann. Das erhöht den Nutzwert in einem VBA Code ganz erheblich.

Sub Minimalwert_in_einem_Datenfeld_ermitteln()
Dim Datenfeld(1 To 5)  As Integer
Datenfeld(1) = 1
Datenfeld(2) = 2
Datenfeld(3) = 3
Datenfeld(4) = 4
Datenfeld(5) = 5
Debug.Print Application.WorksheetFunction.Min(Datenfeld)
End Sub

Dasselbe trifft auch für die Funktion Application.WorksheetFunction.Max zu.

Sub Maximalwert_in_einem_Zellbereich_ermitteln()
Dim ws As Worksheet, Ergebnis As Double
Dim meinBereich As Range
'
' Die beiden folgenden Zeilen muessen angepasst werden
Set ws = Worksheets("Tabelle1")
Set meinBereich = ws.Range("E1:E10")
'
Ergebnis = Application.WorksheetFunction.Max(meinBereich)
Debug.Print Ergebnis

Set ws = Nothing
End Sub
'
Sub Maximalwert_in_einem_Datenfeld_ermitteln()
Dim Datenfeld(1 To 5)  As Integer
Datenfeld(1) = 1
Datenfeld(2) = 2
Datenfeld(3) = 3
Datenfeld(4) = 4
Datenfeld(5) = 5
Debug.Print Application.WorksheetFunction.Max(Datenfeld)
End Sub

Matrixfunktionen
Bei Matrixfunktionen gibt es folgende Besonderheit

  • das Argument der Funktionen muss eine Variable vom Typ Variant oder ein Datenfeld sein.
  • der Rückgabewert muss eine Variable vom Typ Variant sein.

Wie es funktioniert möchte ich am Beispiel der Funktion Application.WorksheetFunction.MInverse ziegen, mit der man die Inverse eine Matrix ermitteln kann.

Sub test_MInverse()
Dim varM As Variant, varErgebnis As Variant
Dim i As Long, j As Long
ReDim varM(1 To 2, 1 To 2) As Double
varM(1, 1) = 5
varM(1, 2) = 5
varM(2, 1) = 0
varM(2, 2) = 3
' Inverse der Matrix varM berechnen
varErgebnis = Application.WorksheetFunction.MInverse(varM)
' Elemente der inversen Matrix im Direktfenster ausgeben
For i = 1 To 2
    Debug.Print varErgebnis(i, 1), varErgebnis(i, 2)
Next i
End Sub

Wichtige Anmerkung:
Die Größe des Matrix darf 52 Spalten mal 52 Zeilen nicht überschreiten.

Auch der folgende Code liefert das gewünschte Ergebnis:

Sub test_MInverse_Datenfeldal_Argument()
Dim dblM(1 To 2, 1 To 2) As Double, varErgebnis As Variant
Dim i As Long, j As Long
dblM(1, 1) = 5
dblM(1, 2) = 5
dblM(2, 1) = 0
dblM(2, 2) = 3
' Inverse der Matrix dblM berechnen
varErgebnis = Application.WorksheetFunction.MInverse(dblM)
' Elemente der inversen Matrix im Direktfenster ausgeben
For i = 1 To 2
    Debug.Print varErgebnis(i, 1), varErgebnis(i, 2)
Next i
End Sub

Die beschriebene Möglichkeit erhöht den Nutzwert der Matrixfunktionen in einem VBA Code ganz erheblich.

Achtung:
Bei folgendem Code wird die Fehlermeldung „Fehler beim Kompilieren – Keine Zuweisung an Datenfeld möglich“ ausgegeben:

Sub test_MInverse_Datenfeldal_Argument_funktioniert_nicht()
Dim dblM(1 To 2, 1 To 2) As Double, dblErgebnis(1 To 2, 1 To 2) As Double
Dim i As Long, j As Long
dblM(1, 1) = 5
dblM(1, 2) = 5
dblM(2, 1) = 0
dblM(2, 2) = 3
' Inverse der Matrix dblM berechnen
dblErgebnis = Application.WorksheetFunction.MInverse(dblM)
' Elemente der inversen Matrix im Direktfenster ausgeben
For i = 1 To 2
    Debug.Print dblErgebnis(i, 1), dblErgebnis(i, 2)
Next i
End Sub

Im folgenden Beispiel wird ein Bereich als Argument verwendet

Sub test_MInverse_range()
Dim varM As Variant, varErgebnis As Variant, meinBereich As Range, i As Integer
Dim ws As Worksheet
Set ws = Worksheets("Inverse")
Set meinBereich = ws.Range("A1:B2")
' Inverse berechnen
varErgebnis = Application.WorksheetFunction.MInverse(meinBereich)
' Ergebnis im Direktfenster ausgeben
For i = 1 To 2
    Debug.Print varErgebnis(i, 1), varErgebnis(i, 2)
Next i
End Sub

Ähnlich funktioniert es auch bei anderen Matrixfunktionen. Zunächst ein Beispiel für die Multiplikation zweier Matrizen

Sub Matrix_Multiplication()
Dim dblM1(1 To 3, 1 To 3) As Double, dblM2(1 To 3, 1 To 2) As Double, varErgebnis As Variant
Dim i As Integer
'
dblM1(1, 1) = 1
dblM1(1, 2) = 2
dblM1(1, 3) = 1
dblM1(2, 1) = 0
dblM1(2, 2) = 1
dblM1(2, 3) = 0
dblM1(3, 1) = 2
dblM1(3, 2) = 3
dblM1(3, 3) = 4

dblM2(1, 1) = 2
dblM2(1, 2) = 5
dblM2(2, 1) = 6
dblM2(2, 2) = 7
dblM2(3, 1) = 1
dblM2(3, 2) = 8

' Matrixprodukt berechnen
varErgebnis = Application.WorksheetFunction.MMult(dblM1, dblM2)

For i = 1 To 3
    Debug.Print varErgebnis(i, 1), varErgebnis(i, 2)
Next i
' Sollergebnis
' 15            27
' 6             7
' 26            63
End Sub

Hier noch die Berechnung der Determinante einer Matrix

Sub test_Determinante_berechnen()
Dim dblM(1 To 2, 1 To 2) As Double, dblErgebnis As Double
dblM(1, 1) = 2
dblM(1, 2) = 3
dblM(2, 1) = 4
dblM(2, 2) = -1
' Determinante berechnen
dblErgebnis = Application.WorksheetFunction.MDeterm(dblM)
' Ergebnis im Direktfenster ausgeben
Debug.Print "Determinante = "; dblErgebnis
' Sollergebnis: -14
End Sub

Wichtige Anmerkung
Die Größe des Arrays darf 73 Spalten mal 73 Zeilen nicht überschreiten.

Dieser Beitrag hat 12 Kommentare

  1. Alois Oktober 31, 2022 Antworten

    Hallo

    Ich bin es wieder einmal.
    Hätte eine Frage zur Funktion Hex, das funktioniert super nur wenn ich eine Zahl um die 1000 umwandeln will klappt das nicht mehr wird dann auf Format wissenschaftilch gestellt und zeigt bei Format Standart 3.000.000 an.
    Liegt das an mir oder VBA
    In Excel mit dezinhex klappt das aber in VBA geht es auch mit Application Worksheet kommt die komische Ausgabe.
    Danke und Gruß

    1. admin Oktober 31, 2022 Antworten

      Hallo Alois,
      ich bin mir nicht sicher, ob ich Dich richtig verstanden habe. Laut Microsoft Hilfe für die VBA Funktion Hex sollte die Funktion im Zahlenbereich von -2.147.483.648 bis 2.147.483.647 (entspricht dem Wertebereich für eine Variable des Typs Long) funktionieren. Ich habe die Funktion Hex mit den Zahlen 10.000 und 100.000 getestet und habe das richtige Ergebnis erhalten.
      Für den Test habe ich folgende Sub Prozedur verwendet
      Sub test_hex()
      Dim lngZahl As Long
      lngZahl = 100000
      Debug.Print Hex(lngZahl)
      End Sub
      Der Wertebereich liegt außerhalb des Wertebereichs für Integer Zahlen (-32.768 bis 32.767). Für das Testen eignet sich übrigens der Windows Taschenrechner sehr gut, wenn man ihn auf „Programmierer“ umstellt. Die Umstellung auf wissenschaftliches Format und Format Standard 3.000.000 konnte ich leider nicht reproduzieren. Ich denke, dass die Funktion Hex nur für ganze Zahlen Sinn macht.
      Konnte ich damit Deine Frage beantworten?
      Viele Grüße
      Stefan

  2. Alois Oktober 31, 2022 Antworten

    Hallo Stefan
    Probier mal die Zahl 1000 nur diese und einige knapp über 1000 funktionieren bei mir nicht.
    Habe das mit der folgenden Prozedur gemacht um zu testen.
    Range („A2“).Value = Hex(Range(„A1“)
    Habe dann die Werte in A1 eingetragen und das hat alles funktioniert bis auf die Zahl 1000.
    Gruß Alois

  3. Alois Oktober 31, 2022 Antworten

    oh sorry Klammer natürlich auch um Hex Funktion zu schließen

    1. admin November 1, 2022 Antworten

      Hallo Alois,
      mit dieser Information konnte ich den beschriebenen Fehler reproduzieren. Der Fehler wird von Excel verursacht und nicht von VBA. Die VBA Funktion Hex gibt den Hex-Wert einer Zahl als Zeichenfolge (engl. String) zurück. Der Hex-Wert der Zahl 1000 ist 3E8. Schreibt man nun mit VBA diesen Hex Wert in eine Zelle, wird die Zeichenfolge automatisch in eine numerische Zahl im wissenschaftlichen Format umgewandelt, also in 3^8 (3 hoch 8). Der Trick, um die automatische Umwandlung der Zeichenfolge zu verhindern ist, dass man die Hex Zahl mit einem führenden Apostroph ‚ (= der einfache Apostroph, den man mit der Taste eingibt, auf der sich auch der Gartenzaun # befindet, rechts neben Ä) wie folgt ergänzt
      Range („A2“).Value = „‚“ & Hex(Range(„A1“)
      Achtung: Word Press wandelt die Anführungszeichen um, man muss sie in VBA durch die korrekten Anführungszeichen ersetzen, wenn man die Zeile in VBA kopiert.
      Dasselbe funktioniert überigens auch in einer Excel Zelle. Wenn in Excel der Inhalt einer Zelle mit einem Apostroph beginnt, dann erzwingt dies, dass Excel den Inhalt der Zelle als Text interpretiert.
      Habe ich mich verständlich ausgedrückt?
      Viele Grüße
      Stefan

  4. Alois November 1, 2022 Antworten

    Hallo Stefan

    Funktioniert Super man kann nur Dankbar sein so einen Fachmann zu kennen.
    Kompetenter und Schneller geht nicht. Das erfährt man sonst von keinem.

    Vielen vielen Dank
    Gruß Alois

  5. Alois Dezember 21, 2022 Antworten

    Hallo Stefan

    Du bist meine letzte Rettung.
    Ich habe Mit Application.Dialogs (???).show herumgespielt nun ist eine riesige Datei die ich habe auf einmal ganz anders aussehend.
    Die Excel Spaltenüberschriften (A B C D usw,) und die Zeilennummern (1 2 3 4 5 usw.) sind auf einmal ganz anders formatiert (größere Schrift und Fett formatiert, passt nichts mehr zusammen Spaltenbreite usw,
    Wie bringe ich das in den alten Zustand zurück.
    Genauer gesagt wie kann ich Spaltenüberschriften und Zeilennummern formatieren.
    Das mit den Dialogs ist ja manchmal ganz gut vielleicht kannst du da was einbauen den vielleicht gibt es noch mehr so Deppen wie mich.
    Vielen vielen Dank und schöne Feiertage
    Gruß Alois

    1. admin Dezember 22, 2022 Antworten

      Hallo Alois,
      ich tue mir etwas schwer mit der Antwort, da ich die Excel Datei nicht sehen kann. Die Beschreibung des Fehlers, dass die Excel Spaltenüberschriften (A B C D usw,) und die Zeilennummern (1 2 3 4 5 usw.) auf einmal ganz anders formatiert sind (größere Schrift und Fett formatiert), deutet darauf hin, dass unbeabsichtigt die Excel Zoom Funktion verwendet wurde, siehe Excel Reiter „Ansicht“, Gruppe „Zoom“. Dort kann man relativ einfach wieder ein Zoom von 100% einstellen. In vba kann man dafür folgenden Code verwenden:
      Sub test_zoom()
      Dim ws As Worksheet
      Set ws = Worksheets(„Tabelle1“)
      ws.Activate
      ActiveWindow.Zoom = 100
      Set ws = Nothing
      End Sub
      Es würde mich brennend interessieren, ob meine Antwort das Problem gelöst hat.
      Viele Grüße und frohe Weihnachten
      Stefan

  6. Alois aus Altmannstein (Bayern) Dezember 22, 2022 Antworten

    Hallo Stefan

    Habe nun herausgefunden was der Grund war.
    Ich wolte ein Arbeitsblatt erstellen mit einigen Schaltknöpfen was auch sehr gut klappte, nur habe ich leider auch die Funktion Application.Dialogs (xlDialogFont) eingebaut und dachte das ändert nur die Schrift im Blatt aber leider wirkt sich das auf die ganze Datei aus, alles andere klappt Super.
    Ist auch sehr praktisch mit den Schaltflächen zu formatieren.
    Vieleicht eine kleine Anregung um deine SUPER Seite noch weiter aus zu bauen.
    Option Explicit
    Sub Rahmenlinien()
    Tabelle21.Activate
    Application.Dialogs(xlDialogBorder).Show
    End Sub
    Sub Schriftart()
    Tabelle21.Activate
    Application.Dialogs(xlDialogFont).Show
    End Sub
    Sub Zellausrichtung()
    Tabelle21.Activate
    Application.Dialogs(xlDialogAlignment).Show
    End Sub

    Vielen Dank für deine Bemühungen
    War nicht schlimm das heraus zu finden (auch ein blindes Huhn findet mal ein Korn)

    Gruß und schönes Weihnachtsfest
    Gruß Alois

    1. admin Dezember 22, 2022 Antworten

      Hallo Alois,
      mit Deiner Information konnte ich das von Dir beschriebene Phänomen nachvollziehen: es liegt in der Tat an der Funktion Application.Dialogs(xlDialogFont).Show. Es freut mich sehr, dass Du die Ursache finden konntest.
      Ich werde die Seite überarbeiten aber das wird sicher einige Zeit dauern.
      Viele Grüße
      Stefan

  7. Alois aus Altmannstein (Bayern) Dezember 22, 2022 Antworten

    Hallo Stefan

    Habe den Fehler gefunden (Auch ein blindes Huhn findet mal ein Korn)

    Option Explicit
    Sub Rahmenlinien()
    Tabelle21.Activate
    Application.Dialogs(xlDialogBorder).Show
    End Sub
    Sub Schriftart()
    Tabelle21.Activate
    Application.Dialogs(xlDialogFont).Show
    End Sub
    Sub Zellausrichtung()
    Tabelle21.Activate
    Application.Dialogs(xlDialogAlignment).Show
    End Sub
    Habe die oben beschriebene Sub erstellt und dabei festgestellt das Application.Dialogs(xlDialogFont).Show die ganze Mappe und nicht nur den markierten Bereich formatiert.
    Application.Dialogs ist ja manchmal ganz gut vielleicht eine kleine Anregung für dich um deine SUPER Seite etwas zu erweitern, den verbessern geht ja nicht ist schon alles optimal.

    Gruß und schöne Weihnacht für dich und deine Familie
    Gruß Alois

    1. admin Januar 8, 2023 Antworten

      Hallo Alois,
      wie gewünscht habe ich Deine Anregung im Beitrag Dialogfenster umgesetzt. Statt „xlDialogFont“ habe ich „xlDialogFontProperties“ bzw. „xlDialogActiveCellFont“ verwendet. Damit lässt sich die Schrift in einem vorher ausgewählten Zellbereich formatieren. Ich dachte, dass das Deinem Anliegen entspricht. Leider ist Appplication.Dialogs nicht sehr gut dokumentiert. Es ist daher nicht einfach, Informationen zu finden.
      Viele Grüße
      Stefan

Schreibe einen Kommentar