Microsoft 365 VBA : formule Excel en variable

Bernard38 · 12 Juillet 2024

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 ++

Bernard38 · 17 Juillet 2024

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.

wDog66 · 12 Juillet 2024

Bonjour Bernard38

C'était presque ça pour moi

VB:

Range("F2").FormulaLocal = MaVarTest

A tester

Le message est normal, Range("F2") est un objet auquel vous voulez attribuer un string

Gégé-45550 · 12 Juillet 2024

wDog66 à dit:
Bonjour Bernard38

C'était presque ça pour moi

VB:

Range("F2").Formula = MaVarTest

A tester

Le message est normal, Range("F2") est un objet auquel vous voulez attribuer un string

Bonjour,
Avec 365, c'est presque mieux d'écrire :

VB:

Range("F2").Formula2 = MaVarTest

surtout si l'on utilise des tableaux structurés.
Cordialement,

mapomme · 12 Juillet 2024

Bonsoir à tous ,

Attention!

VB:

MaVarTest = "=ARRONDI(D" & i & "*E" & i & ";2)"
Range("F2").Formula = MaVarTest

Ne fonctionnera pas. Formula s'attend à traiter une formule avec la langue native de VBA donc en l'américain (version outre-atlantique du grand breton). On écrira donc :

VB:

MaVarTest = "=ROUND(D" & i & "*E" & i & ",2)"

Si vous voulez-vous utiliser une formule en français, on écrira :

VB:

MaVarTest = "=ARRONDI(D" & i & "*E" & i & ";2)"

Remarquez qu'en anglais, notre point-virgule se traduit par une virgule (et vice-versa).

Bernard38 · 12 Juillet 2024

Merci wDog et Gégé
Mais justement c'est ça que je ne veux pas faire... car ça signifie faire une boucle sur le fichier Excel et c'est long.
En fait pour comprendre, je voudrais que mon tableau de variable contienne une formule Excel sur toute une colonne et que, quand le tableau est copié dans Excel, la variable soit "traduite" en formule...

Bernard38 · 12 Juillet 2024

Bonsoir mapomme,
Ah il faudra que j'essaye... Comme la formule est entrée en string dans la variable je pensais qu'il fallait l'écrire exactement comme quand on la saisie dans Excel...

wDog66 · 12 Juillet 2024

Bernard38 à dit:
Merci wDog et Gégé
Mais justement c'est ça que je ne veux pas faire... car ça signifie faire une boucle sur le fichier Excel et c'est long.
En fait pour comprendre, je voudrais que mon tableau de variable contienne une formule Excel sur toute une colonne et que, quand le tableau est copié dans Excel, la variable soit "traduite" en formule...

Désolé, mais pour moi ce n'est pas possible sans boucle

Utiliser une variable tablo, inscrira du texte, point barre

mapomme · 12 Juillet 2024

Re @Bernard38 ,

Un peu mal à comprendre ce que vous désirez. Une fois le tableau chargé, si vous voulez utiliser une formule (variable ou pas) pour une colonne, il faudra (par la porte ou par la fenêtre) qu'Excel recalcule la formule sur toutes les lignes. Ce qui est long c'est le recalcul et non pas l'introduction de la formule qui se fait par une seule instruction.

VB:

Sub testformuleEx()
Dim MaVarTest As String
   MaVarTest = "=ARRONDI(D2*E2;2)"
   Range("F2:F400000").FormulaLocal = MaVarTest
End Sub

Bernard38 · 12 Juillet 2024

mapomme à dit:
Re @Bernard38 ,

Un peu mal à comprendre ce que vous désirez. Une fois le tableau chargé, si vous voulez utiliser une formule (variable ou pas) pour une colonne, il faudra (par la porte ou par la fenêtre) qu'Excel recalcule la formule sur toutes les lignes. Ce qui est long c'est le recalcul et non pas l'introduction de la formule qui se fait par une seule instruction.

VB:

Sub testformuleEx() Dim MaVarTest As String MaVarTest = "=ARRONDI(D2*E2;2)" Range("F2:F400000").FormulaLocal = MaVarTest End Sub

En fait, mon tableau de variables est complété par boucle en fonction d'une recherche sur un autre tableau. Tout se passe en mémoire, c'est rapide. Aussi, je pensais que, pour une des colonnes de mon tableau, le fait d'entrer un texte de style "=ARRONDI(D" & i & "*E" & i & ";2)" (ou i correspond à un numéro de ligne) lors de la l'envoie du tableau entier dans Excel, ce texte ""=ARRONDI(D" & i & "*E" & i & ";2)"" serait automatiquement traduit en formule par Excel. J'ai fait le test sur l'exemple que j'ai donné et ça ne fonctionne pas.

Bernard38 · 12 Juillet 2024

Au pire, je rajoute un caractère devant le "=" pour que ça reste du texte et une fois le tableau de variables copié, je fais sur Excel un 'rechercher/remplacer" ça devrait être plus rapide qu'une boucle...

Bernard38 · 12 Juillet 2024

wDog66 à dit:
Désolé, mais pour moi ce n'est pas possible sans boucle

Utiliser une variable tablo, inscrira du texte, point barre

Et ben c'est là que c'est assez dingue, c'est que l'expression avec un "=" n'est plus considéré comme un texte et produit une erreur... Je pensais justement que ça se copierait et serait traduit en formule ensuite... et ben non

Bernard38 · 12 Juillet 2024

wDog66 à dit:
Bonjour Bernard38

C'était presque ça pour moi

VB:

Range("F2").FormulaLocal = MaVarTest

A tester

Le message est normal, Range("F2") est un objet auquel vous voulez attribuer un string

wDog66 à dit:
Le message est normal, Range("F2") est un objet auquel vous voulez attribuer un string

Ben non : si je fais un range("F2")= MaVarTest et que MaVarTest = "toto", la cellule F2 récupère bien "toto" en valeur

wDog66 · 12 Juillet 2024

Re,
Et bien continuez ainsi alors

Bernard38 · 12 Juillet 2024

wDog66 à dit:
Re,
Et bien continuez ainsi alors

... désolé si j’ai dit un truc qu’il ne fallait pas ? Je n’ai pas l’intention de m’entêter à ”continuer comme ça” puisque ça ne fonctionne pas .. mais comme je ne prétend pas tout savoir en vba je pose une question sur un forum spécifique au cas où quelqu'un aurait une réponse. Merci par ailleurs de ta participation. S’il n’y a pas de solution c pas grave je me débrouillerai autrement

Gégé-45550 · 12 Juillet 2024

Bernard38 à dit:
... désolé si j’ai dit un truc qu’il ne fallait pas ? Je n’ai pas l’intention de m’entêter à ”continuer comme ça” puisque ça ne fonctionne pas .. mais comme je ne prétend pas tout savoir en vba je pose une question sur un forum spécifique au cas où quelqu'un aurait une réponse. Merci par ailleurs de ta participation. S’il n’y a pas de solution c pas grave je me débrouillerai autrement

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,

Microsoft 365 VBA : formule Excel en variable

Bernard38

XLDnaute Nouveau

Bernard38

wDog66

XLDnaute Occasionnel

Gégé-45550

XLDnaute Accro

mapomme

XLDnaute Barbatruc

Bernard38

XLDnaute Nouveau

Bernard38

XLDnaute Nouveau

wDog66

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

Bernard38

XLDnaute Nouveau

Bernard38

XLDnaute Nouveau

Bernard38

XLDnaute Nouveau

Bernard38

XLDnaute Nouveau

wDog66

XLDnaute Occasionnel

Bernard38

XLDnaute Nouveau

Gégé-45550

XLDnaute Accro

Discussions similaires