Bestandteil fast jeder - auch einfachsten - Programmierung sind Funktionen. Bei der Excel/VBA-Programmierung hat man es mit 3 Gruppen von Funktionen zu tun:
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.
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
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:
Sub AbsoluteFormel()
Range("B1").Formula = "=AVERAGE(A1:A20)"
End Sub
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
Sub AbsoluteFormelLocal()
Range("B1").FormulaLocal = "=MITTELWERT(A1:A20)"
End Sub
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.
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.
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
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
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
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.