HERBERS Excel-Forum - VBA-Basics

Thema: Funktionen

Inhaltsverzeichnis
  • 1 Arten der Funktionen
  • 2 Einsatz von Excel-Funktionen
  • 3 Einsatz von VBA-Funktionen
  • 4 Einsatz von benutzerdefinierten Funktionen (UDF)
  • 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

    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 wie 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 können Sie beispielsweise keine Hintergrundformate oder Schriftattribute festlegen, dazu benötigen Sie eine Sub.

    Verwendung innerhalb von VBA-Prozeduren

    Excel-Funktionen müssen in VBA als solche kenntlich gemacht werden, entweder, indem man ihnen ein Application oder ein Worksheetfunction voranstellt. Soll die Arbeitsmappe abwärtskompatibel angelegt werden, ist Application zu verwenden, da die Vorgängerversionen 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 funktionierts 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.

    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 umfaßt einen absoluten Bereich:
      
      
      Sub AbsoluteFormel()
         Range("B1").Formula = "=AVERAGE(A1:A20)"
      End Sub
      
    • FormulaR1C1

      Die Formel wird in englischer Schreibweise eingetragen und umfaßt 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
      
    • FormulaLocal Die Formel wird in deutscher Schreibweise eingetragen und umfaßt einen absoluten Bereich:
      
      Sub AbsoluteFormelLocal()
         Range("B1").FormulaLocal = "=MITTELWERT(A1:A20)"
      End Sub
      
    • FormulaR1C1Local Die Formel wird in deutscher Schreibweise eingetragen und umfaßt 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
      

      Beachten Sie neben der deutschen Schreibweise auch die veränderten Begriffe für Zeilen/Spalten - R(Z) und C(S) - sowie den Austausch der eckigen gegen die runden Klammern.

      Grundsätzlich sollte mit Formula gearbeitet und FormulaLocal gemieden werden.

    • 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 ständig 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, daß 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.