Benutzerdefinierte Funktion berechnet möglicherweise keinen erwarteten Wert in Excel

• Gilt für:

  Microsoft Excel

Symptome

Wenn Sie Ihr Arbeitsblatt berechnen, scheinen einige Zellen möglicherweise den falschen Wert berechnet zu haben.

Ursache

Dieses Problem tritt auf, wenn folgende Bedingungen vorliegen:

• Eine Zelle im Arbeitsblatt enthält eine benutzerdefinierte Funktion.

• Die benutzerdefinierte Funktion enthält mindestens ein Argument, das auf einen Zellbereich im Arbeitsblatt verweist.

• Das Ergebnis der benutzerdefinierten Funktion hängt von mehr Zellen ab, als direkt referenziert werden.

Dieses Verhalten ist von Microsoft Excel beabsichtigt. Wenn Excel die Zelle berechnet, die die benutzerdefinierte Funktion enthält, werden alle Zellbereiche neu berechnet, die als Argumente an Ihre benutzerdefinierte Funktion übergeben werden. Wenn das Ergebnis Ihrer Funktion von Zellen abhängt, auf die nicht explizit von der Funktion verwiesen wird, werden diese Zellen möglicherweise nicht neu berechnet.

Problemumgehung

Verwenden Sie eine der folgenden Methoden, um dieses Problem zu umgehen.

Methode 1: Ändern Sie Ihre Funktion, sodass alle relevanten Zellbereiche übergeben werden.

Ändern Sie Ihre Funktion so, dass alle Zellen, die zum Berechnen des Ergebnisses der Funktion erforderlich sind, als Argumente akzeptiert werden.

Methode 2: Benutzerdefinierte Funktion flüchtig machen

Die Verwendung der hier aufgeführten Informationen, Makro- oder Programmcodes geschieht auf Ihre eigene Verantwortung. Microsoft stellt Ihnen diese Informationen sowie Makro- und Programmlistings ohne Gewähr auf Richtigkeit, Vollständigkeit und/oder Funktionsfähigkeit sowie ohne Anspruch auf Support zur Verfügung. Die zur Verfügung gestellten Makro- und Programmierungsbeispiele sollen lediglich exemplarisch die Funktionsweise des Beispiels aufzeigen. Microsoft-Supporttechniker können ihnen helfen, die Funktionalität eines bestimmten Verfahrens zu erklären, aber sie werden diese Beispiele nicht ändern, um zusätzliche Funktionen bereitzustellen oder Verfahren zu erstellen, die Ihren spezifischen Anforderungen entsprechen. Wenn Sie Ihre benutzerdefinierte Funktion veränderlich machen, tritt dieses Problem nicht auf. Fügen Sie der Funktion die folgende Codezeile hinzu, um ihre benutzerdefinierte Funktion flüchtig zu machen.

VB

Application.Volatile

Hinweis

Wenn Sie Ihre benutzerdefinierte Funktion flüchtig machen, wird sie jedes Mal neu berechnet, wenn Sie eine Änderung an einem Wert vornehmen oder eine geöffnete Arbeitsmappe neu berechnen. Dies kann sich auf die Leistung Ihres Arbeitsblattmodells auswirken.

Methode 3: Erzwingen der Neuberechnung aller geöffneten Arbeitsmappen durch Excel

Drücken Sie STRG+ALT+F9, um die Werte in allen geöffneten Arbeitsmappen neu zu berechnen. Drücken Sie in Microsoft Office Excel 2007 STRG+ALT+UMSCHALT+F9, um die Werte in allen geöffneten Arbeitsmappen neu zu berechnen.

Weitere Informationen

Beispiel für das Problem

Führen Sie die folgenden Schritte aus, um dieses Problem zu veranschaulichen:

1. Schließen und speichern Sie alle geöffneten Arbeitsmappen, und öffnen Sie dann eine neue Arbeitsmappe.

2. Starten Sie die Visual Basic-Editor (drücken Sie ALT+F11).

3. Klicken Sie im Menü Einfügen auf Modul.

4. Geben Sie den folgenden Code in das Modulblatt ein:

   VB

   ' This function counts the number of blank cells by starting from the cell 
   ' referred to by the rngStartCell argument and moving up the column.
   
   Function FindTextUp(rngStartCell As Range) As Single
       Dim iIndex As Integer
   
   For iIndex = 0 To 100
           If rngStartCell.Value <> "" Then
               FindTextUp = iIndex
               Exit Function
           Else
               Set rngStartCell = rngStartCell.Offset(-1, 0)
           End If
       Next iIndex
   End Function
   

5. Drücken Sie ALT+F11, um zu Excel zurückzukehren.

6. Geben Sie test in Zelle A2 ein, und drücken Sie dann die EINGABETASTE.

7. Geben Sie die folgende Formel in Zelle A10 ein, und drücken Sie dann die EINGABETASTE:

   excel

   =FindTextUp(A9)
   

   Die Formel gibt den Wert 7 zurück.

8. Geben Sie In Zelle A5 einen anderen Test ein, und drücken Sie dann die EINGABETASTE.

Die Formel gibt weiterhin einen Wert von 7 zurück, wenn ein Wert von 4 erwartet wird. In diesem Beispiel bezieht sich die FindTextUp-Funktion explizit auf Zelle A9. Die Funktion kann jedoch abhängig von den in das Arbeitsblatt eingegebenen Daten von den Zellen A1:A8 abhängen.

Wenn Sie Methode 1 im Abschnitt "Problemumgehung" dieses Artikels implementieren, berechnet die Funktion das erwartete Ergebnis. Die folgende Codezeile veranschaulicht, wie die Funktion in diesem Beispiel so geändert wird, dass das erwartete Ergebnis berechnet wird.

VB

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

Wenn Sie nun den Funktionsaufruf in Schritt 7 durch den folgenden Funktionsaufruf ersetzen, gibt die Funktion immer das erwartete Ergebnis zurück.

excel

=FindTextUp(A9,A1:A8)