VBA: Application.Volatile, passe 2x ds fonct Fermé

Question

Bonjour, 

Soit une fonction personnalisée écrit en VBA. La première ligne est Application.Volatile afin d'autoriser son recalcul par le code.

Soit une autre fonction, qui effectue un recalcul sur la cellule active (pour lister les valeurs dans la formule de la cellule) avec ActiveCell.Calculate : problème, le recalcul se fait deux fois !

Quelle est la solution ?

Configuration: Windows Vista / Firefox 3.6.13

lermite222 · Answer

Bonjour, 
Tu donne deux alternatives à un problème... 
ça recalcule 2 fois .. ne veux rien dire, 
Exemple : Dans C1 la formule = A1 + B1, ça va recalculer 2 fois si tu change A1 et B1 ?
Pour ta première alternative, tu peu déactiver le recalcul en entrée de la fonction, faire tes modifications de cellule et réactiver le recalcul en fin de fonction.
A+ 
L'expérience instruit plus sûrement que le conseil. (André Gide)   
Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)

fred33450 · Answer

Non, tu n'as pas compris. Je vais détailler dans ce cas.     

Private bool As Boolean 'par défaut, False     
Private formule As String 'par défaut, à vide     

Public Function Test(code As String) As Variant     
     Application.Volatile     
     ValeurRubrique = monObjet.FonctionRequeteDataBase(code)     

     If bool = True Then     
          If formule = "" Then     
               formule = code     
          Else     
               formule = formule & "#" & code     
          End If     
     End If     
End Function     

Private Sub ClicBouton()     
     bool = True     
     ActiveCell.Calculate     
     bool = False  
     
     MsgBox(formule)     
End Sub

Donc on a une fonction personnalisée Test, à laquelle on peut faire appel depuis une cellule comme n'importe quel fonction de base d'Excel. Celle-ci retourne une valeur, ici depuis une base de données mais peu importe, le problème n'est pas là.     

Le problème est que quand on clic sur un bouton, qui exécute la macro ClicBouton, je force le recalcul de la cellule. Et si on met un point d'arrêt sur la fonction Test, on s'aperçoit que la fonction Test est exécutée deux fois (d'où le recalcul deux fois).     

Ainsi donc, si votre formule est : =Test("toto")     
Et si vous interroger la variable formule, vous obtiendrait : toto#toto

lermite222 · Answer

C'est vrai, j'ai rien compris, mais je pense que toi non plus.   
ValeurRubrique = monObjet.FonctionRequeteDataBase(code)   
Là ça veux rien dire...   
1°) c'est quoi "MonObjet"   
2°) C'est déclarer où ?   
3°) Ta fonction renvoi rien  
4°) Où te sert tu de ValeurRubrique  
5°) Etc..   

L'expérience instruit plus sûrement que le conseil. (André Gide)     
Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)

fred33450 · Answer

Pour rappel, il s'agit de VBA dans Excel...    

1) monObjet n'a aucun intérêt dans le cas du problème, mais pour répondre à ta question c'est une classe écrit en VB.Net et inscrit en tant que COM pour pouvoir l'utiliser librement depuis Excel. Et celui-ci fonctionne parfaitement, sans le moindre soucis. Il n'est donc pas question d'en traiter davantage.   
  
2) Et si tu veux, ben c'est déclaré comme ça :    
Public monObjet as New MaClasse.MonObjetPersoVBNet
3) La fonction est faites pour renvoyer un variant. Et il n'y a pas de Return en Excel. Cela s'écrit comme ça en VBA : NomFonction=Resultat. C'est donc cette ligne :    
ValeurRubrique = monObjet.FonctionRequeteDataBase(code)
4) Et je l'ai déjà dit, c'est une fonction personnalisée. Donc ça s'appèle depuis une cellule comme les fonctions Excel (ex: =SOMME(A1:A2). Ben là c'est pareil : =ValeurRubrique("TOTO").

lermite222 · Answer

Ah bon, beh t'en connaîs trop pour moi. :-  -- :-D -- :-X -- :-> -- 8-)
Abandon du suivi 
A+ 
L'expérience instruit plus sûrement que le conseil. (André Gide)   
Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)

eriiic · Answer

Bonjour,

Je suis un peu comme lermite, ton exemple est incomplet et n'est pas exploitable pour tester quoique ce soit ce qui ne facilite pas la tâche...
De plus ta fonction s'appelle test, ça ne serait pas plutôt formule ?

Juste une question : es-tu sûr que ta fonction doive être volatile ?
On la met volatile pour forcer son calcul même si ses antécédents ne changent pas de valeur, sinon c'est inutile.
Si elle doit varier en fonction de bool, ajoute le dans ses parametres.

eric

fred33450 · Answer

Pour te répondre, non formule est juste une variable dans laquelle je veux stocker les paramètres utilisés dans la cellule.  

En fait, dans la cellule tu met une formule par exemple :  
- Test("toto")+Test("tutu")+Test("titi")  

Moi sur le clic d'un bouton, j'exécute une macro qui passe un booléen à vrai puis force le recalcul afin que dans la variable formule j'ai la liste des paramètres, dans mon exemple : toto#tutu#titi .  

Et vu que c'est une fonction personnalisée, qui fait appel à une fonction VB.Net (qui elle même exécute une requête sur une base de données SQL Server) avec une classe COM, ben si je change quelque chose côté base de données, Excel ne le saura forcément pas et ne lancera pas de recalcul automatique... D'où le Application.Volatile.  

Mais pour le coup, j'ai les paramètres en double dans ma variable formule. Ma fonction s'exécute deux fois (vérifié avec un point d'arrêt)...  

Je sais que le côté utilisation de mon objet vous paraît bizarre, mais de ce côté c'est bon je n'ai pas de soucis. C'est juste le pourquoi du comment sur le passage à deux reprises dans ma fonction pour chacun de ses appels.

Jean-Marie · Answer

Salut,

j'ai l'impression que le fonctionnement que tu décrit est parfaitement normal :

Je clic sur ClicBouton() je fais le recalcule de la cellule active donc 1er appel a ta fonction test.

Dans la mesure ou tu as modifié une cellule, toutes les fonction marquée comme volatile vont s'executer donc 2ème appel à test. (aide sur volatile : une fonction volatile doit être recalculée chaque fois qu'un calcul est effectué dans une cellule quelconque de la feuille)

Tu peux eviter ca en deplacant ton bool = False à la fin de test. comme ca au deuxieme appel, etant faux, le recalcul ne se fera pas.

A+ et bon noël.

fred33450 · Answer

Hum, tu as sans doute raison. 

Quelqu'un aurait-il une solution simple pour ne forcer que le recalcul d'une cellule sans passer par Volatile ? Ou mieux, comment lister les paramètres d'une ou plusieurs fonction(s) dans une cellule (car c'est ce que je cherche à faire). 

Par exemple, dans une cellule  la formule est : 
=Test("Toto")+Test("Tata")+Test("Titi") 

Je voudrais générer la chaîne Toto#Tata#Titi.

VBA: Application.Volatile, passe 2x ds fonct

9 réponses

Discussions similaires