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

[Simon Letten]

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
--
HTH

Simon


"keithb" wrote:

> 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
>
>
>
>
>

[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

"Simon Letten" <SimonLetten@discussions.microsoft.com> wrote in message
news:85B5966B-67F4-4E67-8AA5-66DF743F71E0@microsoft.com...
> 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
> --
> HTH
>
> Simon
>
>
> "keithb" wrote:
>
>> 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
>>
>>
>>
>>
>>