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 :
Fix(Log(nb + 0.11) / Log(10)) + 1

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*)
Len(Str$(nb)) - 1

Forme équivalente : (+5*)
Len(Trim$(Str$(nb)))

Programme de test :
temps = Timer
Dim nb As Long, lg As Long
For nb = 1 To 100000
lg = Fix(Log(nb + 0.11) / Log(10)) + 1
' par formule math
'lg = Len(Str$(nb)) - 1 ' chaîne
'lg = Len(Trim$(Str$(nb))) 'par fonction de chaîne
Next nb
MsgBox Timer - temps

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 :
Fix(Log(Abs(nb) + 0.11) / Log(10)) + 2

Forme équivalente :
Len(Str$(nb))

etc...

Parité d’un nombre

Pour tester si un nombre est pair :

Forme optimisée :
If (nb And 1) = 0 Then ...

Forme équivalente : (+1.2*)
If nb Mod 2 = 0 Then ...

Forme équivalente : (+1.3*)
If nb = 2 * (nb \ 2) Then ...

Forme équivalente : (+2.1*)
If nb = 2 * Int(nb / 2) Then ...

Forme équivalente : (+2.7*)
If nb / 2 = Int(nb / 2) Then ...

Programme de test :
temps = Timer
Dim nb As Long, lg As Long
For nb = 0 To 10000000
If (nb And 1) = 0 Then lg = lg + 1
'If nb Mod 2 = 0 Then lg = lg + 1
'If nb = 2 * (nb \ 2) Then lg = lg + 1
'If nb = 2 * Int(nb / 2) Then lg = lg + 1
'If nb / 2 = Int(b / 2) Then lg = lg + 1
Next nb
MsgBox Timer - temps

Pour tester si un nombre est impair :

Forme optimisée :
If (nb And 1) Then ...

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 :
nb = lg * lg * ... * lg

Forme équivalente :
nb = lg ^ 2 (+7.5*)
nb = lg ^ 3 (+5.0*)
nb = lg ^ 4 (+4.6*)
nb = lg ^ 5 (+3.9*)

Programme de test :
temps = Timer
Dim nb As Long, lg As Long, A As Long
lg = 100
For A = 0 To 10000000
nb = lg * lg
'nb = 100 * 100
'nb = lg ^ 2
Next nb
MsgBox Timer - temps

Calcul de la racine carré

Forme optimisée :
lg = Sqr(nb)

Forme équivalente : (+3.5*)
lg = nb ^ 0.5

Programme de test :
temps = Timer
Dim nb As Long, lg As Double
For nb = 1 To 10000000
lg = Sqr(nb)

'lg = nb ^ 0.5
Next nb
MsgBox Timer - temps

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 :
nb = nb1 \ nb2

Forme équivalente : (+2.3*)
Nb = Int(nb1 / nb2)

Programme de test :
temps = Timer
Dim nb As Long, lg As Long
For nb = 1 To 10000000
lg = 1000000 \ nb
'lg = Int(1000000 / nb)
Next nb
MsgBox Timer - temps

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 :
nb = nb1 * [inverse nb2]

Forme équivalente : (+1.1*)
Nb = nb1 / nb2

Programme de test :
temps = Timer
Dim nb As Double, lg As Double
For nb = 1 To 10000000
lg = nb * 0.25
'lg = nb / 4
Next nb
MsgBox Timer - temps

Le Pré-calcul

Dans une boucle, il faut sortir tous les calculs d’expression constante.

Forme optimisée :
Dim nb As Long, lg As Double, z As Double
z = Sqr(2)

lg = nb * z

Forme équivalente : (+2.7*)
lg = nb * sqr(2)

Programme de test :
temps = Timer
Dim nb As Long, lg As Double, z As Double
z = Sqr(2)
For nb = 1 To 10000000
lg = nb * z
'lg = nb * Sqr(2)
Next nb
MsgBox Timer - temps

Maximum et Minimum de 2 nombres

Forme optimisée :
If a < b Then max = b Else max = a

Forme équivalente : (+1.5*)
max = (a+b+abs(a-b))/2
min = (a+b-abs(a-b))/2

Forme équivalente : (+4.3*)
max = IIf(a < b, b, a)
min = IIf(a < b, a, b)

Programme de test :
temps = Timer
Dim a As Long, b As Long, max As Long, c As Long
a = 3: b = 5
For t = 1 To 1000000
If a < b Then max = b Else max = a
'max = (a + b + Abs(a - b)) / 2
'max = IIf(a < b, b, a)
c = a: b = a: a = c
Next t

MsgBox Timer - temps

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 :
a = True
b = False

Forme équivalente : (+1.2*)
a = -1
b = 0

Programme de test :
temps = Timer
Dim t As Long, maxi As Long
maxi = 10000000
ReDim b(maxi) As Boolean
'ReDim b(maxi) As Byte
For t = 0 To maxi
b(t) = False
'b(t) = 0
Next t
MsgBox Timer - temps

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 :
a=(condition)

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*)
If condition Then a = True Else a = False

Programme de test :
temps = Timer
Dim nb As Long, a As Boolean
For nb = 1 To 10000000
a = (nb And 1)
'If (nb And 1) Then a = True Else a = False
Next nb
MsgBox Timer - temps


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 :
If (condition1) Then
(instructions1)
ElseIf (condition2) Then
(instructions2)
ElseIf ...
End If

Forme équivalente : (+1.1*)
If (condition1) Then (instructions1)
If (condition2) Then (instructions2)

Forme équivalente : (+1.2*)
Select Case (variable)
Case (condition1)
(instructions1)
Case (condition2)
(instructions2)
...
End Select

Forme équivalente : (+1.25*)
If (condition1) Then
(instructions1)
Else
If (condition2) Then
(instructions2)
Else
...
End If
End If

Programme de test :
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
If t = 1 Then
a = a + 1
ElseIf t = 2 Then
a = a + 2
ElseIf t = 3 Then
a = a + 3
ElseIf t = 4 Then
a = a + 4
ElseIf t = 5 Then
a = a + 5
End If
Next t, j
MsgBox Timer - temps

Programme de test :
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
If t = 1 Then a = a + 1
If t = 2 Then a = a + 2
If t = 3 Then a = a + 3
If t = 4 Then a = a + 4
If t = 5 Then a = a + 5
Next t, j
MsgBox Timer - temps

Programme de test :
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
Select Case t
Case 1
a = a + 1
Case 2
a = a + 2
Case 3
a = a + 3
Case 4
a = a + 4
Case 5
a = a + 5
End Select
Next t, j
MsgBox Timer - temps

Programme de test :
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
If t = 1 Then
a = a + 1
Else
If t = 2 Then
a = a + 2
Else
If t = 3 Then
a = a + 3
Else
If t = 4 Then
a = a + 4
Else
If t = 5 Then
a = a + 5
End If
End If
End If
End If
End If
Next t, j

MsgBox Timer - temps

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 :
Voir ci-dessus.

Programme de test : (+1.25*)
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
Select Case t
Case 1, 2, 5
a = a + 1
Case 3, 4
a = a + 3
End Select
Next t
Next j

MsgBox Timer - temps

Programme de test : (+1.25*)
temps = Timer
Dim t As Long, j As Long, a As Long
For j = 1 To 1000000
For t = 1 To 5
If t = 1 Or t = 2 Or t = 5 Then
a = a + 1
ElseIf t = 3 Or t = 4 Then
a = a + 3
End If
Next t
Next j
MsgBox Timer - temps

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 :
If (condition) Then (instructions1) Else (instructions2)

Forme équivalente : (+1.1*)
If (condition) Then (instructions1) Else: (instructions2)

Forme équivalente : (+1.3*)
If (condition) Then
(instructions1)
Else
(instructions2)
End If

Programme de test :
temps = Timer
Dim a As Long, b As Long, max As Long, c As Long
a = 3: b = 5
For t = 1 To 10000000
If a < b Then max = b Else max = a
'If a < b Then max = b Else: max = a
'If a < b Then
' max = b
'Else
' max = a
'End If
c = a: b = a: a = c
Next t
MsgBox Timer - temps


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 :
Sub MyInput()
With ActiveSheet.Cells(1, 1)
.Formula = "=SQRT(50)"
With .Font
.Name = "Arial"
.Bold = True
.Size = 8
End With
End With
End Sub


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 :
Dim z As String, lg As String
z = "0"
lg = String$(1000, z)

Forme équivalente : (+1.1*)
String(1000, z)

Forme équivalente : (+1.1*)
String$(1000, 48)

Forme équivalente : (+1.3*)
String$(1000, "0")

Programme de test :
temps = Timer
Dim nb As Long, z As String, lg As String
z = "0"
For nb = 1 To 1000000
lg = String$(1000, z)
'lg = String(1000, z)
'lg = String$(1000, 48)
'lg = String$(1000, "0")
'lg = String(1000, "0")
Next nb
MsgBox Timer - temps

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 :
Dim a As String

Forme équivalente : (+3*)
Dim a As String * (nombre)

Programme de test :
temps = Timer
'Dim a As String * 7, b As String * 7, c As String * 14
Dim a As String, b As String, c As String
Dim t As Long
a = "bonjour"
b = " à toi!"
For t = 1 To 10000000
c = a & b
Next t
MsgBox Timer - temps

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 :
c = Space$(14)
Mid$(c, 1, 7) = a: Mid$(c, 8, 7) = b

Forme optimisée : (+1,04*)
Dim l1 As Long, l2 As Long, l3 As Long, c As String
l1 = Len(a): l2 = Len(b): l3 = l1 + 1
c = Space$(la + lb)
Mid$(c, 1, l1) = a: Mid$(c, l3, l2) = b

Forme équivalente : (+1,5*)
c = a & b

Forme équivalente : (+1,5*)
c = a + b

Programme de test :
temps = Timer
Dim a As String, b As String, c As String, t As Long
a = "bonjour"
b = " à toi!"
c = Space$(14)
'Dim l1 As Long, l2 As Long, l3 As Long
'l1 = Len(a): l2 = Len(b): l3 = l1 + 1
'c = Space$(l1 + l2)
For t = 1 To 1000000
Mid$(c, 1, 7) = a: Mid$(c, 8, 7) = b
'Mid$(c, 1, l1) = a: Mid$(c, l3, l2) = b
'c = a & b
'c = a + b
Next t
MsgBox Timer - temps

La chaîne nulle

Pour attribuer une chaîne nulle à une variable utiliser la constante vbNullString.

Forme optimisée :
mot = vbNullString

Forme équivalente : (+4*)
mot = ""

Forme équivalente : (+5.5*)
mot = Chr$(0)

Programme de test :
temps = Timer
Dim nb As Long, mot As String
For nb = 1 To 1000000
mot = vbNullString
'mot = ""
'mot = Chr$(0)
Next nb
MsgBox Timer - temps


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 :
Dim Variable As Long, borne_fin As Long
borne_fin = (un nb ou une expression)
FOR Variable = (un nb ou une expression) TO borne_fin STEP (un nb ou une expression)
NEXT

Programme de test :
temps = Timer
Dim nb As Integer, nb2 As Integer
'Dim nb As Long, nb2 As Long
'Dim nb As Double, nb2 As Double
For nb = 1 To 1000
For nb2 = 1 To 32766
Next nb2
Next nb
MsgBox Timer - temps

Do.. Loop (While/Until)
Do (While/Until)... Loop
Do.. Exit Do... Loop
While... Wend
étiquette... Goto étiquette ... Goto suite

Formes optimales et test :
Dim A As Long
Do
A = A + 1
Loop Until A = 10000000

Forme équivalente et test : (+1.1*)
Dim A As Long
Do
A = A + 1
Loop While A < 10000000

Forme équivalente et test : (+1.3*)
Dim A As Long
Do Until A = 10000000
A = A + 1
Loop

Forme équivalente et test : (+1.4*)
Dim A As Long
boucle:
A = A + 1
If A < 10000000 Then GoTo boucle

Forme équivalente et test : (+1.4*)
Dim A As Long
Do While A < 10000000
A = A + 1
Loop

Forme équivalente et test : (1.5*)
Dim A As Long
Do
A = A + 1
If A = 10000000 Then Exit Do
Loop

Forme équivalente et test : (+1.5*)
Dim A As Long
While A < 10000000
A = A + 1
Wend

Forme équivalente et test : (+1.9*)
Dim A As Long, nb As Integer
For nb = 0 To 1
nb = 0
A = A + 1
If A = 10000000 Then Exit For
Next

Forme équivalente et test : (+2.7*)
ReDim A(0) As Long
Do
A(0) = A(0) + 1
Loop Until A(0) = 10000000

Exécuter une instruction une fois sur x passages

Forme optimisée :
Dim Saut As Long, A As Long, nb As Long
Saut = 90
For nb = 0 To 10000000
If nb = Saut Then Saut = Saut + 90: A = A + 1 'instructions
Next nb

Forme équivalente : (+1.9*)
Dim A As Long, nb As Long
For nb = 0 To 10000000
If nb mod 90 Then A = A + 1 'instructions
Next nb

Programme de test :
temps = Timer
Dim Saut As Long, A As Long, nb As Long
Saut = 90
For nb = 0 To 10000000
If nb = Saut Then Saut = Saut + 90: A = A + 1
'If nb Mod 90 Then A = A + 1 'instructions
Next nb
MsgBox Timer – temps


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 :
temps = Timer
'Désactive la mise à jour de l'affichage
Application.ScreenUpdating = False
'Désactive la mise à jour des recalculs
Application.Calculation = xlCalculationManual
'code modifiant les cellules
For x = 1 To 4000
ActiveSheet.Cells(x, 1) = x
Next x
'Ré-activations
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox Timer – temps

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 :
Dim Arr as Variant
Arr = Range(plage)
Arr(x,y)=...
Range(plage) = Arr

Forme équivalente : (+5,6*)
Cells(y, x).Value = ...

Forme équivalente : (+6,5*)
Range("A1").Offset(y - 1, x - 1) = ...

Programme de test :
temps = Timer
Dim x As Long
Dim y As Long
Dim Arr As Variant
Arr = Range("A1:Z1000")
'Dim Arr(1 To 1000, 1 To 26) As Variant
For y = 1 To 1000
For x = 1 To 26
Arr(y, x) = "a"
Next x, y
Range("A1:Z1000") = Arr
MsgBox Timer – temps

Programme de test :
temps = Timer
Dim x As Long
Dim y As Long
For x = 1 To 26
For y = 1 To 1000
Cells(y, x).Value = "a"
'Range("A1").Offset(y - 1, x - 1) = "a"
Next y, x
MsgBox Timer – temps


Version Word   |  Version PDF   |  Back   |  Home



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.