Discussion :

[JClmnop]

Salut à tous. Je vous remercie premièrement pour votre beau travail ici. Voici mon problème:

J'ai un fichier source nommé "source.xlsm" et un fichier de destination nommé "destination.xlsm" qui sont situés dans le même répertoire.
Le ficher source contient un tableau structuré nommé "Tableau1". (Onglet accueil > Groupe Style > Mettre sous forme de tableau)

Mon but: Récupérer la référence de la plage occupée par le "Tableau1" en utilisant Application.Run depuis le fichier "destination.xlsm".

J'ai créé deux procédures quasiment identiques pour récupérer cette valeur dans le code du fichier "destination.xlsm":

- LirePlage() dans le module ordinaire "LirePlage" du fichier "destination.xlsm" qui appelle la fonction "Plage" dans le module ordinaire "Plage" (ou "Test_Plage") du fichier "source.xlsm".
- LirePlageTab() dans le module "Feuil1" du fichier "destination.xlsm" qui appelle la fonction "PlageTab" (ou "Test_PlageTab") dans le module "Feuil1" du fichier "source.xlsm".

Idéalement, je voudrais garder qu'une seule procédure par fichier et c'est justement ça le problème.

Dans le module "LirePlage" du fichier "destination.xlsm":

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub LirePlage()
 
Dim X(0 To 4) As String
X(1) = ThisWorkbook.Path    'Chemin d'accès
X(2) = "source.xlsm"            'Nom de fichier
X(3) = "Plage"                     'Nom de la procédure
X(4) = "Tableau1"                'Nom du tableau structuré (argument de la procédure)
 
Range("C5") = Application.Run("'" & X(1) & "\" & X(2) & "'" & "!" & X(3), X(4))    'La cellule choisie (C5) n'a pas d'importance
Workbooks(X(2)).Close
 
End Sub

Dans le module "Feuil1" du fichier "destination.xlsm":

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub LirePlageTab()
 
Dim X(0 To 4) As String
X(1) = ThisWorkbook.Path    'Chemin d'accès
X(2) = "source.xlsm"            'Nom de fichier
X(3) = "PlageTab"                'Nom de la procédure
X(4) = "Tableau1"                'Nom du tableau structuré (argument de la procédure)
 
Range("C5") = Application.Run("'" & X(1) & "\" & X(2) & "'" & "!" & "Feuil1." & X(3), X(4))    ' Ne pas oublié d'ajouter "Feuil1."
Workbooks(X(2)).Close
 
End Sub

Dans le fichier source j'ai deux fonctions par module. Deux dans le module "Plage" et deux dans le module "Feuil1". Il n'en faudrait qu'une seule idéalement.

Dans le module "Plage" du fichier source:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Function Plage(NomTableau As String)
 
Plage = ThisWorkbook.Worksheets(1).ListObjects(NomTableau).Range.Address
Debug.Print "Plage(""" & NomTableau & """) = " & Plage
 
End Function
 
Function Test_Plage(NomTableau As String)
 
Test_Plage = Plage(NomTableau)
 
End Function

Dans le module "Feuil1" du fichier source:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Function PlageTab(NomTableau As String) As String
 
PlageTab = ThisWorkbook.Worksheets(1).ListObjects(NomTableau).Range.Address
Debug.Print "PlageTab(""" & NomTableau & """) = " & PlageTab
 
End Function
 
Function Test_PlageTab(NomTableau As String)
 
Test_PlageTab = PlageTab(NomTableau)
 
End Function

Voici les résultats :

I. LirePlage avec X(3) = "Plage" => Erreur 1004
II. LirePlage avec X(3) = "Test_Plage" => OK

III. LirePlageTab avec X(3) = "PlageTab" => Chaîne vide "" en C5 , mais Débug.Print OK
IV. LirePlageTab avec X(3) = "Test_PlageTab" => Chaîne vide "" en C5 , mais Débug.Print OK

Précisions:
Erreur 1004 Impossible d'exécuter la macro ''<chemin d'accès>\source.xlsm'!Plage'. Il est possible qu'elle ne soit pas disponible dans ce classeur ou que toute les macros soient désactivées.

Question 1: Pouvez-vous me dire pourquoi la procédure fonctionne en II , mais pas en I ? Ce qu'il y a de bien, c'est que j'ai au moins une bonne solution, mais j'aimerais passer directement par la fonction "Plage" et laisser tomber la fonction "Test_Plage" qui appelle "Plage" avec l'argument "Tableau1" passé par Application.Run.

Question 2: Pourquoi la procédure III et IV me retournent ce que je veux dans la fenêtre d'exécution, mais que je ne peux retrouver cette même valeur en l'affectant à la cellule C5 ?

Un grand merci à tous les passionnées.

Exige beaucoup de toi-même et attends peu des autres. Ainsi beaucoup d'ennuis te seront épargnés.

[zenpbb]

Bonjour,
Pour la question 1, cela vient du fait que tu as donné le même nom (Plage) à ton module et ta procédure, ce qu'il vaut mieux éviter. Du coup pour que ça fonctionne, tu aurais dû mettre

Code :

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

X(3) = "Plage.Plage"

, ou mieux, renommer ton module pour qu'il n'y ait pas d’ambiguïté.

[JClmnop]

Bien vu. J'ai renommé le module "Plage" en "Module1" et je peux maintenant passer directement par la fonction "Plage". Je crois que je vais retenir la leçon. Merci pour ta lumière.
Maintenant, j'ai peut-être une piste pour résoudre le problème du module "Feuil1" et de sa fonction "PlageTab". J'ai remarqué en passant par la fenêtre des variables locales que lorsque la procédure "LirePlageTab" entre dans la fonction "PlageTab", qu'il y a deux variables au nom de "PlageTab". L'une demeure inchangée puis égale à "" jusqu'à la sortie et l'autre passe de "" à la valeur tant convoitée. Au final,

Code :

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

1
2
PlageTab = ThisWorkbook.Worksheets(1).ListObjects(NomTableau).Range.Address    '<= PlageTab = ""
'Debug.Print "PlageTab(""" & NomTableau & """) = " & PlageTab                               '<= Affiche PlageTab("Tableau1") = "$A$1:$C$5"

Je me doute donc que c'est une question de ByVal ou ByRef, mais je suis peut-être aussi dans le champ.
Pour moi, "PlageTab" n'est pas vraiment une variable passée (plutôt une fonction appelée), c'est "Tableau1" qui est une variable passée comme argument de la fonction "PlageTab".

Un petit F1 sur le mot clé "Run" me dit ceci : "La méthode Run renvoie ce que la macro exécutée renvoie. Les objets qui sont transmis à la macro en tant qu'arguments sont convertis en valeurs (en appliquant la propriété Value à l'objet). Cela signifie que vous ne pouvez pas passer d'objets aux macros à l'aide de la méthode Run."

Quoi qu'il en soit, j'ai aussi trouvé ça (et j'imagine que ça va vous apprendre quelque chose): http://excelmatters.com/2017/04/07/p...ref-using-run/
Ce que je me suis empressé d'essayé en copiant-collant dans un module. Cela fonctionne réellement, mais l'ajout de Dim A: Set A = Application puis la modification A.Run... n'apporte rien de nouveau sous le soleil.

Ma question : Pourquoi ai-je deux variables locales du même nom dans la fonction "PlageTab" ?

Encore une fois, merci à mon cousin français zenpbb pour son commentaire.

Exige beaucoup de toi-même et attends peu des autres. Ainsi beaucoup d'ennuis te seront épargnés.

[zenpbb]

Salut,

Quoi qu'il en soit, j'ai aussi trouvé ça (et j'imagine que ça va vous apprendre quelque chose): http://excelmatters.com/2017/04/07/p...ref-using-run/

Effectivement, je ne connaissais pas l'astuce, c'est très intéressant, merci pour l'info.

Concernant ton problème, c'est visiblement lié au fait que la procédure que tu exécutes via Application.Run est dans un module Objet, car lorsqu'on la déplace dans un module Standard, ça fonctionne. Je n'ai pas d'explication...
Et s'agissant de la variable apparaissant en double dans la fenêtre des variables locales, c'est sans doute lié à cette particularité des fonctions en VBA qui fait qu'à l'intérieur d'une fonction, on utilise une variable portant le même nom que la fonction, d'où une occurrence pour la variable et une occurrence pour la fonction.

En tout cas, tu peux contourner ton problème, soit en utilisant une variable passée ByRef dans ta fonction grâce à l'astuce que tu as trouvée, soit en déplaçant ton code dans un module Standard (ou en exécutant une fonction créée dans un module standard et appelant elle-même ta fonction située dans un module objet).

[JClmnop]

Un gros merci zenpbb pour ton aide.

L'astuce que j'ai trouvée fonctionne bien avec les arguments passés via Application.Run. En ce qui me concerne, c'est le nom du tableau qui est l'argument et non la procédure (fonction) appelée. J'ai essayé toutes les combinaisons possibles (soit disant 4) de ByRef et ByVal et rien n'y fait. Encore là, c'est le nom du tableau et non le nom de la fonction qui est touché par l'instruction ByRef ou ByVal.

Petite parenthèse ici :

Et s'agissant de la variable apparaissant en double dans la fenêtre des variables locales, c'est sans doute lié à cette particularité des fonctions en VBA qui fait qu'à l'intérieur d'une fonction, on utilise une variable portant le même nom que la fonction, d'où une occurrence pour la variable et une occurrence pour la fonction.

J'ai fait le test avec une fonction bidon et comme tu dis, il y a bien deux variables en jeu. Si je comprends bien, une seule des deux variables est manipulée par la fonction. Elles prennent la valeur par défaut (0 pour un Integer, "" pour une String, etc) au début de la procédure et ... Une d'entre-elles demeure constante durant toute la procédure. J'aimerais comprendre à quoi peut servir deux variables du même nom?

Pour contourner ce problème voici ce que j'ai fait:

Dans le module "LirePlage" du fichier "destination.xlsm" :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub LirePlageTab()
 
Dim x(1 To 5) As String
x(1) = ThisWorkbook.Path         'Chemin d'accès
x(2) = "source.xlsm"             'Nom de fichier
x(3) = "Feuil1"                  'Nom de la feuille de calcul
x(4) = "PlageTab"                'Nom de la procédure
x(5) = "Tableau1"                'Nom du tableau structuré (argument de la procédure)
 
Application.Run "'" & x(1) & "\" & x(2) & "'" & "!" & x(3) & "." & x(4), x(5)
Range("C5") = Application.Workbooks(x(2)).Worksheets(x(3)).PlageTableau1
 
Workbooks(x(2)).Close
 
End Sub

Dans le module "Feuil1" du fichier "source.xlsm":

Code :

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

1
2
3
4
5
6
7
8
Public PlageTableau1 As String
 
Function PlageTab(NomTableau As String) As String
 
PlageTableau1 = ThisWorkbook.Worksheets(1).ListObjects(NomTableau).Range.Address
PlageTab = PlageTableau1
 
End Function

Je trouve cette solution un peu moins intéressante que la précédente puisqu'elle passe par une variable publique intermédiaire. Je suis tout de même satisfait de l'apprentissage que ça apporte. Merci, Merci!

Exige beaucoup de toi-même et attends peu des autres. Ainsi beaucoup d'ennuis te seront épargnés.