Utilisation de Dictionary avec des fonctions de recherche perso

[Arnaud-Denti]

Bonjour,

J'ai créer un fichier qui comporte 15 feuilles de data (toutes ont 8 colonnes, et entre 5000 et 500 000 lignes) que je met à jour à partir d'une base de données access.
J'ai créé des fonctions personnalisées(15) qui permettent avec les bons argument de récupérer la valeur recherchée (cela peut être du texte ou des nombres).
Au final le classeur peut contenir 8000 à 15000 cellules contenant des formules...
Du coup lorsque je met à jour toutes les fonctions(que le classeur passe en mode calcul) cela met un certain temps...qui varie de 15 secondes à 3 minutes (suivant que les données récupérées d'access avaient 50 000 lignes ou 500 000 lignes et suivant où se trouve la valeur recherchée, au début ou à la fin)
J'ai utilisé dans mes fonctions find.
Mon problème est le temps de calcul je voudrais le réduire au minimum, pour cela j'ai déjà mis à off tout ce qui devait l'être, banni les select, limité les interactions entre feuilles etc
je suis tombé sur la fonction dictionary, dans une discussion du forum cela semble très rapide, je cherche à mettre à jour plusieurs dico lors du chargement des données access (là le temps m'importe beaucoup moins), puis dans mes fonctions faire appel à ces dico pour ma recherche, mais je ne sais pas comment faire, pour qu'ils ne soient pas volatils?
merci d'avance de votre aide

 

[BOISGONTIER]

Bonjour,

Exemple de fonction perso matricielle avec dictionnaire

http://boisgontierjacques.free.fr/pages_site/recherchev.htm#FonctionRechVM

http://boisgontierjacques.free.fr/fichiers/Cellules/rechvJB.zip

JB

 

[Arnaud-Denti]

merci beaucoup de ta réponse je vais regarder cela

 

[Arnaud-Denti]

Je n’ai pas très bien compris comment utiliser cela dans mon exemple, peut être que je n’ai pas bien exprimé ce que je recherche .
J’ai une feuille de 8 colonnes et 250 000 lignes (1 cas = 5000 lignes je peux avoir jusqu’à 100 cas)
La première colonne correspond au Tag que je veux rechercher, la deuxième à sa description, les 5 colonnes suivantes sont des valeurs et la dernière de nouveau un texte.
Cela est multiplié par 15 feuilles (le nombre de ligne y est variable)
J’ai créé 15 fonctions très simples qui permettent à un utilisateur de récupérer ce qui l’intéresse, à savoir la description ou la valeur de la colonne 3 ou de la 5 …
Les différents utilisateurs ont fait des fichiers qui peuvent contenir entre 8000 et 15000 formules.
En changeant une cellule correspondant au cas que l’on veut étudier toutes les formules se mettent à jour. (Actuellement cela met 3 secondes pour 5 cas et 3 minutes pour 80 cas testé hier sur un serveur 24 cores)
Si je mets la boucle pour alimenter le dico dans la fonction je perds tout l’intérêt du truc (le temps explose c’est ce que j’ai eu hier plus de 10 minutes).
Ce que j’aurai voulu c’est charger la feuille dans le dico, que le dico reste « actif » et « rempli » tant que je ne sors pas de la feuille puis que je refasse mes fonctions de façon à récupérer ce que je recherche

Ci-dessous une des fonctions actuelle

Public Function MBuy(Model, Product, Cas, Optional Min_Max_DJ_D_Price) As Variant
Dim rr As Range
Dim BuyVal As String
' par défaut si la dernière option n'est pas renseignée on récupère l'activité (2èm position)
If IsMissing(Min_Max_DJ_D_Price) Then Min_Max_DJ_D_Price = 2
'la valeur à rechercher
BuyVal = Cas & "-" & Model & "-" & Product
Set rr = Worksheets("RW_PurchasesW").Range("a:a").Find(What:=BuyVal, LookAt:=xlPart, MatchCase:=True)

If rr Is Nothing Then
Else
' si la valeur existe on renvoie l'argument choisit dans la fonction
MBuy = rr.Offset(0, Min_Max_DJ_D_Price).Value
End If
End Function

J'ai oublié de dire également que le fichier est dynamique, c'est à dire que les utilisateurs peuvent ajouter, retrancher des feuilles, ajouter des formules etc ......
Et lorsqu'ils l'utilisent en changeant juste le numéro du cas tout se met à jour

Il est possible que ce que je recherche ne soit pas possible, mais si quelqu’un a une idée je suis preneur, merci d'avance

 

[Dranreb]

Bonjour.
Bien que ça n'ait plus été proposé depuis un bon bout de temps, j'ai toujours dans un coin de quoi fabriquer des dictionnaires arborescents et un module pour les utiliser dans des formules de cellules (Function DicArbo, SousClés et RestitDico).

 

[Arnaud-Denti]

Bonjour Dranreb,

je suis très intéressé si tu peux retrouver ces codes et les partager, je suis preneur

 

[gosselien]

Bonjour,

si tu déposais une copie anonymisée de ce fichier avec 100 lignes et ce que tu veux obtenir, tu aurais une meilleure aide, plus concrète

P.

 

[Dranreb]

Le principe:
Bien sûr Excel ne connaît pas les objets Dictionary.
Alors on se contente de produire un texte d'identification de celui créé, que les fonctions qui l'exploitent savent retrouver.

 

[Dranreb]

Bonjour.

…mais je ne sais pas comment faire, pour qu'ils ne soient pas volatils?

Un Dictionary est toujours volatile en ce sens qu'il n'est défini qu'en mémoire.
On peut toutefois limiter le nombre de ses reconstructions nécessaires (notamment après une réinitialisation accidentelle du projet VBA) en le déclarant comme variable globale, Public ou Private. Ne le reconstruire dans une fonction qui va l'utiliser que s'il Is Nothing, et de préférence en chargeant préalablement dans un tableau les données servant à l'alimenter.

Dans votre Function MBuy, pourquoi …Find … LookAt:=xlPart ?
Un Dictionary ne permettrait qu'un équivalent à Find … LookAt:=xlWhole