VBA in Excel/ Funktionen

| Hoch zu „Inhaltsverzeichnis“ |

Arten der Funktionen

Bestandteil fast jeder – auch einfachsten – Programmierung sind Funktionen. Bei der Excel-/VBA-Programmierung hat man es mit 3 Gruppen von Funktionen zu tun:

• Excel-Funktionen
• VBA-Funktionen
• benutzerdefinierte Funktionen (UDF)

Einsatz von Excel-Funktionen

Funktionen erwarten in der Regel Übergabewerte, auf deren Grundlage sie ihre Berechnungen durchführen und geben die Berechnungsergebnisse zurück. Sie können grundsätzlich sowohl innerhalb von VBA-Programmen verwendet wie auch in Tabellenblättern eingesetzt werden, wobei beim Einsatz von benutzerdefinierten Funktionen in Tabellenblättern Beschränkungen zu beachten sind.

Eine Reihe von Funktionen gibt es sowohl in Excel als auch in VBA. Bei der Wahl des Einsatzes der einen oder anderen muss beachtet werden, dass gleichlautende Excel/VBA-Funktionen zu durchaus unterschiedlichen Ergebnissen führen können. Hier sei exemplarisch auf die Trim-Funktion hingewiesen, die in VBA Leerzeichen am Anfang und Ende einer Zeichenfolge, bei Excel zusätzlich die überzähligen innerhalb eines Strings entfernt.

Grundsätzlich gilt für alle Funktionen, ob eingebaute, über VBA einzutragende oder benutzerdefinierte, dass sie keine Formatierungen transportieren können. Über Funktionen, die im Tabellenblatt aufgerufen werden, können Sie beispielsweise keine Hintergrundformate oder Schriftattribute festlegen, dazu benötigen Sie eine Sub. Jedoch können Funktionen, die über den VBA Editor ausgeführt werden, solche Änderungen vornehmen.

Verwendung innerhalb von VBA-Prozeduren

Excel-Funktionen müssen in VBA als solche kenntlich gemacht werden, indem man ihnen entweder ein Application oder ein Worksheetfunction voranstellt. Soll die Arbeitsmappe abwärtskompatibel angelegt werden, ist Application zu verwenden, da die Vorgängerversionen (vor Excel 2000) kein Worksheetfunction kennen. Allgemein ist die Verwendung von Worksheetfunction zu empfehlen, da bei deren Einsatz zum einen die Elemente (Funktionen) automatisch aufgelistet werden und zum anderen als weitere Unterstützung die jeweilige Argumentenliste angezeigt wird.

Von diesem Prinzip sollte abgewichen werden, wenn beim Rückgabewert der Funktion Fehlerwerte zu erwarten sind. Worksheetfunction liefert statt des Fehlerwertes den beliebten, zum Programmabbruch führenden Laufzeitfehler 1004.

So funktioniert es nicht:

Function IsExistsA(strTxt As String) As Boolean     
   Dim var As Variant   
   var = WorksheetFunction.Match(strTxt, Columns(1), 0) 
   If Not IsError(var) Then IsExistsA = True      
End Function

Die Notwendigkeit des Abfangens des Fehlers kann man sich ersparen, indem man statt Worksheetfunction jetzt Application verwendet:

Function IsExistsB(strTxt As String) As Boolean     
   Dim var As Variant   
   var = Application.Match(strTxt, Columns(1), 0)
   If Not IsError(var) Then IsExistsB = True      
End Function

Verwendung im Arbeitsblatt

Sie haben die Möglichkeit, Excel-Funktionen oder deren Ergebnisse in einem Arbeitsblatt eintragen zu lassen. Sinnvollerweise werden die Funktionen (Formeln) dann eingetragen, wenn spätere Wertekorrekturen im zu berechnenden Bereich zu einer Neuberechnung in der Ergebniszelle führen sollen.

Hinweis: Wenn Range-Objekte (Range, Cells, Rows, Columns, Areas) nicht explizit auf ein Worksheet-Objekt angewendet werden (so z.B. als Worksheet("Tabelle 1").Range), bezieht Excel auf das aktive, sichtbare Arbeitsblatt (ActiveSheet), VBA liest also die Anweisung Cells(1,1) bzw. Range("A1") immer als ActiveSheet.Cells(1,1) bzw. ActiveSheet.Range("A1").

Nur mit einem Workbook-Objekt als Präfix kann auch auf inaktive, ausgeblendeten oder versteckten Tabellenblättern gearbeitet werden.

Der Eintrag eines absoluten Wertes (Summe des Wertebereiches in Spalte A):

Sub SumValue() 
   Dim intRow As Integer    
   intRow = Cells(Rows.Count, 1).End(xlUp).Row 
   Cells(intRow + 1, 1).Value = WorksheetFunction.Sum(Range("A1:A" & intRow))  
End Sub

Der Eintrag einer Formel (Summe des Wertebereiches in Spalte A):

Sub SumFormula()  
   Dim intRow As Integer    
   intRow = Cells(Rows.Count, 1).End(xlUp).Row 
   Cells(intRow + 1, 1).Formula = "=Sum(A1:A" & intRow & ")" 
End Sub

Für den Formeleintrag bieten sich folgende Möglichkeiten:

Formula

Die Formel wird in englischer Schreibweise eingetragen und umfasst einen absoluten Bereich:

Sub AbsoluteFormel()  
   Range("B1").Formula = "=AVERAGE(A1:A20)"  
End Sub

FormulaR1C1

Die Formel wird in englischer Schreibweise eingetragen und umfasst einen relativen Bereich:

Sub RelativeFormelA()  
   Range("B2").Select  
   Range("B2").FormulaR1C1 = "=AVERAGE(R[-1]C[-1]:R[18]C[-1])"  
End Sub

Sie kann auch einen teils absoluten und teils relativen Bereich umfassen:

Sub RelativeFormelB()  
   Range("C2").Select  
   Range("C2").FormulaR1C1 = "=AVERAGE(R1C[-1]:R20C[-1])"  
End Sub

Lokale Formeln

Bei FormulaLocal und FormulaR1C1Local wird die Formel wird entsprechend der Spracheinstellung des Betriebssystems eingetragen, in den folgenden Beispielen ist das die deutsche Sprache. Wird eine solche Arbeitsmappe auf einem Excel mit französischer Spracheinstellung ausgeführt, kann es zu Fehlern und unerwarteten Ergebnissen kommen.

Berücksichtigen Sie auch, dass sich die Präfixe für Zeilen/Spalten sowie den Austausch der eckigen gegen die runden Klammern der Sprachauswahl angepasst haben: R → Z, C → S, [] → ().

FormulaLocal

Hier umfasst die Formel einen absoluten Bereich:

Sub AbsoluteFormelLocal()  
   Range("B1").FormulaLocal = "=MITTELWERT(A1:A20)"  
End Sub

FormulaR1C1Local

Die Formel umfasst einen relativen Bereich:

Sub RelativeFormelALocal()  
   Range("B2").Select  
   Range("B2").FormulaR1C1Local = "=MITTELWERT(Z(-1)S(-1):Z(18)S(-1))"  
End Sub

Sie kann auch einen teils absoluten und teils relativen Bereich umfassen:

Sub RelativeFormelBLocal()  
   Range("C2").Select  
   Range("C2").FormulaR1C1Local = "=MITTELWERT(Z1S(-1):Z20S(-1))"  
End Sub

FormulaArray

Array-Formeln werden ohne die ihnen eigenen geschweiften Klammern eingegeben. Eine FormulaLocal-Entsprechung gibt es hier nicht.

Sub ArrayFormel()  
   Range("B3").FormulaArray = _  
      "=SUM((D16:D19=""Hosen"")*(E16:E19=""rot"")*F16:F19)"
End Sub

Dem FormulaArray-Befehl kommt einige Bedeutung zu, da Array-Berechnungen in VBA ihre Zeit benötigen und es sich in vielen Fällen empfiehlt, temporäre ArrayFormeln in Zellen eintragen zu lassen, um ihre Werte auszulesen.

Einsatz von VBA-Funktionen

Verwendung innerhalb von VBA-Prozeduren

Beim Einsatz von VBA-Funktionen ist bei geforderter Abwärtskompatibilität Vorsicht geboten. Während die Anzahl der Excel-Formeln seit Jahren im Wesentlichen konstant geblieben ist, trifft dies für VBA-Funktionen nicht zu. Im Interesse eines möglichst weitverbreiteten VBA-Einsatzes wird die Palette der VBA-Funktionen gelegentlich erweitert.

Der Aufruf einer VBA-Funktion ist einfachst; hier wird das aktuelle Verzeichnis geliefert:

Sub PathAct() 
   MsgBox CurDir
End Sub

Verlangt die Funktion Parameter, erfolgt der Aufruf mit der Parameterübergabe:

Sub TypeAct() 
   MsgBox TypeName(ActiveSheet)
End Sub

Verwendung im Arbeitsblatt

Ergebnisse von VBA-Funktionen können über den Aufruf in benutzerdefinierten Funktionen auch direkt ins Tabellenblatt eingetragen werden:

Function UmgebungsVariable() 
   UmgebungsVariable = Environ("Path")
End Function

Einsatz von benutzerdefinierten Funktionen (UDF)

Verwendung innerhalb von VBA-Prozeduren

Benutzerdefinierte Funktionen werden in aller Regel dann eingesetzt, wenn mehrfach wiederkehrende Berechnungen durchgeführt werden sollen. Wenn es denn auch nicht verlangt wird, sollten sowohl die Funktionen selbst, deren Parameter sowie die in den Funktionen verwendeten Variablen sauber dimensioniert werden.

Im folgenden Beispiel wird aus einer Prozedur heraus mehrfach eine Funktion zum Gesperrtschreiben der Ortsnamen aufgerufen:

Sub PLZundOrt()  
   Dim intRow As Integer    
   intRow = 1
   Do Until IsEmpty(Cells(intRow, 1))   
      Cells(intRow, 3) = Cells(intRow, 1) & " " & _
         Gesperrt(Cells(intRow, 2))
      intRow = intRow + 1
   Loop 
End Sub  

Function Gesperrt(strOrt As String) As String    
   Dim intCounter As Integer    
   Do Until Len(strOrt) > 10   
      For intCounter = Len(strOrt) - 1 To 1 Step -1   
         If Mid(strOrt, intCounter, 1) <> " " Then   
            strOrt = Left(strOrt, intCounter) & " " & _ 
               Right(strOrt, Len(strOrt) - intCounter) 
         End If  
      Next intCounter 
   Loop 
   Gesperrt = strOrt 
End Function

Hier wird eine benutzerdefinierte Funktion zur Umrechnung von Uhrzeiten in Industriezeiten unter Berücksichtigung einer Pausenzeit eingesetzt:

Sub DateToNumber()  
   Dim intRow As Integer    
   intRow = 10
   Do Until IsEmpty(Cells(intRow, 1))   
      Cells(intRow, 2) = IndustrieZeit(Cells(intRow, 1)) 
      intRow = intRow + 1
   Loop 
End Sub  

Function IndustrieZeit(dat As Date) As Double      
   Dim dblValue As Double    
   dblValue = dat * 24
   IndustrieZeit = dblValue - 0.25 
End Function

Verwendung im Arbeitsblatt

Dimensionieren Sie die Funktions-Parameter entsprechend dem übergebenen Wert, nicht nach dem Range-Objekt, aus dem der Wert übergeben wird. Dies gilt unabhängig davon, ob die Range-Dimensionierung im aktuellen Fall ebenfalls richtige Ergebnisse zuläßt. Vorstehendes gilt selbstverständlich nicht für zu übergebende Matrizen (Arrays). Im Falle einer evtl. notwendigen Abwärtskompatibilität ist zu beachten, dass die Vorgängerversionen von Excel 8.0 (97) das Range-Objekt in der Parameter-Dimensionierung nicht akzeptieren; verwenden Sie hier das Object-Objekt.

Selbstverständlich lässt sich über Funktionen keine Cursor auf Reisen schicken, jegliches Selektieren entfällt. In Excel 5.0 und 7.0 ist es zudem auch nicht möglich, simulierte Richtungstastenbewegungen einzusetzen. Der nachfolgende Code führt dort zu einem Fehler:

Function GetLastCellValueA(intCol As Integer) As Double       
   Dim intRow As Integer    
   intRow = Cells(Rows.Count, intCol).End(xlUp).Row 
   GetLastCellValueA = Cells(intRow, intCol).Value 
End Function

In diesen Versionen müssen die Zellen abgeprüft werden, wobei man von UsedRange als Ausgangsposition ausgehen kann:

Function GetLastCellValueB(intCol As Integer) As Double       
   Dim intRow As Integer, intRowL As Integer    
   intRowL = ActiveSheet.UsedRange.Rows.Count 
   For intRow = intRowL To 1 Step -1  
      If Not IsEmpty(Cells(intRow, intCol)) Then Exit For      
   Next intRow 
   GetLastCellValueB = Cells(intRow, intCol).Value 
End Function

Der Versuch, einen gesuchten und gefundenen Zellwert an eine Funktion zu übergeben, führt bei Excel 8.0 und höher zu einem falschen Ergebnis (Leerstring) und bei den Vorgängerversionen zu einem Fehler:

Function GetFindCellValue(intCol As Integer, strTxt As String) As String      
   Dim rngFind As Range   
   Set rngFind = Columns(intCol).Find(strTxt, lookat:=xlWhole, LookIn:=xlValues)  
   If Not rngFind Is Nothing Then GetFindCellValue = rngFind.Value     
End Function

Beachten Sie bitte, dass das in diesem Abschnitt geschriebene sich ausschließlich auf benutzerdefinierte Funktionen bezieht, die in ein Tabellenblatt eingetragen werden.

Unter Umständen muss die Adresse der aufrufenden Zelle den Ausgangspunkt für die in der benutzerdefinierten Funktion ablaufenden Berechnungen bilden. Nur beim Eingabezeitpunkt richtige Ergebnisse bringt hier die Festlegung mit ActiveCell, denn bei irgendeiner Eingabe in eine andere Zelle ist dies die aktive Zelle.

Falsche Verankerung:

Function MyValueA(intOffset As Integer) As Variant     
   Application.Volatile
   MyValueA = ActiveCell.Offset(0, intOffset).Value
End Function

Richtige Verankerung:

Function MyValueB(intOffset As Integer) As Variant     
   Application.Volatile
   MyValueB = Application.Caller.Offset(0, intOffset).Value 
End Function

Die korrekte Zuweisung erfolgt über Application.Caller.

Benutzerdefinierte Funktionen berechnen sich auch bei eingeschaltete automatischer Berechnung nicht von selbst. Wünscht man eine Berechnung bei jeder Zelleingabe, ist den Funktionen ein Application.Volatile voranzustellen. Mit dieser Anweisung sollte vorsichtig umgegangen werden, denn sie kann Berechnungsabläufe extrem verzögern. In Arbeitsmappen, mit denen ständig abrufbare Funktionen bereitgestellt werden - bspw. in der Personl.xls - ist sie konsequent zu meiden.

Übergabe von Bereichen

In benutzerdefinierten Funktionen können -neben Werten- auch ein oder mehrere Zellbereiche übergeben werden. So wie man z.B. der eingebauten Funktion =SUMME(D1:D33) mit D1:D33 einen Bereich übergibt, so kann auch einer Benutzerdefinierten Funktion ein Bereich übergeben werden.

Der Variable für einen Bereich (in den Beispielen wird die Variable oft Bereich genannt) kann nur als Typ Range festgelegt werden, es gibt zwar die Auflistungen Cells, Rows, Columns und Areas, aber die listen keine eigenen Objekte auf, sondern immer nur ein Range-Objekt.

Das folgende Beispiel zeigt eine Funktion, die einen Bereich als Argument entgegen nimmt und die Beträge des angegebenen Bereichs aufsummiert:

Public Function SummeBetrag(Bereich As Excel.Range) As Double
    Dim Zelle As Excel.Range
    For Each Zelle In Bereich.Cells
        ' Enthält die Zelle eine Zahl?
        If IsNumeric(Zelle.Value) Then
            ' Nur bearbeiten, falls Zahl:
            SummeBetrag = SummeBetrag + Abs(Zelle.Value)
        End If
    Next Zelle
End Function

Die For Each Schleife geht dabei den markierten Bereich von links nach rechts und dann von oben nach unten durch. Wäre der Bereich A1:B2 markiert worden, würde die Summe in der Reihenfolge A1 + B1 + A2 + B2 berechnet.

Manchmal möchte man einen Bereich spaltenweise durchlaufen. In diesem Beispiel bringt dies keinen Vorteil, aber man kann dazu die Spalteneigenschaft des Range-Objekts nutzen:

Public Function SummeBetrag(Bereich As Excel.Range) As Double
    Dim Zelle   As Excel.Range
    Dim Spalte  As Excel.Range
    
    ' Spalten von 1 bis zur letzten Spalte durchlaufen:
    For Each Spalte In Bereich.Columns
        ' Oberste bis zur untersten Zelle durchlaufen:
        For Each Zelle In Spalte.Cells
            ' Enthält die Zelle eine Zahl?
            If IsNumeric(Zelle.Value) Then
                ' Betrag addieren:
                SummeBetrag = SummeBetrag + Abs(Zelle.Value)
            End If
        Next Zelle
    Next Spalte
End Function

Die verschachtelten For-Each Schleifen gehen dabei den markierten Bereich von oben nach unten und dann von rechts nach links durch. Wäre der Bereich A1:B2 markiert worden, würde die Summe in der Reihenfolge A1 + A2 + B1 + B2 berechnet.

Auch benutzerdefinierte Funktionen sollten fehlerhafte Bereichsauswahlen erkennen und darauf reagieren. Die drei folgenden Beispiele zeigen, wie man Bereiche überprüft:

Enthält der Bereich mehr als nur eine Zeile?

Public Function NurEineZeile(Bereich As Excel.Range) As Boolean
    ' Enthält der Bereich nur genau eine Zeile?
    ' Das Ergebnis wird als Rückgabewert der Funktion gespeichert
    NurEineZeile = (Bereich.Rows.Count = 1)

    If Not NurEineZeile Then
        ' Meldung an den Benutzer, falls mehr als eine Zeile markiert wurde
        MsgBox "Nur eine Zeile erlaubt", vbExclamation
    End If
End Function

Enthält der Bereich mehr als eine Spalte?

Public Function NurEineSpalte(Bereich As Excel.Range) As Boolean
    NurEineSpalte = (Bereich.Columns.Count = 1)
    If Not NurEineSpalte Then
        MsgBox "Nur eine Spalte erlaubt"
    End If
End Function

Ist der Bereich quadratisch?

Public Function NurQuadratischerBereich(Bereich As Excel.Range) As Boolean
    NurQuadratischerBereich = (Bereich.Rows.Count = Bereich.Columns.Count)
    If Not NurQuadratischerBereich Then
        MsgBox "Quadratischer Bereich erwartet"
    End If
End Function

Wenn eine benutzerdefinierte Funktion zwei Bereiche als Argumente erwartet, kann es erforderlich sein, dass sich diese Bereiche nicht überschneiden. Mit der Funktion Intersect wird die Schnittmenge aus beiden Bereichen bestimmt. Falls sich die Bereiche überschneiden, schreibt die Funktion den Fehler #BEZUG ins Arbeitsblatt, sonst die Anzahl der Zellen beider Bereiche:

Public Function GetrennteBereiche(Bereich1 As Excel.Range, _
                                  Bereich2 As Excel.Range) As Variant
    If Intersect(Bereich1, Bereich2) Is Nothing Then
        GetrennteBereiche = Bereich1.Cells.Count + Bereich2.Cells.Count
    Else
        GetrennteBereiche = CVErr(xlErrRef)
    End If
End Function

Damit die vorgesehene Fehlermeldung in der Zelle angezeigt werden kann, muss der Datentyp für den Rückgabewert der Funktion Variant sein, denn nur Der Datentyp Variant kann auch den speziellen Rückgabewert "Fehler" speichern.

Das folgende Beispiel erwartet je eine Spalte mit X- und zugehörigen Y-Werten. Die folgende Funktion prüft, ob die Anzahl der Zeilen gleich ist:

Public Function GleicheZeilenZahl(BereichX As Range, BereichY As Range) As Boolean
    On Error Resume Next
    GleicheZeilenZahl = (BereichX.Rows.Count = BereichY.Rows.Count)
End Function

Enthält eine der Variablen keinen Bereich, sondern den Wert Nothing, führt der fehler dazu, dass Zeile 2 nicht ausgeführt wird. Da der Default-Wert von GleicheZeilenZahl gleich False ist, ist auch im Fehlerfall der Rückgabewert korrekt.

Wenn der Anwender selbst Bereiche auswählen soll, kann der auch viel zu große Bereiche auswählen. Markiert der Anwender beispielsweise die Spalten A:C, obwohl nur der Bereich A1:C3 Daten enthält, dann bearbeitet die oben gezeigte Funktion SummeBetrag() in einer XLSX-Datei 3,2 Milliarden (3×2³⁰ Zellen, nämlich von A1:C10737741824. Um das zu verhindern, vergleicht man den ausgewählten Bereich mit dem benutzten Bereich (UsedRange) und bestimmt die Schnittmenge:

Public Sub BereichKorrigieren(Bereich As Excel.Range)
    Set Bereich = Intersect(Bereich, Bereich.Parent.UsedRange)
End Sub

Ist der Bereich durch die Objektvariable MeinBereich festgelegt, so ruft man die Funktion so auf:

BereichKorrigieren MeinBereich

Nur wenn MeinBereich mehr Zeilen und/oder mehr Spalten als der benutzte Bereich enthält, reduziert ihn die Funktion BereichKorrigieren. Ist MeinBereich dagegen kleiner gewählt, behält er seine Größe. Die Funktion verändert beim Verkleinern auch die Objektvariable MeinBereich selbst, d.h. es ist danach nicht mehr möglich, die Größe des gesamten ursprünglichen Bereichs festzustellen.

| Hoch zu „Inhaltsverzeichnis“ |