VBA – die Zählfunktion Count

Dieses Tutorial zeigt Ihnen, wie Sie die Excel-Funktion COUNT in VBA verwenden können

Die VBA-Funktion COUNT wird verwendet, um die Zellen in Ihrem Arbeitsblatt zu zählen, die Werte enthalten. Sie wird über die Methode WorksheetFunction in VBA aufgerufen.

Die Arbeitsblattfunktion COUNT

Das WorksheetFunction-Objekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfenster „Funktion einfügen“ in Excel verfügbar sind. Die Funktion COUNT ist eine von ihnen.

Sub CountFunktionTest()
  Range("D33") = Application.WorksheetFunction.Count(Range("D1:D32"))
End Sub

Sie können bis zu 30 Argumenten in der Funktion COUNT haben. Jedes der Argumente muss auf einen Bereich von Zellen verweisen.

Im folgenden Beispiel wird gezählt, wie viele Zellen mit Werten in den Zellen D1 bis D9 belegt sind.

Sub Count_Testen()   
   Range("D10") = Application.WorksheetFunction.Count(Range("D1:D9"))
End Sub

Im folgenden Beispiel wird gezählt, wie viele Werte sich in einem Bereich in Spalte D und in einem Bereich in Spalte F befinden. Wenn Sie das Application-Objekt nicht eingeben, wird es angenommen.

Sub CountTestMehrfach()
   Range("G8") = WorksheetFunction.Count(Range("G2:G7"), Range("H2:H7"))
End Sub

Zuweisen eines Count-Ergebnisses einer Variablen

Möglicherweise möchten Sie das Ergebnis Ihrer Formel an anderer Stelle im Code verwenden, anstatt es direkt in einen Excel-Bereich zurückzuschreiben. In diesem Fall können Sie das Ergebnis einer Variablen zuweisen, die Sie später in Ihrem Code verwenden können.

Sub Count_Ergebnis_Zuweisen()
   Dim ergebnis As Integer
'Der Variablen zuweisen   
   ergebnis = WorksheetFunction.Count(Range("H2:H11"))
'Das Ergebnis anzeigen
   MsgBox "Die Anzahl der mit Werten belegten Zellen ist " & ergebnis
End Sub

COUNT mit einem Range-Objekt

Sie können eine Gruppe von Zellen dem Range-Objekt zuweisen und dieses dann mit dem WorksheetFunction-Objekt verwenden.

Sub Count_Test_Bereich()
   Dim bereich As Range
'den Bereich der Zellen zuweisen
   Set bereich = Range("G2:G7")
'den Bereich in der Formel verwenden
   Range("G8") = WorksheetFunction.Count(bereich)
'das Bereichsobjekt freigeben
  Set bereich = Nothing
End Sub

COUNT von mehreren Range-Objekten

Auf ähnliche Weise können Sie zählen, wie viele Zellen mit Werten in mehreren Range-Objekten belegt sind.

Sub CountTestMehrereBereiche() 
   Dim bereichA As Range 
   Dim bereichB as Range
'Zuweisung des Bereichs der Zellen 
   Set bereichA = Range("D2:D10") 
   Set bereichB = Range("E2:E10")   
'den Bereich in der Formel verwenden 
Range("E11") = WorksheetFunction.Count(bereichA, bereichB)
 'das Bereichsobjekt freigeben
  Set bereichA = Nothing 
  Set bereichB = Nothing
End Sub

COUNTA verwenden

Die Count-Funktion zählt nur die numerischen WERTE in den Zellen, aber nicht die Zellen, die Text enthalten. Um die Zellen zu zählen, die mit beliebigen Arten von Daten belegt sind, müssen wir die Funktion COUNTA verwenden.

Sub CountA_Testen()
   Range("B8") = Application.WorksheetFunction.CountA(Range("B1:B6")) 
End Sub

Im folgenden Beispiel würde die Funktion COUNT eine Null zurückgeben, da keine numerischen Werte in Spalte B vorhanden sind, während sie für Spalte C eine 4 zurückgeben würde. Die Funktion COUNTA  würde hingegen die Zellen mit Text zählen und in Spalte B den Wert 5 zurückgeben, während sie in Spalte C immer noch den Wert 4 zurückgeben würde.

Verwendung von COUNTBLANK

Die Funktion COUNTBLANK zählt nur die leeren Zellen in einem Zellbereich, d. h. Zellen, die keine Daten enthalten.

Sub CountBlank_Testen()   
   Range("B8") = Application.WorksheetFunction.CountBlank(Range("B1:B6")) 
End Sub

Im folgenden Beispiel enthält Spalte B keine leeren Zellen, während Spalte C eine leere Zelle enthält.

Verwendung der COUNTIF-Funktion

Eine weitere Arbeitsblattfunktion, die verwendet werden kann, ist die Funktion COUNTIF.

Sub CountIf_Testen()
   Range("H14") = WorksheetFunction.CountIf(Range("H2:H10"), ">0")
   Range("H15") = WorksheetFunction.CountIf(Range("H2:H10"), ">100")
   Range("H16") = WorksheetFunction.CountIf(Range("H2:H10"), ">1000")
   Range("H17") = WorksheetFunction.CountIf(Range("H2:H10"), ">10000")
End Sub

Die obige Prozedur zählt nur die Zellen, die Werte enthalten, die die Kriterien (größer als 0, größer als 100, größer als 1000 und größer als 10000) erfüllen. Sie müssen die Kriterien in Anführungszeichen setzen, damit die Formel korrekt funktioniert.

Nachteile von WorksheetFunction

Wenn Sie WorksheetFunction verwenden, um die Werte in einem Bereich in Ihrem Arbeitsblatt zu zählen, wird ein statischer Wert und keine flexible Formel zurückgegeben. Das bedeutet, dass sich der von der WorksheetFunction zurückgegebene Wert nicht ändert, wenn sich Ihre Zahlen in Excel ändern.

Im obigen Beispiel hat die Prozedur TestCount die Zellen in Spalte H gezählt, in denen ein Wert vorhanden ist. Wie Sie in der Formelleiste sehen können, handelt es sich bei diesem Ergebnis um eine Zahl und nicht um eine Formel.

Wenn sich also einer der Werte im Bereich (H2:H12) ändert, werden sich die Ergebnisse in H14 NICHT ändern.

Anstatt die WorksheetFunction.Count zu verwenden, können Sie mit VBA eine Zählfunktion auf eine Zelle anwenden, indem Sie die Methoden Formula oder FormulaR1C1 verwenden.

Verwendung der Formelmethode

Mit der Formelmethode können Sie speziell auf einen Bereich von Zellen, wie unten gezeigt, verweisen, z. B. H2:H12.

Sub CountFormelTesten()
  Range("H14").Formula = "=Count(H2:H12)"
End Sub

Verwendung der Methode FormulaR1C1

Die Methode FromulaR1C1 ist insofern flexibler, da sie Sie nicht auf einen bestimmten Bereich von Zellen beschränkt. Das folgende Beispiel liefert die gleiche Antwort wie das obige.

Sub CountFormelTesten()
   Range("H14").Formula = "=Count(R[-9]C:R[-1]C)"
End Sub

Um die Formel flexibler zu gestalten, könnten wir den Code jedoch folgendermaßen ändern:

Sub CountFormelTesten() 
   ActiveCell.FormulaR1C1 = "=Count(R[-11]C:R[-1]C)" 
End Sub

Unabhängig davon, wo Sie sich in Ihrem Arbeitsblatt befinden, zählt die Formel dann die Werte in den 12 Zellen direkt über ihr und platziert die Antwort in Ihrer aktuellen Zelle (ActiveCell). Der Bereich innerhalb der Funktion COUNT muss mit der Syntax für Zeile (R) und Spalte (C) angegeben werden.

Mit diesen beiden Methoden können Sie dynamische Excel-Formeln in VBA verwenden.

In H14 steht nun eine Formel anstelle eines Wertes.