Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Inhalt dieser Seite

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.