Excel crashes when programming to the VBA Editor

[keithb]

Here are three code fragments. The first one works propely, the second two
cause Excel to crash. Can someone tell me what I am doing wrong? Thanks

The following code works properly:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
"setLookupList" & Chr(13) & _
"End Sub"
End With

This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"'Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"'doIt Target" & Chr(13) & _
"End Sub"
End With

Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dotIt Target"
End With

 

[Guest]

At what point does Excel crash?

I've noticed the following points (don't know if they are causing the
problem though):
Your second example code has the single quote character before the Private
keyword and before doIt Target - is that intentional?

Your third example is calling dotIt rather than doIt

Is the doIt sub in a location that is within the scope of the
Worksheet_Change event? i.e. in same module or in a standard module

If the following code already exists in Sheet1's module:
Sub doIt(ByVal prngTarget As Range)

MsgBox "doIt sub: " & prngTarget.Address

End Sub

Then either/both of these work ok for me:
Sub TestVbe2()

Dim wb As String

wb = ThisWorkbook.Name
'This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = Workbooks(wb).VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"doIt Target" & Chr(13) & _
"End Sub"
End With
End Sub

Sub TestVbe3()

Dim wb As String

wb = ThisWorkbook.Name
'Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"doIt Target"
End With

End Sub

 

[keithb]

With either approach, Excel crashes on execution of the .insertLines
command. The single quote characters and the dotIt rather than doIt are
entry errors in my email. I typed the information instead of cutting and
pasting from the code. I will play around with the working examples that you
send and let you know what I find.

Thanks for your help,

Keith