VBA in Excel - Grundlagen: Funktionen

Aus Wikibooks

Zurück zu "Prozeduren" | Hoch zu "Inhaltsverzeichnis" | Vor zu "Prozeduraufrufe"

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 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 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.

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 umfasst 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

[Bearbeiten] 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.

[Bearbeiten] Ü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 einzige Unterschied hier ist, dass ein Bereich von zunächst unbekannter Größe ausgewertet werden muss.

Das folgende Beispiel zeigt eine Funktion, die einen Bereich als Argument entgegennimmt:

Public Function SummeX(Bereich As Excel.Range) As Double
    Dim Zelle As Excel.Range
    For Each Zelle In Bereich.Cells
        SummeX = SummeX + Zelle.Value
    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. Für die Summe macht dies keinen Sinn, aber aber man kann dazu die Spalteneigenschaft des Range-Objekts nutzen:

Public Function SummeX(Bereich As Excel.Range) As Double
    Dim Zelle   As Excel.Range
    Dim Spalte  As Excel.Range
 
    For Each Spalte In Bereich.Columns
        For Each Zelle In Spalte.Cells
            SummeX = SummeX + Zelle.Value
        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 erkennt, ob der Bereich

1. nur eine Zeile enthält
2. nur eine Spalte enthält
3. Quadratisch ist, d.h. gleiche Zeilen- und Spaltenzahl besitzt

Public Function NurEineZeile(Bereich As Excel.Range) As Boolean
    NurEineZeile = (Bereich.Columns.Count > 1)
    If Not NurEineZeile Then
        MsgBox "Nur eine Zeile erlaubt"
    End If
End Function
 
Public Function NurEineSpalte(Bereich As Excel.Range) As Boolean
    NurEineSpalte = (Bereich.Rows.Count > 1)
    If Not NurEineSpalte Then
        MsgBox "Nur eine Spalte erlaubt"
    End If
End Function
 
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:

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

Zurück zu "Prozeduren" | Hoch zu "Inhaltsverzeichnis" | Vor zu "Prozeduraufrufe"