Ecran qui papillonne lors de l'exécution d'une macro

J'ai une macro qui effectue de nombreux changements de classeurs à l'aide de l'instruction Worksheets("xxx").Activate.

Sous Excel 2003, l'instruction Application.ScreenUpdating = False évitait à l'écran de papillonner. Par contre, sous Excel 2019, le papillonnement se produit comme si le ScreenUpdating = False n'était pas pris en compte. En revanche, si au lieu de changements de classeurs, on effectue des changements de feuilles au sein d'un même classeur, l'écran reste bien figé.

À titre d'illustration, j'ai mis en PJ deux classeurs Excel avec pour le premier une macro très simplifiée effectuant successivement une vingtaine de copier-coller basiques, soit entre les deux classeurs, soit entre les deux feuilles du premier classeur.

Y a-t-il une explication, par exemple le fait qu'il y ait deux instances d'Excel ouvertes, une par classeur ? Je n'en n'ai pas trouvé dans les archives récentes du présent forum.

D'avance, merci pour l'aide.

Bonjour,

C'est normal. Vous n'avez qu'une instance d'Excel ouverte cependant.

Solution : vous n'avez pas besoin d'activer et désactiver les feuilles à tout va pour copier/coller. Utilisez simplement l'argument destination en précisant la feuille.

Workbook1.Worksheets("Feuil1").Range("A1").Copy Destination:=Workbook2.Worksheets("Feuil3").Range("B1")

Range.Copy, méthode (Excel) | Microsoft Learn

Merci pour cette réponse rapide mais ma demande ne portait pas sur une manière efficace d'effectuer par macro des copier-coller entre deux classeurs. J'ai utilisé cet exemple élémentaire pour illustrer le fait qu'avec Excel 2019, l'écran scintillait malgré le ScreenUpdating = False alors qu'il ne le faisait pas avec Excel 2003.

Je note que ce papillonnement serait "normal" mais j'avoue ne pas bien comprendre pourquoi avec les versions récentes d'Excel, l'écran se rafraîchit quand la macro active un autre classeur et pas quand elle active une autre feuille d'un même classeur. Dois-je reprogrammer ma macro pour éviter les allers-retours fréquents entre les deux classeurs ou bien existe-t-il malgré tout une manière de figer l'affichage de l'écran ?

Cordialement.

Bonjour,

Pour XL 2003 je ne sais pas, je n'ai jamais utilisé cette version. Je vous informais simplement de la manière de procéder pour éviter ce désagrément. Par ailleurs si vos opérations de copies ne sont destinées qu'à copier les valeurs, pas la mise en forme, la synthaxe suivante est à préférer

Range("destination").Value = Range("depart").Value

C'est beaucoup plus rapide.

Mon opinion c'est que votre vieux XL n'arrivait tout simplement pas à suivre les rafraichissements rapides de l'écran, là où la version plus moderne et rapide vous le fait "apparaitre".

Merci pour ces compléments d'information. Il est en effet probable qu'Excel 2003 (ou le processeur du vieux PC sur lequel il était installé) n'arrivait pas à suivre les rafraîchissements d'écrans.

Je suis donc en train de réécrire ma macro en retirant ces multiples activations de classeurs afin d'éviter le papillonnage de l'écran. Une instruction telle que Worksheets("Feuil1").Range("A1:A5").Copy Destination:=Worksheets("Feuil2").Range("B1") fonctionne parfaitement avec des références de type "A1". Or, à cause des boucles, mes plages sont toutes définies avec des références L1C1.

Utiliser par exemple Range(cells(1,1), cells(5,1)) à la place de Range("A1:A5") tout en laissant la destination en référence A1, provoque un message d'erreur d'exécution '1004' - Erreur définie par l'application ou par l'objet … à moins que la feuille source soit active !

D'où ma nouvelle question : quelle syntaxe utiliser pour cette copie "directe" en utilisant des références L1C1 ?

D'avance, merci pour l'aide.

Bonjour,

C'est une erreur "classique". Lorsque vous écrivez

Range(Cells(1,1), Cells(3,4))

cela est strictement équivalent à

Range(ActiveSheet.Cells(1,1), ActiveSheet.Cells(3,4))

Par défaut, si non précisé, les instructions se rapportent toujours à la feuille/au classeur actif.

C'est pourquoi, dans votre cas particulièrement, l'instruction With, instruction (VBA) | Microsoft Learn va etre très utile. Au lieu d'avoir à écrire le nom de la feuille/du classeur à chaque fois, vous pouvez écrire :

    For I = 11 To 20
        With ThisWorkbook.Worksheets(1)
          .Cells(I, 1).Copy Workbooks("Classeur B.xlsx").Worksheets(1).Cells(I, 1) 
          .Cells(I, 2).Copy Workbooks("Classeur B.xlsx").Worksheets(1).Cells(I, 2)
        End With
    Next I

Remarquez, très important, le point "." devant l'instruction cells. Cela indique à Excel de travailler de la manière suivante :

Dans le classeur actuel, feuille 1 : Prendre les cellules blablabla.

J'ai repris votre exemple de code, mais vous pouvez utiliser plusieurs points, c'est là où with devient très intéressant. Par exemple :

With ThisWorkbook.Worksheets(1).Range("A1")
          Set maRange = Range(.Cells, .End(xlDown))
End With

Va nous permettre de définir "maRange" comme toute les cellules pleines consécutives de la colonne A à partir de A1. Très utile.

Enfin, pour revenir sur le dernier point avec ".Value" Propriété Range.Value (Excel) | Microsoft Learn. On peut aussi écrire

    For I = 11 To 20
        With ThisWorkbook.Worksheets(1)
          Workbooks("Classeur B.xlsx").Worksheets(1).Cells(I, 1).Value = .Cells(I, 1).Value
          Workbooks("Classeur B.xlsx").Worksheets(1).Cells(I, 2).Value = .Cells(I, 2).Value
        End With
    Next I

Pour recopier les valeurs sans mise en forme ni formule, c'est beaucoup plus rapide à l'exécution.

Un dernier point d'optimisation, je vois que vous avez cette boucle qui va de la ligne 11 à 20 et copie les valeurs 1 à une… on peut tout copier en 1 étape !

Essayez ceci :

Sub Test_ScreenUpdating_Classeurs()
'
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("FeuilA2").Activate
    Range(Cells(11, 1), Cells(20, 2)).ClearContents
    Workbooks("Classeur B.xlsx").Sheets("FeuilB").Activate
    Range(Cells(11, 1), Cells(20, 2)).ClearContents
'
' '''' PARTIE MODIFIEE 
    Dim maRange As Range
    With ThisWorkbook.Worksheets(1)
      Set maRange = Range(.Cells(11, 1), .Cells(20, 2))
    End With
    With Workbooks("Classeur B.xlsx").Worksheets(1)
      maRange.Copy Destination:=Range(.Cells(11, 1), .Cells(20, 2))
    End With

'''''''''''''''''
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
'
End Sub

bonjour

ceci contient une erreur que tu a soulevé en plus

With Workbooks("Classeur B.xlsx").Worksheets(1)
      maRange.Copy Destination:=Range(.Cells(11, 1), .Cells(20, 2))
    End With

je donne un indice il manque un point

apres on peut simplifier aussi

Dim tbl

    tbl = ThisWorkbook.Worksheets(1).Range(Cells(11, 1).Address).Resize(9, 2).Value 'le 9 c'est 20-11

    Workbooks("Classeur B.xlsx").Worksheets(1).Cells(11, 1).Resize(UBound(tbl), UBound(tbl, 2)) = tbl

l'astuce ici consiste a récupérer l'address de la cells(11,1) pour ne pas avoir a secifier le parent puisque l'on a le ".Range"

et oui cells(11,1) que ce soit sur un classeur ou un autre donnera la même address

donc le (.Range(cells(11,1).address) comme il y a le point devant range c'est le range qui est enfant de ce qui précède

de la même maniere pour le classeur B ou là c'est le .cells(11,2) qui est enfant de ce qui precède

comme je fait un resize je n'ai pas besoins de la dercells de la plage

et pour la destination comme j'ai créer une variable array a deux dimensions je redimensionne la destination a la variable

Hello PatrickT, pas d'erreur sur ce point de mon coté.

Lors de l'utilisation de Range(Cells, Cells), il n'y a pas besoin de "." devant Range car on lui donne déjà les cellules haut gauche et bas droite. Techniquement, on utilise la fonction de création de Range, pas la propriété d'une feuille.

Grand merci à vous deux pour ces infos sur Range que je ne connaissais pas très bien, malgré mes nombreuses "heures de vol" avec les macros Excel. C'est, je pense, une lacune due à mon apprentissage de VBA effectué essentiellement via l'enregistreur de macros ! Et merci également pour ces rappels de programmation concernant l'instruction With.

Cela étant, l'exemple que j'ai fourni avec des copier-coller effectués un par un avait pour but d'introduire à chaque fois un changement de classeur volontaire afin d'illustrer le papillonnement de l'écran que provoque l'exécution de la macro malgré l'instruction ScreenUpdating = False. J'aurais très bien pu me passer de ces copier-coller et me contenter de simples changements de classeur.

En tout cas, vos interventions m'ont permis de résoudre mon problème de copier-coller direct en notations L1C1 et je vais pouvoir clore le sujet.

Amicalement.

re

Bonjour

Hello PatrickT, pas d'erreur sur ce point de mon coté.

Lors de l'utilisation de Range(Cells, Cells), il n'y a pas besoin de "." devant Range car on lui donne déjà les cellules haut gauche et bas droite. Techniquement, on utilise la fonction de création de Range, pas la propriété d'une feuille.

heu..non !!! c'est faux

dans un bloc with

si tu ne fait .range(xxxx) tu tape dans le activesheet du classeur de la macro

il faut reviser les bases fondamentales de la gestion des cellules en VBA excel

c'est le B à BA

With Sheets("taratata")
'dans cet exemple :Range(.Cells(1, 2), .Cells(10, 5)).Address EST UN STRING donc pas de problème qu'il n'y ai pas les points
'car sur une feuille ou une autre l'address sera la même
.Range(.Cells(1, 2), .Cells(10, 5)) = Sheets("turlututu").Range(Range(Cells(1, 2), Cells(10, 5)).Address)
End With

' si je devait l’écrire correctement (façon école)

With Sheets("taratata")
'DANS CET EXEMPLE / .Range(Sheets("turlututu").Cells(1, 2), Sheets("turlututu").Cells(10, 5)) EST UNE PLAGE
'il est donc primordiale de mettre les points et le parent pour bien identifier le parent
.Range(.Cells(1, 2), .Cells(10, 5)) = Sheets("turlututu").Range(Sheets("turlututu").Cells(1, 2), Sheets("turlututu").Cells(10, 5))
'sans les points aux cells
'ça vaudrait sheet("turlututu").range(cells(1,2) de taratata,cells(10,5) de taratata
' et il y aurait une erreur d'ailleurs une plage ne peut pas être séparé dans 2 feuille d'un même classeur ou pas
'un range peut être non contiguë mais pas dans deux feuilles différentes
End With

et quand on veut travailler proprement on variabilise

Sub test2()
'exemple de transfert de donnée avec deux address différentes
Dim R1 As Range, R2 As Range

With Sheets("taratata"): Set R1 = .Range(.Cells(1, 2), .Cells(10, 5)): End With

With Sheets("turlututu"): Set R2 = .Range(.Cells(3, 4), .Cells(12, 7)): End With

'on transfert les données de R2 dans R1
R1 = R2.Value
'on transfert les données de R1 dans R2
R2 = R1.Value

'sinon tu peux faire des copies
R2.Copy Destination:= R1

' voila c'est propre et intelligible
'!!!!!!!!! LES POINTS DANS UN BLOC WITH SONT OBLIGATOIRES!!!!!!!!!
End Sub

Ah ben oui c'est plus net

à méditer ou pas

Bonjour PatrickT, merci pour ton retour, je vois que tu as à coeur de partager ton opinion.

Mais dans ce cas précis je maintiens que vous etes dans le faux. La confusion vient probablement de l'overload de "Range" dans le cas précis.

Quand on écrit Range(cell1, cell2), on appelle le constructeur de Range. Il prend en paramètres 2 cellules, et renvoie une nouvelle range couvrant la plage donnée. Les cellules doivent toutes 2 etre sur la meme feuille, autrement on lève une erreur bien entendu.

L'utilisation de Worksheet.Range par ailleurs fait appel au "getter" de Range, à partir de l'objet Worksheet. Il s'agit d'une autre fonction de récupération des cellules dans une feuille.

Parce que le VBA est "userfriendly", il s'occupe en background de toutes ces conversions entre getter/setter et constructeurs.

En d'autres termes, l'utilisation du point dans l'exemple donné est redondante. Ta proposition est correcte, je ne la remet pas en question, mais la mienne l'est tout autant.

Je t'invite à consulter le classeur ci-joint contenant la macro ci-dessous pour illustrer mon propos.

Au plaisir.

Sub test()
  Sheet1.Activate

  Dim rng2 As Range, rng3 As Range
  With Sheet2
    Debug.Print "Feuille active: "; ActiveSheet.Name
    Set rng2 = Range(.Range("A1"), .Range("C1"))
    Debug.Print "La variable rng2 est d�finie sur la feuille: "; rng2.Worksheet.Name
  End With
  With Sheet3
    Debug.Print "Feuille active: "; ActiveSheet.Name
    Set rng3 = Range(.Range("A1"), .Range("C1"))
    Debug.Print "La variable rng3 est d�finie sur la feuille: "; rng3.Worksheet.Name
  End With

  Sheet1.Range("A2:C2").Value = rng2.Value
  Sheet1.Range("A3:C3").Value = rng3.Value

End Sub

re

écoute tu es têtu mais moi aussi et a tout juste titre

prend un nouveau classeur

met lui deux feuille Feuil1 et Feuil2

alors je te propose juste ce petit test simple dans le bloc with Feuil2

Sub test()
    Feuil1.Activate
    With Feuil2
        MsgBox Range(Cells(1, 1), Cells(10, 5)).Parent.Name
        MsgBox .Range(Cells(1, 1), Cells(10, 5)).Address
    End With
End Sub

après quand tu en aura marre de te poser des questions inutiles mais qui néanmoins vont sérieusement remettre en question ce que tu pense savoir

et bien tu ajoutera le point devant les "cells" et le range qui ne l'a pas

après tu reviens me voir et je te ferait relire mon post précédent

Bonjour PatrickT,

Ton code donne une erreur 1004 chez moi. De plus, je dois avouer que je suis surpris par le ton de ta réponse... Alors que jusque-là tout me paraissait plutôt cordial.

Bon peu importe, la méthode de PatrickT est celle à retenir, c'est celle utilisée dans les exemples Microsoft. Worksheet.Range property (Excel) | Microsoft Learn

Mon expérience perso m'a montré qu'il était redondant de mettre ce point dans ".Range(.Cells(1,1), .Cells(1,2))", mais je pense qu'il est préférable de s'en tenir aux lignes directives proposé par Microsoft.

Ma réponse n'avait pas pour but de froisser qui que ce soit, simplement de montrer par un exemple l'équivalence du "avec point" et "sans point".

re

Bonjour H2so4

mais c'est cordial je te rassure

je n'ai rien contre les têtus au contraire ,j'en suis un et peut être même le pire

on va au fond des choses comme ça

bien sur que dans mon code il y a une erreur c'est bien pour montrer que ce que crois saboh12617 est faux

pour résumer .Range(cells(a,b),cells(x,y)) dans un bloc with ,n'est pas bon même si il y a le point devant range

ça ne peut fonctionner uniquement pour le sheets actif avec le sheets actif (ce qui n'a aucun intérêt bien sur)

Mon expérience perso m'a montré qu'il était redondant de mettre ce point dans ".Range(.Cells(1,1), .Cells(1,2))", mais je pense qu'il est préférable de s'en tenir aux lignes directives proposé par Microsoft.

ça va bien plus loin que les recommandations MS c'est une question de logique

et en respectant cette règle tu peux coder toujours de la même façon sans jamais tomber à coté

et surtout ne pas déclencher l'erreur de détermination du range en cas de bloc with sur un autre sheets que celui qui est actif

comme je le démontre dans ma petite sub piege

simplement de montrer par un exemple l'équivalence du "avec point" et "sans point".

ce qui est faux bien entendu; le point désigne que l'object du bloc with est le parent , sans le point c'est le sheets actif qui est pris comme parent

Effectivement, je pense que l'on s'était mal compris.

Je suis tout à fait d'accord sur ce point.

Ce que j'évoquais, en lien avec mon exemple, c'est que

Set rng2 = Range(.cells(a,b), .cells(x,y))
Et
Set rng2 = .Range(.cells(a,b), .cells(x,y))

Sont absolument équivalents.

La position des points n'est pas la meme, nous sommes bien d'accord qu'ils sont nécessaires devant les 2 "cells" = arguments de la fonction Range. Je remarquais simplement que celui devant Range, dans ce cas précis, devenait superflu.

re

je ne s"ais pas si il est superflu

mais j'"ai toujours respecté cette logique with truc .quelquechose

Effectivement, je pense que c'est plus correcte.

A vrai dire je me rappelle d'une discussion sur l'instruction VBA.Mid suite à une proposition de Optimix ici Macro pour remplacer le troisième chiffre d'une valeur (excel-pratique.com) et je pense que dans le fond, c'est un peu la meme histoire. Il y a deux fonctions avec le meme nom.

Je me rend compte que je n'ai jamais posé ce point devant Range puisque pour moi à partir du moment ou mes 2 cellules cibles étaient bien définies sur la feuille du With, je n'avais pas besoin de "re"-préciser la feuille de destination. Mais à l'avenir je vais peut etre essayer de corriger cette mauvaise habitude.

bonjour,

Mon expérience perso m'a montré qu'il était redondant de mettre ce point dans ".Range(.Cells(1,1), .Cells(1,2))", mais je pense qu'il est préférable de s'en tenir aux lignes directives proposé par Microsoft.

C'est également mon expérience, si on n'utilise pas cette instruction dans un module de feuille, le point devant le premier range n'est pas nécessaire.