Discussion :

[d0n32]

Bonjour,

Je poste dans la section "Macros et VBA", même si je ne suis pas sûr que mon problème viennent bien de l'exécution de macros.
Je dispose d'un fichier de données, qui pèse environ 1 Mo, et comporte autour de 10 000 lignes de données, nombre de lignes qui augmente avec le temps.

Il fonctionnait sans encombres jusqu'à présent, mais je constate depuis quelques semaines des lenteurs à l'écriture : les cellules se remplissent avec latence, l'affichage met quelques secondes à se mettre à jour.
Il y a bien des macros qui s'exécutent à la modification, type

Code :

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

Worksheet_Change(ByVal Target As Range)

ou

Code :

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

Worksheet_BeforeDoubleClick

, et qui traitent ce tas de données.
J'ai essayé d'alléger le fichier en archivant environ 8 000 lignes de données pour ne plus les traiter systématiquement. Sans effet.
J'ai aussi essayé d'optimiser les macros, sans résultat sur le lag.
Je n'ai pas changé de PC.
Je constate simplement que je peux forcer l'accélération de l'affichage d'une cellule modifiée en appuyant sur TABULATION après saisie. Cela décale la latence sur la nouvelle cellule que je modifie.

Une idée ? Faut-il repartir d'un fichier vierge pour reconstruire mon document et voir si cela corrige quelque chose ?

J'espère avoir été suffisamment clair

Bonne journée à tous

[MarcelG]

Salut,

Peut-être 2 pistes à travers l'objet Application

La gestion des évènements (propriété

EnableEvents

)
Le mode de calcul (propriété Calculation)

Pour cette dernière, il faut savoir que la méthode Calculate peut s'appliquer
- à un classeur
- à une feuille
- voire à un objet Range

Sur l'objet Application, consulter cet espace.

[d0n32]

Bonjour,

Merci pour ces conseils. Il s'agit de fonctionnalités que j'utilise déjà, je colle le code si ça peut aider à y voir plus clair :

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
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim c As Range
Dim v As Double
Dim f As String
Dim DernLigne As Long
DernLigne = Range("B1048576").End(xlUp).Row
  If Intersect(Target, Range("B11:B" & DernLigne)) Is Nothing Then Exit Sub
 
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
 
  For Each c In Target.Cells
    If c.Column = 2 Then
    f = c.NumberFormatLocal
    c.NumberFormatLocal = "Standard"
    v = Val(c.Formula)
    On Error Resume Next
    If Not IsDate(c.Text) Then If v >= 1011900 And v <= 31129999 And Int(v) = v Then c.Value = CDate(Format(v, "00\/00\/0000"))
    On Error GoTo 0
    If IsDate(c.Text) Then c.NumberFormatLocal = "jj/mm/aaaa" Else c.NumberFormatLocal = f
    End If
 
    If c.Column = 2 Then
        c.Offset(0, 5).FormulaR1C1 = "=R[-1]C-RC[-2]+RC[-1]"
        c.Offset(0, 6).FormulaR1C1 = "=round(R[-1]C+if(RC[-7]=""X"",1,0)*(-RC[-3]+RC[-2]),2)"
        c.Offset(0, 8).FormulaR1C1 = "=IF(MONTH(RC[-8]) = MONTH(TODAY()), IF(YEAR(RC[-8])=YEAR(TODAY()),IF(DAY(RC[-8])>=DAY(R[-1]C[-8]),DAY(RC[-8]),""""),""""),"""")"
        c.Offset(0, 9).FormulaR1C1 = "=IF(RC[-1] <>"""",RC[-4],"""")"
    End If
  Next c
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
 
End Sub

[MarcelG]

Salut,

Houla!

Les End(xlUp) et FormulaR1C1 ne devraient plus, à mon avis, avoir cours, du moins en 2021

Dès lors, pourquoi ne pas utiliser une table de données (tableau structuré).
La formulation s'implémenterait automatiquement sur tous les enregistrements.
Ce qui aurait pour avantage le développement d'un code VBA qui peut s'avérer très vite fastidieux.
D'autant que tu utilises une version récente d'Excel

D'autre part, ce genre de processus devrait intégrer un module standard non un procédure évènementiel.
Il serait généré, par exemple, par l'activation d'un bouton de commande

Pour tester, place un bouton d'arrêt en début de procédure.
Je parierais que celle-ci sera - trop - souvent sollicitée.

En conclusion, cet handicap de lenteur est lié, d'après moi, à un problème de conception du classeur.
C'est son architecture qui doit faire l'objet d'une réflexion préalable à tout développement VBA.

[d0n32]

Rebonjour Marcel,

Merci pour tes conseils.
Malheureusement, tu as affaire à un utilisateur non pas néophyte, mais disposant d'un savoir assez limité quant à l'utilisation d'Excel.

La procédure est naturellement appelée à chaque modification d'une cellule de la feuille, normal pour un Worksheet_Change.
J'ai instauré un garde-fou en créant une condition en début de procédure pour évincer tous les cas qui ne nécessiteraient pas d'aller plus loin dans la procédure.
Mais au-delà de cela, je ne pense pas disposer des connaissances requises à la bonne compréhension de ton message :

D'autre part, ce genre de processus devrait intégrer un module standard non un procédure évènementiel.
Il serait généré, par exemple, par l'activation d'un bouton de commande

Tu proposes de ne pas avoir recours à un Worksheet_Change, mais d'appeler la procédure associée à l'aide d'un bouton, histoire de ne pas la lancer à chaque modif, si j'ai bien compris ?

Dès lors, pourquoi ne pas utiliser une table de données (tableau structuré).

Pas compris J'ai bien un tableau structuré de données : colonnes A à G remplies manuellement, ligne par ligne, suivant les entrées nécessaires : une sorte d'inventaire que je viens compléter quotidiennement...
Cela n'est pas adapté ?

Les End(xlUp) et FormulaR1C1 ne devraient plus, à mon avis, avoir cours, du moins en 2021

OK, mais par quoi remplacer cela ?

Merci pour ton aide

[MarcelG]

Salut,

Pour placer un point d'arrêt, une illustration ici.

Ce que je veux signifier:
Je crains que la procédure soit appelée au moindre changement pour nombre de cellules.
Tu t'en rendras compte via ce point d'arrêt.

Il faudrait en effet intégrer une procédure à un module standard.
Un bouton de commande ActiveX serait affecté de cette procédure.
Une illustration dans cet espace.

Attention!
Un tableau structuré est un objet (ListObject) bien défini.
Voir ces 2 espaces fondamentaux
les tableaux structurés
les tableaux structurés et VBA
Je persiste à croire que cette utilisation est la plus performante pour la gestion des données sur Excel.
Et, notamment, en termes de formulations.

Les End(xlUp) et FormulaR1C1 ne devraient plus, à mon avis, avoir cours, du moins en 2021

Par quoi les remplacer?
Justement par des fonctionnalités propres aux tableaux structurés
End(xlUp) ==> ListObjects("montablo").Listrows.Count
Formules R1C1 (que j'abhorre pour ma part) ==> Formulation structurée directement dans le tableau.

Consulte, voire étudie, ces espaces documentaires afin d'adapter les bons reflexes.
Dans un 2ème temps, reviens pour toute question.

[d0n32]

Pour les points d'arrêt je sais faire, merci !
La procédure est bien lancée à chaque modification de cellule, comme son nom l'indique. Le gros du code n'est pas exécuté grâce à la première condition If, mais ça n'empêche qu'on a vient à tester ce If systématiquement, d'où la lourdeur peut-être.

Pour le reste, je regarde cette histoire de tableau structuré, je ne connaissais pas. Merci !

[d0n32]

Bonjour,

Me revoici après avoir relu les 2 tuto. Très instructifs, merci beaucoup, et ravi de connaître désormais le fonctionnement des tableaux structurés.
J'ai pu remettre au goût du jour mon classeur, et m'épargner de fastidieuses macros.

J'en ai conservé quelques unes qui restent nécessaires.
Je pense avoir pu isoler la cause de la lenteur du classeur, toujours présente après passage en tableaux structurés.

De nombreux onglets utilisent les données d'entrée du premier d'entre eux, et c'est le calcul automatique qui génère la latence. Le passage du calcul automatique en manuel règle le problème.
Est-il possible de ne passer que certains onglets en calcul manuel, et d'en laisser en calcul automatique ?
Sans passer par VBA, je n'ai pas l'impression...?

Merci et bonne journée

[tototiti2008]

Bonjour,

Non, le mode de calcul s'applique à tout le classeur
pire que ça, si le calcul est manuel, et qu'on ouvre un autre classeur, le classeur ouvert passe en manuel
si on enregistre le classeur ouvert, il sera donc enregistré en manuel
attention à ça si vous manipulez plusieurs classeurs, ça peut se répandre

[MarcelG]

Bonjour à vous, Bonjour au Forum,

Est-il possible de ne passer que certains onglets en calcul manuel, et d'en laisser en calcul automatique ?

Comme l'a indiqué mon prédécesseur, que je salue au passage, le mode de calcul manuel s'applique à tout un classeur.

Par contre, la méthode "Calculate" peut s'affecter à plusieurs étendues
- un classeur
- une feuille de calcul
- une cellule

Ce que je recommande:
- En début de traitement, passer en calcul manuel
- Gérer la méthode Calculate pendant le traitement des données
- En fin de traitement, repasser en mode de calcul automatique