Discussion :

[DarkVader]

[INFO] Ce débat prend sa source dans cette discussion [/INFO]

Bonsoir à tous,
Ci-joint du code, peut-on le simplifier.
Merci de votre aide.

Oui notamment :
1°) Ne jamais travailler sur les données du classeur - toujours passer par des tableaux intermédiaires
2°) Supprimer tous les Select, Selection, ActiveCell, ActiveSheet en utilisant les références aux objects
3°) Autofill dans une procédure de calcul, je suis réservé 8)

[DarkVader]

Darkvader,

Ce serait intéressant, dans une nouvelle discussion, que tu développes ceci

car j'ai des difficultés à comprendre le conseil que tu donnes, et pour tout dire, je le considère comme erroné et inexploitable...

N'y a-t-il une petite contradiction entre «j'ai des difficultés à comprendre» et «je le considère comme erroné et inexploitable»
à moins que ce soit parce que tu penses que le contexte actuel est inadapté ?

Dans le premier cas, il est facile de démontrer que travailler directement sur un tableau
est nettement plus rapide que travailler directement sur les cellules ,
=> si le tableau est saisi directement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Dim plage As Range, tmp()
 
Set plage = [Bruts!D3:AI38]
tmp() = plage
 
For x = LBound(tmp) To UBound(tmp) '.../...

=> et d'autant que le tableau est conséquent.
Idem dans le sens inverse quand une affectation en retour est nécessaire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

plage.Resize(UBound(tmp), UBound(tmp, 2)) = tmp

La démonstration de vitesse comparée entre fonction de feuilles complexe et fonction VBA a démontré un facteur de 1 à 100 dans un exemple récent du forum.


Au sens de la seconde interprêtation, il est vrai que l'architecture d'un projet doit se prêter à utiliser cette méthode
notamment par l'utilisation de plage à affecter contiguë,
au cas contraire, il faut biaiser par l'utilisation de feuille cachée par exemple, etc.

[Pierre Fauconnier]

Bon...

le conseil n° 1 me paraît un peu péremptoire

1°) Ne jamais travailler sur les données du classeur - toujours passer par des tableaux intermédiaires

Dans la pratique, ce n'est applicable que si l'on considère travailler avec les valeurs des cellules. Si l'on doit travailler sur le format, l'ajout de formules, le copier-coller (éventuellement spécial), ..., ce conseil n'est pas applicable...

Les test avec un facteur de 1 à 100...
Mwouaissss... Je suis très dubitabif avec ces tests. Leur validité dépend des machines, de la configuration du fichier, ...

Sur une plage de 60000 cellules, j'ai uniquement un facteur de 1 à 3. La durée du test dépend donc beaucoup de la taille de la plage et de ce que l'on réalise comme calculs, mais je suis loin d'un facteur de 1 à 100
De plus, dans Excel, on n'est pas dans un jet supersonique, et je préfère personnellement de loin la lisibilité du code à la rapidité. Je suis toujours amusé de ces tests extrêmes se basant sur des situations qui, dans la vraie vie d'un fichier, sont rarissimes.

Forme de la plage

Un plage de données "multi-areas" ne se prêtera pas à cette manipulation

En conclusion
C'est donc, comme souvent, l'utilisation de termes définitifs comme toujours et jamais qui me fait sourire, ainsi que la (non)-pertinence de l'argument de pseudo-rapidité (il faudrait encore pouvoir, pour cela, dénombrer le nombre de cellules modifiées avec précision).

J'ajouterai que, au vu du code proposé (source de ce présent débat), l'argument de complexifier le code pour plus de rapidité passe, selon moi, bien après d'autres améliorations (comme les Select que tu mentionnes) ou que la modification de la sélection à l'intérieur d'une fonction...

Personnellement, je ne suis pas convaincu par l'utilisation d'un tableau en lieu et place d'une plage de cellules.

[ucfoutu]

Bonjour,

Je dirais personnellement que la décision de stratégie à observer dépendra (comme c'est presque toujours le cas) :
- de la taille de la plage
- de ce que l'on veut traiter...

Il va par exemple à mes yeux de soi que passer par un tableau (tant à "l'aller" qu'au "retour") est largement conseillé dans certains cas tels (entre autres) :
- le mélange ("shuffling")
- les tirages sans doublons
- ...

Ce ne sont, une autre fois, que des exemples.

[pgz]

Juste un élément de Cette discusion

Addition dans un tableau de feuille et dans une variable tableau
Rng(1,1) = rng(2,1) + rng(3,1) (1 : rng = range)
MonTab(1,1)= MonTab(2,1) + MonTab(3,1) (2 : MonTab variable tableau statique de réels)
(2) est 3100 fois plus rapide que (1).

Pour les tableaux de données, cela semble clair.

[DarkVader]

Bon...

1°) Ne jamais travailler sur les données du classeur - toujours passer par des tableaux intermédiaires

le conseil n° 1 me paraît un peu péremptoire

Le langage utilisé avait pour volonté d'appuyer un conseil dans une discussion où il est fait référence à des select, selection etc. à tire l'arigot ;
sorti de ce contexte, je l'aurais probablement rédigé différemment - lire la suite concernant les conditions d'utilisation -
au sens stricte, dans un autre contexte, je n'aurais pas utilisé le terme notamment.
Sur le fond tu as raison, il est nécessaire de vérifier l'intérêt de la méthode selon le contexte ,
le tableur est d'ailleurs nettement plus puissant quand il s'agit uniquement de calculs (heureusement c'est fait pour ça).
Pour le cas précis en cause, calcul dans VBA, le calcul via un tableau est beaucoup plus rapide que celui utilisant la lecture (ou l'écriture) de références.

Dans la pratique, ce n'est applicable que si l'on considère travailler avec les valeurs des cellules. Si l'on doit travailler sur le format, l'ajout de formules, le copier-coller (éventuellement spécial), ..., ce conseil n'est pas applicable...

Cet avis me paraît un peu péremptoire
car il est possible d'imputer une formule avec la méthode décrite, ce qui peut être utile dans certains cas

Code Exemple :

Sélectionner tout - Visualiser dans une fenêtre à part

tmp(x, y) = "=SOMME(INDIRECT(ADRESSE(LIGNE();1)):INDIRECT(ADRESSE(LIGNE();25)))"

Les test avec un facteur de 1 à 100...
Mwouaissss... Je suis très dubitabif avec ces tests. Leur validité dépend des machines, de la configuration du fichier, ...

Sur une plage de 60000 cellules, j'ai uniquement un facteur de 1 à 3. La durée du test dépend donc beaucoup de la taille de la plage et de ce que l'on réalise comme calculs, mais je suis loin d'un facteur de 1 à 100

Il est évident qu'il faut comparer à conditions égales et que cela dépend des calculs - l'exemple cité correspond à un précédent message
et n'a valeur d'exemple que dans un contexte particulier, il est toutefois représentatif des différents écarts possibles.

De plus, dans Excel, on n'est pas dans un jet supersonique, et je préfère personnellement de loin la lisibilité du code à la rapidité. Je suis toujours amusé de ces tests extrêmes se basant sur des situations qui, dans la vraie vie d'un fichier, sont rarissimes.

Pas forcément, cela dépend du domaine de travail.
Par exemple avec des flux de données DDE conséquents les temps de calculs intermédiaires sont très importants -
si le code n'est pas optimisé, je te laisse imaginer tant l'efficacité de l'application que sa stabilité.
D'autre part, je ne vois pas pourquoi le code serait moins lisible !

[Pierre Fauconnier]

@ DarkVader

Je ne comprends pas bien l'utilité de cet exemple...

tmp(x, y) = "=SOMME(INDIRECT(ADRESSE(LIGNE();1)):INDIRECT(ADRESSE(LIGNE();25)))"

La formule n'est pas appliquée à un range, ce n'est que l'élément du tableau qui prend comme valeur une chaine de caractères.

Il me semble justement que, dans ce cas-ci, il est plus intéressant d'avoir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("A1:d100").formula = "=somme(...)"

qui affecte toutes les cellules en une fois

Le langage utilisé avait pour volonté d'appuyer un conseil dans une discussion où il est fait référence à des select, selection etc. à tire l'arigot ;

Les mots Jamais et Toujours sortent justement, à mon sens, le conseil du cas particulier pour en étendre l'application à toute manipulation de cellules... C'est d'ailleurs l'objet du débat, sinon, celui-ci se restreint au cas particulier évoqué dans la discussion d'origine, et ne regarde que l'aspect de la vitesse d'exécution, parmi d'autres aspects évoqués ici.


@ pgz,

Je ne dis pas que l'utilisation des tableaux n'est pas parfois plus rapide, mais il me semble que regarder uniquement la performance "vitesse" n'est pas toujours pertinent...

@ ucfoutu

Qu'entends-tu par mélange? Peux-tu donner un code?

[Pierre Fauconnier]

Addition dans un tableau de feuille et dans une variable tableau
Rng(1,1) = rng(2,1) + rng(3,1) (1 : rng = range)
MonTab(1,1)= MonTab(2,1) + MonTab(3,1) (2 : MonTab variable tableau statique de réels)
(2) est 3100 fois plus rapide que (1).

Je ne vois pas la pertinence de l'exemple...

Il n'y a pas de boucle... Difficile de tester pour un code qui serait 3200 fois plus rapide... Tu peux donner le code complet utilisé pour le test?

[DarkVader]

@ DarkVader

Je ne comprends pas bien l'utilité de cet exemple...


La formule n'est pas appliquée à un range, ce n'est que l'élément du tableau qui prend comme valeur une chaine de caractères.

Il me semble justement que, dans ce cas-ci, il est plus intéressant d'avoir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("A1:d100").formula = "=somme(...)"

qui affecte toutes les cellules en une fois

.../...

C'était en réponse à

Dans la pratique, ce n'est applicable que si l'on considère travailler avec les valeurs des cellules. Si l'on doit travailler sur le format, l'ajout de formules, le copier-coller (éventuellement spécial), ..., ce conseil n'est pas applicable...

afin d'infirmer qu'il n'était pas possible de copier une formule avec cette méthode.
Evidemment ce n'est pas une solution à privilégier mais, dans certains cas,
les plages saisies peuvent contenir des formules qu'il faut pouvoir restituer
lors de la copie du tableau avec la méthode précitée ( range=tmp() )

[ucfoutu]

@ ucfoutu

Qu'entends-tu par mélange? Peux-tu donner un code?

Pas besoin de code ... juste une explication :

Imagine que tu aies un jeu de cartes énorme et que tu veuilles le brouiller (c'est cela, le "shuffling") avant de servir...
Travailler avec un tableau dynamique sera là tout indiqué, tant pour brouiller que pour (ensuite) servir...
Le faire directement sur des cellules du tableur est également possible, mais nettement moins agilement.

EDIT :
mais si tu veux un exemple, en voici un vite fait :

Ici, je vais utiliser un Array, pour aller à l'essentiel sans avoir à "fabriquer" un tableau dynamique. Le principe restera rigoureusement le même.
J'ai choisi un Array assez court (imagine avec un énorme tableau)...

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
 
Private Sub Command1_Click()
 Randomize
 Dim monarray, i As Integer, ou As Integer, temp As String, nb As Integer
 monarray = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14")
 nb = UBound(monarray)
 For i = 0 To nb \ 2
  ou = Int(((nb - i) * Rnd))
  temp = monarray(ou)
  monarray(ou) = monarray(nb - i)
  monarray(nb - i) = temp
 Next
' =================La listbox n'est pas là pour brouiller (c'est déjà fait) mais  ===========
'========= uniquement pour que tu voies le résultat du "shuffling" =======
 List1.Clear
 For i = 0 To UBound(monarray)
   List1.AddItem monarray(i)
 Next
End Sub

[pgz]

Je ne vois pas la pertinence de l'exemple...

Il n'y a pas de boucle... Difficile de tester pour un code qui serait 3200 fois plus rapide... Tu peux donner le code complet utilisé pour le test?

Pour la pertinence, tu as sûrement raison, je n'ai rien dû comprendre à cette discussion.
Pour le code, c'est celui du fil que je citais, mais ta question, elle, ne manque pas de pertinence : on s'arrange pour ne mesurer que les lignes de codes que je citais et du coup, il faut aménager un peu. Voici le code adapté à cette mesure. Désolé pour sa longueur. Le code chronométré est celui de la procédure 'sub1' et celui de 'sub2'.

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
Public Declare Function GetTickCount& Lib "kernel32" ()
 
Dim rng As Excel.Range
Dim Montab(1 To 3, 1 To 1) As Double
 
'***************************************************************************
'***************  Procédure principale *************************************
'***************************************************************************
Sub subChronoCompare()
Dim d As Long, dT As Long, dA As Long, dB As Long
Dim sDT As Single, sDA As Single, sDB As Single, sMax As Single
Dim start As Long
Dim l As Long
Dim n As Long, nT As Long, nA As Long, nB As Long
Dim mT As Long, mA As Long, mB As Long, Bou As Long
Dim Message As String
 
Const ChMini As Long = 128
Set rng = Application.ThisWorkbook.Sheets(1).Range("A1:A3")
Montab(2, 1) = 245.7894321 'ces mêms valeurs sont inscrites en feuilles(1).A2
Montab(3, 1) = 123456.789 'et .A3
 
'recherche d'une valeur nA, par octave, donnant un chrono > chMini ms
d = 0
n = 1
While d < ChMini
    start = GetTickCount&
    Call subAppelSub1(n)
    d = GetTickCount& - start
    n = n * 2
Wend
dA = d
nA = n / 2
 
'recherche nB
d = 0
n = 1
While d < ChMini
    start = GetTickCount&
    Call subAppelSub2(n)
    d = GetTickCount& - start
    n = n * 2
Wend
dB = d
nB = n / 2
 
'vérification : la durée élémentaire ne doit pas dépasser 1s.
If dA / nA > 1000 Then Message = "La procédure 1 a un temps d'exécution supérieur à 1 s." & vbCrLf
If dB / nB > 1000 Then Message = Message & "La procédure 2 a un temps d'exécution supérieur à 1 s."
If Message <> "" Then MsgBox Message: Exit Sub
 
'recherche NT
d = 0
n = 1
While d < ChMini
    start = GetTickCount&
    Call subAppelTemoin(n)
    d = GetTickCount& - start
    n = n * 2
Wend
dT = d
nT = n / 2
 
'calcul du nombre d'itérations élémentaires pour témoin, sub1 et sub2; calcul du nombre d'itérations commun (Bou)
If dA / nA > dB / nB Then
    mA = Round(nA * ChMini / dA, 0)
    If mA = 0 Then mA = 1
    sMax = mA * dA / nA
    mB = Round(sMax / dB * nB, 0)
    mT = Round(sMax / dT * nT, 0)
    Bou = Round(5000 / sMax, 0)
Else
    mB = Round(nB * ChMini / dB, 0)
    If mB = 0 Then mB = 1
    sMax = mB * dB / nB
    mA = Round(sMax / dA * nA, 0)
    mT = Round(sMax / dT * nT, 0)
    Bou = Round(5000 / sMax, 0)
End If
 
'chronométrage entrelacé
dT = 0: dA = 0: dB = 0
For l = 1 To Bou
    start = GetTickCount&
    Call subAppelTemoin(mT)
    dT = dT + GetTickCount& - start
 
    start = GetTickCount&
    Call subAppelSub1(mA)
    dA = dA + GetTickCount& - start
 
    start = GetTickCount&
    Call subAppelSub2(mB)
    dB = dB + GetTickCount& - start
Next l
 
'calculs des durées unitaires
sDT = dT / Bou / mT
sDA = dA / Bou / mA - sDT
sDB = dB / Bou / mB - sDT
 
MsgBox "Temps moyens corrigés." & vbCrLf & "Proc 1 : " & Format(sDA * 1000, "#,##0.0"" µs""") & _
        vbCrLf & "Proc 2 : " & Format(sDB * 1000, "#,##0.0"" µs""") & vbCrLf & "Ratio P1/P2 : " & Format(sDA / sDB, "#,##0.0")
 
 
End Sub
 
'***************************************************************************
'*************** Procédures d'itération niveau intermédiaire ***************
'***************************************************************************
Private Sub subAppelTemoin(ByVal Iter As Long)
Dim i As Long
 
For i = 1 To Iter
    Call subTemoin
Next i
End Sub
 
Private Sub subAppelSub1(ByVal Iter As Long)
Dim i As Long
 
For i = 1 To Iter
    Call sub1 'placer ici la procédure 1 à appeler ou placer le code dans la procédure de ce nom
Next i
End Sub
 
Private Sub subAppelSub2(ByVal Iter As Long)
Dim i As Long
 
For i = 1 To Iter
    Call sub2 'placer ici la procédure 2 à appeler ou placer le code dans la procédure de ce nom
Next i
End Sub
 
'***************************************************************************
'********* procédures élémentaires contenant le code à mesurer *************
'***************************************************************************
Sub subTemoin()
 
'procédure laissée intentionnellement vide
End Sub
 
Sub sub1()
 
rng(1, 1) = rng(2, 1) + rng(3, 1)
End Sub
 
Sub sub2()
 
Montab(1, 1) = Montab(2, 1) + Montab(3, 1)
End Sub

Cordialement,

PGZ

[DarkVader]

Ci-joint un classeur de test permettant de mieux appréhender la différence entre les 2 méthodes :
- Affectation d'un tableau à un range et
- Affectation des propriétés d'un range (value ou formula).
Ce classeur permet également de voir comment évaluer plusieurs méthodes.


Le nombre de visiteurs de cette file n'étant pas en rapport avec le nombre de chargement du classeur,
je colle ci-dessous les conclusions déduites de ces tests.

Des différents tests, il découle que :

concernant la lecture de données d'une plage de feuille Excel :
1) l'affectation directe d'un tableau de variant(B/) est la méthode à priviligier
pour la lecture de données - l'utilisation de cette méthode est incontournable
dès que la plage à saisir est importante
(facteur> 1x100 dans le test pour seulement 2000 valeurs),

concernant l'écriture de données dans une plage de feuille Excel :
la différence entre les deux méthodes est moins marquée
dès lors que les règles élémentaires indispensables sont respectées :
- raffraichissement de l'affichage interdit (ScreenUpdating)
- calculs interdits tant que la phase d'écriture n'est pas terminée, (Calculation)(*)

toutefois, il est à noter que :
1) à l'affectation cellule par cellule par propriété (D/)
il faut privilégier la méthode par affectation directe d'un tableau(F/)
quand il s'agit d'affecter une valeur,
( facteur x 1 à 30)

2) lorsque qu'il s'agit d'affecter une formule, le gain se réduisant d'un facteur 10
(l'affectation n'est plus que x3 fois plus rapide), cette méthode présente moins d'intérêt,

3) dans le cas particuliers de l'affectation globale d'une même valeur ou d'une même formule
(valeur unique ou formule unique) à une plage de cellules,
préférer le passage par propriété (G/ H/) à la méthode utilisant l'affectation directe d'un tableau (E/ F/)
gain dans un rapport > 1 à 4 - 1 à 5


NB: Il est évident que les rapports de vitesse indiqués ici
n'ont de valeur que dans le contexte initial dans lequel le test a été réalisé ...

[DarkVader]

Comment ouvrir un classeur sans risque :
* Ouvrir un instance d'Excel

* Paramétrer les Options
> Menus Outils/Options (Alt OO) - Onglet «Sécurité» Bouton «Sécurité des macros»
ou
Paramétrer les Options de sécurité - Menu Outils/Macros/Sécurité(Alt OMé)

Onglet «Niveau de Sécurité» => Cocher «Niveau de sécurité élevé» (ou Moyen - Désactiver les macros)
Onglet «Sources fiables» : décocher
- Faire confiance au projet Visual Basic

* Ouvrir le classeur

* Vérifier
- le code VBA
- s'il n'existe pas de références exotiques

Pour permettre l'exécution de code dans le classeur après vérification :
* Fermer le classeur
* Restaurer les options de sécurité
* accessoirement Quitter l'instance Excel en utilisant le menu Fichier/Quitter (Alt FQ)
* Ouvrir à nouveau le classeur depuis une nouvelle instance.