Variable dictionnaire ou tableau Résolu

Question

Bonjour à tous,

J'ai un fichier Excel très simple avec une seule colonne de près de 7000 lignes, contenant des codes produit.
Les informations des produits se trouvent dans un autre fichier csv, qui lui dépasse les 10 000 lignes, et qui a de nombreuses colonnes dont je n'ai pas besoin.

Ce que je fais pour l'instant, c'est importer le fichier csv sur une autre feuille, que je nomme "Temp".
Ensuite à l'aide d'innombrables formules Index/Equiv, je récupère les données dont j'ai besoin, je les colle en valeur et je supprime la feuille "Temp".

Le tout en Vba me prend environ 10 secondes, c'est assez long puisque je dois faire la manoeuvre plusieurs fois par heure (les données produits changent régulièrement).

Je me suis donc intéressé aux variables Dictionnaire et Tableau, mais je n'y arrive pas vraiment. 
Rien que sur une colonne recherchée, je perds du temps par rapport à un Index-Equiv.

Ci-dessous je vous joins ce que j'ai réussi à coder. 
Ce n'est pas très convainquant. Pourriez-vous éventuellement m'aider à l'améliorer ? 

' Création du dictionnaire et alimentation par boucle
Set prod = CreateObject("Scripting.Dictionary")
For i = 2 To Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
    If Not prod.Exists(Sheets("Temp").Cells(i, 1).Value) Then prod.Add Sheets("Temp").Cells(i, 1).Value, Sheets("Temp").Cells(i, 3).Value ' Je stocke le code produit et la troisième colonne
Next

' Récupération des données dans la première feuille
Sheets("Produits").Select
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
  Cells(j, 3).Value = prod.Item(Cells(j, 1).Value)
Next


Configuration: Windows / Firefox 45.0

Patrice33740 · Answer

Bonjour,

Le plus rapide est d'utiliser des tableaux :
Sub test()
Dim P As Object
Dim T, L&, D&
  With Worksheets("Temp")
    D = .Cells(.Rows.Count, 1).End(xlUp).Row
    T = .Range("A2:C" & D).Value
  End With
  Set P = CreateObject("Scripting.Dictionary")
  For L = LBound(T) To UBound(T)
    P(T(L, 1)) = T(L, 3)
  Next L
  With Worksheets("Produits")
    D = .Cells(.Rows.Count, 1).End(xlUp).Row
    T = .Range("A2:C" & D).Value
    For L = LBound(T) To UBound(T)
      T(L, 3) = P(T(L, 1))
    Next L
    .Range("A2:C" & D).Value = T
  End With
End Sub

1Globule · Answer

Merci Patrice,

Effectivement, je n'y étais pas du tout...

Pourrais-tu m'éclairer sur ton code ?

Cette ligne : P(T(L, 1)) = T(L, 3) me semble signifier que :
P est le dictionnaire auquel on ajoute une clé (la ligne L, colonne 1) du tableau T.
Et la valeur associée à cette clé est égale à la ligne L, colonne 3 du tableau T.

Si c'est correct, comment dois-je faire pour récupérer plusieurs valeurs ? 
J'ai besoin de la colonne 3, mais aussi de la 6 et de la 13, or il me semblait que les dictoinnaires ne supportaient qu'une valeur par clé.

Merci pour ton aide,

Patrice33740 · Answer

Bonjour,

Effectivement, la ligne P(T(L, 1)) = T(L, 3), ajoute la clé du tableau T si elle n'existe pas et y associe l'item de la colonne 3. Si la clé existe, elle y associe le nouvel item.
Pour mémoriser 3 valeurs, plusieurs solutions se présentent, plus ou moins gourmandes en mémoire vive.

J'ai opté pour une des moins gourmandes : utiliser 4 tableaux et un dictionnaire en mettant les 3 valeurs dans l'item séparées par un séparateur spécial (hors caractères utilisables).
Sub test()
Dim P As Object
Dim T1, T2, T3, T4, L&, D&
  ' definir les tableaux sources
  With Worksheets("Temp")
    D = .Cells(.Rows.Count, 1).End(xlUp).Row
    T1 = .Range("A2:A" & D).Value  'clé
    T2 = .Range("C2:C" & D).Value  '1° valeur
    T3 = .Range("F2:F" & D).Value  '2° valeur
    T4 = .Range("M2:M" & D).Value  '3° valeur
  End With
  ' mémoriser les valeurs dans un dictionnaire
  Set P = CreateObject("Scripting.Dictionary")
  For L = LBound(T1) To UBound(T1)
    P(T1(L, 1)) = T2(L, 1) & Chr(1) & T3(L, 1) & Chr(1) & T4(L, 1) 'concatène les 3 valeurs dans le dictionnaire
  Next L
  ' restituer les valeurs
  With Worksheets("Produits")
    D = .Cells(.Rows.Count, 1).End(xlUp).Row
    T1 = .Range("A2:A" & D).Value   'clé
    T2 = .Range("C2:C" & D).Value   'colonne de destination de la 1° valeur (on pourrait changer de colonne)
    T3 = .Range("F2:F" & D).Value   'colonne de destination de la 2° valeur (on pourrait changer de colonne)
    T4 = .Range("M2:M" & D).Value   'colonne de destination de la 3° valeur (on pourrait changer de colonne)
    For L = LBound(T1) To UBound(T1)
      T2(L, 1) = Split(P(T1(L, 1)), Chr(1))(0)
      T3(L, 1) = Split(P(T1(L, 1)), Chr(1))(1)
      T4(L, 1) = Split(P(T1(L, 1)), Chr(1))(2)
    Next L
    .Range("C2:C" & D).Value = T2   'Ici on remet les valeurs en place
    .Range("F2:F" & D).Value = T3  '  "
    .Range("M2:M" & D).Value = T4  '  "
  End With
End Sub
On aurait pu utiliser un seul tableau, le code serait plus simple mais cela mémorise de nombreuses colonnes inutilement.

1Globule · Answer

Le code se complique   :/

En tout cas il fonctionne très bien (je gagne plus d'une seconde juste sur cette partie).

Un tout petit souci : Si le produit n'existe pas dans la feuille Temp (ce qui ne devrait pas arriver, mais qui malheureusement arrive de temps en temps), j'ai un plantage (L'indice n'appartient pas à la sélection). 

Est-ce qu'il pourrait renvoyer un #N/A, si possible ?
Si tu veux bien m'aider sur ce dernier point, et ensuite j'étudierais le code en détails pour récupérer des données qui se trouvent dans d'autres feuilles. A priori pas de date (ouf !).

Un grand merci en tout cas !

1Globule · Answer

Merci mille fois Patrice.

Ce code est parfait et très rapide. Je vais maintenant essayer de le transposer aux autres données que je dois récupérer.
Je place le sujet en Résolu, mais j'ai comme l'impression que j'aurais encore besoin d'aide sur les varaibles tableaux et dictionnaires (qui restent quand même un peu mystérieuses pour moi).

Merci encore.

Variable dictionnaire ou tableau

5 réponses

Discussions similaires

Newsletters