VBCodeMod crashing Excel
 

Excel97 Win2000


I have a large number of spreadsheets with VB code that I
need to modify. In each of the worksheet code modules I
want to delete the second line of code and replace it with
something else. Whenever any of these files is opened -
the file 'EScode.xls' is automatically opened, and it is
in this file that I write the code to do the update
whenever one of the spreadsheets that needs modification
is opened. I have written the following code to achieve
this:

Dim VBCodeMod As VBIDE.CodeModule

For Each sh In Worksheets
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sh.index + 1).CodeModule
With VBCodeMod
.DeleteLines 2
.InsertLines 2, " Application.Run ""ESCode.xls!
WorksheetCode.PanelsChange"", Target"
End With
Next

It works the first time, but if the file (but not Excel)
is closed and reopened, it crashes at '.DeleteLines 2'
with a 'memory could not be read...' error.

The problem is repeatable - if I open Excel again, it
works the first time, crashes the second. As there are
hundreds of spreadsheets like this, having it crash on
every second one would be a major headache.

Does anyone know what might be the problem, or how to
either fix or work around it?

mgs

VBCodeMod crashing Excel
 

For anyone interested, after a week of experimenting I
think I have fixed my problem. Instead of deleting line 2
and then adding the new line 2, I add the new line 2 first
and then delete line 3. Don't know why it works, but I
won't argue.

mgs

-----Original Message-----
Excel97 Win2000


I have a large number of spreadsheets with VB code that I
need to modify. In each of the worksheet code modules I
want to delete the second line of code and replace it
with
something else. Whenever any of these files is opened -
the file 'EScode.xls' is automatically opened, and it is
in this file that I write the code to do the update
whenever one of the spreadsheets that needs modification
is opened. I have written the following code to achieve
this:

Dim VBCodeMod As VBIDE.CodeModule

For Each sh In Worksheets
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sh.index + 1).CodeModule
With VBCodeMod
.DeleteLines 2
.InsertLines 2, " Application.Run ""ESCode.xls!
WorksheetCode.PanelsChange"", Target"
End With
Next

It works the first time, but if the file (but not Excel)
is closed and reopened, it crashes at '.DeleteLines 2'
with a 'memory could not be read...' error.

The problem is repeatable - if I open Excel again, it
works the first time, crashes the second. As there are
hundreds of spreadsheets like this, having it crash on
every second one would be a major headache.

Does anyone know what might be the problem, or how to
either fix or work around it?

mgs


.