Excel: Benutzerdefinierte VBA-Tabellenfunktion (UDF) immer neu berechnen

Benutzerdefinierte VBA-Tabellenfunktionen (User Defined Functions) sind ein mächtiges Instrument, um Excel spezifische Geschäftslogik beizubringen. Zudem verbessern sie entscheidend die Lesbarkeit von Excel-Formeln. Wer hat nicht schon über ein mehrfach verschachteltes Funktionsungetüm gegrübelt, um herauszubekommen, was da eigentlich gerechnet wird?

Manchmal braucht man benutzerdefinierte Funktionen, die immer neu berechnet werden, wenn sich irgendetwas in der Excel-Mappe ändert. Solche Funktionen nennt man volatil. Ein Beispiel für eine volatile Funktion ist übrigens die integrierte Funktion JETZT().  

Es ist sehr einfach, eine benutzerdefinierte Funktion volatil zu machen.

Es folgen zwei Beispiele für benutzerdefinierte Funktionen, zuerst eine nicht volatile und dann eine volatile.

Die Funktion Get_A_Dreieck() bestimmt den Flächeninhalt eines gleichschenklichen Dreiecks. Der Parameter SchenkelLaenge wird über einen Zellenbezug übergeben.

Beispiel 1

Wenn sich die Zelle B1 ändert, wird C1 automatisch berrechnet. So weit so gut. Was ist nun aber, wenn der Parameter der Funktion nicht über den Wert einer Zelle geliefert wird  sondern zum Beispiel als fester Wert? Excel berechnet diese nicht volatile Funktion nicht automatisch neu.

Um eine Funktion volatil zu machen, muss mindestens einmal die folgende Zeile in der Excel-Funktion ausgeführt werden.

Application.volatile True

Zur Erinnerung: Eine volatile Funktion wird bei sämtlichen Berechnungen in allen Zellen eines Arbeitsblattes neu berechnet. Eine nicht volatile Funktion wird nur neu berechnet, wenn sich die Eingangsvariable ändert.

Die Anweisung Application.volatile ist nur wirksam, wenn sie sich innerhalb einer benutzerdefinierten Funktion befindet, die zum Berechnen einer Arbeitsblattzelle verwendet wird.

Nun ein Beispiel für eine volatile Funktion:

Mit Hilfe der Funktion Get_Anzahl_Zellen_Benutzter_Bereich() wird die Anzahl der Zellen auf einem Tabellenblatt bestimmt, die im benutzten Bereich liegen. Der benutzte Bereich ist das Rechteck von der obersten linken benutzten bis zur untersten rechten benutzten Zelle. Dabei gilt eine Zelle auch als benutzt, wenn Sie beispielsweise nur eine Hintergrundfärbung oder ein Zahlenformat hat.

Beispiel 2

Wann immer es nun eine Änderung in der Arbeitsmappe gibt, wird die Zelle A1 auf dem Tabellenblatt "Tabelle1" neu berechnet. Die Funktion überwacht sozusagen ein anderes Tabellenblatt.
Vielleicht findet Ihr ja noch hübschere Beispiele für volatile Funktionen.