Erreur d'exécution '9': Indice de gamme uniquement lorsque Excel VBE est fermé

Tous,

Je suis confronté à une erreur avec un code VBA dans une macro Excel. Voici le flux de travail je cherche:

  • J'ai un module qui exécute le code pour créer une nouvelle feuille de calcul, mettre en forme et d'ajouter dans un tas de valeurs
  • dans ce même module, j'ai de déterminer une plage de cellules sur la base de la dernière ligne peuplée (qui sera toujours différent selon les étapes précédentes)
  • Une fois que je sais de cette gamme, j'utilise le code ci-dessous pour écrire dans le nouvellement créé des feuilles de calcul codemodule afin que je puisse mettre en place un " change_event’. Je veux seulement le change_event à déclencher lorsque les valeurs dans la plage je viens déterminée sont modifiées:`

    Dim Startline As Long
    Startline = 1
    Dim x As Integer
    x = Errors.Count - 1
    
    Dim rng As Range
    Set rng = Range("D" & LastRow - x & ":" & "D" & LastRow)
    
           With ThisWorkbook.VBProject.VBComponents(VRS.CodeName).CodeModule
            Startline = .CreateEventProc("Change", "Worksheet") + 1
            .InsertLines Startline, "Dim rng As Range "
            Startline = Startline + 1
            .InsertLines Startline, "Set rng = Range(" & """" & CStr(rng.Address) & """" & ")"
            Startline = Startline + 1
            .InsertLines Startline, "If Target.Count > 1 Then Exit Sub"
            Startline = Startline + 1
            .InsertLines Startline, "If Intersect(Target, rng) Is Nothing Then Exit Sub"
            Startline = Startline + 1
            .InsertLines Startline, "MsgBox (""Value Changed!..."") "
           End With

Le code fonctionne, et écrit ce qui suit dans le codemodule de la feuille de calcul spécifiée:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D58:D62")
If Target.Count > 1 Then Exit Sub  
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox ("Value Changed!...") 
End Sub`

Ce code fonctionne aussi, et la boîte de message apparaît lorsque les cellules de la plage sont modifiées. Cependant, avec le VBE fermé elle produira l'erreur:

Run-time error '9': Subscript out of range

Frapper debug me prend à la ligne:

With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule

Mais en fait il jette l'erreur sur la ligne suivante:

Startline = .CreateEventProc("Change", "Worksheet") + 1
Ok, j'ai donc modifié le code de simplement supprimer tout le code de la feuille de calcul spécifiée et ré-écrire, au lieu de la suppression et de l'écriture fraîche à chaque fois. Cela empêche l'erreur popping up, mais s'ouvre automatiquement le VBE à la place.... J'utilise la ligne ci-dessous pour fermer le VBE...pas très habile. Je suis ouvert à un plus gracieux solution: Application.VBE.MainWindow.Visible = False
Double Possible de VBA à langer nom de code d'Erreur d'Exécution 9: Indice de gamme

OriginalL'auteur gg7aph | 2011-05-26