Pb de recalcul des fonctions

Question

Bonjour,

Voila mon soucis:

J'ai mon sub evenementiel tel que:

    Public x as double
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    x = Sheets("Feuil1").Range("a1")
    End Sub

J'ai ensuite dans un module cette fonction:

    Function test(y As Double)
    test = y * x
    End Function

Le probleme est que, lorsque x est modifié, le résultat de la fonction test n'est pas actualisé dans les cellules . Je vois pas comment faire, ca a pourtant l'air simple.
J'aimerai bien avoir votre avis.

fiko81 · Answer

Salut, c'est quoi y dans ta fonction et quand est-ce que tu l'appelles ta fonction test ? Fiko ;-) La reponse vous convient pensez > Accepter <

cs_kabja · Answer

Dans la fonction, ya rien d'autre que ce que j'ai cité, c'est surtout pour comprendre le fonctionnement:
Ma fonction test, c'est donc uniquement:

  Function test(y As Double)
   test = y * x
   End Function

Cette fonction utilise la variable public x, qui est modifiée de temps en temps par:
Private Sub Worksheet_Change(ByVal Target As Range)
    x = Sheets("Feuil1").Range("a1")
    End Sub

Le problème, c'est que quand la valeur de cette variable est modifiée, les cellules dans laquelle la fonction test est appelée ne sont pas actualisée.

jmfmarques · Answer

Bonjour,

Il est clair qu'une fonction ne peut retourner de valeur si elle n'est pas appelée (en lui passant les paramètres qu'elle attend en entrée) .!

regarde, analyse et comprends :

Private Sub Command1_Click()
  Dim toto As Integer, titi As Integer
  toto = 2
  titi = 3
  MsgBox test(toto, titi)
End Sub


Private Function test(x As Integer, y As Integer) As Integer
  test = x * y
End Function

cs_kabja · Answer

Merci de ta réponse, mais regarde bien ce que je demande:

Je voudrais utiliser des variables publiques. Il n'est pas necessaire de les appeler dans la liste d'arguments pour la fonction puisse s'en servir. Le probleme est surtout que la fonction ne se rends compte de rien quand seul la variable publique change.

Public x as double

Private Sub Worksheet_Change(ByVal Target As Range)
x = Range("A1").value
End Sub

Function test(y As Double)
test = y * x
End Function

y est un argument que la fonction va chercher dans le tableur, mais x est une variable dont la valeur lui a déjà été affecté précedemment.

jmfmarques · Answer

Oui ?
Et alors ?
Tu n'as donc à lui passer qu'un seul paramètre en entrée, s'il connait déjà l'autre ...
Et Alors ?
Celà ne change rien au mécanisme d'utilisation d'une fonction !
T'attends quoi ? Que j'écrive exactement ce dont tu as besoin en fonction de ton contexte ? pour que tu le copies sans comprendre ?
C'est loin d'être ma tasse de thé, pardonne-moi !!!
Un autre que moi, alors ...

cs_kabja · Answer

Tu sais lire, ou bien?
Je t'explique que lorsque l'on change la valeur de A1 (donc de x, si tu suis), le résultat dans la cellule où j'appelle la fonction n'est pas actualisé. C'est le problème, tu comprends.
Mais, laisse tomber, c'est pas grave si tu sais pas. Je ne m'adresse pas à toi en particulier.

jmfmarques · Answer

Ah cà, pour comprendre, oui, je comprends, pardi ...


Je comprends entre autres qu'à moins de faire une fonction Excele personnalisée ( ce qui n'a rien à voir avec une fonction VBA telle que tu l'as écrite... donc dans ta macro...) :


Ta fonction VBA ne s'exécutera que si appelée, avec ses paramètres d'entrée...


Il te faudra donc la lier à un événement de ta feuille.


Reste, sans doute, à rechercher, si tu veux éviter ce mécanisme, comment créer une fonction personnalisée et l'enregistrer dans Excel.


Tu vois ? Je comprends, moi (bien que n'ayant ni VBA ni Excel)... 

En d'autre termes : ne pas confondre une fonction Excel avec une fonction VBA.
Vu ?

jmfmarques · Answer

Tiens (bien que ne connaissant rien à Excel) :

http://www.top-assistante.com/bureau/excel/fonctionperso.php

Lis et fais, alors !

mortalino · Answer

jmfmarques à 100% raison, même si t'as valeur est public, la moindre des choses c'est de passer cette valeur en paramètre, c'est plus conventionnel..

Public x as double

Private Sub Worksheet_Change(ByVal Target As Range)
x = Range("A1").value
End Sub

Function test(y As Double, x2 As Double) As Double
test = y * x2
End Function

Si tu fais MsgBox Test(2.5, x)
Le résultat sera bien calculé, quelque soit la valeur de x (bref, cette valeur à beau changer, elle sera prise en compte) 

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

mortalino · Answer

Ah et tant qu'à faire bien les choses :

Private Sub Worksheet_Change(ByVal Target As Range)    If Target [A1] Then x Range("A1").value
End Sub

Cela t'évitera d'affecter à chaque fois x à la valeur de la cellule A1, quand ce n'est pas elle qui change !

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_kabja · Answer

C'est sur, mais dans ce cas, il n'y a plus d'interet à mettre cette variable en public, s'il faut tout de même aller la chercher dans le tableur. Je voulais faire ca parce que j'ai 10 arguments a mettre dans une fonction, mais avec seulement 2 qui changent souvent. Du coup, je voulais leur attribuer une valeur dans une variable publique (qui ne changerait que de temps en temps) pour ne pas avoir à aller les chercher dans le tableur.

cs_kabja · Answer

Je veux dire, pour les 8 autres, je voulais qu'elle soit a disposition en variable public sans avoir a aller les chercher a chaque fois. Voila.

mortalino · Answer

bah j'ai dit que c'était plus conventionnel, mais si tu y tiens passe toi de ce param Mais pour ton problème de réactualisation de tes autres cellules utilisant cette fonction, c'est pas vraiment comme ça qu'il faut s'y prendre. Pourquoi ne pas faire tout simplement : Private Sub Worksheet_Change(ByVal Target As Range) ~~x = Range("A1").value~~ End Sub Function test(y As Double) test = y * Range("A1").Value End Function @++

( Nouveau forum : Exclusivement Office & VBA

mortalino · Answer

Function test(y As Double) As Double

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

jmfmarques · Answer

Kabja (et s'il te plait ne viens pas nous expliquer que l'on ne sait pas lire) :

Pouyr résumer : ou tu fais une fonction Excel personnalisée (et c'est une autre paire de manche)
ou tu te sert d'une fonction dans un module et alors (quel que soit le cas de figure) : il ne se passera rien si tu n'appelles pas cette fonction à l'occasion d'un événement. C'est là le point essentiel et Mortalino t'a suggéré un événement.

Pour le reste (les paramètres d'entrées) rien ne t'oblige à passer à cette fonction les paramètres dpnt la valeur existerait déjà en public ! Il te suffit de ne passer que les paramètres manquants, si celà t'arrange.
Une chose reste certaine : il te faudra lier la fonction de ton module à un événement (à moins, une fois de plus, que tu ne crées une fonction Excel personnalisée et tout se passera alors automatiquement... mais, dans ce derniers cas, ta fonction personnalisée Excel ignorera totalement les variables VBA et ne s'exécutera qu'en lui indiquant les cellules absolues ou relatives concernées, d'une part.. et, d'auttre part, sera insérée dans Excel et donc dans tous tes classeurs créés ou à venir...)

La chose est simple, donc : 
- soit une fonction VBA à laquelle tu passes les paramètres qu'elle ignore encore, que tu lances à l'occasion d'un événement et qui te retourne une valeur
- soit le fardeau d'une fonction personnalisée Excel à créer et que tu trimbaleras partout.


Choix à faire (je choisirais personnellement le 1er)

cs_kabja · Answer

Merci de ta patience,

La solution que tu proposes fonctionne tres bien, mais ca ne regle pas mon probleme:

Function test(y As Double)
    test = y * Range("A1").Value
End Function

 Dans cette fonction, il faut toujours aller chercher la valeur de A1 dans le tableur. Or, je me sers de cette fonction plus de 30000 fois à chaque recalcul, c'est pour ca que mon idée était justement d'alléger le calcul en mettant à disposition les valeurs qui changent rarement en variable public (pour éviter d'avoir a aller les chercher). 

Néanmoins, je sais pas si c'est possible.

mortalino · Answer

Function test(y As Double, MyCell As Range) As Double
    test = y * MyCell.Value
End Function

Voilà, problème résolu

Si dans C3 tu as =test(1.5;A1)
Et dans A1, tu as 2, C3 t'affichera 3. Passe maintenant A1 à 3, tu auras 4.5

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_kabja · Answer

"Pour le reste (les paramètres d'entrées) rien ne t'oblige à passer à
cette fonction les paramètres dpnt la valeur existerait déjà en public
! Il te suffit de ne passer que les paramètres manquants, si celà
t'arrange."

C'est justement là qu'est tout le problème, la fonction ne "voit pas" que les paramètres en public changent. Pour ceux que j'appelle en argument, pas de pb, le resultat s'actualise. Mais si les variables public changent, il ne se passe rien, même en recalculant (F9).

Il doit y avoir quelque chose qui m'échappe.. Tant pis, merci de votre aide.

jmfmarques · Answer

C'est bien évidemment possible !
Rien ne te l'interdit, puisque x est public !
Tu pourrais donc, si x est punlic, écrire à ton chois :

Function test(y As Double) as double
    test =  y *  Range("A1").Value 
End Function

ou

Function test(y As Double, x as double) as double
    test  = y * x
End Function

ou encore

Function test(y As Double) as double
    test = y * x
End Function

les 3 seront comprises puisque x est public.
Mais aucune ne s'exéutera "automatiquement" sans être subordonnée à un événement ...
Voilà ...

cs_kabja · Answer

Function test(y As Double) as double
    test = y * x
End Function

C'est celle-ci qui me plait. Qd tu parles d'evenement, le fait que la valeur de x change (dans ma cellule A1, et donc aussitot dans ma variable publique x) constitue t'il un evenement? Parce que justement je ne vois pas comment. C'est le coeur de mon probleme. Même un recalcul ne fait rien. Quel type d'evenement puis-je utilisé pour que ma fonction soit recalculer si x change.

mortalino · Answer

Même un recalcul ne fait rien





Mais t'as essayer ce que je t'ai mis!!!!
Avec le param MyCell As Range.. C'est ça qu'il te faut !





@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_kabja · Answer

Et moi, je te dis depuis le début que je ne veux pas d'autres arguments à entrer que y. C'est pourtant clair.


                
                Function test(y As Double) as double
    test = y * x
End Function

c'est juste ca que je veux.

mortalino · Answer

Bah je modifie ma réponse (excuse moi c'est ma faute) 

Mais t'as essayer ce que je t'ai mis!!!! (ce qu'il manquait --> )  Car c'est la seule possibilité pour que cela rafraichisse tes données avec la fonction dans tes autres cellules. (du moins le plus simplement du monde).

Pas de Param, bah débrouille toi sans.. Mais si tu as comme tu dis 3000 cellules contenant cette fonction, ça implique un minimum de 3000 lignes dans l'évènement WorkSheet_Change

A toi de voir, si tu veux faire très chiant et compliqué, ou d'une façon très simple.

Bonne journée

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_IAORANA · Answer

Salut,
Alors soit je suis dans le cas de jmfmarques, soit je ne comprend vraiment rien. Mais comme j'ai Excel et VBA, j'ai pu au moins tester le problème.

J'ai mis dans la cellule A1 une valeur. Dans les cellules B1 à B9, j'ai mis les valeurs 1 à 9. Dans les cellules C1 à C9 la formules en question : =test($A$1; B1) dans la première et copié dans les autres. La formule doit bien avoir deux variables pour fonctionner. Et voici le module à créer : 

Option Explicit
    Dim x As Double
    Function test(x As Double, y As Double)
        test = y * x    
    End Function

Et c'est tout ! Pas "Worksheeet_Change" et autres superflu. La formule marche et est modifiée si une modification intervient soit en A1, soit en B1 à B9. Mais ton Worksheet, il est en calcul automatique ??

mortalino · Answer

Ca revient à faire ce que je dis depuis 2 heures, à part que tu emploies 
...y As Double)
alors que moi je le qualifie en type Range et me sert de sa propriété Value.

Mais c'est effectivement la même chose, et comme je le disais tout à l'heure, c'est pas grand chose à faire en terme de modifications.

Mais vu qu'il ne "veut pas" de param supplémentaire, faudra attendre Windows 2075 pour qu'il puisse lire dans les pensées...

@++ 

<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_kabja · Answer

Bon..
j'ai a peu pres trouver comment faire, avec en gros tout ca:

Worksheet.change() 
x=range("A1")
...

Worksheet.selectionchange()
calculate 
...

et dans un module, le fonction tel que:
function test(y as double)
Application.volatile
test=x*y
End sub

Avec ca, j'ai ma fonction qui marche dans n'importe quelle cellule, et avec un SEUL argument (y en l'occurence). x est lui attribué à une variable publique dès qu'il change. Je suis surpris qd même que personne n'ait compris de quoi je parlais. C'est trivial d'avancer que function(x as double, y as double) fonctionne.. Je voulais autre chose, c'est tout.

Merci pour tout, en tout cas.

cs_IAORANA · Answer

Pourquoi faire simple quand on peut se compliquer la vie.  Tu as une déclaration public, une sub et une fonction avec une variable, et tout ça pour le prix de deux variables dans une seule fonction. Si ce n'est pas bien négocié, ça. Mais tu voulais autre chose, je comprend.

jmfmarques · Answer

Euh...
Tu es bien certain de lire les réponses ?
Tant Mortalino que moi-même t'avons indiqué que si x était public, il t'était possible de ne pas le passer en paramètre !!!

Pour te rafraichir la mémoire, donc :

Citation :

C'est bien évidemment possible !
Rien ne te l'interdit, puisque x est public !
Tu pourrais donc, si x est punlic, écrire à ton choix :

Function test(y As Double) as double
    test =  y *  Range("A1").Value 
End Function

ou

Function test(y As Double, x as double) as double
    test  = y * x
End Function

ou encore

Function test(y As Double) as double
    test = y * x
End Function

les 3 seront comprises puisque x est public.

mortalino · Answer

je réagis juste à         Je suis surpris qd même que personne n'ait compris de quoi je parlais.

Nous avons très bien compris puisque le résultat final (ta proposition et les nôtres) obtient le même résultat. La seule différence est que nous voulions juste t'aiguiller vers une solution plus simple, moins coûteuse au processeur et en mémoire.
Pour infos, ton x =  Range("A1") dans l'évènement  Change  induit une affectation de valeur à la variable X, à chaque changement de valeur dans ta feuille.
Application.volatile te fait réactualiser une macro, chose encore coûteuse.

Là tu ne t'en rends pas compte car c'est une petite fonction. Mais imagine une fonction beaucoup plus complexe, qui travaille sur des objets (style Graphique) pourrait posséder une centaine de ligne de code pour accomplir le travail. Avec ce superflu, ton temps d'exécution serait 2 à 3 fois plus long ! (et des traitements de 10 minutes, j'en ai vu, donc imagine 10min x 2 ou 3... c'est énorme).
Voilà, c'était pas pour t'embêter, mais essayer de t'aider et faire progresser ton code àdes fins d'optimisation. 

@++ 

<hr size ="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA

cs_MPi · Answer

Juste au cas où...
Où est déclarée ta variable x (Public) ?
Dans une feuille ou dans un module ?

MPi

cs_kabja · Answer

C'est vrai, Mortalino en avait parlé dans les 3 solutions qu'il proposait, mais ca ne fonctionnait pas en l'état. Il manquait quelque chose comme application.volatile pour que ca fonctionne. Apres, c'etait surtout pour trouver autre chose, mais il semble en effet que cela complique les choses pour aucun gain de temps dans mes calculs. Cette solution n'est pas satisfaisante, c'est toujours bon savoir.
Voila, voila.

Ps: Ma variable publique x est déclarée dans une feuille.

cs_MPi · Answer

Essaie en la déclarant au niveau d'un module 
Et n'oublie pas d'enlever la déclaration au niveau de la feuille.

MPi

Pb de recalcul des fonctions

32 réponses

Votre réponse

Discussions similaires