Discussion :

[BoromSikim]

Bonjour,

Soit un fichier Tester_Fonction.xlsm vierge.

Dans Feuil1, je dispose d’un tableau (LISTOBJECT) nommé SEMAINE
De A1 à A8 - - > Num_Jour ,1, 2, 3, 4, 5, 6, 7
De B1 à B8 - - > Jour, Lundi, Mardi, Mercredi, Jeudi, Vendredi, Samedi, Dimanche
A1 et B1 sont les titres.

J’ai créé une fonction utilisateur qui ramène le nom du jour en fonction du N° de jour passé en paramètre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Public Function Jsem(Jour) As String
    On Error Resume Next
    Jsem = Application.WorksheetFunction.VLookup(Jour, Range("Semaine"), 2, False)
End Function

En G5, je saisis donc : =jsem(5) et c’est bien VENDREDI qui s’affiche.

Si je modifie B6 (qui contient Vendredi) en saisissant Friday par exemple, rien en se passe. G5 reste à Vendredi. F9 et Maj+F9 n’ont aucun effet.

J’ai googleisé mon soucis et j’ai trouvé la solution : Application.volatile.

Je modifie ma fonction en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Public Function Jsem(Jour) As String
    Application.volatile
    On Error Resume Next
    Jsem = Application.WorksheetFunction.VLookup(Jour, Range("Semaine"), 2, False)
End Function

Le comportement de la fonction est OK. Quand je change une valeur dans mon tableau SEMAINE, le contenu de G5 est bien modifié. Je suis content.

Là où ça pose problème, c’est quand, mon fichier tester_fonction étant ouvert, je crée par exemple un autre tableau (Fichier-Nouveau-Nouveau Classeur) et que j’en modifie une cellule.

Quand je reviens sur TESTER_FONCTION, G5 est vide. Pour ré afficher, je dois faire F9.

Le comportement est identique si j'ouvre un classeur déjà enregistré.

Pouvez-vous m’aider à résoudre ce soucis ?

Merci d'avance.

[Nako_lito]

tu utilise des références relatives, il faut que utilise des références absolues pour appeler tes classeurs, feuilles ou cellules

par exemple, activeworkbook dépend de quel classeur est actif, si tu travaille sur plusieur fichiers en meme temps, ça peu etre très problématique

A la place, il faut utiliser "thisworkbook" qui ciblera le classeur depuis lequel le code est appelé.

Il en est de meme avec les range.
Si tu appele la range "A1" par exemple, il va prendre celle qu'il a sur le classeur en cours d'utilisation sur la feuille en cours d'utilisation

alors que si tu mets "thisworkbook.sheet(1).range ("A1"), il ne fera pas "d'erreur" de référencement.

[Marc-L]

Bonjour.

Il en est de meme avec les range.
Si tu appele la range "A1" par exemple, il va prendre celle qu'il a sur le classeur en cours d'utilisation sur la feuille en cours d'utilisation

Oui sauf si le code est situé dans le module d'une feuille de calculs : dans ce cas,
ce n'est plus la feuille active par défaut mais la feuille de calculs dans laquelle se trouve le code,
même si un autre classeur est actif …

[78chris]

Bonjour

Si Nako-Lito et Marc_L ont raison, je pense que le problème vient de Range("Semaine") associé à la fonction VLOOKUP.


Pas idéal pour une fonction personnalisée : il serait plus logique de gérer les 7 jours dans le code de la fonction que de se référer à une plage du classeur.

Sinon préciser Thisworkbook.Range("Semaine") devrait améliorer mais sans garantie.

[Nako_lito]

exact,

si il veut utiliser thisworkbook.range("semaine"), il doit s'assurer qu'elle existe avant de l'appeler, sinon excel va lui péter à la face

[78chris]

Re

exact,

si il veut utiliser thisworkbook.range("semaine"), il doit s'assurer qu'elle existe avant de l'appeler, sinon excel va lui péter à la face

Oubli de ma part il faut préciser la feuille

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ThisWorkbook.Worksheets("NomFeuille").range("semaine")

Le but, sauf erreur de ma part n'est pas d'appliquer la fonction à d'autre classeurs mais d'éviter la perte du résultat et thisworkbook limite au classeur qui contient le code ce qui est bien la question...

[Nako_lito]

tu peux aussi retrouver le classeur voulu avec :

workbooks("leNomDeMonClasseur").sheets(1).Range("A1")

ou

workbooks(variable).sheets(x).Range(maRange)

[78chris]

Re

tu peux aussi retrouver le classeur voulu avec :

workbooks("leNomDeMonClasseur").sheets(1).Range("A1")

ou

workbooks(variable).sheets(x).Range(maRange)

Je ne souhaite pas polémiquer mais la question, reformulée, était "pourquoi quand j'ouvre plusieurs classeurs la fonction volatile appliquée sur le classeur qui la contient, s'envole ?"

Si tes remarques sont pertinentes dans le cas général , elles ne répondent pas exactement à la question qui est de limiter la fonction au classeur qui la contient et dont on connait le nom et celui de la feuille et risquent peut-être d'embrouiller le demandeur dans ce cas précis.

[Nako_lito]

Si je ne dis pas de bêtise,
La fonction volatile a une toute autre fonctionnalité ... elle définit juste "quand est ce que je dois m'exécuter ?".

fonction Volatile --> a chaque fois qu'une cellule du classeur ou est exécuté le code est modifiée, la fonction s'exécute.
fonction non Volatile --> tant que je ne suis pas spécifiquement appelée, je ne m'exécute pas.

une méthode volatile dans le classeur 1 est exécutée si on modifie une cellule du classeur 1, mais si on bidouille dans le classeur 2, elle ne s'exécute jamais.

C'est un peu le "with events" des feuilles de calcul.

Donc pour en revenir au probleme initial.
Je pense que le référencement à la range ("semaine") est trop ambigu pour Excel. Il faut spécifier la range du classeur voulu de manière plus précise.

[Theze]

Bonsoir,

J’ai créé une fonction utilisateur qui ramène le nom du jour en fonction du N° de jour passé en paramètre :

Te casse pas la tête, VBA propose une fonction qui fait ça très bien (WeekdayName) de même pour les mois (MonthName) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
MsgBox WeekdayName(1, False, vbMonday)

Hervé.

[78chris]

Re

Si je ne dis pas de bêtise,
La fonction volatile a une toute autre fonctionnalité ... elle définit juste "quand est ce que je dois m'exécuter ?".

fonction Volatile --> a chaque fois qu'une cellule du classeur ou est exécuté le code est modifiée, la fonction s'exécute.
fonction non Volatile --> tant que je ne suis pas spécifiquement appelée, je ne m'exécute pas.

une méthode volatile dans le classeur 1 est exécutée si on modifie une cellule du classeur 1, mais si on bidouille dans le classeur 2, elle ne s'exécute jamais.

C'est un peu le "with events" des feuilles de calcul.

Si tu fais le test de la situation décrite pas BoromSikim, tu verras que la valeur s'efface en effet du 1er classeur.

Donc pour en revenir au probleme initial.
Je pense que le référencement à la range ("semaine") est trop ambigu pour Excel. Il faut spécifier la range du classeur voulu de manière plus précise.

La précision que j'ai proposée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ThisWorkbook.Worksheets("NomFeuille").range("semaine")

résout ce problème.

Reste à vérifier si la solution plus simple proposée par Theze doit ou non être déclarée volatile...

[BoromSikim]

Merci à tous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ThisWorkbook.Worksheets("NomFeuille").range("semaine")

règle le problème, mais je ne comprends pas pourquoi.

En effet, il n'y a qu deux classeurs ouverts: tester_fonction et le nouveau. Semaine n'est défini qu'une fois, et le seul code est celui de jsem.
J'avais bien saisi la définition de VOLATILE, et je ne vois pas où Excel perd ses pédales!

Si qqun peut m'expliquer .

Je marque résolu demain.

[78chris]

Bonjour


Lis tous les posts : tu verras qu'il faut définir le rattachement des objets.

Si tu ne comprends pas cela, tu auras plein de problème de code s'appliquant à autre chose que ce qui est prévu à l'origine...

[eriiic]

Bonjour à tous,

Il faut éviter au maximum les fonctions volatiles.

Si je modifie B6 (qui contient Vendredi) en saisissant Friday par exemple, rien en se passe.

Une fonction n'est ré-évaluée que si un de ses antécédent est modifié.
Il faut donc lui passer B2:B8 en paramètre, même si tu ne t'en sers pas dans la fonction.
Et en l’occurrence tu t'en sers puisque c'est justement une partie de ta plage Semaine.
Essaie avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Public Function Jsem(Jour as long, sem As Range) As String
    Jsem = Application.WorksheetFunction.VLookup(Jour, sem, 2, False)
End Function

Appel : =jsem(5;Semaine)

Et je voudrais corriger un truc dit : une fonction volatile est ré-évaluée même si la modif a lieu dans un autre classeur.
Dans ce cas elle aura tendance à utiliser la feuille active pour ses références si la feuille n'est pas précisée dedans.
Théoriquement un nom contient dans sa définition le classeur et le plus souvent la feuille mais bon...

eric

[BoromSikim]

OK

Merci pour tout.