Microsoft 365 VBA : formule Excel en variable

[Bernard38]

Bonjour le forum !

Malgré mes recherches je ne trouve pas de réponse...
Question : est-ce qu'il est possible de stocker dans une variable (de type string) une formule Excel que l'on peut ensuite envoyer dans une cellule ?
Alors pourquoi cette question idiote ? = j'ai un gros code VBA avec plusieurs tableaux de variables qui tourne. A la fin de la procédure, le tableau de variables contenant les résultats de différents calculs est envoyé sur une feuille Excel et l'utilisateur à la main pour faire des modifications sur certaines colonnes avant de revenir sur la macro (par formulaire). Or, j'aimerai qu'une des colonnes contienne une formule Excel SAUF QUE (je vous vois venir ) j'aimerai bien sûr ne pas balayer le fichier via un "range("xx").formula = ...." (sinon trop fastoche) car ce fichier contient plusieurs centaines de milliers de lignes alors bonjour le temps d'exécution (j'avoue, je n'ai même pas essayé pour voir le temps d’exécution, je suppose donc )

Exemple testé qui ne fonctionne pas :
(Oui j'ai quand même tenté avant d'arriver ici)

Sub testformuleEx()
Dim MaVarTest As String
i = 2
MaVarTest = "=ARRONDI(D" & i & "*E" & i & ";2)"
Range("F2") = MaVarTest
End Sub

à priori, le "Range("F2") = MaVarTest" il n'aime pas du tout et me renvoie une "Erreur 1004 : Erreur définie par l'application ou par l'objet".... (En fait il n'accepte tout simplement pas d'envoyer une variable commençant par un "="...
Si quelqu'un a une idée...
Si c'est trop usine à gaz je laisserai tomber mais j'aime bien creuser avant (à la place du gaz je trouverai peut être du charbon ! )

Merci par avance ++

 

[Bernard38]

Bonsoir,
Avec un fichier, peut-être qu'il serait plus facile pour les contributeurs de mieux comprendre ce que vous voulez, ne pensez-vous pas ?
Cordialement,

Oui je comprends mais le fichier est très lourd et confidentiel (boulot) et je ne crois pas que ça serait plus explicite. C’est pas grave je vais chercher une solution de ”contournement”
Merci Gégé, merci à tous

 

[Katido]

Bonjour à tous,

Je ne sais pas si j'ai bien compris le problème, ça m'arrive souvent...

Je pense que tu construis une formule sous forme de chaine avec VBA pour la paramétrer, et que tu veux l'exécuter comme si elle avait été entrée dans une cellule.

Si c'est ça, il faut impérativement que la syntaxe soit en anglais et que tu t'ìnspires du fichier joint.

Il faut créer les 2 fonctions très simples Execu() et ExecuChaine()
Execu() marche pour le contenu d'une cellule sans guillements ou pour une chaine du genre "5+6"
ExecuChaine() marche pour le contenu d'une cellule sous forme de chaine (je pense que c'est ton cas

Tu peux utiliser ces fonctions soit depuis une cellule (comme dáns le fichier dans la ligne 4) ou depuis VBA

 

[Bernard38]

Bonjour à tous,

Je ne sais pas si j'ai bien compris le problème, ça m'arrive souvent...

Je pense que tu construis une formule sous forme de chaine avec VBA pour la paramétrer, et que tu veux l'exécuter comme si elle avait été entrée dans une cellule.

Si c'est ça, il faut impérativement que la syntaxe soit en anglais et que tu t'ìnspires du fichier joint.

Il faut créer les 2 fonctions très simples Execu() et ExecuChaine()
Execu() marche pour le contenu d'une cellule sans guillements ou pour une chaine du genre "5+6"
ExecuChaine() marche pour le contenu d'une cellule sous forme de chaine (je pense que c'est ton cas

Tu peux utiliser ces fonctions soit depuis une cellule (comme dáns le fichier dans la ligne 4) ou depuis VBA

Bonsoir Katido
Merci pour ta réponse !
Oui c’est bien cela je crée la formule en mémoire sous format string dans un tableau de variables.
Et c’est vrai, première chose mettre tout en anglais car je l’avais entrée en français
Entre temps j’ai dû bosser sur autre chose et mettre ce pb en stand by mais dès que je m’y remets je regarde ton fichier joint.
Très bonne soirée

 

[Bernard38]

Bonjour à tous,

Je ne sais pas si j'ai bien compris le problème, ça m'arrive souvent...

Je pense que tu construis une formule sous forme de chaine avec VBA pour la paramétrer, et que tu veux l'exécuter comme si elle avait été entrée dans une cellule.

Si c'est ça, il faut impérativement que la syntaxe soit en anglais et que tu t'ìnspires du fichier joint.

Il faut créer les 2 fonctions très simples Execu() et ExecuChaine()
Execu() marche pour le contenu d'une cellule sans guillements ou pour une chaine du genre "5+6"
ExecuChaine() marche pour le contenu d'une cellule sous forme de chaine (je pense que c'est ton cas

Tu peux utiliser ces fonctions soit depuis une cellule (comme dáns le fichier dans la ligne 4) ou depuis VBA

Bonjour Katido
Je viens d'ouvrir et tester ton fichier. Déjà : pas de plantage ! Par contre il y a un truc que je ne dois pas faire correctement ou alors c'est le fait de passer par une variable... Pour être sûr j'ai refait le code en mettant Lign as double comme variable de numéro de ligne. Si je mets en D2 le chiffre 5 et en E2 le chiffre 32, F2 va bien afficher 160. Par contre si je mets en D2 5.01 et en E2 3.08 alors F2 affiche un #VALEUR au lieu du résultat (15.2805). A priori ce sont les chiffres à virgule qu'il n'aime pas...

Au debug MaVartest prend la valeur : "ROUND(D2*E2,2)"

Voici mon code revu avec ta fonction ExecuChaine(e) :

Sub testformuleEx()

Dim MaVarTest As String
Dim Lign As Double
Lign = 2
MaVarTest = "=ROUND(D" & Lign & "*E" & Lign & ",4)"
Debug.Print MaVarTest
Range("G2") = ExecuChaine(MaVarTest)
End Sub
Public Function ExecuChaine(e)
  Application.Volatile ' facultatif, mais permet que les modifs soient répercutées aussitôt
  ExecuChaine = Evaluate("=" & Evaluate("" & e & ""))
End Function

 

[Bernard38]

Bonjour à tous,
En fait en faisant juste ça :

Sub testformuleEx()

Dim MaVarTest As String
Dim Lign As Currency
Lign = 2
[B][COLOR=rgb(65, 168, 95)]MaVarTest = "=ROUND(D" & Lign & "*E" & Lign & ",4)"
Range("F2") = MaVarTest[/COLOR][/B]
End Sub

C'est à dire : formule en anglais correctement écrite (j'avais au départ mis un ; au de la , ) avec le signe = et au format string dans la variable. Lors de l'envoi de la variable sur Excel, la formule est bien comprise comme formule, mise en français et exécutée.
Merci à tous pour votre aide
Excellente journée à tous.

 

[Katido]

Bonjour,

Les nombres doivent être écrits avec la VIRGULE. Il faut mettre 5,01 et non 5.01
Sauf avec Eval !!!

Si tu n'as pas besoin d'exécuter tes formules comme des fonctions, tu peux faire tout simplement :
[A2].Formula = "=" & [A1]
Ça marche aussi en français en faisant[A2].FormulaLocal = "=" & [A1] si la formule en A1 a la syntaxe française

J'ai ajouté cette possiblité dans le fichier joint.

Attention dans tous les cas aux guillemets et à la virgule


Cette idée de paramétrer les formules n'est pas mauvaise en soit, mais elle présente 2 gros inconvénients :
- les adresses des cellules se trouvent figées, alors qu'Excel adapte automatiquement les adresses quand les cellules sont déplacées
- les mises à jour ne sont pas automatiques, sauf si on utilise les fonctions Execu ou ExecuChaine avec VOLATILE. Mais dans ce cas, les calculs sont refaits à tous les coups, même si on modifie une cellule qui n'a rien à voir, donc baisse de performances.

 

[mapomme]

Re ,

Les messages #2, #3 et #4, avait déjà donné la solution .

 

[Bernard38]

Bonjour,

Les nombres doivent être écrits avec la VIRGULE. Il faut mettre 5,01 et non 5.01
Sauf avec Eval !!!

Si tu n'as pas besoin d'exécuter tes formules comme des fonctions, tu peux faire tout simplement :
[A2].Formula = "=" & [A1]
Ça marche aussi en français en faisant[A2].FormulaLocal = "=" & [A1] si la formule en A1 a la syntaxe française

J'ai ajouté cette possiblité dans le fichier joint.

Attention dans tous les cas aux guillemets et à la virgule


Cette idée de paramétrer les formules n'est pas mauvaise en soit, mais elle présente 2 gros inconvénients :
- les adresses des cellules se trouvent figées, alors qu'Excel adapte automatiquement les adresses quand les cellules sont déplacées
- les mises à jour ne sont pas automatiques, sauf si on utilise les fonctions Execu ou ExecuChaine avec VOLATILE. Mais dans ce cas, les calculs sont refaits à tous les coups, même si on modifie une cellule qui n'a rien à voir, donc baisse de performances.

Re,
Autant pour moi, j'ai pas vu les "." et les ",".
Je veux justement éviter le "[A2].Formula = "=" & [A1]" car je ne veux justement pas partir dans une boucle par cellule. Mon tableau de variable va contenir en colonne 29 (équivalent AC sur Excel), sur 160 000 lignes la formule en format string pour chaque ligne et la totalité du tableau sera rapatrié sur Excel. Après c'est sûr il faut qu'Excel lance le calcul. Je mettrais un Application.Volatile en fin de procédure.

Pour info, voici la dernière version de mon code qui fonctionne avec un variable unique. Il faut que je le teste avec la copie du tableau entier...

Sub testformuleEx()

Dim MaVarTest As String
Dim Lign As Integer, Col1 As Integer, Col2 As Integer
i = 2 'Numéro de ligne
Col1 = 4 'Numéro de colonne 1
Col2 = 5 'Numéro de colonne 2
MaVarTest = "=ROUND(INDIRECT(ADDRESS(" & i & "," & Col1 & ",4))*INDIRECT(ADDRESS(" & i & "," & Col2 & ",4)),4)"
Debug.Print MaVarTest
Range("F2") = MaVarTest
End Sub

Le Debug donne ça : =ARRONDI(@INDIRECT(ADRESSE(2;4;4))*@INDIRECT(ADRESSE(2;5;4));4)
Effectivement la formule est figée sur Excel pour chaque ligne mais dans mon cas ça n'est pas problématique.

 

[Bernard38]

Re ,

Les messages #2, #3 et #4, avait déjà donné la solution .

Salut mapomme,
Tu as tout à fait raison ! Mais je suis parti sur le test de la réponse de Katido et c'est après que j'ai repensé à tester avec la formulation anglaise ! C'est d'ailleurs grâce à ta réponse que j'ai fait ma nouvelle formule

 

[mapomme]

Re,

Concernant la formule avec des références de type A1, on peut très bien l'affecter à une plage et Excel adaptera automatiquement la formule pour chaque ligne sans aucune boucle.

Exemple pour tester :

Sub Test()
Dim maFormuleLocale As String
   [a1] = 1 + Int(Rnd * 3)
   [a2] = 1 + Int(Rnd * 3)
 
   maFormuleLocale = "= a1 + a2"
   Range("a3:a50").FormulaLocal = maFormuleLocale
End Sub