Optimisation ludique du vba

Le code VBA ne demande qu’à être optimisé pour rendre une base Access efficace. Bien sûr, mais l’optimisation est-elle réelle lorsque vous optez pour un code à la place d’un autre ? Le mieux n’est-il pas l’ennemi du bien ? Avez-vous mesuré l’impact de votre optimisation en terme de délai d’exécution ?

Jouons à l’optimisation VBA :

La ligne      If Not (X Or Y) Then

Est-elle plus efficace que

La ligne:     If (Not X) And (Not Y) Then

A première vue, oui, car on économise un test Not à chaque exécution et si on se trouve dans un millier de boucles, on va gagner énormément de temps. Erreur !

Exemple :

Private Sub Commande1_Click()
    Dim X As Boolean, Y As Boolean, I As Long
    X = False
    Y = False
    For I = 1 To 100000000
        If (Not X) And (Not Y) Then
        Else
        End If
    Next I
End Sub

Temps économisé : 1,5 seconde ! pour 100.000.000 de boucles ! soit une économie de 1,5/100 de seconde pour 1.000.000 de boucles.

La solution 1 est-elle plus lisible que la solution 2 pour le développeur ? Non !  La solution 2 est beaucoup plus claire lors de la maintenance de votre code.

Méfiez-vous des fausses optimisations.

Une optimisation réelle :

Un exemple d’optimisation sur lequel il faut se pencher, chaque fois que des boucles imbriquées sont en jeu. Vous voulez placer des valeurs dans des colonnes et des lignes  et bien sûr vous faîtes une boucle sur les lignes et dans cette boucle une boucle pour les colonnes. Faut-il opter pour la boucle des colonnes dans la boucle des lignes ou la boucle des lignes dans la boucle des colonnes ? Le nombre de boucles est-il identique ?

    For I = 1 To 2
        For J = 1 To 100000000
        Next J
    Next I


Est-elle identique à :

    For I = 1 To  100000000
        For J = 1 To 2 
        Next J
    Next I

Dans le résultat obtenu, oui , mais dans le nombre de boucles ?

Dans le 1^er cas, on exécute 100000000 x 2 de boucles J , plus 2 boucles I soit 200000002 de boucles.

Dans le 2^e cas , on exécute 2 x 100000000 de boucles J , plus 100000000 de boucles I, soit 300000000 de boucles, soit 1/3 de plus d’instructions de boucles.

En temps d’exécution, la 2e boucle demandera 13 secondes alors que la 1ere boucle ne demandera que 3 secondes.

Ce type de procédure demande que le développeur se penche sérieusement dessus et l’optimise au maximum. Dans le principe, et si c’est réalisable, il faut partir de la boucle la plus petite à la boucle la plus grande, autrement dit, la boucle la plus petite doit être à l’extérieur et la boucle la plus grande à l’intérieur. 

Select…case   vs   If…Else

De même, le remplacement d’une instruction Select case par un IF permet de gagner 35% du temps d’exécution.

        If I <= 20 Then
            A = 1
        ElseIf I < 100 Then
            B = 1
        Else
            C = 1
        End If

permet de gagner 35% sur :

        Select Case I
            Case Is <= 20
                A = 1
            Case Is >= 21 < 100
                B = 1
            Case Else
                C = 1
        End Select

Optimisation emblématique :

Parmi les optimisations les plus nécessaires il est le test de valeur nulle et de chaîne vide d’un contrôle de formulaire ou d’une variable variant.

La ligne :

If IsNull(Me.ClientNom) Or Me.ClientNom = "" Then

Est utilisée à de maintes reprises dans une application. Cette instruction peut être remplacée par :

If Nz(Me.ClientNom, "") = "" Then

Qui donnera le même résultat avec un gain de temps d’exécution de 40%.

A vous de présenter des cas d’optimisations ludiques dans les commentaires.