Ce tutoriel va vous apprendre à créer des formules de cellules en utilisant VBA.
Formules en VBA
En utilisant VBA, vous pouvez écrire des formules directement dans des plages ou des cellules dans Excel. Cela ressemble à ceci :
| 123456789 | Sous-Formule_Exemple()'Attribuer une formule codée en dur à une seule cellulePlage("b3").Formule = "=b1+b2"'Attribuer une formule flexible à une plage de cellulesPlage("d1:d100").FormuleR1C1 = "=RC2+RC3"Fin du sous-marin |
Il y a deux propriétés Range que vous devez 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.
- .FormuleR1C1 - 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, vous pouvez utiliser la propriété .Formula. Cependant, pour tout le reste, nous vous recommandons d'utiliser le 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 cellule avec VBA. Vous pouvez simplement :
- Commencer l'enregistrement
- Tapez la formule (avec des références relatives / absolues si nécessaire) dans la cellule et appuyez sur Entrée
- Arrête d'enregistrer
- Ouvrez VBA et passez en revue la formule, en l'adaptant au besoin et en copiant + collant le code si nécessaire.
je trouve que c'est beaucoup plus facile pour saisir une formule dans une cellule que de taper la formule correspondante en VBA.
Remarquez deux ou trois choses :
- L'enregistreur de macros utilisera 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:
| 12345678910111213141516171819 | Sous-formuleR1C1_Examples()'Référence D5 (Absolu)'=$D$5Plage("a1").FormuleR1C1 = "=R5C4"'Référence D5 (Relatif) de la cellule A1'=D5Plage("a1").FormuleR1C1 = "=R[4]C[3]"'Référence D5 (ligne absolue, colonne relative) de la cellule A1'=D$5Plage("a1").FormuleR1C1 = "=R5C[3]"'Référence D5 (ligne relative, colonne absolue) de la cellule A1'=$D5Plage("a1").FormuleR1C1 = "=R[4]C4"Fin du sous-marin |
Notez que le référencement de cellule 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". En notation R1C1, cela ressemble à ceci : "=R2C3".
Pour créer une référence de cellule absolue à l'aide du 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 3e colonne).
| 123 | 'Référence D5 (Absolu)'=$D$5Plage("a1").FormuleR1C1 = "=R5C4" |
Références relatives
Les références de cellule relatives sont des références de cellule qui « bougent » 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 par rapport à la cellule actuelle.
Exemple : La saisie de la formule « =R[1]C[1] » dans la cellule B3 ferait référence à la cellule D4 (la cellule 1 ligne en 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.
| 123 | 'Référence D5 (Relatif) de la cellule A1'=D5Plage("a1").FormuleR1C1 = "=R[4]C[3]" |
Références mixtes
Les références de cellule peuvent être partiellement relatives et partiellement absolues. Exemple:
| 123 | 'Référence D5 (ligne relative, colonne absolue) de la cellule A1'=$D5Plage("a1").FormuleR1C1 = "=R[4]C4" |
Propriété de formule VBA
Lorsque vous définissez des formules avec le .Propriété de formule vous utiliserez toujours une notation de style A1. Vous entrez la formule comme vous le feriez dans une cellule Excel, sauf entourée de guillemets :
| 12 | 'Attribuer une formule codée en dur à une seule cellulePlage("b3").Formule = "=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:
| 1234567 | Sous-Formule_Variable()Dim colNum As LongColNum = 4Plage("a1").FormuleR1C1 = "=R1C" & ColNum & "+R2C" & ColNumFin du sous-marin |
Citations de formule
Si vous devez ajouter une citation (") dans une formule, entrez la citation deux fois ("") :
| 123 | Sous-Macro2()Plage("B3").FormuleR1C1 = "=TEXTE(RC[-1],""mm/jj/aaaa"")"Fin du sous-marin |
Une simple guillemet (") signifie pour VBA la fin d'une chaîne de texte. Alors qu'une double citation ("") est traitée comme une citation dans la chaîne de texte.
De même, utilisez 3 guillemets (""") pour entourer une chaîne d'un guillemet (")
| 12 | MsgBox """Utilisez 3 pour entourer une chaîne de guillemets"""' Cela imprimera la fenêtre immédiate |
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 de chaîne :
| 123 | 'Attribuer une formule de cellule à une variableDim strFormula en tant que chaînestrFormula = Range("B1").Formule |
Différentes manières d'ajouter des formules à une cellule
Voici quelques exemples supplémentaires sur la façon d'attribuer une formule à une cellule :
- Affecter directement la formule
- Définir une variable de chaîne contenant la formule
- Utiliser des variables pour créer une formule
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()' Autres façons d'ajouter la formule SUM' à la cellule B1'Dim strFormula en tant que chaîneDim cellule comme plagedim fromRow as Range, toRow as RangeDéfinir la cellule = Plage("B1")' Affectation directe d'une chaînecellule.Formule = "=SOMME(A1:A10)"' Stockage de la chaîne dans une variable' et affectation à la propriété "Formule"strFormule = "=SOMME(A1:A10)"cellule.Formule = strFormule' Utilisation de variables pour construire une chaîne' et en l'affectant à la propriété "Formule"fromRow = 1toRow = 10strFormula = "=SUM(A" & fromValue & ":A" & toValue & ")cellule.Formule = strFormuleFin du sous-marin |
Actualiser les formules
Pour rappel, pour rafraîchir les formules, vous pouvez utiliser la commande Calculer :
| 1 | Calculer |
Pour actualiser une formule, une plage ou une feuille de calcul entière, utilisez plutôt .Calculate :
| 1 | Feuilles("Feuille1").Plage("a1:a10").Calculer |
