Optimiser (améliorer) code VBA

[rudymagny]

Bonjour à tous,
Voilà j'ai un code qui me permet de calculer des indicateurs.
Il marche très bien mais j'ai essayé de l'optimiser mais à chaque fois ça plante, je ne retrouve pas le même résultat.

http://cjoint.com/?dqrT2FaKpU

Dans le fichier, c'est le module TVAL_CE. (il y a aussi TVAL_ACC qui est dans le même style)

Dans l'USF , le bouton CE déclenche cette macro.

Pouvez vous m'orienter vers son optimisation? Je sais que ça peut se faire sur les boucles mais morche pas.

Merci d'avance

 

[skoobi]

Re : Optimiser (améliorer) code VBA

Bonsoir,

Je sais que ça peut se faire sur les boucles mais morche pas.

En tout cas moi je vois pas car les formules sont différentes:

                Cells(x1, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI"")))"
                Cells(x2, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
                Cells(x3, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")))"
                Cells(x4, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mec" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
                'Totaux pour MES
                Cells(x5, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI"")))"
                Cells(x6, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"
                Cells(x7, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")))"
                Cells(x8, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mes" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"

Par contre, si j'ai bien vu, la partie suivante ce répète 3 fois:

            For Each M In Array("Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre")
                i = i + 1
                ActiveWindow.ScrollRow = x1
                'Totaux pour MEC
                Cells(x1, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI"")))"
                Cells(x2, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
                Cells(x3, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")))"
                Cells(x4, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mec" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
                'Totaux pour MES
                Cells(x5, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI"")))"
                Cells(x6, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"
                Cells(x7, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")))"
                Cells(x8, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mes" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"
            Next M
            i = 2
            x1 = x1 + 15
            x2 = x2 + 15
            x3 = x3 + 15
            x4 = x4 + 15
            x5 = x5 + 15
            x6 = x6 + 15
            x7 = x7 + 15
            x8 = x8 + 15

Tu peux le mettre dans une autre macro (macro2 par exemple) que tu appelles:

    For Each G In Array("NO", "EST")
        For Each C In Array("CE1", "CE2")
            Call macro2
        Next C
    Next G

 

[rudymagny]

Re : Optimiser (améliorer) code VBA

Bonsoir skoobi,
merci pour ton idée, je vais la mettre en oeuvre dans mon code pour tester et je te tiens au courant

Merci encore

 

[rudymagny]

Re : Optimiser (améliorer) code VBA

j'ai crée une macro pour la partie qui se répète mais j'ai une erreur:

 type d'argument byref incompatible

appel de la macro:

x1 = 6
    x2 = 7
    x3 = 8
    x4 = 9
    x5 = 11
    x6 = 12
    x7 = 13
    x8 = 14

For Each G In Array("NO", "EST")
        For Each C In Array("CE1", "CE2")
            For Each M In Array("Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre")
                Call nbre(G, C, M, x1, x2, x3, x4, x5, x6, x7, x8)
            Next M
            i = 2
            x1 = x1 + 15
            x2 = x2 + 15
            x3 = x3 + 15
            x4 = x4 + 15
            x5 = x5 + 15
            x6 = x6 + 15
            x7 = x7 + 15
            x8 = x8 + 15
        Next C
    Next G

et la macro:

Sub nbre(G As Variant, C As Variant, M As Variant, x1 As Byte, x2 As Byte, x3 As Byte, x4 As Byte, x5 As Byte, x6 As Byte, x7 As Byte, x8 As Byte)
i = i + 1
    
With Sheets("IndicateursCE")
    ActiveWindow.ScrollRow = x1
    'Totaux pour MEC
    Cells(x1, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*((ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMec" & M & "<>""Panne TI"")))"
    Cells(x2, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMec" & M & "=7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
    Cells(x3, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")))"
    Cells(x4, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mec" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMec" & M & "<>7)*(ColGetMec" & M & "=""" & G & """)*(ColCEMec" & M & "=""" & C & """)*(ColTypeMec" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMec" & M & "<>""Panne TI"")*(ColE2Mec" & M & "=""ý"")))"
    'Totaux pour MES
    Cells(x5, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*((ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT""))*(ColTypeMes" & M & "<>""Panne TI"")))"
    Cells(x6, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý""))),""SO"",SUMPRODUCT((ColUMes" & M & "=7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"
    Cells(x7, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""o""))),""SO"",SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")))"
    Cells(x8, i).Formula = "=IF(ISERROR(SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColE2Mes" & M & "=""ý""))),""SO"", SUMPRODUCT((ColUMes" & M & "<>7)*(ColGetMes" & M & "=""" & G & """)*(ColCEMes" & M & "=""" & C & """)*(ColTypeMes" & M & "=""Ouvrage Neuf ou Refonte BT"")*(ColTypeMes" & M & "<>""Panne TI"")*(ColE2Mes" & M & "=""ý"")))"
    
End With
End Sub

Je vois pas?

 

[skoobi]

Re : Optimiser (améliorer) code VBA

Modifie comme ceci le début de la macro:

Sub nbre(ByVal G As Variant, ByVal C As Variant, ByVal M As Variant, ByVal x1 As Byte, ByVal x2 As Byte, ByVal x3 As Byte, ByVal x4 As Byte, ByVal x5 As Byte, ByVal x6 As Byte, ByVal x7 As Byte, ByVal x8 As Byte)

 

[rudymagny]

Re : Optimiser (améliorer) code VBA

Merci skoobi,

Le problème c'est qu'il ne m'insère rien dans mon tableau.

J'essaye de comprendre.

 

[skoobi]

Re : Optimiser (améliorer) code VBA

As-tu mis la procédure (macro) "nbre" dans le même module que la procédure "Indicateurs_CE" ?