Excel · VBA · A3iformations

Les formules en VBA : WorksheetFunction, Formula, FormulaLocal et FormulaR1C1

Une macro peut utiliser les formules d’Excel de deux façons. Elle peut calculer en VBA avec les fonctions de la feuille, grâce à Application.WorksheetFunction : le résultat est une valeur. Elle peut aussi écrire une formule dans une cellule avec Formula : Excel la recalcule ensuite à chaque modification. Les simulateurs reproduisent une feuille Ventes avec sa barre de formule, modifiable comme dans Excel, et affichent ce que renvoient Value, Formula, FormulaLocal et FormulaR1C1 pour la cellule sélectionnée. Voir aussi les cellules et les classeurs.

WorksheetFunction.Sum(…)Calculer

Application.VLookupGérer les erreurs

.Formula = « =SUM(…) »Écrire

.FormulaR1C1Recopier

La classe Application.WorksheetFunction

VBA possède ses propres fonctions (Len, Left, Round, Date…) mais pas celles qui travaillent sur des plages : somme, moyenne, NB.SI, RECHERCHEV. L’objet WorksheetFunction les met à disposition : WorksheetFunction.Sum(Range("C2:C6")) renvoie la somme, que l’on affiche, range dans une variable ou écrit dans une cellule. On peut écrire Application.WorksheetFunction.Sum ou simplement WorksheetFunction.Sum. Les fonctions portent leur nom anglais et leurs arguments se séparent par des virgules.

Excel en français	WorksheetFunction	Exemple
SOMME, MOYENNE	`Sum`, `Average`	`WorksheetFunction.Sum(Range("C2:C6"))`
MAX, MIN, MEDIANE	`Max`, `Min`, `Median`	`WorksheetFunction.Max(Range("C2:C6"))`
NB, NBVAL	`Count`, `CountA`	`WorksheetFunction.CountA(Range("A:A"))`
NB.SI, SOMME.SI	`CountIf`, `SumIf`	`WorksheetFunction.CountIf(Range("B2:B6"), "Lille")`
RECHERCHEV, EQUIV, INDEX	`VLookup`, `Match`, `Index`	`WorksheetFunction.VLookup("VBA", Range("A2:C6"), 3, False)`
ARRONDI, GRANDE.VALEUR	`Round`, `Large`	`WorksheetFunction.Large(Range("C2:C6"), 2)`

Simulateur : calculer avec WorksheetFunction

Écriture	Si la fonction renvoie une erreur (#N/A…)	Test
`WorksheetFunction.VLookup(…)`	La macro s’arrête : erreur d’exécution 1004	`On Error Resume Next` puis `If Err.Number <> 0`
`Application.VLookup(…)`	La fonction renvoie une valeur d’erreur (Erreur 2042), la macro continue	`If IsError(r) Then`, avec `Dim r As Variant`

La saisie semi-automatiqueTapez WorksheetFunction. dans l’éditeur : la liste des fonctions disponibles s’affiche, avec leurs arguments (Arg1, Arg2…).

Pas de traductionWorksheetFunction.Somme n’existe pas : seuls les noms anglais sont reconnus, quelle que soit la langue d’Excel.

Fonctions absentesUne fonction qui existe déjà en VBA n’est pas dans WorksheetFunction : Len, Left, UCase, Date. SI se remplace par If ou IIf.

Formula : écrire une formule dans une cellule

La propriété Formula lit ou écrit la formule d’une cellule, en anglais : Range("C7").Formula = "=SUM(C2:C6)". Excel l’affiche ensuite en français dans la barre de formule et la recalcule à chaque changement, ce que ne fait pas une valeur calculée par WorksheetFunction. FormulaLocal utilise la syntaxe de votre Excel (SOMME, point-virgule), FormulaR1C1 la notation ligne-colonne. Écrite sur une plage, la formule est recopiée comme avec la poignée de recopie : les références relatives s’adaptent, les références absolues ($B$8) restent fixes.

Propriété	Syntaxe	Exemple
`.Formula`	Anglaise : SUM, virgule entre les arguments, point décimal	`"=ROUND(C2*1.2,0)"`
`.FormulaLocal`	Celle de votre Excel : SOMME, point-virgule, virgule décimale	`"=ARRONDI(C2*1,2;0)"`
`.FormulaR1C1`	Anglaise, références en lignes (R) et colonnes (C)	`"=RC[-2]*RC[-1]"`
`.HasFormula`	True si la cellule contient une formule	`If Range("C7").HasFormula Then`
`.Value` / `.Text`	Le résultat, ou le texte affiché (#NOM? par exemple)	`Range("C7").Value`

Simulateur : Formula et FormulaLocal (cliquez une cellule, modifiez la barre de formule puis Entrée)

L’enregistreur de macrosPour connaître la syntaxe anglaise d’une formule, tapez-la en français dans une cellule pendant un enregistrement : le code produit contient sa version VBA (en R1C1).

Les guillemetsUne formule qui contient du texte double ses guillemets dans la chaîne VBA : "=IF(B2=""Lille"",""Local"","""")".

En notation R1C1, une référence se décrit par rapport à la cellule de la formule : RC[-2] signifie même ligne, deux colonnes à gauche ; R[-1]C la ligne au-dessus ; R8C2, sans crochets, la cellule fixe ligne 8, colonne 2, soit $B$8. Toutes les cellules d’une colonne recopiée ont donc la même formule R1C1, ce qui la rend idéale pour écrire une formule sur une plage dont on ne connaît pas la taille.

Simulateur : FormulaR1C1

Formula2 et les tableaux dynamiquesDans Excel 365, Formula2 écrit une formule qui peut se propager (FILTRE, UNIQUE, TRIER). Formula garde le comportement des versions précédentes.

Figer les résultatsRange("E2:E6").Value = Range("E2:E6").Value remplace les formules par leurs résultats.

Exercice : statistiques et formules sur la feuille Ventes

Classeur d’exercice (Énoncé et Corrigé)

Enregistrez le classeur en .xlsm et collez le code de la feuille « Code VBA » dans un module standard.
Exécutez Statistiques (Sum, Average, Max, CountIf, SumIf) et comparez avec les cellules calculées à la main.
Exécutez ChercherPrix avec une formation existante, puis avec « Python » : observez Application.VLookup et IsError.
Exécutez PoserFormules (Formula, FormulaLocal) puis FormulesR1C1, modifiez un prix et regardez les totaux se recalculer.
Exécutez ListerFormules et répondez aux questions de la feuille Contrôle.