XL 2016 Erreur 28 : Espace de pile insuffisant

[LoubnaHhch]

Bonjour tout le monde,

j'ai un petit code qui m'affiche (pas tout le temps) l'erreur 28 : Espace de pile insuffisant.

Voilà mon code :

-------------------------- Début Code ---------------

Private Sub Worksheet_Change(ByVal Target As Range)


For i = 3 To 357

If Not Intersect(Target, Range("F" & i, "Q" & i)) Is Nothing And (Cells(i, 6) <> 0 Or Cells(i, 7) <> 0 Or Cells(i, 8) <> 0 Or Cells(i, 9) <> 0 Or Cells(i, 10) <> 0 Or Cells(i, 11) <> 0 Or Cells(i, 12) <> 0 Or Cells(i, 13) <> 0 Or Cells(i, 14) <> 0 Or Cells(i, 15) <> 0 Or Cells(i, 16) <> 0) Or (Cells(i, 17) <> 0) Then Cells(i, 18) = 0

Next i


For i = 3 To 357


If Not Intersect(Target, Range("R" & i)) Is Nothing And Cells(i, 18) <> 0 Then Range("F" & i, "Q" & i) = 0

Next i



For i = 3 To 357

If Not Intersect(Target, Range("E" & i, "Q" & i)) Is Nothing And (Cells(i, 5).Value = "" And (Cells(i, 6) <> 0 Or Cells(i, 7) <> 0 Or Cells(i, 8) <> 0 Or Cells(i, 9) <> 0 Or Cells(i, 10) <> 0 Or Cells(i, 11) <> 0 Or Cells(i, 12) <> 0 Or Cells(i, 13) <> 0 Or Cells(i, 14) <> 0 Or Cells(i, 15) <> 0 Or Cells(i, 16) <> 0 Or Cells(i, 17) <> 0 Or Cells(i, 18) <> 0)) Then MsgBox "Please enter UOM"

Next i

End Sub

-------------------------- Fin Code ------------------
Merci de m'aider à résoudre le souci

 

[mapomme]

Bonjour,

Excel a lancé la procédure évènementielle Sub Worksheet_Change(ByVal Target As Range) car au moins une cellule a été modifiée.

Dans cette procédure, vous modifiez des cellules.
Dès la première cellule modifiée par le code, Excel détecte un second évènement Worksheet_Change et relance la procédure Sub Worksheet_Change(ByVal Target As Range) (il met l'exécution de la première en mémoire pour y revenir plus tard autrement dit "il empile").

Dans cette deuxième procédure, vous modifiez encore une cellule. Excel détecte un troisième évènement Worksheet_Change et relance la procédure Sub Worksheet_Change(ByVal Target As Range) (il met l'exécution de la seconde en mémoire pour y revenir plus tard autrement dit "il empile" une deuxième fois).
On a donc deux empilements maintenant.

Et ainsi de suite. A un moment donné, à force d'empiler, Excel VB sature la mémoire, provoque un débordement et une erreur survient.


Pour inhiber ce phénomène, VBA dispose de l'instruction Application.EnableEvents = False qui empêche VBA de déclencher les actions évènementielles même si l'évènement se produit.

Dans votre procédure avant les lignes de code modifiant les cellules, insérerez la ligne Application.EnableEvents = False.

N'oubliez pas - Très important! Après les lignes de code modifiant les cellules, ajoutez la ligne Application.EnableEvents = True pour réactiver le fonctionnement normal d'Excel.

Pour bien faire, il faudrait aussi faire la gestion d'une éventuelle erreur entre le
Application.EnableEvents = False et le Application.EnableEvents = True mais chaque chose en son temps.

edit : Bonjour @BrunoM45 .

 

[Compte Supprimé 979]

Et oui @mapomme

être codeur ne s'improvise pas... la preuve

 

[LoubnaHhch]

Bonjour,

Excel a lancé la procédure évènementielle Sub Worksheet_Change(ByVal Target As Range) car au moins une cellule a été modifiée.

Dans cette procédure, vous modifiez des cellules.
Dès la première cellule modifiée par le code, Excel détecte un second évènement Worksheet_Change et relance la procédure Sub Worksheet_Change(ByVal Target As Range) (il met l'exécution de la première en mémoire pour y revenir plus tard autrement dit "il empile").

Dans cette deuxième procédure, vous modifiez encore une cellule. Excel détecte un troisième évènement Worksheet_Change et relance la procédure Sub Worksheet_Change(ByVal Target As Range) (il met l'exécution de la seconde en mémoire pour y revenir plus tard autrement dit "il empile" une deuxième fois).
On a donc deux empilements maintenant.

Et ainsi de suite. A un moment donnée, à force d'empiler, Excel VB sature la mémoire, provoque un débordement et une erreur survient.


Pour inhiber ce phénomène, VBA dispose de l'instruction Application.EnableEvents = False qui empêche VBA de déclencher les actions évènementielles même si l'évènement se produit.

Dans votre procédure avant les lignes de code modifiant les cellules, insérerez la ligne Application.EnableEvents = False.

N'oubliez pas - Très important! Après les lignes de code modifiant les cellules, ajoutez la ligne Application.EnableEvents = True pour réactiver le fonctionnement normal d'Excel.

Pour bien faire, il faudrait aussi faire la gestion d'une éventuelle erreur entre le
Application.EnableEvents = False et le Application.EnableEvents = True mais chaque chose en son temps.

edit : Bonjour @BrunoM45 .

Merci @mapomme , j'ai essayé du coup d'insérer Application.EnableEvents dans mon code mais la seconde boucle de mon code ne fonctionne plus maintenant

----------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)



Application.EnableEvents = False


For i = 3 To 357

If Not Intersect(Target, Range("F" & i, "Q" & i)) Is Nothing And (Cells(i, 6) <> 0 Or Cells(i, 7) <> 0 Or Cells(i, 8) <> 0 Or Cells(i, 9) <> 0 Or Cells(i, 10) <> 0 Or Cells(i, 11) <> 0 Or Cells(i, 12) <> 0 Or Cells(i, 13) <> 0 Or Cells(i, 14) <> 0 Or Cells(i, 15) <> 0 Or Cells(i, 16) <> 0) Or (Cells(i, 17) <> 0) Then Cells(i, 18) = 0

Next i



For i = 3 To 357


If Not Intersect(Target, Range("R" & i)) Is Nothing And Cells(i, 18) <> 0 Then Range("F" & i, "Q" & i) = 0

Next i

Application.EnableEvents = True

For i = 3 To 357

If Not Intersect(Target, Range("E" & i, "Q" & i)) Is Nothing And (Cells(i, 5).Value = "" And (Cells(i, 6) <> 0 Or Cells(i, 7) <> 0 Or Cells(i, 8) <> 0 Or Cells(i, 9) <> 0 Or Cells(i, 10) <> 0 Or Cells(i, 11) <> 0 Or Cells(i, 12) <> 0 Or Cells(i, 13) <> 0 Or Cells(i, 14) <> 0 Or Cells(i, 15) <> 0 Or Cells(i, 16) <> 0 Or Cells(i, 17) <> 0 Or Cells(i, 18) <> 0)) Then MsgBox "Please enter UOM"

Next i

End Sub


----------------------------------------------

 

[mapomme]

Re,

J'avais écrit qu'il fallait entourer le code qui est susceptible de modifier des cellules de la feuille par :
Application.EnableEvents = False - Application.EnableEvents = True .

Or après l'instruction Application.EnableEvents = true, vous avez encore du code (2ème boucle - vous en avez même une troisième) susceptible de modifier des cellules. Vous avez donc placé l'instruction Application.EnableEvents = true trop tôt dans le code.

Le mieux est de fournir un fichier anonymisé et de dire ce que vous voulez faire. Votre code me semble un peu "chargé".

edit : Bonsoir @Usine à gaz

 

[LoubnaHhch]

Re,

J'avais écrit qu'il fallait entourer le code qui est susceptible de modifier des cellules de la feuille par :
Application.EnableEvents = False - Application.EnableEvents = True .

Or après l'instruction Application.EnableEvents = true, vous avez encore du code (2ème boucle - vous en avez même une troisième) susceptible de modifier des cellules. Vous avez donc placé l'instruction Application.EnableEvents = true trop tôt dans le code.

Le mieux est de fournir un fichier anonymisé et de dire ce que vous voulez faire. Votre code me semble un peu "chargé".

Dans mon code :
La première boucle était du coup pour vérifier si j'avais une valeur dans la cellule R1 et je change la valeur d'une cellule de la plage F1 à Q1, il faut affecter zéro à la cellule R1;

La deuxième boucle, si j'avais des valeurs dans les cellules de la plage F1 à Q1, et je mets une valeur dans la cellule R1, il faut affecter la valeur de zéro à toutes les cellules de la plage F1 à Q1.

La troisième boucle, check si je saisis une valeur dans une des cellules de la plage F1 à R1, il faut afficher un message qui invite à remplir la cellule E1 aussi

Merci beaucoup de votre aide @mapomme

 

[patricktoulon]

bonsoir tu m’étonne que ton pc est en souffrance
déjà comme l'a dit @mapomme la pile d'event que tu engage est impensable
si les pc parlerais il te dirait quelque chose mon ami

et en plus 3 boucle pour travailler sur les même cellules
ton truc ça se résume a ça

si les colonne 6 a 17 de la ligne icontiennene alors la 18 = 0

MAIS!!! dans ta seconde boucle(toujours avec l'events change) LOL!!!
tu met a zero ces même cellule si la colonne 18 est pleine
déjà là ça moi je le remet dans la first boucle ça

et pour couronner le tout un 3 eme boucle pour contrôler l'inverse des colonnes 6 à 17
soit si pleines
ben ça aussi je met dans la first boucle
et je bloque les events

bref je ferais un truc du genre

'-------------------------- Début Code ---------------

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i&, txt$
    On Error GoTo Gestionerr
    
    Application.EnableEvents = False

    For i = 3 To 357

        If Not Intersect(Target, Range("F" & i, "Q" & i)) Is Nothing Then

            If WorksheetFunction.CountIfs(Cells(i, 6).Resize(, 12), ">0") Then
                
                Cells(i, 18) = 0
            
            Else
                
                Cells(i, 6).Resize(, 11) = 0 'la partie else remplace ta 2d boucle
            
            End If
        
        End If

        'ta 3eme boucle intégrée aussi dans la premiere
        If Not Intersect(Target, Range("E" & i, "Q" & i)) Is Nothing Then
            
            If Cells(i, 5).Value = "" And WorksheetFunction.CountIfs(Cells(i, 6).Resize(, 12), ">0") Then
                
                txt = txt & " ; " & i
            
            End If
        
        End If

    Next i
    Application.EnableEvents = True
    If txt <> "" Then MsgBox "entrez des UOM dans les lignes ; " & txt

Gestionerr:
    Application.EnableEvents = True
    MsgBox Err.Description
End Sub

'-------------------------- Fin Code ------------------