XL 2013 CreateObject("scripting.dictionary") et/ou variable tableau ?

[BenHarber]

Bonjour le Forum,
J'ai trouvé la fonction VBA suivante sur développez.net (merci Mchel M) qui me permet de compter sans doublon les enregistrements d'une plage de cellules. Elle fonctionne bien (même si je ne vois pas trop à quoi CreateObject("scripting.dictionary") correspond...)

A présent, je souhaiterais utiliser les différentes valeurs trouvées dans cette plage de cellules. Je pensais pour cela les enregistrer dans une variable tableau [du type : monTab(0 à n)] : ma question est de savoir si je peux pour cela utiliser "dico" (?) ou si je dois obligatoirement créer une nouvelle variable tableau ?
Merci d'avance pour vos idées toujours constructives !
BH
Function compter_uniques(plage As Range) As Long

Set dico = CreateObject("scripting.dictionary")
For Each cellule In plage
ref = cellule.Value
If Not dico.exists(ref) Then
dico.Add ref, ref
End If
Next
compter_uniques = dico.Count
End Function

 

[BOISGONTIER]

Test sur un ensemble de 50.000 chaines de la colonne A
On aboutit au même résultat.

Sub CollectionSansDoublons()
  Dim Maliste As New Collection
  On Error Resume Next
  n = 50000
  Tbl = Range("A2:A" & n).Value
  t = Timer()
  For i = 1 To n
    temp = Tbl(i, 1)
    Maliste.Add Item:=temp, Key:=temp
  Next i
  On Error GoTo 0
  Dim a(): ReDim a(1 To Maliste.Count)
  For i = 1 To Maliste.Count
    a(i) = Maliste(i)
  Next i
  [c2].Resize(Maliste.Count) = Application.Transpose(a)
  MsgBox Timer() - t
End Sub

Sub DicoSansDoublons()
  Set d = CreateObject("scripting.dictionary")
  n = 50000
  Tbl = Range("A2:A" & n).Value
  t = Timer()
  For i = 1 To n - 1
    temp = Tbl(i, 1)
    d(temp) = ""
  Next i
  Tbl = d.keys
  [D2].Resize(d.Count) = Application.Transpose(Tbl)
  MsgBox Timer() - t 
End Sub

Boisgontier

 

[Staple1600]

Re

Snif
Dico=>0,453125
Collection=>25,64063
Excel 2013 32bits - W10 64bits 4Go
Si quelqu'un peut m'expliquer les 25 secondes

NB: Résultats obtenus avec le fichier de JB (post#53 )

 

[jmfmarques]

Bonjour Staple1600

Si quelqu'un peut m'expliquer les 25 secondes

j'avais décidé de me tenir à l'écart de la suite de cette discussion, mais ta question appelle les réponses qui suivent :
- 1) je ne confonds pas la vitesse d'abondement d'une collection ou d'un dico avec la vitesse d'utilisation de leur contenu. Ce sont pour moi deux choses distinctes
- 2) Que l'on veuille bien faire l'essai/comparaison limité au seul abondement d'un dico et d'une collection. Et qu'on aille (pour voir) jusqu'à 500000 items aléatoires (y compris avec le code, tel qu'écrit)
-3) si c'est le nombre résiduel (après suppression des doublons), qui intéresse, la propriété Count suffit, in fine
- S'il s'agit par contre d'abonder ensuite une plage par les seuls non doublons, je ne vois plus la nécessité d'utilisation : ni d'une collection, ni d'un dico (relire ce que j'en ai dit plus haut. Et relire le besoin exprimé ).
Amitiés à tous

 

[BOISGONTIER]

>Si quelqu'un peut m'expliquer les 25 secondes

C'est une simple constatation.

Boisgontier

 

[Staple1600]

Bonsoir jmfmarques

Merci de ta réponse.
Ce qui m'interpellait c'est que les résultats des autres XLDiens qui on fait le test sont assez proche: autour de 3 à 4 secondes.
Comment se fait-il que je sois le seul autour de 25?
Qu'est-ce qui peut expliquer une telle différence?
1) Intel vs AMD
2) Version d'Excel
3) Les add-on lancés dans mon Excel (PowerQuery et FuzzyLookup)
4) Les autres programmes qui tournent en tâche de fond
(anti-virus etc..)

 

[Staple1600]

Re


Merci. JB
Mais cela n'explique pas pourquoi les autres ont des résultats assez proches et moi qui fait un bond à 25 secondes !

 

[jmfmarques]

Tu es peut-être en situation de débordement de la mémoire vive pure et passé maintenant (beaucoup plus lent et d'une lenteur pouvant être accrue, alors, par une fragmentation importante, puisque accès disque nécessaires) en utilisation du fichier de pagination (en "swapping", donc)
Mais ce ne sont là que des suppositions (il me faudrait être sur ta machine)

 

[Staple1600]

Re

je viens de refaire le test (en demarrant Excel en mode sans échec)
C'est guère mieux (voir pire)
Collection: 26,07813
Dico: 0,453125

 

[Staple1600]

Re

Mon OS n'est pas sur un HD SSD
(C'est un W7 upgradé en W10)
Pas de fragmentation (selon Windows)
C'est pas grave, cela me permets de donner du temps au temps

EDITION1: En enregistrant le fichier en *.xlsb
0,46875
26,78125

EDITION2; En ajoutant ScreenUpdating=False
0,421875
25,14063

EDITION3: En mettant sur xlCalculationManual
0,421875
24,92188

 

[Dranreb]

@BOISGONTIER, Il est probable que vous n'ayez pas la meilleure méthode pour constituer une liste non classée sans doublon avec une Collection, dans la mesure où vous la réexplorez pour constituer la liste. Il vaudrait mieux la constituer au moment du rangement dans la Collection, chaque fois qu'il n'échoue pas.

 

[cp4]

Re

Mon OS n'est pas sur un HD SSD
(C'est un W7 upgradé en W10)
Pas de fragmentation (selon Windows)
C'est pas grave, cela me permets de donner du temps au temps

EDITION1: En enregistrant le fichier en *.xlsb
0,46875
26,78125

EDITION2; En ajoutant ScreenUpdating=False
0,421875
25,14063

EDITION3: En mettant sur xlCalculationManual
0,421875
24,92188

Bonjour,
Peut-être en défragmentant sous dos (en passant par la commande cmd) et taper cette instruction
defrag C: -b , C étant le disque où se trouve le système.
En espérant que ça résoudra ton problème de vélocité.

Bonne journée.

 

[BOISGONTIER]

Annulé!

 

[dysorthographie]

bonjour,
pour ce qui me concerne
Collection := 2,81640625
Dico :=0,11328125

Excel 2007

 

[BOISGONTIER]

>Collection := 2,81640625
>Dico :=0,11328125

Merci a tous ceux qui ont testé.
-Confirmation de ce que tout le mode sait depuis longtemps
-Pour indexer un Array, c'est simple avec un dico. Avec Collection, il faut 2 Collections (clé+item)
Le seul avantage de Collection sur le Dico, c'est la compatibilité MAC.

Boisgontier

 

[jmfmarques]

Je croyais avoir été très clair, mais constate que tel n'est pas le cas.

Je propose donc à ceux qui veulent tester ce (et uniquement ce) qu'il y a à tester, à savoir l'abondement et non son exploitation ultérieure, le petit test suivant :

Const nb As Long = 500000

Sub restons_pur()
   Dim t As Single, i As Long, temp As Long, k As String
   t = Timer()
   Dim Maliste As New Collection
   For i = 1 To nb
     temp = Int(Rnd * nb)
     k = CStr(temp)
     If Not deja(Maliste, k) Then
       Maliste.Add temp, k
     End If
   Next
   dureecol = Maliste.Count & " ITEMS EN  " & Timer - t & " SECONDES"
   t = Timer()
   Set d = CreateObject("scripting.dictionary")
   For i = 1 To nb
     temp = Int(Rnd * nb)
     d(temp) = ""
   Next i
   dureedico = d.Count & " ITEMS EN  " & Timer() - t & " SECONDES"
   MsgBox "par collection " & dureecol & vbCrLf & _
   "par dico " & dureedico
End Sub


Function deja(c As Collection, k As String) As Boolean
    On Error GoTo erreur
    c.Item k
    deja = True
erreur:
End Function

Voilà tout et je n'y reviendrai pas.