Lenteur VBA Excel 2010

**trismouth** · 03/10/2013, 14h59

Bonjour,

Je vous explique briévement mon problème.

J'ai développé pour un client des macros sur Excel 2003, les temps de traitements sont optimisés (vérouillage de l'écran, peu de select,...). Jusque là pas de problème.

Mon client a décidé d'installé Office 2010 sur tous c'est postes donc conversions de tous les fichiers et modifications des macros pour qu'elles continuent de fonctionner.
Le problème est que les temps de traitement sont maintenant multipliés par 4 ou 5 alors les macros sont identiques ou presque.

Quelqu'un aurait-il une explications à ceci (VBA 2010 plus lent, bugs connus,...)

Merci d'avance pour votre aide.

Cordialement,

**EngueEngue** · 03/10/2013, 15h01

Excel 2003 a 65 k lignes excel 2010 1 million... Il nous faudrait voir tes macros pour pouvoir voir ce qui pourrait provoquer un tel comportement

**trismouth** · 03/10/2013, 15h25

Mes macros sont composés de dizaines de milliers de lignes dans différents fichiers et sont en plus confidentielles. Donc cela va être compliqué de vous les communiquer.

Mais le problème n'est pas dans le code lui même car avant la migration vers 2010 tous les traitements étaient rapide.

Je suis d'accord que le nombre de lignes et colonnes et plus important mais la taille des fichiers est beaucoup importante quand on utilise le format .xlsb par rapport a .xls. De plus le nombre de cellules n'a pas vraiment d'importance car je désactive les calculs lors de mes tratiements.

**EngueEngue** · 03/10/2013, 15h29

Que font tes traitements...

**trismouth** · 03/10/2013, 15h48

Composition de l'applicatif (en bref) :

- Un fichier avec montant de CA répartis par Compte Comptable, région,...
- Un fichier contenant la hiérarchie des région Niveau 1, Niveau2,..., Niveau 6
- Un fichier contient une maquette vierge avec un onglet composé du tableau modèle vierge et d'un onglet contenant la ligne de destination des Comptes Comptables dans ce tableau.

En gros le programme principal créer autant de fichier que de Région (selon Niveau choisit) à partir de la maquette, récupérer les montants dans le fichier de CA et les écrit dans les fichiers de chaque Région. (Construction d'une seul Région à la fois pour ne pas avoir une multitude de fichier ouvert an même temps)

**EngueEngue** · 03/10/2013, 15h51

La création d'un xls et un xlsb ne devrait pas ralentir de 4x la macro... Il faudrait que tu places des timer dans tes macros pour voir où le décalage a lieu afin de cibler le problème.

**trismouth** · 03/10/2013, 16h06

Est-ce possible que tous simplement le nombre de lignes de code traité (par seconde par exemple) soit moins important sur Excel 2010 que sur Excel 2003 ?

**EngueEngue** · 03/10/2013, 16h10

non.
Mets des timers tout au long de ton code.
Execute le sur 2003 Puis sur 2010 et regarde où les temps d'execution varient.. Tu pourras cerner le problème. Et on pourra t'aider.

**Marc-L** · 03/10/2013, 16h16

Bonjour.

Envoyé par EngueEngue

Excel 2003 a 65 k lignes excel 2010 1 million... Il nous faudrait voir tes macros pour pouvoir voir ce qui pourrait provoquer un tel comportement

Je penche aussi vers une mauvaise programmation !

Par exemple du genre parcours d'une colonne entière au lieu de la plage utilisée de la colonne.

Qui plus est cellule par cellule au lieu d'utiliser une variable tableau …

Mais comme confidentiel le code est , tout seul tu es …

**trismouth** · 03/10/2013, 16h17

Si vous prenez l'exemple du fichier en pièce jointe, il contient une macro totalement inutile mais qui m'a servi de test entre 2003 et 2010

Elle ne fait Copier/Coller un tableau 3 lignes en dessous 10000 fois.

J'ai lancer l'exécution sur 2003 : Temps = 15sec
J'ai lancer l'exécution sur 2010 : Temps = 1minute

4,5 fois plus long alors que le traitement est en tous point identique...

**patricktoulon** · 03/10/2013, 16h28

Bonjour

pour copier une plage dans une autre plage tu n'est pas obligé de selectionner ta plage a copier

car selectioner 10000 fois une plage de bourre la memoire grave si je peux m'exprimer ainsi

moi je ferait plutot

Code :

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

1
2
3
4
5
for i= 1 to 10000
with sheets("nom du sheets")
.range("a26:a46")=.range("a1:a20")
end with
next i

ce qui corespond exactement a l'exemple que tu a posté

au plaisir

**EngueEngue** · 03/10/2013, 16h29

En cherchant un peu sur internet j'ai vu des forums parlant de

Code :

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

ActiveSheet.DisplayPageBreaks = True

Essaie de le glisser dans les classeurs que tu traites ... On ne sait jamais..

**Marc-L** · 03/10/2013, 16h35

Peut-être qu'il y a un souci sur l'ordinateur en question …

Mais déjà le code est vraiment affreux, en fait généré par l'Enregistreur de macros, pas du tout optimisé côté rapidité !

Ce code prend 7s sur un PC portable de test, ma procédure Demo n'en a besoin que de 3 !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub Demo()
                    [A1].Value = Time
    Application.ScreenUpdating = False
                            TS = [A7:K20].Value
    For N% = 1 To 10000
        [A26:K39].Value = TS
    Next
 
    Erase TS
                    [A2].Value = Time
    Application.ScreenUpdating = True
End Sub

Donc si l'application est comme le classeur en pièce jointe …

**trismouth** · 03/10/2013, 17h01

Envoyé par Marc-L

Bonjour.

Je penche aussi vers une mauvaise programmation !

Par exemple du genre parcours d'une colonne entière au lieu de la plage utilisée de la colonne.

Qui plus est cellule par cellule au lieu d'utiliser une variable tableau …

Mais comme confidentiel le code est , tout seul tu es …

Le classeur que j'ai mis en pièce jointe ,je le répète ne sert que d'exemple pour comparer la vitesse de traitement entre 2003 et 2010.
Il ne fait en aucun cas partie des applications que je développe qui elles sont optimisés (traitement des fichier en mémoire par variable tableau multidimensionnel et non pas une malheureuse lecture ligne à ligne des fichiers, limité au maximum les accés fichier->VBA VBA->fichier, mise à jour de l'ecran bloqué, déclarations de toutes les variables, libération de la mémoire en fin d'exécution, etc...).

Ma question n'est pas comment puis-je optimiser mon code pour qu'il s'exécute plus rapidement mais si quelqu'un et au courant de problème ou de spécificité sur la version 2010 de Excel à propos des traitements VBA.

Cordialement,

**Marc-L** · 03/10/2013, 17h05

Je préfère, t'aurais pu nous mettre un zoli code !

Par souci de maintenance d'anciens applicatifs, j'ai toujours une version 2003 sur laquelle je continue de développer

même pour des versions supérieures, et jusqu'à présent, je n'ai pas rencontré ce souci …

**EngueEngue** · 03/10/2013, 17h20

Le fait d'insérer une ligne ou d'en supprimer et les copiers collers sont plus gourmands sur excel 2007+ du fait de classeurs avec 1 M de lignes plutôt que 65k... Pour remédier à ce problème de programmation de bas étage il est possible d'activer l'option:

Code :

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

ActiveSheet.DisplayPageBreaks = True

Invité · 03/10/2013, 18h16

Salut,

Quand j'ai commencé la programmation VBA il y a quelques mois déjà, je progressait quasiment uniquement grâce aux Macro (et au explication sur PDF bien présenté et super compréhensible) de George Lungu de ExcelUnusual. Il utilisait principalement Excel 2003 plutôt qu'une version récente dû aux performances justement dans les simus de vols.
Sur Excel 2013, sur ça Macro de simu de vol je devait être à 3 ou 4 FPS si je me rappel contre pour lui une 20 aine sur des PC pas hyper différents).

Mais ce genre de Macro n'ai pas du même genre que beaucoup utilisent en VBA, donc à voir si tu y trouve une correspondance ou non. Puisque les macros qui sont traités en désactivant l'affichage n'ont rien à voir avec des formules sur feuille Excel qui mettent à jour un graphique. La qualité graphique générale et les options incorcorporé à Excel ont augmenté entre 2003 et 2010.

Quelques pistes ici :
http://excelunusual.com/about-the-sp...preadsheets-1/
http://excelunusual.com/wp-content/u...Tutorial_2.pdf
Il utilise beaucoup le couple VBA / feuille de calcul (pour permettre un affichage au niveau graphique). Essaie de voir si ton cas correspond à ces courbes de performances entre 2003 et 2007 (utilisation de formules sur feuille).

**nibledispo** · 04/10/2013, 02h06

Bonjour,

Sur mon ordinateur (excel 2013 64 bits), avec l'exemple qu tu donnes, copie1 met 19 s contre 28 pour copie2

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
 
Sub copie1()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = Time
    Application.ScreenUpdating = False
 
    With Sheets("feuil1")
       For i = 1 To 10000
           .Rows("7:20").Copy Rows("26")
       Next i
    End With
 
Range("A2").Select
    ActiveCell.FormulaR1C1 = Time
    Range("A3").Select
End Sub

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
 
Sub copie2()
Range("A1").Select
    ActiveCell.FormulaR1C1 = Time
    Application.ScreenUpdating = False
 
    With Sheets("feuil1")
       For i = 1 To 10000
          .Rows("7:20").Copy
          .Rows("26:26").Select
          ActiveSheet.Paste
       Next i
    End With
 
    Range("A2").Select
    ActiveCell.FormulaR1C1 = Time
    Range("A3").Select
End Sub

Cordialement,

**Marc-L** · 04/10/2013, 09h34

Salut Paul,

et c'est quoi comme processeur & fréquence ?

Test sur un i5-3210M 2.5Ghz en Windows 7 64 bits sur Excel 2003 : 7 secondes pour Copie1 comme pour Copie2 …

**nibledispo** · 04/10/2013, 11h10

Bonjour Marc,

Je te livre ce qui est indiqué dans "panneau de configuration" menu "Systeme".

Le processeur est Intel(R) Core(TM) 15-3750k CPU 3.40 GHz 3.80 GHz
mémoire 16 Go.

Je suis avec Windows 7 64 bits et office 2013 64 bits

Indice générale de performance Windows 7.6.

Il faut croire que notre ami a raison: Les versions postérieures à office 2003 souffrent d'un problème de lenteur manifeste.

Cordialement.

Lenteur VBA Excel 2010

Macros et VBA Excel

Discussions similaires

Partager

Partager