Discussion :

[Grandmaster_splinter]

Bonjour à tous,

Je m'excuses par avance d'ouvrir une nouvelle discussion sur l'optimisation de code VBA, mais ma question porte plutôt sur : pourquoi ma modification n'améliore pas le temps d'exécution de ma macro ?

Le contexte est que ma macro liste tous les fichiers d'une arborescence. Or cette arborescence est souvent très lourde avec plusieurs dizaines de milliers de fichiers.
J'avais un programme comme base de travail, je vous montre ici la fonction intéressante qui récursivement va chercher tous les fichiers :

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Private Function ScanFolder(FolderName As String)
    Dim oFSO As Scripting.FileSystemObject
    Dim ObjDossier As Object
    Dim ElementFichier As File
    Dim ElementDossier As Folder
    Dim ligne As Integer
    Dim Taille As Long
    Dim nomFic As String
    Dim dossierFic As String
    Dim typeFic As String
    Dim dateModif As Date
 
    Set oFSO = New Scripting.FileSystemObject
 
    For Each ElementFichier In oFSO.GetFolder(FolderName).Files
        If Range("A4").End(xlDown).Row > 65500 Then
            ligne = 5
        Else
            ligne = Range("A4").End(xlDown).Row + 1
        End If
        nomFic = ElementFichier.Name
        dossierFic = ElementFichier.ParentFolder & "\"
        typeFic = ElementFichier.Type
        Taille = Round(ElementFichier.Size / 1024, 2)
        dateModif = ElementFichier.DateLastModified
 
        Range("A" & ligne).Value = nomFic
        Range("B" & ligne).Value = "Ouvrir"
        Range("C" & ligne).Value = dossierFic
        Range("D" & ligne).Value = typeFic
 
        If Taille < 1024 Then
            Range("E" & ligne).Value = Taille & " Ko"
        Else
            Range("E" & ligne).Value = Round(Taille / 1024, 2) & " Mo"
        End If
        Range("F" & ligne).Value = dateModif
    Next ElementFichier
 
    For Each ElementDossier In oFSO.GetFolder(FolderName).SubFolders
        ScanFolder ElementDossier.Path
    Next ElementDossier
End Function

La partie intéressante est que chaque cellule de la feuille est modifier une par une. Or selon mes recherches ceci pourrait expliqué la lenteur du programme. J'ai donc modifier cette fonction pour arriver à ça :

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Private Function ScanFolder(FolderName As String, tableau As Variant)
 
    Dim oFSO As Scripting.FileSystemObject
    Dim ElementFichier As File
    Dim ElementDossier As folder
    Dim ligne As Long
    Dim Taille As Long
    Dim tailleTab As Long
 
    Set oFSO = New Scripting.FileSystemObject
 
    For Each ElementFichier In oFSO.GetFolder(FolderName).files
 
        Taille = Round(ElementFichier.Size * 0.0009765625, 2)
 
        tailleTab = UBound(tableau, 2)
 
        tableau(0, tailleTab) = ElementFichier.Name
        tableau(2, tailleTab) = ElementFichier.ParentFolder & "\"
        tableau(3, tailleTab) = ElementFichier.Type
        If Taille < 1024 Then tableau(4, tailleTab) = Taille & " Ko" Else tableau(4, tailleTab) = Round(Taille * 0.0009765625, 2) & " Mo"
        tableau(5, tailleTab) = ElementFichier.DateLastModified
 
        ReDim Preserve tableau(8, tailleTab + 1)
 
    Next ElementFichier
 
    For Each ElementDossier In oFSO.GetFolder(FolderName).SubFolders
        ScanFolder ElementDossier.path, tableau
    Next ElementDossier
 
    Range("A5").Resize(UBound(tableau, 2), UBound(tableau, 1)) = Application.Transpose(tableau)
 
    Set oFSO = Nothing
 
End Function

Je passe en paramètre de cette fonction un tableau dynamique de deux dimensions. La fonction ne modifie donc que ce tableau et ensuite à la toute fin va recopier le tableau sur la feuille.
La fonction marche bien, mais théoriquement et à en croire de nombreux sites le gains de temps devrait être considérable puisqu'on passe de 6 modifications de feuille par fichier à une seule pour tout le programme.
Mais malheureusement je n'observe aucun gains de temps lors de l'exécution de la macro. Je viens donc vers vous afin de savoir si vous auriez une idée du pourquoi ? Si j'ai loupé quelque chose, ou mal compris.. Je n'en sais rien..

Quelques infos complémentaires :
- Les fichiers sont situés sur un serveur distant ce qui peut expliquer que l'exécution soit plus longue qu'en local, mais ça n'explique pas pourquoi ce temps ne diminu pas.
- J'ai essayé aussi beaucoup d'autres méthodes afin d'optimiser le temps (dont certaines que vous pouvez voir dans le code), mais ma question porte principalement sur la partie que je vous explique plus haut.

En tout cas merci d'avance pour votre aide,

Cordialement,

[clementmarcotte]

Bonjour,

Si tu veux moindrement de performance oublie FSO. Il y a des choses intéressantes, mais la vitesse n'en fait pas partie.

Regarde plutôt ceci

Et si tu veux creuser encore plus

[galopin01]

bonsoir,
Je ne suis pas un grand expert... mais il me semble qu'un "Redim Preserve" à chaque tour de boucle soit à peu près aussi chronophage qu'écrire directement sur la feuille.
A tout prendre j'utiliserai un Array largement surdimensionné quitte à le Redim tout aussi largement une seule fois si tu n'as pas été assez généreux la première fois...
A+

[patricktoulon]

bonsoir
utilise la fonction dir dans une fonction(macro) recursive pour les subfolders

tu a plein d'exemples sur le forum et les contributions

dvp est muni d'un moteur de recherche utilise le!!

[Grandmaster_splinter]

Bonjour à tous, et merci pour votre réactivité.

@galopin01 ton idée m'avait l'air très pertinente mais malheureusement après l'avoir implémenté je n'observe pas d'amélioration particulière...

@clementmarcotte et @patricktoulon merci pour votre recommendation de passer par Dir().
Mais j'ai rencontré un problème avec les noms de fichiers que je vais chercher sur le serveur distant.
L'expert que j'ai contacté en interne m'a dis que le problème vient de l'encodage du serveur (j'ai oublié le terme qu'il a utilisé mais ce n’était pas encodage). Du coup je ne peux pas utiliser la fonction Dir(), par contre FSO comble se problème.

Selon lui la seul manière d'optimiser ce code est de rendre la fonction itérative, je vais donc me pencher sur un algo avec une pile afin de voir si je constate des amélioration.

En tout cas merci à vous pour vos conseil !