Erreur d'exécution '28' Espace pile insuffisante

Question

Bonjour,

je suis en train de développer une application en utilisant excel.
J'ai créé un module qui est appelé à chaque fois qu'on modifie la valeur d'une certaine colonne (liste) dans la feuille active.
Seulement, toutes les valeurs marchent sauf une. Quand je met "PAS PRÊT ALERTE 2" comme choix, une erreur s'affiche : Erreur d'exécution '28' Espace pile insuffisante
Et pourtant quand je valide l'erreur, le traitement est quand même effectué.


Pouvez vous m'aidez à savoir d'où ça vient et comment résoudre ce problème ?


Merci bien.


Voilà mon code :


Sub Gestion_Retard()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim t As Integer
Dim nomTBdependant As String
Dim nomTB1 As String


'Blocage de l'écran
Application.ScreenUpdating = False
TBPrincipal = Evaluate("TBPrincipal")
Application.Goto Reference:="Curseur"
'Récupère le numéro de la dernière ligne de la zone
dernièreLigne1 = Cells(65536, 1).End(xlUp).Row


'Instruction de blocage du recalcul automatique
Application.Calculation = xlCalculationManual


For x = 6 To 31 'dernièreLigne1
    Select Case Range("H" & x).Value
        Case "PAS PRÊT - ALERTE 2"
            Range("D" & x).Value = "RETARD !"
            For y = 2 To 27
                z = 0
                Do While Sheets("TB").Range(Chr(67 + z) & y).Value <> ""
                    nomTB1 = Sheets("TB").Range(Chr(67 + z) & y).Value
                    If Range("C" & x).Value = nomTB1 Then
                        nomTBdependant = Sheets("TB").Range("B" & y).Value
                        For t = 6 To 31
                            If Range("C" & t).Value = nomTBdependant Then
                                Range("D" & t).Value = "RETARD ! cause : TB " & Range("C" & x).Value
                                Range("H" & t).Value = "EN ATTENTE DES SOURCES"
                            End If
                        Next t
                        Exit Do
                    Else
                        If Sheets("TB").Range(Chr(67 + z) & y).Value = "" Then
                            z = z + 1
                            Exit Do
                        End If
                    End If
                    z = z + 1
                Loop
            Next y
            
        Case "LIVRE"
            Range("D" & x).Value = "OK"
        Case "PRÊT"
            Range("D" & x).Value = ""
        Case Else
            If Range("D" & x).Value = "RETARD !" Then
                Range("D" & x).Value = ""
            ElseIf Range("D" & x).Value = "OK" Then
                Range("D" & x).Value = ""
            End If
    End Select
Next x


'Instruction de déblocage du recalcul automatique
Application.Calculation = xlCalculationAutomatic


End Sub

PS : Avant d'utiliser un select case, j'utilisais des If ... Then... Else, l'erreur ne se produisait pas mais le temps d'exécution était interminable.

Renfield · Answer

quand l'erreur survient, fais 'deboguage' puis presse Ctrl+L
tu verra ce qui plombe la pile

quel algo complexe... :S

tes temps interminables viennent de là...

Range("D" & x).Value 

colonee 4, ligne x

fais donc :

Cells(x,4).Value
ca eviteraa a Excel d'avoir a analyser l'adresse

évite le Sheets("TB") partout, fais:

Dim oSheetTB as WorkSheet
Set oSheetTB = Sheets("TB")

et dans ton code, remplace Sheets("TB") par oSheetTB

Renfield
Admin CodeS-SourceS- MVP Visual Basic

mimi_939 · Answer

Oui merci, j'ai effectué les modifications que vous m'avez conseillé. Je n'ai plus l'erreur. Par contre, le temps d'exécution est trop long. Il faut que je tape Echap pour arreter l'exécution, et le traitement est quand même effectué.
Il semblerait que j'ai trop d'appel à ce module.
Comment faire pour réduire  le temps d'exécution ? 
Mon algo est trop complexe ? 

Merci

jmfmarques · Answer

Inhibe déjà la mise à jour de l'affichage pendant la durée du traitement (rétablis-lopà ensuite)

Je crois qu'il te faut voir du côté de quelquechose qui, sous VBA, ressemble à VBA Application.ScreenUpdating = False

False pour inhiber, True pour rétablir.

cs_MPi · Answer

Do While Sheets("TB").Range(Chr(67 + z) & y).Value
Tu pourrais initialiser z =  3  ' tu sembles commencer à C (colonne 3)
et utiliser
 Cells(y, z) 

De cette manière, tu ne te limites pas aux colonnes C à Z

Aussi,
                        Next t
                        Exit Do
                    Else
                        If Sheets("TB").Range(Chr(67 + z) & y).Value  = "" Then
                            z = z + 1
                            Exit Do
                        End If
                    End If
                    z = z + 1
                Loop
            Next y
J'enlèverais la condition en bleu qui n'est pas nécessaire

MPi

mimi_939 · Answer

Merci beaucoup pour la réponse précédente!

J'ai cru que cette modification m'avait résolu complètement mon problème.
Seulement, il y a un petit problème.
Au début, j'ai eu aucun problème, dès que je faisais une modification dans la colonne en question, le traitement était instantané mais de temps en temps, le problème se présente à nouveau sans que j'ai changé le code.
Je ne comprends plus, y a-t-il un moyen de résoudre définitivement le problème. C'est assez curieux quand même.

Merci pour vos réponses.

Renfield · Answer

4 ou 5 boucles imbriquées, ton algo est assez complexe, enfin, l'enchainement...

a revoir si tu veux pouvoir soulager un peu tes temps de traitement.

Renfield
Admin CodeS-SourceS- MVP Visual Basic

cs_MPi · Answer

Tu refais 2 fois la boucle 6 to 31
... à première vue, je ne pense pas que ce soit nécessaire. En fait, il doit y avoir moyen d'en laisser tomber une, non ?

Peut-être que si tu copiais/collais une partie de tes données et expliquais ce que tu cherches à faire, ce serait plus simple te t'aiguiller (?) seulement quelques lignes et quelques colonnes pour faciliter la lecture...

MPi

mimi_939 · Answer

---- Prévisions , Périodicité , Etat du TB , ---- , HEBDOMADAIRE, FAISABLE, ---- , MENSUEL, FAISABLE, ---- , MENSUEL, FAISABLE, ---- , MENSUEL, FAISABLE, ---- OK, MENSUEL, LIVRE, ---- , MENSUEL, FAISABLE La colonne "Etat du Tb" est une liste déroulante et on peut choisir 1 valeur parmi plusieurs. En fonction de la valeur choisie par l'utilisateur, je veux effectuer un traitement sur la colonne "Prévisions". Par exemple, si je choisi la valeur "PAS PRÊT ALERTE NIVEAU 2", je voudrais que dans prévisions il y ait RETARD qui s'inscrive! Si un Tableau de bord en retard a une incidence sur un autre TB, alors lui aussi sera en retard et en plus je veux indiqué le Tb en cause... Voila en gros les traitements que je veux effectuer! J'espère que c'est assez explicite.

cs_MPi · Answer

Lorsque tu dis "liste déroulante" veux-tu parler d'une liste de choix que tu remplis avec le menu "Validation" ?

J'ai encore de la difficulté à comprendre exactement ce que tu cherches à faire et tout ce qui est impliqué... mais peut-être que ce petit bout de code pourrait aider (?)

Donc, si tu as une liste déroulante, tu peux intercepter les changements dans Worksheet_Change(...)
Tu dois vérifier si la colonne est bien celle qui contient la liste et, selon le choix, inscrire les données nécessaire dans les cellules voisines sur la même ligne... j'espère que je suis dans la bonne voie... Pour mon test, j'ai mis la liste de choix dans la colonne J (10)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 10 Then  'la colonne de la liste de choix
        Select Case Target.Value
            Case "LIVRE" 'différentes valeurs de cette liste de choix
                Range("D" & Target.Row) = "OK"
            Case "PAS PRÊT ALERTE NIVEAU 2"
                Range("D" & Target.Row) = "RETARD ! cause : TB " & Range("C" & Target.Row)
            Case "FAISABLE"
                Range("D" & Target.Row) = "Whatever"
            Case "PRÊT"
                Range("D" & Target.Row) = ""
            Case Else
                If InStr(1, Range("D" & Target.Row), "RETARD !") > 0 Then
                    Range("D" & Target.Row) = ""
                ElseIf Range("D" & Target.Row) = "OK" Then
                    Range("D" & Target.Row) = ""
                End If
        End Select
    End If
End Sub

Il reste des choses à y ajouter, mais je pense que tu comprendras mon idée ...
en espérant que ça puisse s'adapter à ton problème...

MPi

mimi_939 · Answer

Merci beaucoup pour l'astuce!

Erreur d'exécution '28' Espace pile insuffisante

10 réponses

Votre réponse