Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

[rouillld]

Bonjour à toutes et à tous,

Le code suivant me permet de remplir un classeur excel à l'aide de 2 boucles for next, l'une pour les lignes, l'autre pour les colonnes :

Sub Remplir_tableau()

'Déclaration variables
Dim i As Integer
Dim j As Integer
Dim nblig As Integer
Dim nbcol As Integer

'Remplissage tableau

nblig = Range("A65536").End(xlUp).Row
nbcol = Range("A1").End(xlToRight).Column

For i = 3 To nbcol
For j = 2 To nblig
If Cells(j, 2) = Cells(1, i) Then
Cells(j, i) = Cells(j, 1)
Else
Cells(j, i) = ""
End If
Next j
Next i

End Sub

L'exemple ci-joint montre que la macro fonctionne très bien. Cependant, le fichier que je manipule tous les jours fait plutôt une centaine de colonnes et entre 500 et 2000 lignes en moyenne et l'exécution est très longue...

J'aimerais accélerer le code en traitant les données dans une variable tableau, puis en collant le contenu du tableau dans le classeur. Cependant je n'arrive pas du tout à faire fonctionner quoi que ce soit (j'ai pourtant passé beaucoup de temps sur les tutoriels en question... je dois vraiment être mauvais pour le vba....).

Merci d'avance aux personnes qui pourraient m'éclairer sur le sujet.

Bonne soirée

 

[Hippolite]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonsoir,
Non testé, je n'ai pas excel sous la main :

Option explicit
Option Base 1 'Indiçage du tableau à partir de 1 (sinon par défaut c'est 0)

Sub Remplir_tableau()
'Déclaration variables
	Dim i As Integer, j As Integer, nblig As Integer, nbcol As Integer
	Dim Tablo() As Variant
With Application: .ScreenUpdating = False: .Calculation = xlCalculationManual: End With
'Initialisation du tableau
	nblig = Range("A65536").End(xlUp).Row
	nbcol = Range("A1").End(xlToRight).Column
	Redim Tablo(nbcol , nblign - 1 ) 
	For i = 1 To 2
		For j = 1 To nblig - 1
			Tablo(i, j) = Cells(j + 1, i)
		Next j
	Next i
'Traitement
	For i = 3 To nbcol
		For j = 2 To nblig
			If Tablo(2, j) = Tablo(i, 1) Then
				Tablo(i, j) = Tablo(1, j)
			Else
				Tablo(i, j) = ""
			End If
		Next j
	Next i
'Remplissage des cellules
	For i = 3 To nbcol 
		For j = 1 To nblig - 1
			Cells(j + 1, i ) = Tablo(i, j)
		Next j
	Next i
With Application: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True: End With
End Sub

J'ai transposé par habitude, car seule la dernière dimension du tableau est redimensionnable et en général, c'est le nombre de lignes qui est sujet à variation.

Une synthèse sur les tableaux : Utiliser les variables tableaux en VBA Excel

A+

Edit : ajouté les neutralisation des calculs et de l'affichage

 

[ROGER2327]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonsoir à tous

Une proposition qui fonctionne assez vite (environ deux secondes pour 2000 lignes et 200 colonnes) :

Sub Remplir_tableau()
Dim i&, j&, l&, c&, tmp$, odat()
  With Application: .ScreenUpdating = 0: .Calculation = -4135: End With
  With Range("A1").Resize(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, 1).End(xlToRight).Column)
    l = .Rows.Count
    c = .Columns.Count
    .Offset(1, 2).Resize(l - 1, c - 2).ClearContents
    odat = .Value
    For j = 3 To c
      tmp = odat(1, j)
      For i = 2 To l
        If odat(i, 2) = tmp Then odat(i, j) = odat(i, 1)
      Next i
    Next j
    .Value = odat
  End With
  With Application: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub

​

ROGER2327
#5190

Lundi 9 Clinamen 138 (Sainte Trique, lunatique, SQ)
11 Germinal An CCXIX
2011-W13-4T22:10:21Z

 

[rouillld]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonsoir,

Un grand merci à vous 2...

Hippolite : j'ai réussi à faire marcher la macro avec quelques difficultés, mais au bout du compte, ça fonctionne bien.

Roger : impec et très rapide (beaucoup plus que mon code original), c'est exactement ce qu'il me fallait...

Je crois que je vais me pencher plus sérieusement sur ces variables tableaux, c'est vraiment puissant.

Bonne soirée (ou plutôt bonne nuit à cette heure...) !

 

[Hippolite]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour à tous, bonjour Roger,
Tant qu'à optimiser,il faut aller jusqu'au bout, j'ai ajouté les neutralisation des calculs et de l'affichage.
Merci à Roger qui m'a permis de voir mon oubli.
A+

 

[david84]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour tout le monde,
une autre possibilité à tester et à comparer en vitesse d'exécution avec les autres code (pas testé pour l'instant mais j'aimerais bien savoir...) :

Sub test()
Dim Plage, Tabl, Entete, DerLigne&
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
DerLigne = Range("A" & Rows.Count).End(xlUp).Row
Set Entete = Range([C1], [C1].End(xlToRight))
Set Plage = Range("A2:B" & DerLigne)
Set Zone = Range(Cells(2, 3), Cells(DerLigne, Entete.Columns.Count + Plage.Columns.Count))
Zone.ClearContents
Tabl = Evaluate("IF(" & Plage.Columns(2).Address & "=" & _
Entete.Address & "," & Plage.Columns(1).Address & ","""")")
Zone.Value = Tabl
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

A+

 

[JNP]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour le fil ,
Comme le demandais David, et par curiosité, j'ai ajouté les calculs de temps et les macros de chacun sur le fichier.
Test sur 2500 lignes. 4 tests par personne.
Résultats sous 2010 64 :
Rouild : 0.89
Hypolite : plante ...
Roger : 0.12
David : 0.11
Si vous pouvez donner vos propres résultats, merci d'avance .
Bonne journée

 

[MJ13]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour à tous

comme demandé par Jean-Noël :
XL2007, Win XP, Pentium E2180 2Ghz 2 Go Ram
Voici mes résultats:
Roullid: 1,58 sec +-0,02
Hippolite: Plante
Roger: 0,2 +- 0,02
David: 0,2 +- 0,02

Merci, j'aime bien ces petits codes optimisés .

 

[ROGER2327]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour à tous

Les résultats de certaines procédures dépendent beaucoup de l'environnement. Dans les mêmes conditions pour les quatre procédures, j'obtiens :
Hippolite : ne fonctionne pas ;
rouillld : environ 20 minutes ;
david84 : 0,312 s ;
ROGER2327: 0,187 s.

La procédure de david84 est nettement plus rapide (0,187 s) en écrivant

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

au lieu de

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Essai avec 200 colonnes au lieu de 4 :
david84 : 3,39 s ;
ROGER2327: 2,18 s.​

ROGER2327
#5201

Jeudi 12 Clinamen 138 (Saint Georges Dazet, poulpe au regard de soie, SQ)
14 Germinal An CCXIX
2011-W13-7T00:34:39Z

 

[david84]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Re
Merci à Jean-Noël, Michel et Roger pour ces tests.
Je retiens avant tout que les 2 codes sont fonctionnels et rapides. Après concernant les "disparités" entre les différents tests, je pense effectivement comme Roger que les résultats dépendent de l'environnement et qu'il faudrait tester sur un même fichier, sur lequel on ferait varier son environnement.

La procédure de david84 est nettement plus rapide (0,187 s) en écrivant
Code :
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
au lieu de
Code :
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

c'est bon à savoir : dans une procédure utilisant une formule il vaut mieux donc commencer par gérer le mode de calcul...

La procédure de david84 est nettement plus rapide (0,187 s) en écrivant

c'est dans la configuration où votre procédure est mesurée à 0,187 s ?

Essai avec 200 colonnes au lieu de 4 :
david84 : 3,39 s ;
ROGER2327: 2,18 s.

Essais effectués je suppose avec

Code :
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Et sur combien de lignes ? Les 2500 du fichier de Jean-Noël ?
Et en restant sur 4 colonnes et en augmentant le nombre de lignes, vous avez testé ?
Personnellement, avec mon vieux portable en utilisant le fichier de Jean-Noël : test sur 4 colonnes et 10000 lignes
1er test :
David : 0,59 sec
Roger : 0,67 sec
2ème test :
Roger : 0,609 sec
David : 0,676 sec
Donc cela varie...
Je n'ai pas fait de tests sur les colonnes...
A+

 

[ROGER2327]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour david84

Re
Merci à Jean-Noël, Michel et Roger pour ces tests.
Je retiens avant tout que les 2 codes sont fonctionnels et rapides(1). Après concernant les "disparités" entre les différents tests, je pense effectivement comme Roger que les résultats dépendent de l'environnement et qu'il faudrait tester sur un même fichier, sur lequel on ferait varier son environnement.

c'est bon à savoir : dans une procédure utilisant une formule il vaut mieux donc commencer par gérer le mode de calcul...

c'est dans la configuration où votre procédure est mesurée à 0,187 s ?(2)

Essais effectués je suppose avec(3)
Et sur combien de lignes ? Les 2500 du fichier de Jean-Noël ?(4)
Et en restant sur 4 colonnes et en augmentant le nombre de lignes, vous avez testé ?(5)
Personnellement, avec mon vieux portable en utilisant le fichier de Jean-Noël : test sur 4 colonnes et 10000 lignes
1er test :
David : 0,59 sec
Roger : 0,67 sec
2ème test :
Roger : 0,609 sec
David : 0,676 sec
Donc cela varie...
Je n'ai pas fait de tests sur les colonnes...
A+

(1).- Même conclusion. Reste à peaufiner des détails comme le comportement en l'absence de données, par exemple.
(2).- Oui, même configuration, avec votre code modifié.
(3).- Non, c'est avec votre code original.
(4).- Oui, j'ai testé sur 2500 lignes.
(5).- Non.​

ROGER2327
#5202

Jeudi 12 Clinamen 138 (Saint Georges Dazet, poulpe au regard de soie, SQ)
14 Germinal An CCXIX
2011-W13-7T09:00:59Z

 

[rouillld]

Re : Accéler le fonctionnement d'une macro à l'aide d'une variable tableau

Bonjour,

Je vois que la discussion a continué après ma réponse (c'est toujours sympa de susciter quelques réactions et discussion, quand ça va dans le bon sens).

J'aimerais "paufiner" ma macro en supprimant les colonnes dont la somme est nulle, toujours à l'aide d'un tableau.

Bon, je coince encore une fois (ça marche pô bien du tout) je reviens donc vers vous, il y a vraiment plusieurs trucs que je ne comprend pas.

Sub Supprimer_colonnes_somme_nulle()

Dim i As Integer
Dim j As Integer
Dim l As Integer
Dim c As Integer
Dim Somcol()

l = Range("A1:A65536").End(xlUp).Row
c = Range("A1:IV256").End(xlToRight).Column

ReDim Somcol(l)

For j = 3 To c
For i = 2 To UBound(Somcol)
Somcol(l) = Cells(i, j)
Next i

If Application.Sum(Somcol) = 0 Then Columns(j).Delete Shift:=xlToLeft

Next j

End Sub

Bon, en gros, la macro ne me supprime pas les bonnes colonnes... J'ai l'impression que ce ne sont pas les bonnes données qui remplissent le tableau.

Merci d'avance pour vos réponses, vous trouverez sur ce lien Free - Envoyez vos documents le fichier exemple complété en annexe (avec la macro originale de Roger pour le remplissage des cellules).