VBA - Alternative à Application.Volatile

Bonjour et meilleurs voeux à tous,

Je vous sollicite en raison d'un problème concernant le recalcul automatique de fonctions personnelles créées en VBA.

Les fonctions personnelles que j'ai crées fonctionnent parfaitement, elles ne sont pas appelées par des macros (je n'en veux pas) mais sont directement insérées dans des cellules de feuilles Excel.
Pour qu'elles se recalculent lorsque je modifie la cellule, j'ai mis Application.Volatile True au niveau des fonction.
Mon problème est que lorsque je modifie n'importe quelle cellule du classeur, même celles qui ne sont pas affectées par la fonction, le classeur se recalcule entièrement.
Vu que mon PC est récent, ça ne me pose pas un réel gros problème, le calcul se faisant assez rapidement mais à la longue, ça devient un peu gênant.
De plus, si j'ai un autre fichier Excel qui est ouvert, qui ne contient aucune fonction, Excel lance quand même un recalcul.

Est-ce que quelqu'un connaîtrait une solution pour pallier à mon problème?


Par exemple, une de mes fonctions est la suivante. Elle permet de rechercher dans un onglet le texte (colonne B par exemple) associé à un identifiant (colonne A par ex) et de concaténer tout le texte en les séparant par un saut de ligne et des traits _______ . La cellule retourne alors la concaténation de tous les identifiants recherchés.
ça marche très bien, c'est juste un peu lourd

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

Function Recherche_Concatener(Référence_Cherchée As String, TableMatrice As range, Indice_Colonne_Référence_Cherchée As Integer, Index_Colonne_Description As Integer, Taille_Colonne As Integer) As String   '******************************************************************************************************************************** 'Cette fonction concatène dans une seule cellule toutes les descriptions en recherchant leur identifiant dans un autre onglet 'Les identifiants ou "Référence_Cherchée" doivent être espacés d'un retour à la ligne '********************************************************************************************************************************   Application.Volatile True 'Permet à la fonction de se recalculer immédiatement   Dim Tableau Dim i, j As Integer Dim FinLigne As Integer   FinLigne = TableMatrice.End(xlDown).Row 'Retourne la dernière ligne non vide du tableau de recherche Recherche_Concatener = ""   Tableau = Split(Référence_Cherchée, Chr(10)) 'Permet de mettre dans le tableau les caractères de la chaîne d'identifiants espacés du caractère spécial "Retour à la ligne <=> Ch(10)   For i = 0 To UBound(Tableau) '1° Boucle sur chaque identifiant d'exigence Référence_Cherchée = Tableau(i) If Référence_Cherchée = "" Then Exit For Else 'Ici, on balaye ligne par ligne (j) le tableau défini en argument et on concatène toutes les préconisations For j = 0 To FinLigne If TableMatrice(j, Indice_Colonne_Référence_Cherchée) = Référence_Cherchée Then Recherche_Concatener = Recherche_Concatener & TableMatrice(j, Index_Colonne_Description) & Chr(10) & String(Taille_Colonne, "_") & Chr(10) j = FinLigne - 1 End If Next j End If Next i   'Suppression du dernier retour à la ligne et des caractères spéciaux If Recherche_Concatener = "" Then Recherche_Concatener = "" Else Recherche_Concatener = Left(Recherche_Concatener, Len(Recherche_Concatener) - (Taille_Colonne + 2)) End If   End Function

---

Bonjour

C'est comme dans la chanson brésilienne "Nem Vem Que não Tem", reprise et interprétée par Brigitte Bardot sous le nom de "Tu veux ou tu veux pas".

C'est à TOI, qu'il appartient, en fonction de ce que TU "construis", connais et veux faire, de décider quand, à partir de quoi, en fonction de quoi, où et comment TU permets ou non ce re-calcul systématique.

EDIT : pour mes amis les plus curieux :

Citation:

---

Ahahahahahaha!
Vamos voltar a pilantragem
Xá comigo, uma musiquinha
Pra machucar os corações

Nem vem que não tem
Nem vem de garfo
Que hoje é dia de sopa
Esquenta o ferro
Passa a minha roupa
Eu nesse embalo
Vou botar pra quebrar
Sacudim, sacundá
Sacundim, gundim, gundá!

Nem vem que não tem
Nem vem de escada
Que o incêndio é no porão
Tira o tamanco
Tem sinteco no chão
Eu nesse embalo
Vou botar pra quebrar
Sacudim, sacundá
Sacundim, gundim, gundá!

Nem vem!
Numa casa de caboclo
Já disseram um é pouco
Dois é bom, três é demais

Nem Vem!
Guarda teu lugar na fila
Todo homem que vacila
A mulher passa pra trás

Nem vem que não tem
Pra virar cinza
Minha brasa demora
Michô meu papo
Mas já vamos'imbora
Eu nesse embalo
Vou botar pra quebrar
Sacudim, sacundá
Sacundim, gundim, gundá!

Nem vem!
Numa casa de caboclo
Já disseram um é pouco
Dois é bom, três é demais

Nem Vem!
Guarda teu lugar na fila
Todo homem que vacila
A mulher passa pra trás

Nem vem que não tem!

---

Bonjour,

Il n'est nécessaire de la rendre volatile que si tu utilises dedans des paramètres qui ne lui sont pas passés.
Ici ça n'a pas l'air d'être le cas et j'ai l'impression que tu l'as faite volatile pour rien.
Si Référence_Cherchée As String, TableMatrice As Range changent elle est ré-évaluée automatiquement sans Volatile.
A la limite il faut peut-être Référence_Cherchée As Range pour assurer le coup (?) A tester.
Et si c'est le changement d'autres cellules qui imposent le recalcul, passe-lui les référence en paramètre même si tu ne les utilises pas directement dedans.
eric

PS : dans Dim i, j As Integer seul j est Integer. i est Variant, il faut toutes les typer individuellement

Citation:

---

Envoyé par eriiic

---

J'avoue ne pas tout comprendre à la réponse :)
Ma Référence_Cherchée est bien une chaîne de caractère et non un range.
En fait dans une cellule, je mets les identifiants relatives à des exigences d'un tableau. Dans le tableau, qui est dans un onglet du fichier Excel, j'ai en première colonne des identifiants de style "Ex.052", "Ex.189" ..... et le texte associé à chaque identifiant (par exemple: tester le moteur en maintenance).

Dans la cellule de la feuille où je place la fonction, je retourne donc tout le texte concernant les identifiants que je cherche, qui sont par exemple dans la cellule de la colonne D. Dans ces cellules se trouve les identifiants qui sont mis comme ceci:
Ex.002
_____
Ex.054
_____
Ex.158
....
....

Ainsi, je souhaite que la fonction se recalcule que si dans cette cellule, je remplace Ex.002 par Ex.548 (par exemple). C'est pour ça que je l'avais mise volatile.

J'ai cru comprendre qu'il fallait peut être d'une part appliquer la fonction au classeur (pour éviter que lorsqu'on travaille sur un autre classeur ouvert, il y ait des recalculs) et appliquer également la fonction au worksheet. Sauf que j'utilise la même fonction sur plusieurs autres colonnes et / ou onglet du classeur (pour éviter de créer pleins de fonctions identiques mais avec un nom différent)

J'ai supprimé application.volatile et placer en variant comme tu me l'as suggéré Eric et ça a l'air de marcher.

Pour bien comprendre quand placer Application.volatile, est-ce qu'une fonction Excel comme "=RECHERCHEV(Valeur cherchée, Table matrice, no_index_col,[Valeur proche])" est une fonction volatile? Est-ce qu'elle intègre autre chose qui fait qu'elle se recalcule dès qu'il y a modification, soit de la valeur cherchée ou soit du tableau?

Bonjour,

excel ne recalcule pas toutes les formules à chaque modification.
Il crée un arbre des dépendances (les paramètres passés à une fonction) et ne recalcule que les formules concernées par ce changement (fonctionnement par défaut).
Si toutes les plages utilisées par une fonction personnalisée lui sont passées en paramètre elle est donc ré-évaluée automatiquement.
Théoriquement tu devrais pouvoir laisser As String, autant typer au plus près.
On ne met volatile une fonction personnalisée que si un événement non détectable doit entrainer une ré-évaluation.
Par exemple un changement de couleur qui est utilisé dans la fonction.

Les fonctions natives d'excel volatiles sont Alea(), Maintenant(), Aujourdhui(), decaler(), Cellule(), Indirect() ou Info() pour des raison évidentes.
Et Somme.Si() dans un cas particulier.
eric

Pourquoi dans certains cas, si je mets une fonction personnelle dans une cellule, sans mettre Application.volatile, la cellule ne se recalcule que si j'interviens en faisant entrée?

Impossible de répondre sans boule de cristal

J'ai édité mon post précédent :

Citation:

---

Théoriquement tu devrais pouvoir laisser As String, autant typer au plus près.

---

Citation:

---

Envoyé par eriiic

...
Et si c'est le changement d'autres cellules qui imposent le recalcul, passe-lui les références en paramètre même si tu ne les utilises pas directement dedans.
...

---

J'avais trouvé ça tout seul un peu par hasard et je me préparai à diffuser l'info ! Encore grillé au poteau :D ... et ce ne sera surement pas la dernière fois !

Ce forum est décidément une mine inépuisable : Grâce en soit rendu à ses contributeurs émérites !

Cordialement,
Olivier