Jeffreys Unternehmen verfügt über eine Reihe von Berichten, die eine umfangreiche Anzahl von CSE-Array-Formeln (Strg + Umschalt + Eingabetaste) verwenden. Wenn jemand vergisst, beim Drücken der Eingabetaste Strg und Umschalt gedrückt zu halten, entsprechen die resultierenden Formeln nicht der richtigen Antwort. Das Überprüfen jeder Zelle und das Suchen nach den Klammern \ {} ist sowohl mühsam als auch zeitaufwändig. Jeffrey fragt sich, ob es eine schnelle Möglichkeit gibt, die „fehlenden Klammern“ zu finden oder ein Fehlerflag auszulösen, wenn Strg + Umschalt + Eingabetaste nicht gedrückt wird, wenn dies der Fall sein sollte.

In Excel gibt es keine intrinsische oder formelhafte Methode, um dies zu tun. Dies bedeutet, dass Sie sich einer Lösung zuwenden müssen, die auf einem Makro basiert.

Glücklicherweise bietet VBA verschiedene Möglichkeiten, um dieses Problem anzugehen. Ein Ansatz besteht darin, einfach eine Formel zu verwenden, um sicherzustellen, dass jede Formel innerhalb einer Auswahl tatsächlich eine Arrayformel ist.

Sub MakeCSE1()

Dim rCell As Range

For Each rCell In Selection         rCell.FormulaArray = rCell.Formula     Next rCell End Sub

Dieses Makro setzt voraus, dass Sie die Zellen auswählen, die „konvertiert“ werden sollen, bevor Sie das Makro tatsächlich ausführen. Wenn Sie möchten, können Sie einen Zellbereich definieren (dem Bereich einen Namen geben) und dann ein ähnliches Makro ausführen, das immer für diesen Bereich arbeitet.

Sub MakeCSE2()

Dim rng As Range     Dim rCell As Range     Dim rArea As Range

Set rng = Range("CSERange")

For Each rArea In rng.Areas         For Each rCell In rArea.Cells             If rCell.HasArray = False Then                 rCell.FormulaArray = rCell.Formula             End If         Next rCell     Next rArea End Sub

Dieses Makro sucht nach einem Bereich mit dem Namen CSERange und überprüft dann jede Zelle im Bereich. Wenn es keine Array-Formel enthält, wird die Formel in eine Array-Formel konvertiert.

Beachten Sie die Verwendung der HasArray-Eigenschaft, um zu überprüfen, ob eine Zelle eine Arrayformel enthält. Diese Eigenschaft kann auf andere Weise hilfreich sein. Sie können beispielsweise eine einfache benutzerdefinierte Funktion erstellen, z. B.:

Function NoCellArray1(rng As Range) As Boolean     NoCellArray1 = Not rng.HasArray End Function

Diese Funktion gibt True zurück, wenn die Zelle, auf die verwiesen wird, keine Arrayformel enthält. Wenn es eine enthält, wird False zurückgegeben. Sie können diese Funktion dann als Grundlage für ein bedingtes Format verwenden. Sie müssen lediglich ein Format erstellen, das es folgendermaßen verwendet:

=NoCellArray1(A5)

Da NoCellArray True zurückgibt, wenn die Zelle keine Arrayformel enthält, kann Ihr bedingtes Format die Farbe der Zelle auf Rot setzen oder ein anderes sichtbares Zeichen dafür setzen, dass die Zelle nicht über die erforderliche Arrayformel verfügt. Sie können auch die folgende Funktion verwenden, um dieselbe Aufgabe auszuführen:

Function NoCellArray2(rng As Range) As Boolean     NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)

End Function

Ein ganz anderer Ansatz besteht darin, Ihren Formeln etwas hinzuzufügen, mit dem sie leicht als Array-Formeln erkannt werden können. Sie können beispielsweise am Ende jeder Ihrer Array-Formeln Folgendes hinzufügen:

+N("{")

Dies wirkt sich in keiner Weise auf die Berechnung aus, kann jedoch leicht überprüft werden, um festzustellen, ob sie vorhanden ist. Die Überprüfung kann von einem Ereignishandler durchgeführt werden, z. B. wie folgt:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Beachten Sie, dass der Handler prüft, ob die Formel mit („\ {„) endet, und in diesem Fall erzwingt, dass die Formel als Array-Formel behandelt wird. Das Tolle an diesem Ansatz ist, dass Sie nie wieder Strg + Umschalt + Eingabetaste im Arbeitsblatt drücken müssen – der Ereignishandler kümmert sich um Sie. Wenn Sie die Formel irgendwann wieder in eine reguläre Version (ohne Array) konvertieren möchten, ändern Sie die Formel einfach so, dass sie nicht + N („\ {„) enthält.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (478) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Überprüfung auf korrekte Eingabe von Array-Formeln.