Formules Excel VBA – Le Guide Ultime

Ce tutoriel vous apprendra à créer des formules de cellule à l’aide de VBA.

Formules en VBA

En utilisant VBA, vous pouvez écrire des formules directement dans des plages ou des cellules d’Excel. Voici à quoi le code peut ressembler :

Sub Exemple_Formule()

    'Assigner une formule absolue à une cellule
    Range("b3").Formula = "=b1+b2"
    
    'Assigner une formule relative à une cellule
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

Il y a deux propriétés de l’objet Range que vous devrez connaître :

• .Formula – Crée une formule exacte (références de cellule absolues). Idéal pour ajouter une formule à une seule cellule.
• .FormulaR1C1 – Crée une formule flexible. Idéal pour ajouter des formules à une plage de cellules où les références de cellule doivent changer.

Pour les formules simples, il est possible d’utiliser la propriété .Formula. Cependant, pour tout le reste, nous vous recommandons d’utiliser l’Enregistreur de macros…

L’Enregistreur de Macros et les Formules de Cellules

L’enregistreur de macros est notre outil de prédilection pour écrire des formules de cellules avec VBA. Vous pouvez simplement :

• Débuter l’enregistrement
• Taper la formule (avec des références relatives/absolues si nécessaire) dans la cellule et appuyer sur la touche Entrée
• Arrêter l’enregistrement
• Ouvrir VBA et réviser la formule, en l’adaptant et en copiant/collant le code si nécessaire.

Je trouve qu’il est beaucoup plus facile de saisir une formule dans une cellule que de taper la formule correspondante en VBA.

Remarquez deux choses :

• L’enregistreur de macros utilise toujours la propriété .FormulaR1C1
• L’enregistreur de macros reconnaît les références de cellules absolues et relatives

Propriété VBA FormulaR1C1

La propriété FormulaR1C1 utilise le référencement de cellules de style R1C1 (par opposition au style A1 standard que vous avez l’habitude de voir dans Excel).

Voici quelques exemples :

Sub Exemple_FormulaR1C1()

    'Référence D5 (Absolue)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'Référence D5 (Relative) à partir de la cellule A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'Référence D5 (Rangée Absolue, Colonne Relative) à partir de la cellule A1
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'Référence D5 (Rangée Relative, Colonne Absolue) à partir de la cellule A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

Notez que le référencement de cellules de style R1C1 vous permet de définir des références absolues ou relatives.

Références Absolues

En notation A1 standard, une référence absolue ressemble à ceci : « =$C$2 ». Dans la notation R1C1, elle ressemble à ceci : « =R2C3 ».

Pour créer une référence absolue de cellule en utilisant le style R1C1, tapez :

• R + Numéro de ligne
• C + Numéro de colonne

Exemple : R2C3 représenterait la cellule $C$2 (« C » est la 3ème colonne).

    'Référence D5 (Absolue)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

Références Relatives

Les références de cellules relatives sont des références de cellules qui « se déplacent » lorsque la formule est déplacée.

En notation standard A1, elles se présentent comme suit : « =C2 ». Dans la notation R1C1, vous utilisez des crochets [] pour décaler la référence de cellule par rapport à la cellule actuelle.

Exemple : Si vous entrez la formule « =R[1]C[1] » dans la cellule B3, la référence à la cellule D4 (la cellule située 1 rangée en dessous et 1 colonne à droite de la cellule de formule) sera utilisée.

Utilisez des nombres négatifs pour référencer les cellules situées au-dessus ou à gauche de la cellule actuelle.

    'Référence D5 (Relative) à partir de la cellule A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

Références Mixtes

Les références de cellule peuvent être partiellement relatives et partiellement absolues. Exemple :

    'Référence D5 (Ligne Relative, Colonne Absolue) à partir de la cellule A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

Propriété VBA « Formula »

Lorsque vous définissez des formules avec la propriété Formula. vous utilisez toujours la notation de type A1. Vous entrez la formule comme vous le feriez dans une cellule Excel, mais entre guillemets :

    'Attribue une formule absolue à une seule cellule
    Range("b3").Formula = "=b1+b2"

Astuces pour l’Utilisation des Formules en VBA

Formule avec Variable

Lorsque vous travaillez avec des formules en VBA, il est très courant d’utiliser des variables dans les formules de cellules. Pour utiliser des variables, vous utilisez le symbole « & » pour combiner les variables avec le reste de la chaîne de formule. Exemple :

Sub Formule_Variable()
    Dim numCol As Long
    numCol = 4

    Range("a1").FormulaR1C1 = "=R1C" & numCol & "+R2C" & numCol 

End Sub

Guillemets dans les Formules

Si vous devez ajouter des guillemets (« ) dans une formule, entrez le caractère deux fois («  ») :

Sub Macro2()
    Range("B3").FormulaR1C1 = "=TEXTE(RC[-1],""mm/dd/yyyy"")"
End Sub

Les guillemets simples (« ) signifient à VBA la fin d’une chaîne de texte. Alors que les guillemets doubles («  ») sont traités comme des guillemets à l’intérieur de la chaîne de texte.

De même, utilisez 3 guillemets («  » ») pour entourer une chaîne de caractères avec un guillemet (« )

MsgBox """Utilisez 3 pour entourer une chaîne de caractères avec des guillemets""
' Ceci imprimera <"Utiliser 3 pour entourer une chaîne de caractères avec des guillemets"> 
' dans une boite de message

Affecter une Formule de Cellule à une Variable de Type Chaîne

Nous pouvons lire la formule dans une cellule ou une plage donnée et l’affecter à une variable de type chaîne:

'Attribuer une formule de cellule à une variable
Dim strFormule as String
strFormule = Range("B1").Formula

Différentes Façons d’Ajouter des Formules à une Cellule

Voici quelques autres exemples de la façon d’attribuer une formule à une cellule :

1. Affectation directe d’une formule
2. Définir une variable de type chaîne contenant la formule
3. Utiliser des variables pour créer une formule

Sub AutresExemplesFormules()
' Facons alternatives d'ajouter la formule SOMME
' à la cellule B1
'
  Dim strFormule As String
  Dim cellule As Range
  Dim rangéeOrigine As Integer, rangéeDestination As Integer

  Set cellule = Range("B1")

  ' Assigner une chaine directement
  cellule.Formula = "=SOMME(A1:A10)"

  ' Stocker une chaine dans une variable
  ' et l'assigner à la propriété "Formula"
  strFormule = "=SOMME(A1:A10)"
  cellule.Formula = strFormule

  ' Utiliser des variables pour construire une chaîne
  ' et l'assigner à la propriété "Formula"
  rangéeOrigine = 1
  rangéeDestination = 10
  strFormule = "=SOMME(A" & rangéeOrigine & ":A" & rangéeDestination & ")"
  cellule.Formula = strFormule
End Sub

Rafraîchir les Formules

Voici un rappel, pour rafraîchir les formules, vous pouvez utiliser la commande Calculate:

Calculate

Pour rafraîchir une formule, une plage ou une feuille de calcul entière, utilisez plutôt la commande .Calculate :

Sheets("Feuil1").Range("a1:a10").Calculate