Développement Excel : accélérer le chargement des contrôles ListBox ou ComboBox

Si vous développez vos propres applications Excel, vous avez certainement déjà eu recours aux listes déroulantes – ces contrôles placés sur les formulaires qui permettent d’afficher un ensemble de valeurs les unes sous les autres et, accessoirement, d’en sélectionner une en particulier.

Dans cet article, nous allons voir comment peupler efficacement une liste déroulante en vue d’accélérer considérablement le code, notamment lorsque vous avez beaucoup d’éléments à lister.

Il existe 2 contrôles de la bibliothèque MS Forms 2.0 (celles des contrôles de base) permettant de créer des listes déroulantes :

  • ListBox qui affiche plusieurs lignes les unes sous les autres dans un cadre
  • ComboBox qui n’affiche que la ligne sélectionnée
Exemple de ComboBox

Contrôle ComboBox de la bibliothèque MS Form 2.0

Ces 2 contrôles étant des cousins très proches (ils partagent un grand nombre de propriétés, méthodes et événements en commun), l’astuce expliquée en détail dans cet article peut alors s’appliquer indistinctement aux 2.

La méthode classique que nous appellerons « méthode A »

La plupart des développeurs Excel utilisent la méthode AddItem afin d’ajouter un élément à leur liste. Cela donne dans le cas d’une liste à une seule colonne :

maListeBox.AddItem "ma valeur"

Si l’on prend un cas un peu plus concret où il s’agirait de peupler une liste à 100 lignes et 5 colonnes, cela donnerait le code suivant :

Sub Methode_A()
    
    Dim monForm As New UserForm1
    Dim i As Long
    
    For i = 0 To 99
        monForm.ListBox1.AddItem
        monForm.ListBox1.List(i, 0) = i  'on inscrit par exemple un identifiant unique de ligne
        monForm.ListBox1.List(i, 1) = "lib_A_" & i 'on inscrit le libellé de 1ère colonne
        monForm.ListBox1.List(i, 2) = "lib_B_" & i 'on inscrit le libellé de 2ème colonne
        monForm.ListBox1.List(i, 3) = "lib_C_" & i 'on inscrit le libellé de 3ème colonne
        monForm.ListBox1.List(i, 4) = "lib_D_" & i 'on inscrit le libellé de 4ème colonne
    Next i

    monForm.Show
End Sub

Comme vous le voyez, on incrémente le contrôle ligne à ligne.

Et bien il existe une méthode beaucoup plus rapide et peu connue qui consiste à passer l’ensemble des valeurs au contrôle ListBox en une seule fois.

Méthode B

Comme souvent, le fait de lire ou écrire les valeurs d’un tableau en une fois permet d’accélérer considérablement la vitesse d’exécution. Vous connaissez certainement déjà cette technique en VBA pour la lecture/écriture depuis/vers une plage de cellules entière, n’est-ce pas? Une astuce similaire existe pour affecter les valeurs à un contrôle ListBox ou ComboBox. Si nous adaptons le code VBA plus haut, cela donne :

Sub Methode_B()

    Dim monForm As New UserForm1
    Dim i As Long
    Dim mesValeurs(99, 4) As Variant

    For i = 0 To 99
        mesValeurs(i, 0) = i 'on inscrit par exemple un identifiant unique de ligne
        mesValeurs(i, 1) = "lib_A_" & i 'on inscrit le libellé de 1ère colonne
        mesValeurs(i, 2) = "lib_B_" & i 'on inscrit le libellé de 2ème colonne
        mesValeurs(i, 3) = "lib_C_" & i 'on inscrit le libellé de 3ème colonne
        mesValeurs(i, 4) = "lib_D_" & i 'on inscrit le libellé de 4ème colonne
    Next i

    monForm.ListBox1.List = mesValeurs 'on charge les valeurs en une fois dans le contrôle ListBox1
    monForm.Show
End Sub

Dans le code ci-dessus, nous dimensionnons d’abord le tableau mesValeurs, lui affectons des valeurs, puis l’affectons directement à la propriété .List du contrôle ListBox. C’est le fait de ne pas accéder sans cesse au contrôle qui permet de gagner en vitesse d’exécution.

Tests comparatifs des méthodes A et B

J’ai effectué des tests sur les 2 méthodes, pour des listes à taille variable allant de 100 à 50 000 lignes et de 2 à 10 colonnes. Les résultats sont sans appel !

Temps d’affichage en secondes pour la méthode A :

Résultats bruts méthode A

Résultats bruts méthode A (temps d’exécution en secondes)

Temps d’affichage en secondes pour la méthode B :

Résultats bruts méthode B

Résultats bruts méthode B (temps d’exécution en secondes)

D’un coup d’œil, on constate que la méthode B est nettement plus rapide que la A, notamment lorsqu’il est question d’afficher des listes volumineuses.

Pour que les choses soient plus claires, voici la matrice représentant le facteur de gain de la méthode B sur la méthode A :

Ratios obtenus en divisant les temps d'exécution de la méthode A sur ceux de la méthode B

Ratios obtenus en divisant les temps d’exécution de la méthode A sur ceux de la méthode B

Nous constatons que le gain est d’autant plus important que le nombre de valeurs à afficher est élevé.

Conclusion

Nous voyons ici que le passage en une seule fois des valeurs à la liste permet d’obtenir un gain de temps crucial sur le chargement, en particulier pour les applications VBA qui s’interfacent avec des bases de données. Cette méthode rend ainsi possible l’utilisation d’Excel comme système de gestion de BDD relationnelle.

On néglige bien trop souvent l’optimisation du code dans nos développements Excel. Lorsque l’on applique ces techniques d’optimisation à chaque niveau du développement, le gain peut être énorme. C’est ce qui fait la différence entre « une macro Excel » et un développement professionnel.

Cette astuce de programmation représente une partie seulement des optimisations de code que Nexicube recherche et met en œuvre dans ses propres développements. Je vous en présenterai quelques unes dans un prochain article.
De votre côté, n’hésitez pas à nous faire part de vos trouvailles ou commentaires, toujours les bienvenus.

Laisser un commentaire