Discussion :

[Julie_67]

Bonjour à tous,

voici ci-joint mon fichier avec une macro qui met en feuille3 les données de la feuille1 et de la feuille2. Cette macro fonctionne parfaitement cependant elle met du temps à s'exécuter (dû certainement au grand nombre de lignes). Avez-vous des idées pour optimiser ce code afin qu'il soit plus rapide lors de l'exécution?

[Philippe Tulliez]

Bonjour,
Personnellement, je n'ouvre jamais les classeurs

Voir cette discussion qui peut-être t'apportera des idées Comment accélérer l'exécution de la procédure

[Transitoire]

Bonsoir,

voici ci-joint mon fichier avec une macro qui met en feuille3 les données de la feuille1 et de la feuille2. Cette macro fonctionne parfaitement cependant elle met du temps à s'exécuter (dû certainement au grand nombre de lignes). Avez-vous des idées pour optimiser ce code afin qu'il soit plus rapide lors de l'exécution?

J'ai regardé, mais ça ne met pas les données de feuilles 1 et 2 en feuille 3, mais une partie des données seulement. une partie des colonnes et le nombre de lignes étant différent, le nombre de lignes de feuille2. Ca serait super que vous fassiez un effort pour nous expliquer au moins sommairement .
En tout cas,cela me semble très long.
Cordialement

[Pierre Fauconnier]

Salut.

J'te crois que c'est lent. Transférer les données d'une plage vers un tableau en bouclant sur 20000 lignes, boucler sur 20.000 autres lignes pour récupérer une autre plage, puis boucler pour incorporer le tableau 2 dans le 1, c'est forcément très lent. Au passage, il n'est pas nécessaire de sélectionner une feuille pour travailler avec.

Transférer une plage dans un tableau sans boucles

Code :

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

t1 = range("a1:d20000").value

Dès lors, ton code pourrait devenir ceci, exécution instantanée.

Code :

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

1
2
3
4
5
6
7
8
Sub Test2()
  Dim t1, t2
 
  t1 = Feuil1.Range("a1:d" & Feuil1.Range("a1").End(xlDown).Row).Value
  t2 = Feuil2.Range("a1:b" & Feuil1.Range("a1").End(xlDown).Row).Value
  Feuil3.Range("a1").Resize(UBound(t1) - LBound(t1) + 1, UBound(t1, 2) - LBound(t1, 2) + 1).Value = t1
  Feuil3.Range("a1").End(xlDown)(1).Resize(UBound(t2) - LBound(t2) + 1, UBound(t2, 2) - LBound(t2, 2) + 1).Value = t2
End Sub

Cela dit, au-delà de l'exercice de manipulation de tableau, ça sert à quoi? Ca pue l'exercice mal torché par un "prof" qui n'y connait rien à qui on a demandé de donner une initiation à l'informatique.

Parce que pour copier/coller des plages, il y a plus simple, tout aussi instantané. Et on pourrait réaliser un collage spécial valeur si on le souhaitait.

Code :

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

1
2
3
4
Sub Test3()
  Feuil1.Range("a1:d" & Feuil1.Range("a1").End(xlDown).Row).Copy Destination:=Feuil3.Range("a1")
  Feuil2.Range("a1:b" & Feuil2.Range("a1").End(xlDown).Row).Copy Destination:=Feuil3.Range("a1").End(xlDown)(1)
End Sub

Au passage, attention à tes propriétés d'application que tu modifies en début de proc. Si tu plantes en cours d'exécution, ça te posera des problèmes... Tu dois passer par une gestion d'erreur pour être sûr de remettre les choses en l'état à la sortie.

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
 
Sub ...()
    On Error Goto EndHandler
    Application.ScreenUpdating = False 'arrête le travail en arrière plan
    Application.DisplayStatusBar = False 'inhibe la mise a jour de la barre d'état
    Application.Calculation = xlCalculationManual 'le mode de calcul devient manuel
    ActiveSheet.DisplayPageBreaks = False 'n'affiche plus les sauts de pages
    Application.DisplayAlerts = False
    ...
    ...
EndHandler:
    Application.ScreenUpdating = True ' travail en arrière plan
    Application.DisplayStatusBar = True 'la barre d'état est mise a jour
    Application.Calculation = xlAutomatic 'le mode de calcul redevient automatique
    ActiveSheet.DisplayPageBreaks = True 'affiche les sauts de pages
    Application.DisplayAlerts = True
End Sub

[EricDgn]

Bonjour Pierre Fauconnier,

Dans un des exemples vous indiquez:

Code :

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

 ...Copy Destination:=Feuil3.Range("a1").End(xlDown)(1)

Pourriez-vous me donner l'explication de (1)?
J'ai compris que cela forçait de passer 1 ligne plus bas que la ligne obtenue par xlDown, mais je ne comprends pas la mécanique, le principe, qu'il y a derrière.
Merci.

[Pierre Fauconnier]

Bonjour Eric,

[...]

Code :

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

 ...Copy Destination:=Feuil3.Range("a1").End(xlDown)(1)

Pourriez-vous me donner l'explication de (1)?[...]

En fait, il y a une erreur dans mon code (en plus de la remarque de Patrick), ce n'est pas (1) qu'il faut mettre, mais (2) pour passer à la ligne suivante. Dans le cas où l'index suit une cellule unique, c'est une technique "rapide" qui remplace Feuil3.Range("a1").End(xlDown).Offset(1,0). Le principe derrière est que l'on regarde la cellule précédent cet indice comme la cellule du coin supérieur gauche d'une plage et qui se trouve dès lors en ligne 1 et colonne 1 de cette plage. On peut alors se déplacer dans les 4 directions au moyen des indices. (1,1) reste sur place, (2,4) descend d'une ligne et va 3 colonnes à droite, (0,0) remonte d'une ligne et part d'une colonne à gauche. Bien sûr, les déplacements doivent atterrir sur la feuille sous peine d'erreur . Attention que (1) ne veut pas toujours dire une ligne plus bas. Tout dépend de la configuration de la plage sur laquelle tu appliques l'indice. Attention également qu'en utilisant les indices, tu arrives toujours sur une plage d'une cellule, contrairement à l'Offset qui lui renvoie toujours une plage de même taille que la plage sur laquelle on l'applique. De plus, les indices permettent le déplacement par rapport à la plage alors que Offset le fait toujours par rapport à la feuille. J'avais fait un papier là-dessus en son temps mais je ne remets pas la main dessus


Bonjour Patrick,

Oui, tu as sans doute raison. J'avoue que sans les explications du demandeur et au vu du code qui bouclait 3x sur 20.000 lignes, je me suis arrêté là pour juste illustrer deux techniques rapides, l'une pour charger un tableau sur base d'une plage, l'autre pour copier rapidement une plage vers un autre endroit. Comme en plus c'est sûrement un exo (mal conçu, mal compris ou les deux), je me suis arrêté là. Par contre, ce serait étonnant qu'il y ait trop de lignes pour Excel...

[patricktoulon]

bonsoir pierre
aux vues des résultats que l'on obtient avec sont code je suis pas sur que ca soit le résultat escompté

cela dit ubound(t2) fois ubound(t1) tours de boucle avec 200000 lignes ca fait beaucoup

en corrigeant le code et en remettant tout en base 1 puisque l'on parle de source range
j'obtient ceci

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
 
Sub colonnes()
    Application.ScreenUpdating = False    'arrête le travail en arrière plan
    Application.DisplayStatusBar = False    'inhibe la mise a jour de la barre d'état
    Application.Calculation = xlCalculationManual    'le mode de calcul devient manuel
    ActiveSheet.DisplayPageBreaks = False    'n'affiche plus les sauts de pages
    Application.DisplayAlerts = False
    Dim ligne1 As Long, ligne2 As Long, c As Long, i As Long, j As Long, fin As Long, l As Long
    Dim T1, T2
    ligne1 = Sheets(1).Range("A1").End(xlDown).Row
    ligne2 = Sheets(2).Range("A1").End(xlDown).Row
    'ReDim T1(1 To ligne1, 3)
    ' Sheets(1).Activate
    'c = 1
    'For i = 1 To ligne1
    'T1(c, 0) = Range("A" & i).Value
    'T1(c, 1) = Range("B" & i).Value
    'T1(c, 2) = Range("C" & i).Value
    'T1(c, 3) = Range("D" & i).Value
    'c = c + 1
    'Next
    'sheets(2).Activate
    'l = 1
    'For i = 1 To ligne2
    'T2(l, 0) = Range("A" & i).Value
    'T2(l, 1) = Range("B" & i).Value
    'l = l + 1
    'Next
    'tes tableaux a la base sont issus de range reste en base 1  et prends les directement comme ca (pas besoins de boucle de construction )
    'bien qu'il ne soit pas necessaire de créer des variable tableau t1 et t2 car tes 2 plages  ne servent qu'en lecture!!!! mais bon si tu veux
    T1 = Sheets(1).Range("A1:D" & 500).Value 'remplace 500 par ligne1 j'ai mis 500 pour mes tests
    T2 = Sheets(2).Range("A1:B" & 500).Value 'remplace 500 par ligne2 j'ai mis 500 pour mes tests
    ReDim resultat(1 To ligne1, 1 To UBound(T1))    'on reste en base 1 lignes/colonnes
    For j = 1 To UBound(T2)
        For i = 1 To UBound(T1)
            If T1(i, 2) = T2(j, 2) Then
                'les indexx de colonne resultat sont les meme que la plage de depart(1,2,3,4) et non (0,1,2,3)
                resultat(i, 1) = T1(i, 1)
                resultat(i, 2) = T2(j, 1)
                resultat(i, 3) = T1(i, 3)
                resultat(i, 4) = T1(i, 4)
            End If
        Next
    Next
    ' encore une fois on reste en base 1
    'Sheets(3).Range("A1").Resize(UBound(resultat, 1) + 1, UBound(resultat, 2) + 1) = resultat '???????????????????????????????
    'donc
    Sheets(3).Range("A1").Resize(ligne1, UBound(resultat, 2)) = resultat
 
    Erase T1
    Erase T2
    Erase resultat
    Application.ScreenUpdating = True    ' travail en arrière plan
    Application.DisplayStatusBar = True    'la barre d'état est mise a jour
    Application.Calculation = xlAutomatic    'le mode de calcul redevient automatique
    ActiveSheet.DisplayPageBreaks = True    'affiche les sauts de pages
    Application.DisplayAlerts = True
End Sub

beaucoup trop de lignes
il faut s'orienter vers une autre app

[Pierre Fauconnier]

Pour coller à l'exercice (dont je me demande toujours la finalité)...

Code instantané

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Test1()
  Dim Target As Range
  Dim LastRow As Long
 
  Set Target = Feuil3.Range("a2")
  LastRow = Feuil1.Range("a1").End(xlDown).Row
  If LastRow > Feuil2.Range("a1").End(xlDown).Row Then LastRow = Feuil2.Range("a1").End(xlDown).Row
  Set Target = Target.Resize(LastRow)
  Target.Value = Feuil1.Range("a1").Resize(LastRow).Value
  Target.Offset(0, 1).Value = Feuil2.Range("a1").Resize(LastRow).Value
  Target.Offset(0, 2).Value = Feuil1.Range("c1").Resize(LastRow).Value
  Target.Offset(0, 3).Value = Feuil1.Range("d1").Resize(LastRow).Value
End Sub

[Transitoire]

Bonjour, il est clair que tout cela est étrange, on mélange des colonnes de deux tableaux différents, mais surtout dont les nombres de lignes sont différents? d'ou la question qu'en est-il des lignes supplémentaires du tableau1.
A défaut de faire travailler correctement les élèves, ça fait travailler les membres du forum.

Cordialement

[Transitoire]

Rebonjour, juste pour le fun, je me suis amusé à faire a peu près la même chose avec des select(beurk,beurk....) et j'obtiens un temps 10 fois plus rapide que celui du code proposé par Julie67. 3 secondes au lieu de 30 secondes

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
Sub test()
Application.ScreenUpdating = False
    Sheets(1).Select
    Range("A1:A" & Cells.End(xlDown).Row).Select
    Selection.Copy
    Sheets(3).Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets(2).Select
    Range("A1:A" & Cells.End(xlDown).Row).Select
    Selection.Copy
    Sheets(3).Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets(1).Select
    Range("C1:D" & Cells.End(xlDown).Row).Select
    Selection.Copy
    Sheets(3).Select
    Range("C1").Select
    ActiveSheet.Paste
    Application.ScreenUpdating = True
End Sub

Cordialement

[OrDonc]

Bonjour

Avec la classe Tableau (ListObjects) et dans le cadre fourni (sans doute pas celui du projet global) …
En Feuil1, le Tableau nommé ç1, en Feuil2, un Tableau nommé ç2, dans l’onglet récepteur un Tableau nommé ç3 et dans la fenêtre des codes de cet onglet :

Code :

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

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Activate()
    Dim Dl As Long
    Application.ScreenUpdating = 0
    Dl = Application.Min([ç1].Rows.Count, [ç2].Rows.Count)
    [ç1].Resize(Dl).Copy [ç3].Item(1, 1)
    [ç2].Columns(1).Copy [ç3].Item(1, 2) 'la colonne à réajuster
End Sub
Private Sub Worksheet_Deactivate()
  If Application.CountA([ç3]) > 1 Then [ç3].Delete
End Sub

Remarque : la macro Deactivate permet, en plus du nettoyage, d’alléger le classeur.

[Pierre Fauconnier]

Salut

[...]

Avec la classe Tableau (ListObjects)[...]

Pour rappel car déjà signalé ailleurs, [c1] n'utilise pas un listobject mais une plage de données. Ce n'est pas parce qu'on utilise une référence structurée que l'on utilise un ListObject. Que MonTableau corresponde au nom d'une table de données (ListObject) ne veut pas dire que Range("MonTableau") utilise le ListObject. Range("MonTableau") et [MonTableau] sont des plages (objets de la classe Range)

Outre le fait que la syntaxe [MonTableau] prive le programmeur de la saisie semi-automatique. Donc perso, je ne suis pas fan de cette syntaxe...

[EricDgn]

Merci Pierre pour l'explication.
Le papier dont tu parles est sans doute celui-ci: Excel VBA: Range(x), Range(x,y) et Range.Offset(x,y) que je viens de trouver.
Encore merci.

[Pierre Fauconnier]

Merci Eric. C'est effectivement celui-là. Yeah

[OrDonc]

Re

@Pierre Fauconnier,
puisque tu insistes … il me semble que tu fais plutôt une fixation sur le choix de l’utilisation de [ç1].

Il permet à tout intervenant d’utiliser les propriétés de base des classes Ranges et Range nécessaires pour se lancer dans des conceptions de codes avec une syntaxe basique et sans pour autant, abandonner les avantages fournis par la classe ListOjects.

Maintenant, chacun doit savoir que chaque outil a ses défauts et qualités, ses inconvénients et ses avantages, ses limites.
La bonne attitude est, me semble-t-il, de choisir le bon au bon moment, sans se priver de certains autres palliant ses inconvénients.

Ma proposition est-elle défaillante par rapport aux résultats escomptés même si je n’ai pas utilisé la syntaxe propre à ces Tableaux ?

Remarques personnelles

- je déplore tous ces abus des With … End With utilisés dans n’importe quelles circonstances.
Je n’ai pas eu besoin de préciser Feuil1.[ç1] …, Feuil2.[ç2] ... avec la macro écrite ailleurs !
- quand j’ai des doutes, Range(). me permet d’avoir la liste dont tu fais allusion.

- en prenant connaissance des derniers messages, j’aurais remplacé, dans ton code,
̶T̶a̶r̶g̶e̶t̶.̶O̶f̶f̶s̶e̶t̶(̶0̶,̶ ̶1̶)̶ ̶p̶a̶r̶ ̶T̶a̶r̶g̶e̶t̶(̶1̶,̶ ̶2̶)̶
̶T̶a̶r̶g̶e̶t̶.̶O̶f̶f̶s̶e̶t̶(̶0̶,̶ ̶2̶)̶ ̶p̶a̶r̶ ̶T̶a̶r̶g̶e̶t̶(̶1̶,̶ ̶3̶)̶
̶T̶a̶r̶g̶e̶t̶.̶O̶f̶f̶s̶e̶t̶(̶0̶,̶ ̶3̶)̶ ̶p̶a̶r̶ ̶T̶a̶r̶g̶e̶t̶(̶1̶,̶ ̶4̶)̶
non pas par plaisir, mais pour indiquer une syntaxe que peu connaissent et que je propose continuellement.

- Si je ne trompe, ici ç1 est bien défini, comme un Tableau de la classe ListObjects, non ?

[Pierre Fauconnier]

Ordonc,

Ne discutons pas des styles de codage. Chacun a le sien. Quand j'argumente sur une façon de coder ou une syntaxe particulière, c'est parce que j'ai mes raisons, que je détaille d'ailleurs toujours... . Nous pourrons toi et moi argumenter à loisir sur les avantages et inconvénients de nos styles et de nos habitudes sans nous convaincre l'un l'autre. Je n'aime pas [a1] et lui préfère Range("a1"). Toi, c'est l'inverse? Tant mieux. Sur ce plan-là, j'ai simplement signalé que la syntaxe [] ne donne pas accès à la saisie semi-automatique, ce qui amène notamment à ne pas voir avec quel objet on travaille vraiment.

Sur les With... End With, perso, ce n'est pas que j'aime ou que je n'aime pas, mais que je les utilise à bon escient (de mon point de vue). Tu n'as pas besoin de préciser Feuil1.[NomTableau], mais si tu utilises Range("MonTableau"), tu n'en as pas besoin non plus. A ce niveau-là, les deux écritures se valent. Je ne comprends donc pas bien ton argument. Mais de toute façon, c'est là aussi préférer un style à un autre et, sauf autour d'une triple Karmeliet, il n'est pas très utile d'en débattre.


Tu demandes si ta proposition est défaillante, je réponds: Non, elle ne l'est pas. C'est une autre façon de coder, c'est tout, mais elle n'est pas défaillante. Il faut juste savoir ce que tu utilises comme objet, car Range et ListObject sont des objets bien distincts, et tu ne peux pas utiliser un à la place de l'autre.

Par contre:

Lorsque tu dis que tu utilises un listobject en utilisant [NomTableau], tu es dans l'erreur car [NomTableau], tout comme Range("NomTableau"), pointe vers un objet RANGE et pas vers un objet LISTOBJECT. Le fait d'utiliser une référence structurée, quelle que soit l'écriture utilisée, n'amène pas à utiliser un LISTOBJECT, dont les propriétés et méthodes sont bien différentes des propriétés et méthodes d'un objet RANGE.

Si tu veux utiliser le LISTOBJECT qui contient ton objet Range ([MonTableau] ou Range("MonTableau")), tu dois utiliser [MonTableau].ListObject ou Range("MonTableau").ListObject. Sans cela, tu ne pourras pas utiliser les propriétés et méthodes d'un ListObject. Et donc, lorsque tu dis : ici ç1 est bien défini, comme un Tableau de la classe ListObjects, non ? , tu te trompes, c1 n'est pas défini comme tableau (listobject) mais comme plage (range).




D'ailleurs, avec la saisie semi-automatique, on voit bien que l'objet que l'on utilise est différent






Pour l'illustration du ruban, il ne faut pas comparer Excel à VBA. En Excel, on n'utilise pas le listobject dans des formules parce que c'est tout simplement impossible. Lorsque l'on utilise les références structurées, on utilise des plages (à l'instar des plages nommées, même si une référence structurée n'est pas une plage nommée). Il y a bien une aide due au ListObject lorsque l'on saisit la référence structurée d'une colonne d'un tableau, à l'ouverture du crochet, mais l'utilisation d'un listobject s'arrête là. D'ailleurs, on peut utiliser les références classiques à la place des références structurées, elles fonctionnent de la même manière et s'adaptent aux changements de taille de la table comme les références structurées. Et si tu rétrogradais un xlsx en xls, Excel transformerait les références structurées en références classiques.




Pour ce qui est du Target.Offset(0, 1) par Target(1, 2)... Tu ne peux pas l'écrire comme tu l'écris. Les deux ne sont pas équivalents... Comme je le dis dans mon billet cité par Eric, Offset renvoie une plage de taille identique à la plage source du décalage, alors que la deuxième syntaxe renverra toujours une seule cellule.




Cela étant dit, de grâce, sois convaincu qu'il n'y a aucune critique de la personne que tu es. Je critique (ou plutôt, je discute) les façons de coder parce que j'estime que ça apporte un plus au lecteur. C'est tout. Sauf quand on me sort de mes gonds, il n'y a jamais d'attaque personnelle.

[patricktoulon]

re
bonjour
sans oublier et la il n'est nullement une facon de coder ou une autre mais le désavantage aussi de la syntaxe "[A1]"par exemple
sont des caractères réservés au formation de tableaux(variables)
et force donc vba a calculer donc mémoire plus sollicitée
si je me souviens bien Pierre c'est toi qui me la dit
donc en gros quand tu fait "[A1] " tu créée un tableau ( range) de 1 ligne sur 1 colonne tu fait donc travailler vba pour rien , nothing, des nèfles,queue Dale
je dis bien tu le créée

[OrDonc]

re

Cela étant dit, de grâce, sois convaincu qu'il n'y a aucune critique de la personne que tu es. Je critique (ou plutôt, je discute) les façons de coder parce que j'estime que ça apporte un plus au lecteur. C'est tout. Sauf quand on me sort de mes gonds, il n'y a jamais d'attaque personnelle.

Sois rassuré, je suis du genre à accepter toute critique !
Quand elle est constructive, j’en tiens compte sinon je n’en fais pas un flan tout en refusant de débattre.
J’apprends tout aussi bien de mes erreurs* que de celles des Autres et il me reste encore énormément à apprendre !
En espérant ne pas t'avoir fait sortir de tes gonds ...

* Mea culpa, j’ai proposé le Target(1, x) car j’ai zappé la ligne Set Target = Target.Resize(LastRow) tout en restant sur la ligne Set Target = Feuil3.Range("a2").
Ma proposition n’est valable que pour 1 cellule et non pas pour une plage multi-cellules .

[Pierre Fauconnier]

Ordonc,

C'est pour ça que je suis content qu'Eric ait remis la main sur mon billet, car je pense qu'il illustre bien les différences entre Range.Offset(x,y) et range(x,y) et les dangers d'utiliser l'un ou l'autre sans savoir en quoi ils diffèrent

Et non, tu ne m'as pas sorti de mes gonds...


Partick,

je ne me souviens pas avoir dit un jour que [a1] créait un tableau. Je ne suis pas spécialiste de ce qui se passe en interne dans le vba et je ne parlerai jamais d'un truc que je ne maîtrise pas. J'ai justement écrit dans mon précédent message que [a1] était une plage (objet Range) et je l'ai illustré avec Typename([a1]) => "Range"

[patricktoulon]

re
je ne sais plus qui me la dit il me semblait que c'était toi
et oui c'est un range créé avec les caractères réservés

pour les range(x,y)/range(X)

perso je me sert des indexs(range(X) quand je fait une boucle (for each cells in)comme la lecture se fait linéairement je sais ou je vais (1,1);(1,2);(1,3)2,1);(2,2);(2,3) etc.....