Discussion :

[NEC14]

Bonjour à tous,

Avec la précieuse aide de Daniel, j'ai pu mettre en place ce code. Il marche très bien, mais à chaque fois que je saisi quelque chose dans la feuille de calcul, je mets 10 à 15 secondes avant de pouvoir passer à un autre cellule.
Je ne dis pas que la macro en est la cause, je cherche juste à comprendre pourquoi cela se produit.

Merci pour vos suggestions
Cordialement
René

Dans chacune des 8 feuilles du classeur

Code :

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

1
2
3
4
 
Private Sub Worksheet_Calculate()
    Couleurs
End Sub

Dans un module

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 Couleurs()
For Each c In Range([A4], Cells(Rows.Count, 1).End(xlUp))
        For i = 8 To 64 Step 4
            If Cells(c.Row, i + 2) = "" Then
                Cells(c.Row, i).Interior.ColorIndex = xlNone
            Else
                If IsDate(Cells(c.Row, i + 2)) And Cells(c.Row, i + 2) > [A1] Then
                    Cells(c.Row, i).Interior.ColorIndex = 4 'vert
                End If
                If Cells(c.Row, i + i) < [A1] Then
                    If Cells(c.Row, i + 2) - [A1] <= 15 Then
                        Cells(c.Row, i).Interior.ColorIndex = 45 'orange
                    End If
                End If
                If [A1] >= Cells(c.Row, i + 2) Then
                    Cells(c.Row, i).Interior.ColorIndex = 3 'rouge
                End If
            End If
        Next i
    Next c
End Sub

[NEC14]

Je viens de comprendre ce qui se passe. A chaque changement de cellule, le tableau entier est recalculé. Je ne rencontre plus se problème si dans les options d'Excel, j'enlève le calcul automatique et le remplace sur ordre.

Ce n'est pas l'idéal car il me faut appuyer sur la touche F9 pour que les calculs soient opérants.

Je ne sais pas quoi faire

[Guiiand]

Bonjour,

Je ne connais pas le détail de ton fichier, mais si tu n'as pas de formule entre les cellule, tu peux peut etre utiliser Intersect pour ne mettre en fomre que la cellule que tu viens de modifier.

[NEC14]

En fait la feuille de calcul est pleine de formules, une toutes les 5 colonnes sur 69 colonnes et toutes les lignes, environ 150 lignes.

Est-il possible de faire en sorte que le calcul ne se fasse que sur si la cellule est modifiée ?

[issoram]

Bonjour,

Tout d'abord il faut que tu détermines à quel moment tu veux que la sub Couleurs se lance i.e. quel événement doit la déclencher.

Dans ton cas c'est après le recalcul de la feuille qu'elle est lancée.
Est-ce de bien ce que tu souhaites? Si oui afin d'éviter les recalculs intempestifs et donc le(s) lancement(s) de "Couleurs", il faut passer en mode "calcul manuel" via le code et lancer le recalcul de la feuille au moment opportun.
Ce qui donnerait quelque part dans ton code:

Code :

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

1
2
3
4
5
6
7
Application.Calculation=xlCalculationManual
....
....
TaFeuille.Calculate  'déclenche l'événement Calculate et donc lance Couleurs
....
....
Application.Calculation = xlCalculationAutomatic   'repasse en calcul auto si besoin

Cordialement.

[Guiiand]

Re,

Tes lignes interagissent-elles les unes avec les autres ?

Si oui, je ne vois pas trop quoi faire.

Si non, tu peux modifier ta macro couleur pour qu'elle n'agisse que sur la ligne qui viens d'être modifier via target.row au lieu de toute ta plage, et pourquoi pas une ligne total ou je ne sais quoi.

[NEC14]

Issoram,
Je n'ai pas tout compris (ça viens de mon petit cerveau) mais je vais essayer de trouver.

Merci beaucoup pour cette piste

@ Guiiand
Non elles sont indépendantes. Mais il y a 8 feuilles dans le classeur et chaque fois tout se recalcule.

[issoram]

Désolé si je n'ai pas été clair. Pour pouvoir t'aider il faudrait que tu répondes à la question:

A quel moment tu veux que la sub Couleurs se lance? => quel événement doit la déclencher?

Bon courage.

[NEC14]

Je souhaiterais que seule la cellule modifiée soit recalculée, et cela au moment de la validation de la modification. Si les autres cellules n'ont pas été touchée alors inutile de les recalculer.

Je ne sais pas si je suis très clair

La mauvaise compréhension vient de moi, j'ai un peu de mal en ce moment. A force de lecture je vois où tu veux en venir, mais je ne sais pas comment le faire. J'ai essayé plusieurs choses, soit qu'elles ne fonctionnent pas, soit la macro tourne en rond et me bloque l'application.

Merci pour ta patience

[issoram]

Non ce n'est pas très clair effectivement . Et tu ne réponds toujours pas à la question...

Bon pour faire simple, tu as une macro qui modifie les couleurs de certaines cellules en fonction de leur valeur. Cette macro est effective sur plusieurs feuilles.

Quand souhaites-tu que cette modification de couleur intervienne?
De ce que je comprends ce serait lorsque l'utilisateur modifie une valeur de cellule dans une de ces feuilles. Si c'est bien ça il faut que tu regardes du côté de l'événement Change pour les feuilles concernées.

Code :

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

1
2
3
4
Sub Worksheet_Change(ByVal Target as Range)
'Cet événement se produit lorsque les cellules de la feuille de calcul sont modifiées par l'utilisateur ou par un lien externe.
...
End sub

Avant d'aller plus loin confirmes-tu ce qui précèdes?

[NEC14]

En fait ce que j'aurai aimé faire c'est que la macro ne s'applique qu'à la seule la cellule modifiée de la feuille active. Cette macro doit être active à chaque modification.

Dans une feuille j'ai environ 10350 formules, il est évident que si la feuille se recalcule entièrement cela prend du temps, c'est pour cette raison que le recalcule ne doit se faire que pour la seule cellule modifiée de la feuille active.

Le recalcule se fait lorsque je change de cellule.

J'ai essayé de faire ça, mais sans succès.
Au départ je mets l'option Calcul d'Excel sur ordre. Ensuite, je reviens sur la dernière cellule modifiée et je la recalcule, mais malheureusement cela ne fonctionne pas.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static selection_precedente As String
    If selection_precedente <> "" Then
        'recalcul de la sélection précédente :
        Range(selection_precedente).Calculate
    End If
Couleurs
    'Enregistrement de l'adresse de la sélection actuelle :
    selection_precedente = Target.Address
End Sub

[NEC14]

Re,

J'ai trouvé une solution, voici mon code et cela fonctionne plutôt bien.

Donc, dans les options d'Excel j'ai mis le calcul sur ordre. Ensuite, j'ai fait ça et le calcul ne se fait que sur les cellules modifiées.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MaPlage As Range
On Error Resume Next
Set MaPlage = Target.Dependents
Err.Clear
If Not MaPlage Is Nothing Then
MaPlage.Calculate
End If
Couleurs
End Sub

Merci à ceux qui ont contribué à ce post.
René

[NEC14]

Eh bien en fait ça ne fonctionne pas aussi bien que je le pensais. Pour valider le calcul je suis obligé de re-cliquer sur la dernière cellule modifiée.

Bon, et bien je repars sur mes recherches.

[NEC14]

Bonjour,

J'ai trouvé ces explications, elles sont très intéressantes, mais je n'arrive pas à les concrétiser dans une macro. Plus je remplie mon tableau plus la durée de recalcul est longue. J'ai essayé en mettant le recalcul sur ordre et en fin de saisie appuyer sur F9, mais le recalcul dure pas mal de temps.

Calculation (xlCalculationAutomatic, xlCalculationManual, xlCalculationSemiautomatic):
Définit le mode de recalcul d'Excel. Il s'agit d'une propriété très importante pour l'optimisation du temps d'exécution. En effet, pour les feuilles contenant des formules de calcul, le mode de calcul automatique peut être très coûteux en temps, en effet Excel recalcule intégralement une feuille de calcul à chaque modification ou suppression. C'est pourquoi, en général on bloque le mode de calcul au départ en faisant :

Code :

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

1
2
 
Application.Calculation = xlCalculateManual

Puis on exécute les calculs lorsqu'on en a besoin avec la méthode Calculate

Calculate : Permet de forcer le calcul. La syntaxe Application.Calculate est peu utilisée. On l'utilise
principalement sous la forme MaFeuille.Calculate. Sachez toutefois que l'on peut restreindre le calcul à une plage à des fins de performance. Exemple :

Code :

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

1
2
 
Worksheets(1).Rows(2:3).Calculate 'ne fait les calculs que sur les lignes 2 et 3.

Dans mon cas, je souhaiterai que le Calculate ne se fasse que sur la dernière cellule modifiée au moment de la validation ou changement de cellule. Peut-être n'est-ce pas possible.

[NEC14]

Bon tant pis, je n'ai pas réussi à régler mon problème.
Merci pour les suggestions apportées.