Améliorer un code lent

Version imprimable

Bonjour !

Je travaille sur mon premier projet VBA Excel depuis bientôt un an et demi, étant novice, votre aide m'a permis de résoudre tous mes problèmes et je me retrouve aujourd'hui avec un outils tout simplement génial ! Un grand merci donc ! :D
Il est tellement complet, qu'Excel rame à chaque interaction ou presque. Je suis donc en quête de conseils pour améliorer tout ça...

J'ai isolé par déduction une fonction, j'aimerais votre avis pour l'améliorer :

Objectif : Les utilisateurs du programme doivent pouvoir modifier des cellules contenant des formules sans pour autant les effacer.
Solution : J'ai mis mes formules dans une autre cellule et utilisé ce code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 If Target.Count = 1 And Target.Address = "$C$5" Then Application.EnableEvents = False Target.Offset(1, 0).Value = Target.Offset(1, 2).Value Target.Offset(2, 0).Value = Target.Offset(2, 1).Value Target.Offset(3, 0).Value = Target.Offset(3, 1).Value Target.Offset(4, 0).Value = Target.Offset(4, 1).Value Target.Offset(6, 0).Value = Target.Offset(6, 1).Value Target.Offset(7, 0).Value = Target.Offset(7, 1).Value Target.Offset(8, 0).Value = Target.Offset(8, 1).Value Target.Offset(9, 0).Value = Target.Offset(9, 1).Value Target.Offset(10, 0).Value = Target.Offset(10, 1).Value Target.Offset(13, 0).Value = Target.Offset(13, 1).Value Target.Offset(14, 0).Value = Target.Offset(14, 1).Value Target.Offset(15, 0).Value = Target.Offset(15, 1).Value Target.Offset(16, 0).Value = Target.Offset(16, 1).Value Application.EnableEvents = True End If
Tout fonctionne bien, mais j'ai une vingtaine de bloc comme celui ci parmi les 1000 lignes de codes VBA et les calculs des formules Excel --> ça rame.

J'utilise déjà :

Code:

Application.ScreenUpdating = False

qui est très efficace, puis j'ai essayé d'utiliser :

Code:

Application.Calculation = xlCalculationManual

le temps de calcul est réduit à 0, mais la fonction ne s'exécute plus...

Je sèche, et recherche tout bon conseil.

Merci d'avance et bonne journée !

Tu peux déjà réduire ton code comme ça.

Il faut aussi que tu retire les .select

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Dim i As integer
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$C$5" Then
Target.Offset(1, 0).Value = Target.Offset(1, 2).Value
End If
For i =2 to 14 
If Target.Count = 1 And Target.Address = "$C$5" Then
Target.Offset(i, 0).Value = Target.Offset(i, 1).Value
End If
Next i
Application.EnableEvents = True
End If

08/09/2014, 12h19
atk_49

Bonjour had4789 et merci pour ta réponse,

J'ai testé mais malgré ta simplification, la vitesse d’exécution n'est pas meilleure.

Que voulais-tu dire par les .select ?
08/09/2014, 12h49
had4789

Comment indique tu as a ta macro sur quel feuille elle doit travailler?
08/09/2014, 13h55
atk_49

J'ai mis mon code dans le projet VBA de ma feuille avec au départ

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
08/09/2014, 15h03
had4789

Du coup dès que tu change la valeur d'une de tes cellules tu relance ton programme de A à Z?

Tu ne peux pas couper ton code?

Tu n'utilise que le code que tu nous a donné?
08/09/2014, 15h43
bboy-eazy

PS: petite question au passage en lisant la discution, pourquoi la macro marche alors que la macro s'execute en fonction de l'evènement Worksheet_Change or dans le code il y a ceci:

Code:

Application.EnableEvents = False

sinon niveau lenteur, il faudra réussir à recalculé la formule de la celllule cible. Je m'explique, si la cellule A1 est changée, alors recalculer uniquement la formule pour la cellule A1 et non pas toutes les autres cellules si il n'y a pas de changement.

Une petite question aussi au passage: le fichier est partagé? sur réseau?
08/09/2014, 16h38
atk_49

@had4789
Oui c'est ça, lorsque je modifie une cellule, le code est entièrement ré-exécuté.

Pour "couper" le code, j'utilise :

Code:

If Not Application.Intersect(Target, [C47]) Is Nothing Then

Ça m'a permis d'éviter un recalcule total à chaque fois, et de n’exécuter que le nécessaire.

En cherchant sur le web j'ai trouvé la fonction :

Code:

Application.Calculation = xlCalculationManual

Mais ça bloque l’exécution des fonctions.

@bboy-eazy
Je ne peux pas te répondre, mais je viens d'essayer d'enlever les Application.EnableEvents et ça multiplie par 20 le temps d'éxécution

Pour ton idée, j'ai trouvé la solution indiquée juste au dessus. (If Not Application.Intersect....) Ça fonctionne mais le code reste lent.

Oui le fichier est en réseau, disponible en lecture seule. Il pèse 3247 Ko.

Bonjour,

Ce code s'exécute très vite chez moi :

Code:

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
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim I As Integer
 
    With Target
 
        If .Count = 1 And .Address = "$C$5" Then
 
            Application.EnableEvents = False
 
            .Offset(1, 0).Value = .Offset(1, 2).Value
 
            For I = 2 To 16
 
                .Offset(I, 0).Value = .Offset(I, 1).Value
 
            Next I
 
            Application.EnableEvents = True
 
        End If
 
    End With
 
End Sub

Hervé.

08/09/2014, 16h53
atk_49

Bonjour Theze,

En effet, mis sur une feuille à part, l’exécution du code est instantanée.

Mais je dois avoir une trentaine de bloc comme celui ci qui modifie une centaine de cellules. (Mais pas en simultanée grâce à If Not Application.Intersect...)

Pour info ce programme Excel "gère" environ 5000 lignes de données bruts (principalement du texte), répartie sur 11 colonnes et sur 6 feuilles.
Il a pour but d'aider une équipe de technico-commerciaux à faire des choix technique permettant une réponse rapide et sûre à mes clients.
Il utilise 200 Mo de RAM en moyenne.

L’exécution la plus longue dure +/- 6 secondes, dois-je me considérer heureux de ce résultat ? (Je n'ai pas de moyen de comparaison)

Re,

Si le code s'exécute sur plusieurs milliers de lignes, fige la mise à jour de l'écran et arrête le calcul, ça peut faire gagner quelques secondes :
Code:

1 2 3 4 5 6 7 8 9 10 Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'code... Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic
Hervé.

08/09/2014, 17h19
atk_49

Merci Theze, je me sert déjà de ScreenUpdating .

La fonction Application.Calculation = xlCalculationManual empêche certains bouts de codes de bien fonctionner.
D'ailleurs j'ai du mal à bien comprendre cette fonction, je pensais qu'elle n'agissait que sur les formules Excel de base et pas sur le VBA, mais en ajoutant cette ligne à mon code, les fonctions target.offset (entres autres) ne fonctionnent pas.
09/09/2014, 06h59
Theze

Bonjour,

Citation:

je pensais qu'elle n'agissait que sur les formules Excel de base et pas sur le VBA, mais en ajoutant cette ligne à mon code, les fonctions target.offset (entres autres) ne fonctionnent pas.

Le mode de calcul n'a aucune incidence sur le code VBA, si la récup de valeurs par offset ne fonctionne pas correctement, je pense que ton classeur a un problème ! Montre ton code en entier.

Hervé.
09/09/2014, 08h52
atk_49

Je pense avoir compris pourquoi le blocage du recalcule provoque des erreurs.
Le code Application.Calculation = xlCalculationManual bloque le recalcule automatique de tout le classeur, hors le fonctionnement de mon programme repose entièrement sur des choix à faire dans des listes déroulantes dont le contenu change en fonction de la liste du dessus. (En cascade)
Le recalcule étant bloqué, les listes ne se mettent pas à jour correctement et le programme indique des erreurs.

Est-ce possible ?
Si oui, peut-on bloquer le recalcule sur des zones/feuilles précises ? (Mes bases de données par exemple)

Encore merci et bonne journée.