VBA – Summenfunktion (Bereiche, Spalten und mehr)

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

Die Summenfunktion ist eine der am häufigsten verwendeten Excel-Funktionen und wahrscheinlich die erste, die Excel-Benutzer zu benutzen lernen. In VBA gibt es kein Äquivalent, d.h. der Benutzer muss die eingebaute Excel-Funktion in VBA nutzen, indem er das WorkSheetFunction-Objekt verwendet.

Summe WorksheetFunction

Das WorksheetFunction-Objekt kann zum Aufrufen der meisten Excel-Funktionen verwendet werden, die im Dialogfeld „Funktion Einfügen“ in Excel zur Verfügung stehen. Die Funktion SUM ist eine davon.

Sub TestFunktion()
  Range("D33") = Application.WorksheetFunction.Sum(Range("D1:D32"))
End Sub

Sie können bis zu 30 Argumenten in der Funktion SUM haben. Jedes der Argumente kann sich auch auf einen Bereich von Zellen beziehen. Im folgenden Beispiel werden die Zellen D1 bis D9 aufsummiert:

Sub SummeTesten()   
   Range("D10") = Application.WorksheetFunction.SUM(Range("D1:D9"))
End Sub

Das folgende Beispiel summiert einen Bereich in Spalte D und einen Bereich in Spalte F auf. Wenn Sie das Application-Objekt nicht eingeben, wird es angenommen.

Sub SummeTesten()
   Range("D25") = WorksheetFunction.SUM (Range("D1:D24"), Range("F1:F24"))
End Sub

Beachten Sie, dass Sie bei einem einzelnen Bereich von Zellen das Wort „Range“ in der Formel vor den Zellen nicht angeben müssen, da der Code dies voraussetzt. Wenn Sie jedoch mehrere Argumente verwenden, müssen Sie dies tun.

Zuweisung eines Summenergebnisses 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 SummeVariableZuweisen()
   Dim ergebnis as Double
'Einer Variablen zuweisen
   ergebnis = WorksheetFunction.SUM(Range("G2:G7"), Range("H2:H7"))
'Das Ergebnis anzeigen
  MsgBox "Die Summe der Bereiche ist " & ergebnis
End Sub

Summe eines Range-Objekts

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

Sub Bereich_Summe_Testen()
   Dim bereich As Range
'Den Bereich der Zellen zuweisen
   Set bereich = Range("D2:E10")
'Den Bereich in der Formel verwenden
   Range("E11") = WorksheetFunction.SUM(bereich)
'Das Range-Objekt freigeben
  Set bereich = Nothing
End Sub

Mehrere Range-Objekte aufsummieren

Auf ähnliche Weise können Sie mehrere Range-Objekte aufsummieren.

Sub MehrereBereiche_Summe_Testen() 
   Dim Bereich_A As Range 
   Dim Bereich_B as Range
'Den Zellenbereich zuweisen 
   Set Bereich_A = Range("D2:D10") 
   Set Bereich_B = Range("E2:E10")   
'Den Bereich in der Formel verwenden 
Range("E11") = WorksheetFunction.SUM(Bereich_A, Bereich_B)
 'Das Range-Objekt freigeben
  Set Bereich_A = Nothing 
  Set Bereich_B = Nothing
End Sub

Gesamte Spalte oder Zeile aufsummieren

Sie können die Funktion Sum auch verwenden, um eine ganze Spalte oder eine ganze Zeile aufzuaddieren.

Die folgende Prozedur summiert alle numerischen Zellen in Spalte D auf.

Sub SummeTesten()    
   Range("F1") = WorksheetFunction.SUM(Range("D:D"))
End Sub

Während diese Prozedur unten alle numerischen Zellen in Zeile 9 aufaddiert.

Sub SummeTesten()     
   Range("F2") = WorksheetFunction.SUM(Range("9:9")) 
End Sub

Ein Array aufsummieren

Sie können auch die WorksheetFunction.Sum verwenden, um die Werte in einem Array aufzuaddieren.

Sub TestArray()
   Dim intA(1 To 5) As Integer
   Dim SummeArray As Integer
'Das Array auffüllen
   intA(1) = 15
   intA(2) = 20
   intA(3) = 25
   intA(4) = 30
   intA(5) = 40
'Das Array aufsummieren und das Ergebnis anzeigen
   MsgBox WorksheetFunction.SUM(intA)
End Sub

Verwendung der SumIf-Funktion

Eine weitere Arbeitsblattfunktion, die verwendet werden kann, ist die SUMIF-Funktion.

Sub SumIfTesten()
Range("D11") = WorksheetFunction.SumIf(Range("C2:C10"), 150, Range("D2:D10"))
End Sub

Die obige Prozedur addiert die Zellen in Range(D2:D10) nur auf, wenn die entsprechende Zelle in Spalte C = 150 ist.

Summenformel

Wenn Sie die WorksheetFunction.SUM verwenden, um eine Summe zu einem Bereich in Ihrem Arbeitsblatt hinzuzufügen, wird eine statische Summe zurückgegeben, keine flexible Formel. 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 TestSum den Range(D2:D10) aufaddiert, und das Ergebnis wurde in D11 eingetragen. 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 Range(D2:D10) ändert, wird sich das Ergebnis in D11 NICHT ändern. Anstatt die WorksheetFunction.SUM zu verwenden, können Sie mit VBA eine Summenfunktion an einer Zelle anwenden, indem Sie die Methoden Formula oder FormulaR1C1 verwenden.

Formel-Methode

Mit der Formula-Methode können Sie speziell auf einen Bereich von Zellen verweisen, z. B. D2:D10, wie unten gezeigt.

Sub SumFormelTesten()
  Range("D11").Formula = "=Sum(D2:D10)"
End Sub

FormulaR1C1-Methode

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 SumFormelTesten()
   Range("D11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

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

Sub SumFormelTesten() 
   ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)" 
End Sub

Unabhängig davon, wo Sie sich in Ihrem Arbeitsblatt befinden, addiert die Formel dann die 8 Zellen direkt darüber und platziert die Antwort in Ihrer aktiven Zelle. Der Bereich innerhalb der Funktion SUM 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 D11 wird nun eine Formel anstelle eines Wertes stehen.