Formule excel en VBA

Version imprimable

13/06/2019, 16h19
Shaplow

Formule excel en VBA

Bonjour,

J'ai une formule excel qui marche très bien: =IF(E3 - D3)< 1/24; 1/24; E3 - D3) seulement j'aimerai la convertir pour l'implanter dans une macro en VBA, ce qui me donne:

Code:

=IF((" & Range("E" & i).Address(0, 0) & "-" & Range("D" & i).Address(0, 0) & ")< 1/24; 1/24;" & Range("E" & i).Address(0, 0) & "-" & Range("E" & i).Address(0, 0) & ")

Mais cela ne fonctionne pas, pourquoi ?

Bonne journée

Bonjour,

En anglais, le séparateur d'attribut dans les formules est la virgule (et non pas le point-virgule)

D'autre part pourquoi utiliser address ?
Autant faire simple :
Code:

1 2 3 4 5 6 7 8 9 10 Option Explicit Sub Test() Dim f As String Dim i As Long Dim c As Range i = 3 f = "=IF((E" & i & "-D" & i & ")< 1/24, 1/24," & "E" & i & "-D" & i & ")" Set c = ActiveSheet.Range("F3") c.Formula = f '"=IF((E3-D3)< 1/24, 1/24,E3-E3)" End Sub
Edit : Correction en rouge (merci Menhir)

13/06/2019, 17h58
Menhir

Citation:

Envoyé par Shaplow

J'ai une formule excel qui marche très bien: "=IF(E3 - D3)< 1/24; 1/24; E3 - D3)

Plus simple : =MAX(1/24;E3-D3).

Il y a aussi un petit détail qui me surprend : tu utilises les fonction anglophones ( IF ) mais les séparateur francophones ( ; )... :koi:

Citation:

Envoyé par Patrice740

Autant faire simple :

Bonjour Patrice,
Je me permets de corriger juste un détail dans ta formule (un D à la place d'un E).

Code:

f = "=IF((E" & i & "-D" & i & ")< 1/24, 1/24," & "E" & i & "-D" & i & ")"

C'est la formule qui convient si on veut l'implanter dans une cellule en utilisant VBA.

En revanche, si on veut l'évaluer dans VBA, par exemple avec un Evaluate, ce sera plutôt :

Code:

"IF(" & Cells(i, "E") & "-" & Cells(i, "D") & "<1/24,1/24," & Cells(i, "E") & "-" & Cells(i, "D") & ")"

ou en pur VBA sans Evaluate :

Code:

IIf((Cells(i, "E") - Cells(i, "D") < 1/24, 1 / 24, Cells(i, "E") - Cells(i, "D"))

ou encore, en utilisant ma simplification de départ avec un WorksheetFunction :

Code:

WorksheetFunction.MAX(1 / 24, Cells(i, "E") - Cells(i, "D"))
14/06/2019, 09h58
Shaplow

Citation:

Envoyé par Menhir

Il y a aussi un petit détail qui me surprend : tu utilises les fonction anglophones ( IF ) mais les séparateur francophones ( ; )... :koi:

Je ne connais pas ce langage du coup je ne savais même pas qu'il fallait faire la différence entre fonctions anglophones/françaises etc ! En tout cas merci pour votre aide j'ai pu régler mon problème :)
14/06/2019, 10h43
Menhir

Citation:

Envoyé par Shaplow

Je ne connais pas ce langage du coup je ne savais même pas qu'il fallait faire la différence entre fonctions anglophones/françaises etc !

Je ne parlais pas de la tentative d'interprétation en VBA mais de la formule qui marche très bien.
14/06/2019, 10h54
Patrice740

Bonjour,

Citation:

Envoyé par Shaplow

Je ne connais pas ce langage du coup je ne savais même pas qu'il fallait faire la différence entre fonctions anglophones/françaises etc !

C'est tout simplement une question de logique :
- Quand tu utilises une formule anglophone (.Formula), il faut utiliser la syntaxe anglophone (avec des ,)
- Quand tu utilises une formule francophone (.FormulaLocal), il faut utiliser la syntaxe francophone (avec des ; )
et il parait évident que lorsqu'on mélange les deux, Excel ne comprend pas.
14/06/2019, 11h07
MarcelG

Bonjour à vous,

Pour ces raisons, j'ai banni, récemment j'en conviens, la méthode FormulaLocal.

A toute fin utile, une astuce, de Philippe je crois (hé salut!):
Pour connaître la formule écrite en langage VBA
- l'écrire dans le tableur (exemple en "A1")
- dans l'éditeur VBE, afficher la fenêtre Exécution (CTRL+G)
- écrire

Citation:

?Range("A1").Formula

(ne pas oubier "?")
- activer la touche Entrée
14/06/2019, 11h46
Menhir

Citation:

Envoyé par MarcelG

- dans l'éditeur VBE, afficher la fenêtre Exécution (CTRL+G)

Perso, je la garde ouverte en permanence.
Elle sert aussi bien à vérifier des valeurs en cours de code (ou à afficher des messages sur l'avancement d'une exécution) avec des Debug.Print qu'à tester des syntaxes en les tapant directement dedans sans avoir besoin de créer une instruction et de la lancer.

Un outil indispensable pour développer.
14/06/2019, 13h06
Shaplow

Re,

Merci de vos explications, j'ai un autre problème sur une autre formule maintenant: =SOMME(N18;O18+PRODUIT(O18;0,25);P18 + PRODUIT(P18;0,5);PRODUIT(Q18;2))Ça c'est la formule que j'ai faîte pour voir si elle fonctionnait, mais maintenant j'aimerai remplacer mes cellules N18, O18, P18 et Q18 par des colonnes. En gros je veux un résultat total qui additionne:
=SOMME(N3;O3+PRODUIT(O3;0,25);P3 + PRODUIT(P3;0,5); PRODUIT(Q3;2)) + =SOMME(N4;O4+PRODUIT(O4;0,25);P4 + PRODUIT(P4;0,5); PRODUIT(Q4;2)) + =SOMME(N5;O5+PRODUIT(O5;0,25);P5 + PRODUIT(P5;0,5); PRODUIT(Q5;2)) etc etc
J'aimerai commencer à la ligne 3 jusqu'à la fin du tableau ou de la colonne entière peu importe mais je ne sais pas comment faire !

Bonne journée !
14/06/2019, 13h34
MarcelG
Salut,

Pour une formulation sur une colonne entière.

Je te conseillerais de procéder en 3 étapes
- Sur le tableur Excel, écrire une formule avec résultat acceptable sur la 1ère ligne
- Procéder à sa transformation en langage VBA comme je l'ai indiqué plus avant (par la fenêtre Exécution)
- Ecrire cette formulation sur toute la colonne
Code:

1 2 Const laformule As String = "=A2*B2" Worksheets("lawks").Range("C2:C250").Formula = laformule
Personnellement, c'est le processus que je suis toujours avec un collage spécial de valeurs soit
Code:

1 2 3 4 5 6 7 [Sub fo() Const laformule As String = "=A2*B2" With ThisWorkbook.Worksheets("lawks").Range("C2:C250") .Formula = laformule .Value = .Value End With End Sub
Si maintenant, tu souhaites le produit des colonnes, tu peux utiliser la fonction

Citation:

SommeProd
14/06/2019, 15h10
Menhir

Citation:

Envoyé par Shaplow

=SOMME(N18;O18+PRODUIT(O18;0,25);P18 + PRODUIT(P18;0,5);PRODUIT(Q18;2))

Plus simple : =N18+(O18*1,25)+(P18*1,5)+(Q18*2).
Etant donné que la multiplication est prioritaire sur l'addition, les parenthèses ne sont pas utiles mais je les laisse pour la compréhension.

Citation:

j'aimerai remplacer mes cellules N18, O18, P18 et Q18 par des colonnes. En gros je veux un résultat total qui additionne:
=SOMME(N3;O3+PRODUIT(O3;0,25);P3 + PRODUIT(P3;0,5); PRODUIT(Q3;2)) + =SOMME(N4;O4+PRODUIT(O4;0,25);P4 + PRODUIT(P4;0,5); PRODUIT(Q4;2)) + =SOMME(N5;O5+PRODUIT(O5;0,25);P5 + PRODUIT(P5;0,5); PRODUIT(Q5;2)) etc etc
J'aimerai commencer à la ligne 3 jusqu'à la fin du tableau ou de la colonne entière peu importe mais je ne sais pas comment faire !

Il suffit de faire une formule matricielle.
Si ton tableau se termine en ligne 20 :

Code:

=SOMME(N3:N20+(O3:O20*1,25)+(P3:P20*1,5)+(Q3:Q20*2)

A valider avec Ctrl+Shift+Entrée comme toute fonction matricielle.