déclencher une macro (ecriture) à partir d'une formule

[gabyg]

Bonjour à tous,

Je souhaite déclencher une macro qui me renvoie le résultat d'un calcul dans une cellule précise et qui se déclenche à partir d'une formule.

J'ai trouvé pas mal de réponse sur ce sujet mais aucune ne correspondant exactement à mon cas.
J'ai dans ma cellule A1 une formule "linkée" avec un autre logiciel (lien DDE je crois). Ma formule est figée mais le résultat change en permanence avec le temps, passant de 100 à 90 à 110 à 150,...
Pour moi ce résultat est aléatoire.
Je souhaiterais faire ceci: si à un moment donné, ma cellule A1 vaut 100, cela déclenche un calcul prenant en compte différents paramètres dans différentes cellules et me renvoyant le résultat en B1.

Pour cela, je pensais faire en A2 par exemple une formule du type:
=si(A1=100;MaMacro();0)
et MaMacro() effectuerait le calcul que souhaite et irait au final écrire en B1 le résultat.

Mais je rencontre un problème à ce niveau. J'ai l'impression qu'à partir d'une formule on ne peut pas appeler une macro qui va modifier la valeur d'une cellule. J'arrive à obtenir des Msgbox ou à lire, mais écrire des données dans une cellule, je n'y arrive pas. Dès lors que je place par exemple workbooks.("Classeur1.xls").worksheets("Feuil1").range("B1").value=resultat dans MaMacro(), et bien je n'obtiens pas le résultat en B1 et en A2 où je fais appelle à MaMacro(), j'ai #VALEUR.
Ici j'arrive donc à lancer une macro mais qui ne fait pas ce que je souhaite.

J'ai essayé aussi avec Worksheet_Change mais là aussi problème. J'ai l'impression que pour lui il ne s'agit que de formule donc, pas de changement et donc dans ce cas pas de déclenchement possible.

Je ne suis pas expert d'excel et de vba donc je passe peut être à côté de solutions simples. Aussi je ne suis pas sûr d'avoir été bien clair, demandez moi s'il vous faut plus de détails.

Un grand merci pour toute votre aide

 

[wilfried_42]

Re : déclencher une macro (ecriture) à partir d'une formule

bonjour

c'est exacte, une fonction renvoie une valeur donc toutes les actions affectant une cellule sont à proscrire
si tu mets ta formule dans la cellule concernée, le calcul est perdu à la prochaine mise à jour... donc ce n'est pas la solution

mais tu peux peut être utiliser, non pas un fonction mais un Sub d'interruption
Si ta cellule est modifiée en permanence, elle provoque une interruption : le code est à placer dans la feuille concernée

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

 

[Compte Supprimé 979]

Re : déclencher une macro (ecriture) à partir d'une formule

Salut Gabyg et bienvenu sur ce forum
Salut Wilfried_42

Je n'ai pas totu compris, mais pourquoi ne pas utiliser une fonction personnalisée !?

Dans ta cellule B1, tu mets ta formule : =SI(A1=100;MaFonction(A1;C1;D1);0)

Et dans un "Module", tu mets ta fonction

Function MaFonction(MaVal1, MaVal2, Maval3)
  ' Pour permettre le calcul en temps réel
  Application.Volatile
  MaFonction = (MaVal1 * MaVal2) + Maval3
End Function

Comme ça en B1 tu as le résultat de ton calcul

A+

 

[BOISGONTIER]

Re : déclencher une macro (ecriture) à partir d'une formule

Bonjour,

Avec Calculate()

Private Sub Worksheet_Calculate()
  If [A6] > 100 Then
     MsgBox " A6 Sup à 100"
  End If
End Sub

JB

 

[gabyg]

Re : déclencher une macro (ecriture) à partir d'une formule

Bonjour et un grand merci à tous!!
Je vais tester ce soir vos différentes solutions, je n'ai pas trop le temps en journée...

Rapidement pour répondre à BrunoM45 et BOISGONTIER, le problème est que le résultat ne sera que passager. Je souhaiterais conserver la valeur de mon calcul: si à t=0 A1=100 ->1er calcul avec certains paramètres (qui bougent aussi automatiquement) -> 1er résultat (conserver par exemple en C1), si à t=4 A1 aussi =100 -> 2eme calcul avec d'autres paramètres -> 2eme résultat en C2, etc...
Un Msgbox ou une fonction vont bien me donner le résultat mais seulement temporairement, je ne peux pas le conserver.
Merci également Wilfried 42 pour ta réponse. Je ne suis pas sûr de comprendre exactement pour le moment. Je verrai ce soir.

Merci encore

 

[gabyg]

Re : déclencher une macro (ecriture) à partir d'une formule

Peut être pour donner un exemple plus parlant:

Admettons qu'en A1 j'ai pu définir une formule qui va me donner un nombre aléatoire entre 0 et 200 à chaque seconde.
Je souhaite savoir combien de fois j'ai eu A1=100 sur une heure de temps.
Comment faire sans utiliser l'itérateur d'excel (qui fonctionne pour ça mais mon calcul est en fait plus élaboré)?

Un Worksheet_Change ne semble pas utile à priori puisqu'en A1, il s'agit d'une formule donc pas de modification -> pas de declenchement de calcul.
Une solution comme Bruno45 me conseille est bonne puisqu'elle déclenche le calcul.
Mais elle ne conserve aucune mémoire. Je ne vois pas comment définir une méthode qui s'appuie sur sa valeur antérieure, comme un itérateur. Je ne crois pas que je puisse définir MaFonction avec MaFonction = MaFonction +1. Si je pouvais définir MaFonction comme ceci:

Function MaFonction(cellule as range)
Application.Volatile
dim i as integer
i=cellule.value
cellule.value=i+1
MaFonction = 0
End Function

Alors dans cellule, j'aurais mon résultat.
Mais malheureusement je crois qu'on ne peut pas faire ça avec une fonction

 

[wilfried_42]

Re : déclencher une macro (ecriture) à partir d'une formule

re:

le formule que bruno t'a suggéré, provoque un calcul, donc l'interruption qu'à donnée boisgontier (calculate) est lancée. c'est une procedure, la cellule peut etre mise à jour

 

[gabyg]

Re : déclencher une macro (ecriture) à partir d'une formule

Merci énormément, ça fonctionne avec calculate!!


Juste pour en savoir un peu plus sur Worksheet_calculate() et finalement un peu moins dans l'ensemble....
Cette méthode est appelée dès lors qu'excel réalise un calcul sur la feuille, comme une formule, c'est ça? Elle n'est pas appelée quand on fait appele à une fonction, une macro ou autre?

Cordialment

 

[wilfried_42]

Re : déclencher une macro (ecriture) à partir d'une formule

re:

pour les fonctions personalisées, pas de problemes
pour les macro, si tu mets à jours une cellule qui provoque un calcul par formule, l'interruption est lancée
pour pallier à ce qui pourrait devenir un probleme

mettre dans la macro :
avant les mises à jour

Application.enableevents = false

apres les mises à jour

Application.enableevents = true