Voir le flux RSS

Philippe Tulliez

[Actualité] Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2)

Noter ce billet
par , 23/03/2015 à 17h19 (1623 Affichages)
Cet article est la suite de ce billet.
Nous allons maintenant voir comment écrire des formules plus complexes
Pour tous les plages de données évoquées plus bas la cellule de départ est la cellule A1 et la première ligne contient les étiquettes de colonnes.
Comment écrire une formule plus complexe dans une cellule ?
Prenons la formule NB.SI dont la syntaxe est NB.SI(Plage;Critère) et qui a pour but de compter le nombre de cellules répondant au critère.
Pour illustrer notre exemple, imaginons une plage de données A1:F31 dont les cellules de la colonne C contiennent le nom d'une ville.
Le but sera donc de compter le nombre de fois que le critère (ici Marseille) se retrouve dans la plage C2:C31
la formule sera donc
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
=NB.SI($C$2:$C$31;"Marseille")
Soit en VBA après avoir utilisé l'astuce présentée dans le premier article
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
? range("H2").Formula
=COUNTIF($C$2:$C$31,"Marseille")
Comment traiter les constantes alphanumériques dans une formule ?
Si nous plaçons telle quelle cette chaîne de caractères comme constante ou comme variable suivant l'exemple ci-dessous, nous aurons immanquablement un message "Erreur de compilation, fin d'instruction
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
Const myFormula As String = "=COUNTIF($C$2:$C$31,"Marseille")"
Pour pallier ce problème, nous devons doubler les guillemets soit
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
Const myFormula As String = "=COUNTIF($C$2:$C$31,""Marseille"")"
Ce qui évidemment n'est pas simple à relire surtout si nous avons beaucoup de constantes alphanumériques dans une formule comme cet exemple ci-dessous avec la formule SOMMEPROD dont la valeur renvoyée équivaut à un NB.SI avec deux critères (Les personnes travaillant à Marseille au service Achat).
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
Const myFormula As String = "=SUMPRODUCT( ($A$2:$A$31=""Achat"") * ($C$2:$C$31=""Marseille"") )"
Comme on peut le constater, l'encodage de la formule devient très rapidement rébarbatif.
Utiliser des balises
Pour éviter de s'encombrer d'une multitude de guillemets difficile à gérer, nous allons utiliser une balise que nous remplacerons par la valeur du paramètre entouré des guillemets grâce à la fonction Replace
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
Const myFormula As String = "=COUNTIF($C$2:$C$31,<Ville>)"
Range("H2").Formula = Replace(myFormula, "<Ville>", Chr(34) & "Marseille" & Chr(34))
Chaque caractère en informatique possède un code ASCII ou ANSI. Les guillemets ont comme code 34.
Pour insérer les guillemets nous utilisons donc la fonction Chr qui renvoie un caractère de type String correspondant au code ASCII de son argument.
Comment rendre dynamique les références à des cellules dans une formule ?
Lorsque l'on souhaite écrire dans une cellule d'excel une formule qui fait référence à une plage de cellules, il est important de rendre la référence à celle-ci dynamique. Pour cela nous utiliserons également une balise.
Si nous reprenons notre exemple de la formule NB.SI, cela donnera
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
Const myFormula As String = "=COUNTIF(<data>,<Ville>)"
La balise nommée data sera remplacée par l'adresse de la plage et la balise nommée Ville par la valeur du critère à compter
Voici la procédure complète utilisée
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub InsertFormula()
 Const myFormula As String = "=COUNTIF(<data>,<Ville>)"
 Dim rng As Range, rngData As Range
 Dim newFormula As String
 Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion
 With rng ' Calcul la plage de données
  ' Colonne 3 des donnnées après déplacement d'1 ligne et redimensionnement de la plage
  Set rngData = .Offset(1).Resize(.Rows.Count - 1).Columns(3)
 End With
 ' Remplacement des balises
 newFormula = Replace(myFormula, "<Ville>", Chr(34) & "Marseille" & Chr(34))
 newFormula = Replace(newFormula, "<data>", rngData.Address)
 ' Ecriture de la formule
 ThisWorkbook.Worksheets("db").Range("H2").Formula = newFormula
End Sub
Nous avons utilisé une variable intermédiaire nommée newFormula pour effectuer le remplacement des balises contenues dans la constante myFormula pour rendre plus lisible l'exemple.
Bien entendu nous aurions pu nous passer de cette variable en effectuant ce remplacement immédiatement mais cela aurait rendu sa lecture plus indigeste.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
With ThisWorkbook.Worksheets("db").Range("H2")
.Formula = Replace(Replace(myFormula, "<Ville>", Chr(34) & "Marseille" & Chr(34)), "<data>", rngData.Address)
End With

Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Viadeo Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Twitter Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Google Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Facebook Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Digg Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Delicious Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog MySpace Envoyer le billet « Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2) » dans le blog Yahoo

Mis à jour 22/04/2017 à 14h06 par Philippe Tulliez (Corr.Orthogr.)

Catégories
Sans catégorie

Commentaires