Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 VBA : formule Excel en variable

Bernard38

XLDnaute Nouveau
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)

Code: 
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 ++
 
Solution
Bonjour à tous,
En fait en faisant juste ça :

VB: 
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.
Trier par date Trier par votes

Bernard38

XLDnaute Nouveau
Gégé-45550 à dit:
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,
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
K

Katido

XLDnaute Occasionnel
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
 

Pièces jointes

  • ExecutionCellule.xlsm
    16.3 KB · Affichages: 3
Vote positif 0 Vote négatif

Bernard38

XLDnaute Nouveau
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
 
Vote positif 0 Vote négatif

Bernard38

XLDnaute Nouveau
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) :
Code: 
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
 
Vote positif 0 Vote négatif

Bernard38

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

VB: 
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.
 
Vote positif 0 Vote négatif
Solution
K

Katido

XLDnaute Occasionnel
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.
 

Pièces jointes

  • ExecutionCellule.xlsm
    20.4 KB · Affichages: 1
Vote positif 0 Vote négatif

Bernard38

XLDnaute Nouveau
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...

VB: 
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.
 
Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc
Supporter XLD
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 :
VB: 
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
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

M
  • Question
XL 2021 VBA Excel Erreur 2023
Réponses
2
Affichages
184
MICHIS
M
  • Question
Microsoft 365 vba : pb format de variable
Réponses
1
Affichages
323
wDog66
N
  • Résolu(e)
Microsoft 365 Ecrire une formule de calcul en Vba Excel
24 25 26
Réponses
385
Affichages
10 K
TooFatBoy
P
  • Résolu(e)
XL 2013 EXCEL VBA
Réponses
2
Affichages
329
pangasoul
P
F
  • Question
Microsoft 365 aide VBA excel
Réponses
4
Affichages
302
Oneida
J
  • Question
XL 2016 formule excel en vba
Réponses
6
Affichages
373
Jorelle27
J
  • Question
Microsoft 365 Variable tableau : coller uniquement une partie des colonnes
2 3
Réponses
31
Affichages
1 K
BenHarber
M
  • Question
XL 2021 Colorer avec condition de temps
2
Réponses
27
Affichages
717
matlatarte
M
  • Question
XL 2019 Une formule sur Excel qui me permis de calculer le nombre de jours de congé avec queqlques conditions
Réponses
4
Affichages
149
job75
J
  • Question
Microsoft 365 VBA et mise en forme conditionel
Réponses
2
Affichages
339
jalaba
J
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…