VBA – Fonction Somme (Plages, Colonnes et Autres)

Ce tutoriel montre comment utiliser la fonction Excel Sum en VBA

La fonction somme est l’une des fonctions Excel les plus utilisées, et probablement la première que les utilisateurs d’Excel apprennent à utiliser. VBA n’a pas vraiment d’équivalent – un utilisateur doit utiliser la fonction Excel intégrée dans VBA en utilisant l’objet WorkSheetFunction.

Somme WorksheetFunction

L’objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel disponibles dans la boîte de dialogue Insérer une Fonction d’Excel. La fonction SOMME (SUM puisque le code VBA doit être écrit en anglais) est l’une d’entre elles.

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

Vous pouvez avoir jusqu’à 30 arguments dans la fonction SUM. Chacun de ces arguments peut également faire référence à une plage de cellules. L’exemple ci-dessous additionne les cellules D1 à D9

Sub TestFonction()   
   Range("D10") = Application.WorksheetFunction.Sum(Range("D1:D9"))
End Sub

L’exemple ci-dessous additionne une plage de la colonne D et une plage de la colonne F. Si vous ne tapez pas explicitement l’objet Application, il sera assumé que vous y faites référence.

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

Affectation du Résultat d’une Somme à une Variable

Il se peut que vous souhaitiez utiliser le résultat de votre formule ailleurs dans le code plutôt que de le réécrire directement dans une plage Excel. Si c’est le cas, vous pouvez affecter le résultat à une variable que vous utiliserez plus tard dans votre code.

Sub AssignerSommeVariable()
    Dim résultat As Double
'Assigner la variable
    résultat = WorksheetFunction.Sum(Range("G2:G7"), Range("H2:H7"))
'Affiche le résultat
    MsgBox "La somme de la plage est : " & résultat
End Sub

Somme d’un Objet Range

Vous pouvez attribuer un groupe de cellules à l’objet Range, puis utiliser cet objet Range avec l’objet WorksheetFunction.

Sub TestSommeObjetRange()
   Dim rng As Range
'Assigne la plage de cellules
   Set rng = Range("D2:E10")
'Utilise l'objet range dans la formule sum
   Range("E11") = WorksheetFunction.Sum(rng)
'Utilise l'objet range
  Set rng = Nothing
End Sub

Additionner Plusieurs Objets Range

De la même manière, vous pouvez additionner plusieurs objets Range

Sub TestSommeObjetRangeMultiples()
   Dim rngA As Range
   Dim rngB As Range
'Assigne la plage de cellules
   Set rngA = Range("D2:D10")
   Set rngB = Range("E2:E10")
'Utilise l'objet range dans la formule sum
Range("E11") = WorksheetFunction.Sum(rngA, rngB)
'Utilise les objets range
  Set rngA = Nothing
  Set rngB = Nothing
End Sub

Somme d’une Colonne ou d’une Rangée Entière

Vous pouvez également utiliser la fonction Sum pour additionner une colonne ou une ligne entière.

La procédure ci-dessous additionne toutes les cellules numériques de la colonne D.

Sub TestSommeColonne()
    Range("F1") = WorksheetFunction.Sum(Range("D:D"))
End Sub

Tandis que la procédure ci-dessous additionnera toutes les cellules numériques de la rangée 9.

Sub TestSommeRangée()
    Range("F1") = WorksheetFunction.Sum(Range("9:9"))
End Sub

Somme d’un Tableau

Vous pouvez également utiliser la fonction WorksheetFunction.Sum pour additionner les valeurs d’un tableau.

Sub TestSommeTableau()
   Dim intA(1 To 5) As Integer
   Dim SommeTableau As Integer
'Popule le tableau
   intA(1) = 15
   intA(2) = 20
   intA(3) = 25
   intA(4) = 30
   intA(5) = 40
'Additionne les valeurs et affiche le résultat
   MsgBox WorksheetFunction.Sum(intA)
End Sub

Utilisation de la Fonction SumIf

Une autre fonction de feuille de calcul qui peut être utilisée est la fonction SUMIF (« Somme Si »).

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

La procédure ci-dessus n’additionnera les cellules de la plage (D2:D10) que si la cellule correspondante de la colonne C = 150.

Formule de Somme

Lorsque vous utilisez la fonction WorksheetFunction.SUM pour ajouter une somme à une plage de votre feuille de calcul, c’est une somme statique qui est renvoyée, et non une formule flexible. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur renvoyée par la WorksheetFunction ne change pas.

Dans l’exemple ci-dessus, la procédure TestSum a additionné Range(D2:D10) et le résultat a été placé en D11. Comme vous pouvez le voir dans la barre de formule, ce résultat est un chiffre et non une formule.

Si l’une des valeurs change donc dans l’intervalle (D2:D10), le résultat en D11 ne changera PAS. Au lieu d’utiliser la fonction WorksheetFunction.SUM, vous pouvez utiliser VBA pour appliquer une fonction de somme à une cellule à l’aide des méthodes Formula ou FormulaR1C1.

Méthode Formula (« Formule »)

La méthode Formula de l’objet range vous permet d’indiquer spécifiquement une plage de cellules, par exemple D2:D10, comme illustré ci-dessous.

Sous TestSumFormula
  Range("D11").Formula = "=SUM(D2:D10)"
End Sub

Méthode FormulaR1C1

La méthode FormulaR1C1 est plus souple, car elle ne vous limite pas à une plage de cellules donnée. L’exemple ci-dessous nous donnera la même réponse que celle ci-dessus.

Sous TestSommeFormule()
   Range("D11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

Cependant, pour rendre la formule plus flexible, nous pourrions modifier le code de la manière suivante :

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

Où que vous soyez dans votre feuille de calcul, la formule additionnera les 8 cellules situées directement au-dessus d’elle et placera la réponse dans la cellule active. La plage dans la fonction SUM doit être mentionnée à l’aide de la syntaxe Row (R) et Column (C).

Ces deux méthodes vous permettent d’utiliser des formules Excel dynamiques dans VBA.

Il y aura maintenant une formule dans D11 au lieu d’une valeur.