Discussion :

[tomasdev]

Bonjour,

j'aimerais integrer sur tous les postes de mon equipe une sorte d'extension Excel qui serait une fonction VBA,

En gros ce serait une fonction VBA dans l'esprit de RechercheV dans un referentiel Excel qu'on a, selon ni'mporte quelle cellule clé genre :

=Referentiel(A1;"CODE";"LIBELLE")

ou Referentiel() va chercher A1 dans la cellule "CODE" du referentiel excel et me retourner la cellule "LIBELLE" liée,

donc pas de soucis pour moi pour coder cette fonction, mais peut-on deployer cette fonction sur tous les postes, sur n'importe quel classeur excel ?

En gros mon collegue crée un nouveau classeur, il tape "XYZ123" dans A1 et "=Referentiel(A1;"CODE";"LIBELLE")" dans B1 et j'ai mon libellé ?

Le referentiel sera sur le reseau, accessible par tous.

Possible ou pas ?

Merci !

[ZebreLoup]

Il suffit de le mettre dans les macro complémentaires pour que ce soit accessible à l'ouverture d'Excel.

Par contre, pour le déploiement, c'est un peu plus compliqué, il faut recopier le .xla sur tous les postes et vérifier que chaque Excel l'a bien pris en compte. Mais ça doit marcher normalement.

[tomasdev]

ok parfait, je viens de trouver la doc, et c'est bien ca

Faut justre faire attention aux feuilles qui sortent par email, quand elles atterissent sur des postes sans ce XLA

Question subsidiaire, existe t'il un RechercheV à qui on pourrait preciser le numero de la colone clé ?
Car RechercheV cherche dans la colonne 1 et retourne la colonne qu'on lui indique, mais j'aimerais preciser les deux!

[ZebreLoup]

Il faut travailler avec INDEX et EQUIV dans ce cas.

[tomasdev]

En effet, c'est bien avec Index et Equiv que j'y arrive,

par contre, impossible de faire marche ma Function, soucis quand passage vers VBA, en gros, sous excel pur, j'ai ca :

Code :

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

=INDEX('C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$X;EQUIV(A1;'C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$A;0);6)

qui me renvoit la colonne n°6 dans le range $A:$X qui est liée a la valeur de A1 trouvée dans ref.xlsm dans la collone A,

je souhaite donc creer la fonction :

Code :

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

Function Referentiel(Source as Variant, Type as String, Retour as String) as Variant

que j'appelerais comme ceci dans ma cellule :

Code :

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

=Referentiel(A1; "CODE_BLABLA"; "LIBELLE_TOTO")

avec une petite association au debut de la fonction Referentiel qui dit que :

CODE_BLABLA = Colonne 1,
LIBELLE_BLABLA = Colonne 2,
LIBELLE_TOTO = Colonne 3

Pas de soucis pour l'association, c'est pour le passage en VBA de

Code :

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

=INDEX('C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$X;EQUIV(A1;'C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$A;0);6)

que je bloque

Une piste ?

Je pensais à genre :

Code :

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

1
2
Dim equi as Variant
equi = Application.worksheetfunction.match(Source, 'C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$A, 0)

mais deja la ca bloque, je pense que le soucis vient de ma syntaxe sur le second parametre de match

[ZebreLoup]

Tu es sur la bonne piste, mais il faut utiliser des Ranges dans les paramètres

[tomasdev]

J'ai beau essayer, ca me semble pas possible sans ouvrir physiquement le classeur via VBA

Du genre :

Code :

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

1
2
3
4
5
6
7
8
Dim sh As Worksheet
    sh = Workbooks("C:\Users\XX0000\Desktop\ref.xlsm").Worksheets("Ref")
 
    Dim ra As Range
    ra = sh.Range("$A:$A")
 
    Dim mat As Long
    mat = Application.WorksheetFunction.Match("A13", ra, 0)

bloque sur le Workbooks(), c'est jouable en VBA de referencer un classeur fermé ?

Sans passer par ADO ?

Sinon autre idée, J'ai ma formule excel qui marche à base de Index(Equal()),
c'est pas jouable que ma formule Referentiel() traduise en formule excel, et remplace dans la cellule ? il restera à tirer vers le bas pour remplir les tableaux, et ce sera peut etre plus efficace niveau lectures de fichier non ?

[ZebreLoup]

Effectivement, ça ne marchera pas de cette manière sans ouvrir physiquement le classeur VBA. Après, tu peux toujours l'ouvrir en arrière-plan, ce sera transparent pour l'utilisateur. Ou encore écrire la formule dans une cellule pour ne pas utiliser WorksheetFunction. Mais tu vas avoir un gros problème de performance dès qu'il y aura trop d'accès à ta fonction dans un classeur.

J'avais déjà travaillé sur ce type de problème. Au début de ma fonction, je vérifiais si le classeur était ouvert ou pas et adaptait en fonction. Je conseillais à ceux qui l'utilisaient d'ouvrir le classeur pour que ce soit plus rapide.

Au final, j'ai réussi à les convaincre de faire une base Access (Pour moi, quelqu'un qui dit qu'il a fait une base de données sous Excel, je me dis "aïe, aïe, aïe" - Sauf bien sûr pour une application mono-utilisateur, avec des données limitées et sans requêtage)

[tomasdev]

En fait, ils ont 3 logiciels lourds (SAP, Arcgis pour situer),
Avec un export hebdomadaire vers plusieurs Excels,

et un des collegues recolle tout sous excel en faisant les liaisons entre les ID des un et des autres,

mais la base de données commune , c'est un projet que je veut developper

En attendant, ils ont souvent des listes de codes, id ou libellés, et ne maitrisent pas RechercheV pour integrer certains champs à ces listes pour faire leurs traitements

Ma fonction marche la, pas super propre mais j'ai pas trouvé mieux :

Etape 1.

Code :

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

=Referentiel("A", "CD_SAP", "LIB_ARCGIS")

Ce qui pond dans ma cellule :

Code :

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

=INDEX('C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$A:$X;EQUIV(A35;'C:\Users\XX0000\Desktop\[ref.xlsm]Ref'!$D:$D;0);22)

Apres, copier / collage valeur dans la meme cellule et roule