OPTIMISATION EN VISUAL BASIC APPLICATION
Avant propos
Voici un petit recueil d'idées pour réaliser une optimisation des programmes en VBA. IL résulte de mes propres expériences et lectures. D'une manière générale, il n'est pas évident de savoir si telle ou telle forme optimise un codage. Il convient par conséquent de tester soi-même, différentes formes pour en garder que les meilleurs. Ce tutoriel hiérarchise un peu ces différentes formes qu’on peut imaginer.
J'indique les performances approximatives des formes non optimisées par rapport à la forme optimisée testée à "vide". La mesure du temps étant réalisée simplement par la fonction "Timer" car seul le classement importe. Il est certain, que cela peut varier beaucoup selon le contexte (appel en cache) ou selon le mode d'exécution. Mais, le classement lui varie assez peu.
La notation (+5*) signifie une exécution 5 fois plus lente (soit environ 400%). Vous trouverez aussi un petit listing, permettant à chacun d’avoir une base pour élaborer ses propres procédures de test.
Dans une certaine mesure, ces optimisations sont applicables pour un programme VB compilé.
Généralités
Par optimisation, je considère la vitesse d'exécution uniquement. Néanmoins, il me semble assez logique, qu'on puisse interpréter l'optimisation de différentes façons. Par exemple, on peut considérer l'optimisation comme l'algorithme le plus court, le plus simple, le plus clair afin d'en faciliter la relecture ultérieure ou encore le moins gourmand en ressource matérielle, etc... Ces aspects sont tout aussi louables que l'optimisation de la vitesse, et se cumulent souvent. En réalité, le programmeur doit souvent faire des compromis entre ces différents aspects.
Pour optimiser une programmation existante, il est assez évident qu’on peut opérer des priorités, dont la première consiste à optimiser les boucles les plus sollicitées, puis en allant vers les répétitions de codes les moins courantes.
Un autre principe évident, c’est de réutiliser au maximum une expression calculée stockée dans une variable, et d’utiliser un nombre restreint de variables.
Mais, la meilleure optimisation reste d’un autre ordre non évoqué ici, consistant à trouver le meilleur algorithme, qui dépend que de votre réflexion !
Optimisation des variables
Déclarations des variables
La déclaration du type de variable est une première optimisation extrêmement classique et indispensable. Afin de n'oublier aucune déclaration, utiliser en tête de module ou de Form etc... la fonction :
Option Explicit
Succinctement, à retenir pour choisir le type :
Bien sur, les déclarations dépendent aussi des besoins.
Mais d’une manière générale, l’emploi du type Variant (autre que dans les appels de fonctions) n'est jamais indispensable, et est à proscrire pour l’optimisation. Ce type est surtout intéressant pour faciliter la programmation aux débutants. Mais cette facilité se paye par une baisse importante des performances.
Une remarque de syntaxe.
A tord, certains pensent que : " Dim a, b, c As Long" déclare les 3 variables en Long. Seul "c" est ici en Long, "a" et "b" sont déclarées en Variant, par défaut. La syntaxe correcte est : " Dim a As Long, b As Long, c As Long"
Utilisation des tableaux (Dim)
Quelques évidences. L’optimisation avec les tableaux, consiste d’abord à déclarer son type. Puis, autant que possible, il faut trouver une " structure " et/ou déterminer le nombre d’éléments, quitte à définir un nombre en excès, afin de ne jamais manipuler une nouvelle fois la déclaration d’un tableau ; par exemple, éviter de réutiliser Redim (Preserve).
L’utilisation d’une variable "tableau" est toujours moins rapide qu’une variable "simple" de type équivalent. Cette caractéristique est générale. La durée est au moins doublée. Donc, n’utilisez pas une variable "tableau" dans des instructions spécifiques, comme par exemple dans une boucle FOR avec FOR M(0)=…etc.
Optimisation sur les nombres
Longueur d'un nombre entier positif ou nul.
Pour connaître le nombre de chiffre.
Forme optimisée :
Ici 0.11 est utile dans le cas ou nb=0, ainsi la formule renvoi 1. Si nb ne peut pas être nul, retirer cette constante.
Forme équivalente : (+3.9*)
Forme équivalente
: (+5*)Programme de test
:Pour un nombre entier négatif
A partir de la forme précédente, il faut adapter la programmation et on rajoutera +1 pour tenir compte du signe négatif ou +1 pour la virgule.
Forme optimisée
:Forme équivalente
:etc...
Parité d’un nombre
Pour tester si un nombre est pair :
Forme optimisée
:Forme équivalente
: (+1.2*)Forme équivalente
: (+1.3*)Forme équivalente
: (+2.1*)Forme équivalente
: (+2.7*)Programme de test
:Pour tester si un nombre est impair :
Forme optimisée
:Remarque
: Entre les deux formes optimisées, cette dernière est plus rapide car il n’y a pas de test d’égalité (=0) prenant du temps dans le traitement. (pair : +1.1*)Calculs des puissances entières
Curieuse fonction que " ^ " qui s’effectue en un temps quasi-constant quelle que soit la puissance, mais reste moins rapide que la multiplication successive jusqu’à puissance 9 environ.
Forme optimisée
:Forme équivalente
:Programme de test
:Calcul de la racine carré
Forme optimisée
:Forme équivalente
: (+3.5*)Programme de test
:Remarque
: comme pour les puissances, on peut cumuler les racines carrés optimisées jusqu’à 4. Ainsi lg = Sqr(Sqr(Sqr(Sqr(nb)))) est la dernière expression plus rapide que lg = nb ^ 0.0625.Division entière
Dans le cas où les nombres peuvent être déclarés en type Long sans dépassement de capacité.
Forme optimisée
:Forme équivalente
: (+2.3*)Programme de test
:La multiplication plus rapide que la division
Dans une expression mathématique, il est légèrement plus rapide d'effectuer une multiplication par l'inverse d'un nombre que de diviser par ce même nombre. Ainsi, par exemple, pour diviser par 4, au lieu de faire (expression)/4, faire 0.25*(expression).
Forme optimisée :
Forme équivalente
: (+1.1*)Programme de test
:Le Pré-calcul
Dans une boucle, il faut sortir tous les calculs d’expression constante.
Forme optimisée
:lg = nb * z
Forme équivalente
: (+2.7*)Programme de test
:Maximum et Minimum de 2 nombres
Forme optimisée
:Forme équivalente
: (+1.5*)Forme équivalente
: (+4.3*)Programme de test
:Attribution de 2 valeurs alternatives
On rencontre assez souvent en programmation des variables utilisées comme indicateurs “ flags ” pouvant prendre que deux valeurs. On peut évidemment utiliser le type Booléan dans ce cas, avec TRUE et FALSE. Mais est-ce le meilleur ? La réponse est positive. Or, ce n’est pas toujours ce choix qui est fait.
Forme optimisée
:Forme équivalente
: (+1.2*)Programme de test
:On observe également sur ce test qu’il est aussi préférable d’utiliser le type Boolean au lieu de Byte même si ces deux types sont codés sur 8 bits chacun.
Optimisation des tests
Vérification d’une condition avec renvoi Booléan
Forme optimisée
:Ici condition pourrait être constituer par une expression comme b=c, soit a=(b=c). L’interprétation est facile, si b=c alors VB remplace en interne cette condition par la valeur vraie (True), puis l’affecte à "a", soit a=True. Même chose dans le cas contraire, avec False. Cette optimisation est valable que pour le renvoi d’une valeur Booléan.
Forme équivalente : (+1.5*)
Programme de test
:Les fonctions conditionnelles
Au vu des très nombreuses formes qu'on peut imaginer pour utiliser les fonctions conditionnelles, je les présente au fur et à mesure, sans être exhaustif.
Dans une série de tests conditionnels et quel que soit le nombre de condition et la complexité des conditions, " If... Then... ElseIf... End If " est le plus performant. Mais il faut savoir aussi regarder le côté pratique du codage, ce qui privilégie sans doute Select Case. De plus, on s’efforcera de mettre la condition qui a le plus de chance d’être vérifiée en premier pour sortir du test le plus tôt possible.
Forme optimisée
:Forme équivalente
: (+1.1*)Forme équivalente
: (+1.2*)Forme équivalente
: (+1.25*)Programme de test
:Programme de test
:Programme de test
:Programme de test
:On pourrait encore se poser la question dans le cas d’une série de conditions rassemblées avec un opérateur logique, par exemple avec OR. La structure " If... Then... ElseIf... End If " pourrait toujours être utilisée et resterait la plus rapide, mais serait très lourde à écrire, puisqu'il faudrait répéter de très nombreuses fois les mêmes instructions...
Forme optimisée
:Programme de test : (+1.25*)
Programme de test
: (+1.25*)Les différentes écritures de If
En VBA, les différentes formes d'écriture de l'instruction "IF" ont une influence sur la vitesse d'exécution. (Cette différence n'existe pas après compilation avec VB.)
Forme optimisée
:Forme équivalente
: (+1.1*)Forme équivalente
: (+1.3*)Programme de test
:Optimisation sur les références
Lorsque des objets sont appelés plusieurs fois, il est avantageux d’utiliser "With". Voici un exemple tiré de l’aide de VBA sur le sujet :
"L'instruction With permet de spécifier un objet ou un type défini par l'utilisateur pour une série d'instructions. Les instructions With accélèrent l'exécution des procédures et permettent d'éviter des saisies répétitives. Vous pouvez imbriquer des instructions With pour en améliorer l'efficacité. L'exemple suivant insère une formule dans la cellule A1, puis formate la police."
Exemple :
Optimisation sur les chaînes
Les fonctions de chaînes
Certaines fonctions sur les chaînes existent en deux versions, correspondant soit à un type de données en Variant, soit en String déclaré par le signe dollar ($). Cette dernière version est la plus rapide. Par exemple, String deviendra String$.
Les fonctions concernées sont :
Chr$, ChrB$, CurDir$, Date$, Dir$, Error$, Format$, Hex$, Input$, InputB$, LCase$, Left$, LeftB$, LTrim$, Mid$, MidB$, Oct$, Right$, RightB$, RTrim$, Space$, Str$, String$, Time$, Trim$, UCase$.
String$
L’appel à certaines de ces fonctions peuvent être encore plus optimisée, en déclarant les paramètres de l’instruction dans une variable adaptée. Utile seulement dans une boucle.
Forme optimisée
:Forme équivalente
: (+1.1*)Forme équivalente
: (+1.1*)Forme équivalente
: (+1.3*)Programme de test
:Chaîne statistique ou dynamique
La déclaration de chaîne peut être fait de deux manières, soit statistique (au maximum 63ko) en définissant une longueur fixe grâce à String * (longueur), soit dynamique (jusqu’à 2 milliards de caractère).
Curieusement la déclaration dynamique est nettement plus rapide pour la concaténation de chaîne.
Forme optimisée
:Forme équivalente : (+3*)
Programme de test :
La concaténation de chaîne
La concaténation est identique avec " & " ou " + ". Préférer " & " pour éviter toute confusion.
Néanmoins, on peut faire une concaténation plus rapide, utile dans une boucle. Cela consiste à déclarer la variable de réception avec une taille valant (ou par excès), la taille des chaînes à concaténer, puis d’utiliser Mid$. Cette astuce semble également beaucoup plus performante sous les dernières versions de VBA (version 6 ou plus).
Forme optimisée
:Forme optimisée
: (+1,04*)Forme équivalente : (+1,5*)
Forme équivalente : (+1,5*)
Programme de test :
La chaîne nulle
Pour attribuer une chaîne nulle à une variable utiliser la constante vbNullString.
/FONT>Forme optimisée
:Forme équivalente
: (+4*)Forme équivalente : (+5.5*)
mot = Chr$(0)
Programme de test
:Optimisation des boucles
La variable d’une boucle doit être déclarée pour être optimale. Option Explicit est donc très pratique ici...
Parmi toutes les syntaxes et conditions d’utilisation on peut faire un classement des boucles en fonction de leur rapidité d’exécution, pour réaliser exactement le même nombre de passage.
IL est à noter qu’on peut retenir uniquement deux formes de boucles optimales : FOR TO NEXT, dans le cas où l'on connaît exactement les bornes, où DO.. LOOP (UNTIL/WHILE) sinon.
For... To... (Step)... Next
La vitesse est la plus rapide pour le type Integer. A défaut, si la borne > 32766 alors prendre le type Long (presque équivalent).
Forme optimisée
:Programme de test
:Do.. Loop (While/Until)
Do (While/Until)... Loop
Do.. Exit Do... Loop
While... Wend
étiquette... Goto étiquette ... Goto suite
Formes optimales et test
:Forme équivalente et test
: (+1.1*)Forme équivalente et test
: (+1.3*)Forme équivalente et test
: (+1.4*)Forme équivalente et test
: (+1.4*)Forme équivalente et test
: (1.5*)Forme équivalente et test
: (+1.5*)Forme équivalente et test
: (+1.9*)Forme équivalente et test
: (+2.7*)Exécuter une instruction une fois sur x passages
Forme optimisée
:Forme équivalente
: (+1.9*)Programme de test
:Optimisation pour Excel
Désactivation de processus internes
Pour un gain global, il peut être intéressant de désactiver certains processus intrinsèque à Excel. Ainsi, la désactivation temporaire de la mise à jour de l'affichage, en début du code permet un gain d'exécution important. Dans la même idée, si votre projet le permet, la désactivation temporaire du recalcule des références des formules peut être désactiver. Bien sur, il faut faire attention, à ne pas faire dépendre la suite de votre code, à un résultat d'une formule dans une cellule. Le gain global dépendra essentiellement de la façon dont votre code est construit. Il peut s'élever jusqu'à 50%.
Programme de test :
Tableau Excel dans une Array
Au lieu d'accèder directement dans le tableur Excel pour y effectuer les traitements des données des cellules, il est trés avantageux de transfèrer le contenue des cellules (hors formules) dans un tableau de VBA en variant, et de faire l'inverse à la fin du traitement.
Forme optimisée
:Forme équivalente : (+5,6*)
Forme équivalente : (+6,5*)
Programme de test :
Programme de test :
Autres sources d'informations :
Microsoft
présente d'autres remarques générales pour optimiser votre code.
Aivosto
présente des tests et remarques similaires à ceux proposés ici.