Exécution macros très lente

Le 23/06/2018 à 15:46

Bonjour,

je remarque que des macros simples prennent un temps anormalement long à s’exécuter sur mon pc (processeur intel corei3, 4G de mémo vive).

De manière générale, mon fichier est très lent, alors que les codes sont simples et compacts.

Je connais mal les bonnes pratiques en terme d'optimisation d'espace, j'ai trouvé des posts mais difficiles à comprendre.

J'ai donc deux questions:

1. le code ci-dessous prend presque une minute à s'éxécuter: il y a 4 ComboBox , 15 TextBox et 19 Label: est-ce aberrant ou cohérent?

2. Avez-vous une ref. d'article ou de post qui me permettrait de comprendre et de mettre en application des bonnes pratiques de libération d'espace.

Merci d'avance!

Pierre

Private Sub CommandButton1_Click()

'-----------------------------------------------------------------------

'je vérifie que les controles sont complétés

Dim ctl as object

For Each ctl In Me.Controls

If TypeOf ctl Is MSForms.TextBox Then

If ctl.Value = "" Then

MsgBox ("Veuillez compléter " & ctl.Name)

Exit Sub

Else

End If

ElseIf TypeOf ctl Is MSForms.ComboBox Then

If ctl.ListIndex = -1 Then

MsgBox ("Veuillez compléter " & ctl.Name)

Exit Sub

Else

End If

Else

End If

Next ctl

'-----------------------------------------------------------------------

'puis je transfère les valeurs des controles dans BDD PRIX LAIT

Dim i As Integer

Sheets("BDD PRIX LAIT").Activate

i = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Row

For Each ctl In Me.Controls

If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then

Sheets("BDD PRIX LAIT").Cells(i, Range(ctl.Name).Column).Value = ctl.Value

Else

End If

Next ctl

MsgBox ("Saisie validée")

end sub

Le 23/06/2018 à 16:17

Bonjour,

Une autre façon d'écrire mais je ne pense pas que ça va améliorer la vitesse car il n'y a pas de raison que ça mette du temps. Par contre, est ce que ton classeur comporte de nombreuses formules ? Si c'est le cas, un blocage du calcul avant l'exécution du code serait peut être une solution :

Private Sub CommandButton1_Click()

    Dim ctl As Control
    Dim i As Integer

    'je vérifie que les controles sont complétés et transfère les valeurs des controles dans BDD PRIX LAIT
    For Each ctl In Me.Controls

        i = Sheets("BDD PRIX LAIT").Range("A1").End(xlDown).Offset(1, 0).Row

        Select Case TypeName(ctl)

            Case "TextBox"
                If ctl.Text = "" Then
                    MsgBox "Veuillez compléter " & ctl.Name: Exit Sub
                Else
                    Sheets("BDD PRIX LAIT").Cells(i, Range(ctl.Name).Column).Value = ctl.Text
                End If

            Case "ComboBox"
                If ctl.ListIndex = -1 Then
                    MsgBox "Veuillez compléter " & ctl.Name: Exit Sub
                Else
                    Sheets("BDD PRIX LAIT").Cells(i, Range(ctl.Name).Column).Value = ctl.Text
                End If

        End Select

    Next ctl

    MsgBox ("Saisie validée")

End Sub
Le 23/06/2018 à 18:31

Merci Theze,

pas de changement, je continue de chercher!

Bonne soirée.

Le 23/06/2018 à 20:56

Bonsoir,

Ce type de macro ne saurait prendre plus d'une seconde à s'exécuter ! Certainement beaucoup moins d'ailleurs mais en dessous de la seconde on aura du mal à percevoir les différences sans mesurer...

Alors une minute est en tout état de cause un temps anormal qui ne peut s'expliquer que par un problème affectant le fichier...

Cordialement.

Le 23/06/2018 à 22:19

Bonsoir pierrebastres, le forum,

j'avais lu un message où le demandeur disait qu'ça tournait plus vite sur son vieux PC avec Excel 2007 que sur son nouveau PC tout neuf avec Excel 2013 ; alors p't'être que ton problème est simplement « la rançon du progrès » ?

ps : c'est pas toi que j'raille, mais les nouvelles versions Microsoft, le soi-disant progrès, et l'obsolescence programmée !

dhany

Le 23/06/2018 à 23:43

Bonjour,

4 Go de ram ce n'est pas beaucoup de nos jours.

Tu te retrouves peut-être en mémoire virtuelle (extension de la mémoire sur le disque dur) et là tout devient lent, très lent.

Regarde dans le Gestionnaire de tâches onglet Performance ou dans le Moniteur de ressources, où tu en es en RAM dispo avec ton fichier d'ouvert.

Si tu arrives à pas grand chose essaie de rebooter, de fermer tout ce qui c'est lancé et qui ne t'est pas indispensable, et de retester ta macro.

Si tes colonnes d'écritures sont consécutives tu peux mettre les données des textbox dans un tableau que tu écriras en une fois au lieu de 15 écritures. Multiplier les écritures ralentit. Mais bon, 15 ce n'est pas la mer à boire non plus...

eric

Le 24/06/2018 à 00:56

Bonjour,

Effectivement, 4 go Ram ... c'est extrêmement peu.

Un renseignement qu'on a pas est le poids du fichier en question.

Un autre renseignement qu'on a pas ... y a-t-il des graphiques dans ce chiffrier ?

Les graphiques ne peuvent pas être "swapés" (mis en mémoire virtuelle); ils restent en mémoire vive. (source Microsoft).

Il y a aussi les autres applications qui tournent en arrière-plan.

J'ose ajouter le nettoyage du disque ... a-t-il été fait dernièrement ... menu contextuel (bouton de droite de la souris) sur le C:, Propriété, dans la nouvelle fenêtre ... bouton "Nettoyage du disque".

Tout cela avec 4 go Ram. Le système doit être en mode constant de lecture/écriture pour utiliser la mémoire virtuelle.

Pour en arriver à dire que le temps d'exécution "pourrait" être normal.

ric

Le 24/06/2018 à 04:02

Bonjour,

C'est un secret d'état ou on peut avoir le fichier ?

A+

Le 24/06/2018 à 10:57

Bonjour Ric, Eriic , Galopin01 et le Forum,

Merci pr vos messages

j'ai supprimé toutes les lignes et colonnes semblant vierges, dans chaque onglet du classeur. La taille est alors passée de 20 à 2Mo, et les opérations st plus rapides.

Effectivement, il y a un certain nbre de graphiques, comme vous le verrez ds le fichier cj.

La RAM est utilisée à 77%!

Pas de secret d'état Galopin, voici une copie du fichier.

Bonne journée.

Pierre

38gestion-collecte-production-essai.zip (791.04 Ko)
Le 24/06/2018 à 12:36

Bonjour,

Mes connaissances en VBA ne sont pas suffisantes pour faire une bonne analyse de ce chiffrier.

Mais, je veux porter à l'attention des gens compétents les noms des "champs nommés". Il y a des doublons dont je ne saurais dire si tout est en ordre.

ric

Le 24/06/2018 à 13:13

Bonjour,

Ben c'est sur que dès qu'on enlève tout ce qui sert à rien, ça améliore pas mal les choses...

Il reste encore quelques naïvetés mébon dans l'ensemble c'est pas mal.

Reste qu'il y a quand même pas mal de formules complexes SOMMEPROD, SOMME.SI.ENS...

Formules que je n'ai pas cherché à évaluer !

Moi j'essaierai de mettre :

Application.Calculation = xlManuel

au chargement du UserForm

et remettre

Application.Calculation = xlAutomatic

à la fermeture du UserForm

Bien sur cela suppose qu'on est dans un UserForm qui n'utilise pas des valeurs calculées en retour...

Sinon dans cette situation, je ne connais que 2 manières d'améliorer le temps de traitement

1 - Diviser pour mieux régner (virer tout ce qui est tableau de bord (statistiques, graphiques...)

2 - Virer tous les SOMMEPROD, SOMME.SI.ENS et assimilés (et les remplacer par des fonctions de Base de données...)

A+

Le 24/06/2018 à 15:50

Je dois dire, sans aucunement prendre le contrepied de Galopin dont les conseils sont toujours pertinents en la matière, mais en ressenti personnel que je considère un tel fichier comme inutilisable.

Ça rame dès qu'on bouge le curseur ! Dans de telles conditions je ne considère pas possible de travailler correctement. Que le calcul soit auto ou manuel, le passage à des feuilles déclenchant des macros prend plusieurs secondes, inacceptable !

Un examen panoramique rapide : trop de formules, trop de noms, trop de modules (13 pour 26 procédures, en laissant Calendar de côté), et je laisse de côté l'usage d'un bon nombre d'API, qui nécessiteraient un examen approfondi... mais bon, je commencerais par éclater le tout en plusieurs classeurs selon fonctionalité, pilotés à partir d'un classeur principal...

Bref ! Je pense qu'il tout refaire en fait ! en veillant dès le départ à conserver en permanence une fluidité fonctionnelle d'utilisation... Mais n'étant pas candidat pour entreprendre ce type de travail, je me retire...

Cordialement.

Le 24/06/2018 à 15:58

Ok, merci pour vos conseils précieux!

si je comprends bien, c'est un problème de conception donc, et mieux vaudrait tout éclater en différents classeurs.

Galopin, quelles sont les naïveries évoquées stp?

Cordialement.

Pierre

Le 24/06/2018 à 19:01

Euh... Le mot est peut-être mal approprié !

Ce sont des détails sans incidence sur le temps de traitement. Mais l'accumulation de ces détails fait qu'on n'a pas trop envie de se pencher sur le bébé :

De plus comme je ne peux pas prendre en charge ce dossier je me borne à donner un avis. Je suppose que d'autres donneront le leur... A toi d'en faire une synthèse et de tirer tes conclusions???

Ferrand à déjà donné pas mal d'indications...

Trop de Noms peu ou pas évocateurs, et trop longs (y compris les noms des feuilles) : Un nom ne devrait pas dépasser 7 ou 8 digits.

Pour les variables idem.

Trop de modules 1 seul suffirait. Trop de lignes vides dans ces modules

Pas de déclaration Explicit : Quantité de variables non déclarées...

Déclaration imprécises (non typées : dans cette déclaration :

Dim h1, b1, h2, b2, h3, b3 As Integer 'seul b3 est integer !

Absence de blocs With.

Encore trop de Activate inutile.

Absence d'utilisation des Array.

Ça serait pour moi, je commencerai par diviser le classeur en (au moins) 2 parties.

  • La partie données brutes avec juste des sorties CSV pour extraction...
  • La partie analyse ou tu récupères ces extractions pour en tirer ce que tu veux. Pas de liens entre les deux.
Après pour les calculs, moi je préconise les fonctions de base de donnée. Couplées à un peu de VBA, ç'est magique.

Il reste que il faut savoir s'en servir. Quand on a compris on ne peut plus s'en passer...

Je sais que suis un des derniers dinosaures à utiliser ça. De nombreux intervenant te diront le contraire : J'en vois même qui rigolent !

Selon mon avis, si on avait attendu les SOMMEPROD et autres fonctions à la mode on n'aurait pas encore marché sur la Lune et on roulerait toujours en vélo...

Cette parenthèse refermée, je vais faire comme mon ami. : Me retirer car tout cela dépasse la vocation du forum et je suis vraiment surbooké en ce moment.

En conclusion, je dirais c'est dommage car je crois déceler chez le rédacteur de réelle qualités... Mébon pour des projets de cette envergure, on ne peut pas se contenter de "tant bien que mal" : Il faut absolument utiliser des méthodes éprouvées.

A+

Le 24/06/2018 à 19:27

Merci pour ttes ces indications Galopin,

Bonne continuation, et je vais me pencher sur les formules matricielles!

Pierre

Rechercher des sujets similaires à "execution macros tres lente"