Optimisation Calculs Excel - VBA

Version imprimable

12/11/2018, 17h44
Ludivine_

Optimisation Calculs Excel - VBA

Bonjour à tous,

Je suis nouvelle sur le forum, et j'ai besoin de vos lumières. J'ai des bases sur VBA mais surtout grâce à l'enregistreur de macro et à Google :)

J'ai construit une macro qui doit faire plusieurs choses mais principalement générer des écritures comptables = 6 lignes par ID pour environ 40 000 ID soit beaucoup de lignes ...

Pour chaque ligne plusieurs colonnes calculées : compte CG, un montant, établissement, dept, emploi, typ contrat, etc.

Donc j'ai fait ma boucle : je prends une cellule = ID, et mes formules pointent sur cet ID et se mettent à jour, et je copie/colle ce tableau en valeur sur une autre page. et ensuite je passe à l'ID suivant.

Sauf que la macro plantait - et oui beaucoup trop longue à cause des formules ... J'ai donc scindé en plusieurs fichiers :

1- Un fichier Excel en .xlsm où il y a toutes les macros et les inputs à mettre à jour manuellement
2- Un premier fichier excel en .xlsb pour une première génération avec une dizaine de colonnes [Cette partie ne prend que 45 min, ce qui est acceptable pour une génération de plus de 250 000 lignes]
3- Un deuxième fichier excel en .xlsb où je prends les données du premier fichier et je les copie colle en valeur + des colonnes avec des formules .... [Mais là, l'actualisation des formules (INDEX/EQUIV ou DATE) met tellement longtemps ... :calim2: ]
4- Un troisième fichier excel en .xlsb ou je prends les données du deuxième fichier et je les copie colle en valeur + 3 colonnes de formules. [Cette partie est OK aussi]

Donc je voulais savoir si vous avez des conseils pour optimiser ce temps de calcul pour le fichier 2 (qui n'est pas pris en charge par la macro mais bien par l'action "calculer la feuille", je l'avais insérer dans la macro mais c'était beaucoup trop long).

Merci pour vos idées :)

Bonne fin de journée,

Ludivine.
12/11/2018, 21h30
Arkham46

Bonjour,

Il y a beaucoup de pistes d'optimisation, mais on ne peut pas tout lister.

Le temps de 45min me semble long quand même. Si le code vient de l'enregistreur de macro, ce n'est pas étonnant. Il faut le nettoyer, éviter les .activate, désactiver tout ce qui ne sert à rien pendant l'exécution (enableevents, screenupdating, calcul...). Recherchez sur le forum, il y a beaucoup de discussions sur ce sujet.

Pour la deuxième partie avec les formules qui sont longues, on ne peut pas non plus dire grand chose sans les voir.
Comment c'est fait ? Copier-coller dans un classeur vierge puis ajout des formules par vba?
13/11/2018, 09h14
Menhir

Citation:

Envoyé par Arkham46

... éviter les .activate, ...

... et surtout les Select/Selection que l'enregistreur de macro sème si généreusement dans le code et qui, la plupart du temps, sont non seulement inutiles mais néfastes aussi bien à l'exécution qu'à la compréhension du code.
13/11/2018, 15h20
Ludivine_

3 pièce(s) jointe(s)

Je vous mets mes docs :
- le fichier avec la macro, le fichier de génération et le fichier qui me pose problème avec les formules

J'ai essayé d'enlever les selects mais j'ai eu des beugs donc ceux qui beuguaient j'ai remis les select, sinon j'ai laissé en optimisé.

Les fichiers sont en xlsx mais chez moi ils sont en xlsb / sinon je n'aurais pas pu vous les envoyés.

Et j'ai supprimé toutes mes données confidentielles. Donc il n'y a plus que 6 ID.

Merci pour vos conseils.

Je vais également continuer à regarder sur les tutos.

Bon après-midi à tous.
13/11/2018, 15h24
Ludivine_

Merci en tout cas pour vos premiers retours :)
13/11/2018, 16h22
Menhir

Citation:

Envoyé par Ludivine_

- le fichier avec la macro, le fichier de génération et le fichier qui me pose problème avec les formules

Pour info, beaucoup de participants à ce forum n'ouvrent pas les fichiers joints.
https://www.developpez.net/forums/d8...s-discussions/

Citation:

J'ai essayé d'enlever les selects mais j'ai eu des beugs donc ceux qui beuguaient j'ai remis les select, sinon j'ai laissé en optimisé.

Il ne faut pas seulement les enlever mais aussi remplacer le "Selection" par la référence qui était sélectionnée.

OK (j'avais refait tous mes fichiers pour la bonne cause tant pis ...

Donc ma macro me permet de mettre à jour mes inputs :

Code:

1
2
3
4
5
6
7
 'Nettoie les inputs précédents du fichier Génération : 
            Windows("Generation_schémas_cptbles_VP.xlsb").Activate
            Sheets("Table ID").Select
            Range("A:O").Select
            Range(Selection, Selection.End(xlDown)).Select
            Application.CutCopyMode = False
            Selection.ClearContents

Code:

1
2
3
4
5
6
7
'Met à jour les nouveaux depuis le fichier macro :        
            Windows("Macro_Génération Schémas comptables.xlsm").Activate
            Sheets("Table ID").Range("A1").CurrentRegion.Copy
 
            Windows("Generation_schémas_cptbles_VP.xlsb").Activate
            Sheets("Table ID").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

Et quand c'est fini, je fais ma boucle pour la génération de mes schémas comptables, je pense que ma macro est correcte là :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub Schémas_intermedaires()
 
'Générer les schémas comptables intermédiaires
 
    Windows("Generation_schémas_cptbles_VP.xlsx").Activate
    Application.Calculation = xlAutomatic
 
        'Déclaration des variables
            Dim nmatri As Long
            Dim DernLigne As Long
            DernLigne = Range("A" & Rows.Count).End(xlUp).Row
            Dim i As String
 
        'Génération des schémas comptables
            Sheets("ID").Select
            Range("M2").Select
 
            With Worksheets("ID")
                nmatri = .Cells(.Rows.Count, "M").End(xlUp).Row
            End With
 
            'Boucle d'exécution
                i = 2
                    While i <= nmatri
                        Sheets("ID").Select
                        ID = Range("M" & i).Value
 
                        Sheets("Calcul").Select
                        Range("E1") = ID
                        Range("A17:M22").Copy
 
                        Sheets("Génération").Select
                        DernLigne = Range("A" & Rows.Count).End(xlUp).Row + 1
                        Range("A" & DernLigne).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    i = i + 1
                    Wend
 
    '- Sauvegarder
        ActiveWorkbook.Save
 
End Sub

-------- Voilà pour les macros ---------

Et ensuite je copie colle les données générer dans mon fichier schémas comptables (donc env 275 000 lignes) où j'ai 6 colonnes de formules :
- INDEX/EQUIV : =INDEX(AGENT;EQUIV($K2;ID!$M:$M;0);7)
- Date du mois de traitement : qui fait référence à une date donc =une cellule dans une autre feuille

Et ce sont des 6 colonnes qui prennent du temps ...

Merci pour votre temps.

Ludivine

Sur les deux premiers code, il pourrait y avoir des choses à améliorer pour améliorer la lisibilité et la souplesse du code mais ce n'est pas ça qui te ferait gagner du temps.

Pour ce qui est de ta boucle.

Déjà, je suis très étonné qu'elle fonctionne avec une déclaration Dim i As String. 8O
Etant donné que, visiblement, "i" est un compteur numérique, je te suggère de le déclarer de type "Long".

Il est également préférable de passer par Worksheet et non Windows quand on veut activer un classeur.

Mais pour accélérer ta boucle, remplace ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Sheets("ID").Select Range("M2").Select With Worksheets("ID") nmatri = .Cells(.Rows.Count, "M").End(xlUp).Row End With 'Boucle d'exécution i = 2 While i <= nmatri Sheets("ID").Select ID = Range("M" & i).Value Sheets("Calcul").Select Range("E1") = ID Range("A17:M22").Copy Sheets("Génération").Select DernLigne = Range("A" & Rows.Count).End(xlUp).Row + 1 Range("A" & DernLigne).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 1 Wend
Par :
Code:

1 2 3 4 5 6 7 8 9 Sheets("ID").Select nmatri = Cells(Rows.Count, "M").End(xlUp).Row 'Boucle d'exécution For i = 2 To nmatri Sheets("Calcul").Range("E1").Value = Cells(i, "M").Value Range("A17:M22").Copy Sheets("Génération").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues Next i
Cela dit, j'ai optimisé ton code mais je ne comprends pas ce que tu cherches à faire dedans.
Tu copies toujours des cellules successives de la colonne M de ID dans la même cellule (E1 de Calcul) ?
Puis à chaque tour de boucle, tu copies une plage fixe A17:M22 de ID dans la première ligne vide de Génération.

La logique de tout ça m'échappe.

Citation:

Et ensuite je copie colle les données générer dans mon fichier schémas comptables (donc env 275 000 lignes) où j'ai 6 colonnes de formules :

Pourquoi ne pas demander à ta macro de le faire ?

14/11/2018, 15h04
Ludivine_

Bonjour,

Alors en fait ma macro génère ces 275 000 lignes qui correspondant à des schémas comptables.

Je regarderais ta proposition, merci :)

Mais une personne est venue et m'a dit regarde je l'ai mis sous access ... et ce que je faisais en 2/3 h, si pas de beugues .... en ca se fait en 15 secondes -_-

Du coup je tate un peu access et j'apprends ^^

Merci pour votre temps en tout cas !

Je vais clôturer la discussion.

Bonne continuation à tous :)