Voir le flux RSS

Philippe Tulliez

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

Note : 3 votes pour une moyenne de 3,33.
par , 30/11/2014 à 17h27 (9212 Affichages)
Un sujet fréquemment évoqué sur les forums est l'insertion de formule à l'aide d'une procédure VBA.
Une façon simple d’intégrer une formule dans un code VBA est l’utilisation de l’enregistreur de macros. Cependant l’enregistreur génère la formule en style R1C1 ce qui n’est pas toujours simple à gérer.
Le plus simple est d’écrire la formule dans excel et ensuite avec une procédure VBA lire à l’aide de la propriété formula de l’objet Range, la cellule où se trouve la formule.
Comment écrire sa première formule dans une cellule ?
Exemple 1 :
Prenons un exemple simple, en A2 et B2 un nombre et en C2 une formule qui renvoie le produit de la valeur de ces deux cellules.
  1. Ecrire en C2 la formule
  2. Dans la fenêtre d’exécution de l’éditeur VBA (Ctrl+G), tapez l’instruction qui suit en la validant par la touche Entrée. ( ? est une commande raccourcie pour l'instruction Debug.Print)
    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    ? RANGE("C2").Formula
  3. Le code va donner =A2*B2 qu’il suffira d’intégrer dans une instruction VBA en utilisant la propriété Formula de l’objet Range en lui passant la formule comme valeur Alphanumérique. Soit :
    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("C2").Formula = "=A2*B2"

Exemple 2 : Avec une formule de base d’excel, la somme d’une plage en ligne ($A$2 :$G$2) que l’on placera en cellule H2
Répétons les étapes 1 à 3 décrites plus haut.
  1. Ecriture de la formule en H2
  2. La ligne tapée dans la fenêtre d’exécution nous renvoie
  3. Formule qu'il suffit maintenant de placer comme valeur alphanumérique dans la propriété Formula
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("H2").Formula = "=SUM(A2:G2)"

Comment écrire sa formule dans une plage de cellule de la même colonne ?
Evidemment, il faut écrire notre formule dans une plage complète de la même colonne;
Par exemple de G2 à G101
Il suffit tout simplement d'écrire.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
 With ThisWorkbook.Worksheets("Feuil1")
 .Range("H2:H101").Formula = "=SUM(A2:G2)"
 End With
Comment écrire des formules plus complexes et rendre dynamique les références aux plages de cellules ?
La suite de ce billet traite des formules plus complexes.

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

Mis à jour 19/01/2020 à 12h51 par Philippe Tulliez

Catégories
VBA Excel

Commentaires

  1. Avatar de yanmin
    • |
    • permalink
    Bonjour, je suis novice dans le VBA, j'ai fait un petit programme pour la gestion d'une nomenclature, mais je n'arrive pas à écrire une formule dans mon fichier excel.
    Voici un bout du programme:

    If g = 4 Then
    Sheets("Nomenclature").Select
    REP = Cells(ActiveCell.Row, 6).Value

    Cells.Find(What:=REP).Activate
    Set res = Cells.Find(What:=REP, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If res Is Nothing Then
    If MsgBox("REP INCONNUe", vbOKOnly, "Erreur") = vbOK Then
    Exit Sub
    End If
    Else
    Cells(ActiveCell.Row, 21).FormulaLocal = "=SIERREUR(RECHERCHEV($U9;'M:\2-T\2-TO\2-Public\OUTILLAGE\PROJET_SERIE\EK_PROJECT\" & Projet & "\1-PLAN OUTIL\001-EN SERIE\[" & NWT & ".xlsm]DT PIECE'!$A$1:$T$999;14;FAUX);"")"


    ActiveWorkbook.Save
    End If

    End If

    Mais si on enlève le "=" la formule s’insère correctement mais ne fonctionne pas du coup...
  2. Avatar de Philippe Tulliez
    • |
    • permalink
    Bonjour,
    Sans connaître le numéro de l'erreur et le numéro de la ligne (que vous obtiendrez en mettant le code entre les balises), c'est diffiiciile de vous aider.
    Toutefois, je peux déjà vous indiquer que dans cette ligne vous avez omis de doubler les guillemets

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
     "=SIERREUR(RECHERCHEV($U9;'M:\2-T\2-TO\2-Public\OUTILLAGE\PROJET_SERIE\EK_PROJECT\" & Projet & "\1-PLAN OUTIL\001-EN SERIE\[" & NWT & ".xlsm]DT PIECE'!$A$1:$T$999;14;FAUX);"")"
    Avez-vous lu mon billet suivant titré Comment écrire une formule plus complexe dans une cellule ? qui indique comment ne pas utiliser les guillemets mais des balises.

    Citation Envoyé par yanmin
    Bonjour, je suis novice dans le VBA, j'ai fait un petit programme pour la gestion d'une nomenclature, mais je n'arrive pas à écrire une formule dans mon fichier excel.
    Voici un bout du programme:

    If g = 4 Then
    Sheets("Nomenclature").Select
    REP = Cells(ActiveCell.Row, 6).Value

    Cells.Find(What:=REP).Activate
    Set res = Cells.Find(What:=REP, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If res Is Nothing Then
    If MsgBox("REP INCONNUe", vbOKOnly, "Erreur") = vbOK Then
    Exit Sub
    End If
    Else
    Cells(ActiveCell.Row, 21).FormulaLocal = "=SIERREUR(RECHERCHEV($U9;'M:\2-T\2-TO\2-Public\OUTILLAGE\PROJET_SERIE\EK_PROJECT\" & Projet & "\1-PLAN OUTIL\001-EN SERIE\[" & NWT & ".xlsm]DT PIECE'!$A$1:$T$999;14;FAUX);"")"


    ActiveWorkbook.Save
    End If

    End If

    Mais si on enlève le "=" la formule s’insère correctement mais ne fonctionne pas du coup...