Discussion :

[Ludivine_]

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 ... ]
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.

[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?

[Menhir]

... é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.

[Ludivine_]

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.

[Ludivine_]

Merci en tout cas pour vos premiers retours

[Menhir]

- 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/

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.

[Ludivine_]

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

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

Code :

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

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 :

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

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 :

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
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