Ce tutoriel vous apprendra à créer des formules de cellules à l’aide de VBA.
Formules dans VBA
En utilisant VBA, vous pouvez écrire des formules directement dans des plages ou des cellules dans Excel. Cela ressemble à ceci:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
Il y a deux propriétés de plage que vous devrez connaître:
- .Formule – Crée une formule exacte (références de cellules codées en dur). Bon pour ajouter une formule à une seule cellule.
- .FormulaR1C1 – Crée une formule flexible. Bon pour ajouter des formules à une gamme de cellules où les références de cellules devraient changer.
Pour des formules simples, il est bon d’utiliser le.Propriété de formule. Cependant, pour tout le reste, nous vous recommandons d’utiliser l’Enregistreur de macros
Enregistreur de macros et 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émarrer l’enregistrement
- Tapez la formule (avec des références relatives / absolues selon les besoins) dans la cellule & appuyez sur entrée
- Arrêtez l’enregistrement
- Ouvrez VBA et passez en revue la formule, en l’adaptant au besoin et en copiant + collant le code si nécessaire.
Je trouve qu’il est beaucoup plus facile d’entrer une formule dans une cellule que de taper la formule correspondante dans VBA.
Remarquez quelques choses:
- L’enregistreur de macro utilisera toujours le.Propriété FormulaR1C1
- L’Enregistreur de macros reconnaît les références de cellules absolues par rapport aux Références de cellules 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 FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"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: « =CC$2″” En notation R1C1, cela ressemble à ceci: « = R2C3 ».
Pour créer une référence de cellule absolue en utilisant le type de style R1C1:
- R + Numéro de ligne
- C + Numéro de colonne
Exemple: R2C3 représenterait la cellule $C$2 (C est la 3ème colonne).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
Références relatives
Les références de cellule relatives sont des références de cellule qui « se déplacent” lorsque la formule est déplacée.
En notation A1 standard, ils ressemblent à ceci: »=C2″. Dans la notation R1C1, vous utilisez des crochets pour décaler la référence de cellule de la cellule actuelle.
Exemple: Entrer la formule « = RC » dans la cellule B3 ferait référence à la cellule D4 (la cellule 1 ligne ci-dessous et 1 colonne à droite de la cellule de formule).
Utilisez des nombres négatifs pour référencer les cellules au-dessus ou à gauche de la cellule actuelle.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Références mixtes
Les références de cellules peuvent être partiellement relatives et partiellement absolues. Exemple :
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
Propriété de formule VBA
Lors de la définition de formules avec le .Propriété de formule vous utiliserez toujours la notation de style A1. Vous entrez la formule comme vous le feriez dans une cellule Excel, sauf entourée de citations:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
Conseils de formule VBA
Formule avec variable
Lorsque vous travaillez avec des formules dans VBA, il est très courant de vouloir utiliser des variables dans les formules de cellule. Pour utiliser des variables, vous utilisez & pour combiner les variables avec le reste de la chaîne de formule. Exemple :
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Citations de formule
Si vous devez ajouter une citation (« ) dans une formule, entrez la citation deux fois (« »):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
Une seule citation (« ) signifie pour VBA la fin d’une chaîne de texte. Alors qu’un guillemet double (« ”) est traité comme un guillemet dans la chaîne de texte.
De même, utilisez 3 guillemets (« ” ») pour entourer une chaîne d’un guillemet (« )
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Attribuer une formule de cellule à une variable de chaîne
Nous pouvons lire la formule dans une cellule ou une plage donnée et l’affecter à une variable:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Différentes façons d’Ajouter des Formules à une Cellule
Voici quelques exemples supplémentaires pour assigner une formule à une cellule :
- Assigner directement une Formule
- Définir une Variable de Chaîne Contenant la Formule
- Utiliser des Variables pour créer une Formule
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
Actualiser les formules
Pour rappel, pour actualiser les formules, vous pouvez utiliser la commande Calculer:
Calculate
Pour actualiser une seule formule, une plage ou une feuille de calcul entière.Calculer à la place:
Sheets("Sheet1").Range("a1:a10").Calculate