Erreur de compilation - Procédure trop longue

Le 19/11/2018 à 22:37

bonsoir à tous,

j'avance dans mon projet mais je rencontre une nouvelle difficultés. J'ai une "erreur de compilation-procédure trop longue". J'en ai compris que j'avais mis trop d'instructions dans la macro.

je pense que je n'ai pas écrit la macro de façon optimale mais je n'ai pas compris l'utilisation des selects case (peut être sans intérêt dans ce cas ?) et cela doit alourdir la procédure.

Je voulais savoir si je devais segmenter le code en plusieurs petits morceaux et les appeler au fur et à mesure ?

Si oui quelle est la procédure à suivre ?

Merci d'avance pour vos retours.

9cpp-vtravail-draft.xlsm (0.98 Mo)
Le 19/11/2018 à 22:48

Bonjour,

Quelle procédure donne le message ? Avenants_OS ??

Ça pourrait-être optimisé, mais rien ne semble trop long.

ric

Le 19/11/2018 à 22:57

Bonsoir Ric,

le message apparait à partir du moment où j'ai ajouté la cellule C971 à surveiller.

Les éléments dans le module 1 peuvent être supprimés je pense car je les avais laissé là en attente de reprise dans la feuille DONNEES.

je ne sais pas comment faire car j'ai d'autres cellules à surveiller qui doivent ouvrir des lignes dans les autres onglets et là avec ce message je bloque pour la suite

Le 19/11/2018 à 23:40

Bonjour,

Un essai

Prenons comme exemple le bloc "Range("I5")" dont la valeur varie de 1 à 8.

Ce bloc peut être déplacé dans une macro séparée ... disons : Bloci5

donc :

sub Bloci5
le code déplacé ici
end sub

Où était le bloc ... tu remplaces par ..

If Range("I5").Select Then Call Bloci5

ric

Le 20/11/2018 à 01:01

Bonjour,

déjà :

Range("C5,C6,C7,C5,C9,C10,C11,C12,C13,C18,C19,I5, I14, F52, F136, F220, F304, F388, F472, F556, C25:E32, R54, S54, T54, L54, M54, C17, U18,C893, C906, C919, C984, C932,C945, C958, C997")

Sélectionne toutes ces cellules et nomme la plage, et remplace par :

Range(ton_nom)

En plus de gagner qq caractères, ça allège la lecture, et en cas d'évolution tu as juste la définition de ton nom à changer et plus à fouiller partout dans le code pour le modifier

Ensuite partout où tu as une régularité il faut la mettre à profit pour calculer la plage concernée. Ca te divise par 8 ces lignes.

Ex :

   If Range("I5") = 1 Then
         Feuil7.Visible = xlSheetVisible
        Worksheets("DONNEES").Rows(6 & ":" & 6).Hidden = False
        Worksheets("DONNEES").Rows(42 & ":" & 42).Hidden = False
        Worksheets("DONNEES").Rows(62 & ":" & 70).Hidden = False
    ElseIf Range("I5") = 2 Then
   ' etc
    End if

peut être avantageusement remplacé par :

With Worksheets("DONNEES")
    If Range("I5") > 0 Then
        Feuil7.Visible = xlSheetVisible
        .Rows(6).Resize([I5].Value).Hidden = False
        .Rows(42).Resize([I5].Value).Hidden = False
        .Rows(62).Resize([I5].Value).Hidden = False
    Else
        Feuil7.Visible = xlSheetHidden
        .Union(Rows("6:13"), Rows("42:49"), Rows("62:133")).Hidden = True
    End If
End With

Ca fait une cinquantaine de ligne en moins, multiplié par le nombre de fois où tu utilises cette structure.

Autre exemple plus bas :

    If Range("I5") = 1 And (Range("C27") <> "" Or Range("E27") <> "") Then
        Worksheets("DONNEES").Rows(291 & ":" & 318).Hidden = False
    ElseIf Range("I5") = 2 And (Range("C27") <> "" Or Range("E27") <> "") Then
        Worksheets("DONNEES").Rows(291 & ":" & 331).Hidden = False
    'etc

peut être remplacé par :

With Worksheets("DONNEES")
    If Range("C27") <> "" Or Range("E27") <> "" Then
        Select Case Range("I5").Value
        Case 1 - 8
            .Rows(291).Resize(27 + ([I5] - 1) * 13).Hidden = False
        Case ""
            .Rows(291 & ":" & 305).Hidden = False
            .Rows(306 & ":" & 409).Hidden = True
        Case Else
            MsgBox "Anomalie en I5": Stop
        End Select
    Else
        .Rows(291 & ":" & 409).Hidden = True
    End If
End With

Note qu'avec With Worksheets("DONNEES") un point . dit que tu utilises cet objet.

Comme dans .Rows(6) qui est équivalent à Worksheets("DONNEES").Rows(6)

Ca allège le code en plus de rendre le rendre plus facile à lire.

Ce sera tout pour ce soir, ça te fait déjà pas mal de boulot pour appliquer ce principe partout où tu peux

Bien sûr je n'ai rien testé. Pas impossible que tu aies qq adaptations à faire.

eric

Le 20/11/2018 à 09:54

Merci Ric et Eriiic pour vos messages.

j'essaye d'utiliser la première piste d'amélioration que tu proposes Eriiic

With Worksheets("DONNEES")
    If Range("I5") > 0 Then
        Feuil7.Visible = xlSheetVisible
        .Rows(6).Resize([I5].Value).Hidden = False
        .Rows(42).Resize([I5].Value).Hidden = False
        .Rows(62).Resize([I5].Value).Hidden = False
    Else
        Feuil7.Visible = xlSheetHidden
        .Union(Rows("6:13"), Rows("42:49"), Rows("62:133")).Hidden = True
    End If
End With

dans ce code, tu prends la valeur de I5 pour redimensionner la ligne 6 par exemple. Est ce que le principe est que on aditionne la valeur de I5 à la ligne 6 pour ouvrir n lignes ?

Pour le moment ça retourne une erreur d'éxecution 438 "propriété ou méthode non gérée par cet objet" et la ligne .union(Rows....) est mise en surbrillance par le débogueur. Est ce parce qu'il faut que je déclare quelque chose au début de ma macro ?

Sur ton autre proposition, je ne comprend pas le code dans la partie ".Rows(291).Resize(27 + ([I5] - 1) * 13).Hidden = False"

With Worksheets("DONNEES")
    If Range("C27") <> "" Or Range("E27") <> "" Then
        Select Case Range("I5").Value
        Case 1 - 8 
            .Rows(291).Resize(27 + ([I5] - 1) * 13).Hidden = False
        Case ""
            .Rows(291 & ":" & 305).Hidden = False
            .Rows(306 & ":" & 409).Hidden = True
        Case Else
            MsgBox "Anomalie en I5": Stop
        End Select
    Else
        .Rows(291 & ":" & 409).Hidden = True
    End If
End With

Par exemple si je sélectionne 2 en I5 cela devrait m'afficher les lignes de 291 à 331 si je mets un montant en C27 ou E27.

Est ce que cela veut dire que le calcul se fait de la façon suivante :

on part de la ligne 291, (27+(2-1)*13) = 27+13 = 40 lignes à afficher ?

Ensuite la message Box apparait quand je mets un nombre de ma liste déroulante en I5 et que je remplis un montant en C27 ou E27. et là je ne vois pas pourquoi

désolé pour toutes ces questions qui doivent sembler très basiques

Le 20/11/2018 à 11:15

Bonjour,

Par exemple si je sélectionne 2 en I5 cela devrait m'afficher les lignes de 291 à 331 si je mets un montant en C27 ou E27.

Est ce que cela veut dire que le calcul se fait de la façon suivante :

on part de la ligne 291, (27+(2-1)*13) = 27+13 = 40 lignes à afficher ?

Voilà, c'est exactement ça. Il faut calculer la plage concernée (fait F1 sur Resize) plutôt que de faire x fois le même modèle de code.

Ensuite la message Box apparait quand je mets un nombre de ma liste déroulante en I5 et que je remplis un montant en C27 ou E27. et là je ne vois pas pourquoi

Ah oui, petite erreur de ma part :

Case 1 To 8

et non Case 1-8

Et puisque c'est une validation par liste plus besoin de contrôler l'entrée. Tu peux supprimer :

        Case Else
            MsgBox "Anomalie en I5": Stop

eric

Le 20/11/2018 à 16:24

merci Eriiic , ça fonctionne super bien et effectivement ça allège grandement le travail. Je vais m'appliquer à reproduire ce type de formule sur mes autres cas.

En revanche, je n'arrive pas à faire pareil pour cette partie dont tu parlais dans ton message

With Worksheets("DONNEES")
    If Range("I5") > 0 Then
        Feuil7.Visible = xlSheetVisible
        .Rows(6).Resize([I5].Value).Hidden = False
        .Rows(42).Resize([I5].Value).Hidden = False
        .Rows(62).Resize([I5].Value).Hidden = False
    Else
        Feuil7.Visible = xlSheetHidden
        .Union(Rows("6:13"), Rows("42:49"), Rows("62:133")).Hidden = True
    End If
End With

J'ai modifié pour reprendre la même structure que ce tu m'as proposé et j'ai fait ça (car avec le code que tu proposais, quand je modifie en I5 j'ai un message d'erreur d’exécution 438 "propriété ou méthode non gérée par cet objet" et la ligne ".union Rows"est mise en surbrillance par le débogueur, ce que je n'arrivais pas à résoudre).

With Worksheets("DONNEES")
    If Range("I5") >= 1 Then
        Feuil7.Visible = xlSheetVisible
        Select Case Range("I5").Value
        Case 1 To 8
            .Rows(5).Resize(1 + ([I5] - 1)).Hidden = False
            .Rows(42).Resize(1 + ([I5] - 1)).Hidden = False
        Case ""
        Feuil7.Visible = xlSheetHidden
            .Rows(6 & ":" & 13).Hidden = True
            .Rows(42 & ":" & 49).Hidden = True
            .Rows(39 & ":" & 41).Hidden = False
             End Select
    End If
End With

J'arrive à ouvrir mes lignes 42 et suivantes mais pas à les masquer (alors que ça fonctionne plus bas avec la macro que tu m'as conseillée). Et je n'arrive pas à faire varier l'affichage des lignes 6 à 13 ni à masquer ma feuille 7 quand la valeur de I5 est vide.

Peux tu m'aiguiller ? merci

j'ai résolu (enfin je pense) mon souci en faisant de cette façon (sans doute pas de la meilleure façon)

With Worksheets("DONNEES")
    If Range("I5") > 0 Then
        Feuil7.Visible = xlSheetVisible
        .Rows(6).Resize([I5].Value).Hidden = False
        .Rows(42).Resize([I5].Value).Hidden = False
        .Rows(62).Resize([I5].Value).Hidden = False
    Else
        Feuil7.Visible = xlSheetHidden
        Worksheets("DONNEES").Rows(6 & ":" & 13).Hidden = True
        Worksheets("DONNEES").Rows(42 & ":" & 49).Hidden = True
    End If
End With
Le 20/11/2018 à 19:06

Bonjour,

Oui. Il était tard et à main levée j'ai mis un peu n'importe quoi...

Union(.Rows("6:13"), .Rows("42:49"), .Rows("62:133")).EntireRow.Hidden = True

eric

Le 20/11/2018 à 19:58

j'ai vu effectivement que tu avais répondu à une heure plus qu'avancée.

ça m'a permis d'essayer de comprendre comment fonctionnait ce que tu as proposé.

Un très grand merci à toi (et à Ric) pour votre aide !

Bonne soirée à tous

Le 27/11/2018 à 17:40

bonsoir à tous,

j'ai une interrogation dans la lignée de la précédente.

Il me semble avoir pigé la manip du calcul :

Dans mon exemple décris ci-dessus, pour lequel Eriiic m'a donné une solution, si je sélectionne "2" en I5 et que je je mets un montant en C25 ou E25 cela m'affiche les lignes de 52 à 90 si . On part de la ligne 52 : (27+(2-1)*13) = 27+13 = 40 lignes à afficher.

J'en déduis que la formule :

           .Rows(51).Resize(27 + ([I5] - 1) * 13).Hidden = False

permet d'afficher ou masquer des lignes contigües.

Ma question est donc de savoir si il serait possible de n'afficher que les lignes 51 à 55 / 66 à 68 / 79 à 81 (finalement les entêtes de tableau) pour ensuite faire ouvrir les lignes du dessous en sélectionnant la valeur de la case E52 (j'utiliserai alors la formule fournie par Eriiic pour cette partie).

merci pour votre aide.

4cpp-vtravail-draft.xlsm (0.98 Mo)
Le 27/11/2018 à 18:46

Bonjour,

feuille protégée...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address = "$E$52" Then
        For i = 0 To 2
            Rows(56 + 13 * i).Resize(10).Hidden = True
            Rows(56 + 13 * i).Resize([E52]).Hidden = False
        Next i
    End If
End Sub

eric

Le 27/11/2018 à 22:18

merci Eriiic et désolé pour la protection de la feuille (il me semblait pourtant qu'elle ne l'était pas.Le mdp est CRF). Je viens de la retirer dans la PJ à ce post.

J'ai inséré ta macro dans la macro existante. Le seul souci c'est que lorsque je mets la valeur 0 en E52 (cela masque uniquement les lignes 56 à 65) DAns l'absolu je voudrais masquer dans ce cas les lignes 69 à 78 / 82 à 91 etc) ça génère une erreur d’exécution 1004 sur la ligne :

 
 Rows(56 + 13 * i).Resize([E52]).Hidden = False

Ce point n'est pas primordiale en soit car en définitive dans le processus on ne revient pas en arrière.

En revanche J'essaye aussi de modifier la partie suivante pour ouvrir uniquement les entêtes de tableau (ex lignes 51 : 55 / 66 : 68 / 79 : 81) et je comprends pas comment faire la modification. Dans le déroulement de la saisie, on commence par saisir le nombre en I5 puis le nombre en I14 avant enfin de sélectionner un nombre en E52.

With Worksheets("DONNEES")
    If Range("F25") > 0 Then
    Worksheets("CPP TITULAIRE-ST").Rows(11 & ":" & 11).Hidden = False
    Worksheets("CPP TITULAIRE-ST").Rows(119 & ":" & 119).Hidden = False
    Worksheets("CPP TITULAIRE-ST").Rows(138 & ":" & 138).Hidden = False
        Select Case Range("I5").Value
        Case 1 To 8
            .Rows(51).Resize(27 + ([I5] - 1) * 13).Hidden = False
        Case ""
            .Rows(51 & ":" & 65).Hidden = False
            .Rows(66 & ":" & 169).Hidden = True
             End Select
    Else
    Worksheets("CPP TITULAIRE-ST").Rows(11 & ":" & 11).Hidden = True
    Worksheets("CPP TITULAIRE-ST").Rows(119 & ":" & 119).Hidden = True
    Worksheets("CPP TITULAIRE-ST").Rows(138 & ":" & 138).Hidden = True
        .Rows(51 & ":" & 169).Hidden = True
    End If
End With
5cpp-vtravail-draft.xlsm (0.98 Mo)
Le 28/11/2018 à 00:07

Le seul souci c'est que lorsque je mets la valeur 0 en E52...

Ta liste déroulante démarrant à 1 je n'ai pas analysé ce cas.

Servira-t-il au moins ? Tu peux avoir un tableau avec 0 sous-traitants ?

Si oui il suffit de tester pour ne pas ré-afficher de ligne dans ce cas :

If [E52] > 0 then Rows(56 + 13 * i).Resize([E52]).Hidden = False

En revanche J'essaye aussi de modifier la partie suivante pour ouvrir uniquement les entêtes de tableau (ex lignes 51 : 55 / 66 : 68 / 79 : 81)

J'ai regardé ton code et j'avoue que j'ai du mal à comprendre.

Sur un événement Change, on teste l'adresse de la cellule ou plage modifiée et on exécute les actions prévues uniquement pour ce cas.

Toi tu testes en bloc toutes les cellules et tu refais.t toutes les actions.

Il faut faire sur ce modèle :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address = "$E$52" Then
        For i = 0 To 2
            Rows(56 + 13 * i).Resize(10).Hidden = True
            Rows(56 + 13 * i).Resize([E52]).Hidden = False
        Next i

    ElseIf Target.Address = "$C$5" Then
        'actions à faire si C5 est modifié

    ElseIf Target.Address = "$E$5" Then
        'actions à faire si E5 est modifié

    ElseIf Not Intersect(Target, Range("F772,F652,F532")) Is Nothing Then
        'actions à faire si au moins une de ces cellules est modifiée

    ' etc
    End If
End Sub

A moins que tu ne copies-colles plusieurs cellules d'un coup, auquel cas il faut mettre certains dans des If séparés de façon à être sûr que toutes les modifs soient traitées.

Dans un premier temps tu peux juste ajouter ler dernier bout au début pour tester, sans oublier de fermer avec le End If.

Comme c'est l'événement Change de la feuille Données, elle obligatoirement active.

C'est donc la feuille par défaut et :

With Worksheets("DONNEES")
   '...
End With

ne gène pas mais est inutile. Il ne faut le mettre que lorsque tu t'adresses à une autre feuille.

eric

Rechercher des sujets similaires à "erreur compilation procedure trop longue"