Excel crashes when adding code using vbproject object?

[mikeb]

Hi,
I'm running Office 2003, the code below causes Excel to crash. I tried to
accomplish this with several workarounds but it still crashed Excel. Any
ideas?
It usually crashes at the "End With" statement

filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
filename4 = ThisWorkbook.Path & "\menuxxx.bas"

ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export filename2
ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export filename4

Set vbp = ActiveWorkbook.VBProject
On Error Resume Next
With vbp.vbcomponents
.Remove vbp.vbcomponents("moditerate")
.Import filename1
.Remove vbp.vbcomponents("runsfgcalcs")
.Import filename2
.Remove vbp.vbcomponents("sfcalcs")
.Import filename3

Lines = vbp.vbcomponents("thisworkbook").codemodule.countoflines
vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, Lines
vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4
vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
End With

Kill filename1
Kill filename2
Kill filename3
Kill filename4

[Doug Glancy]

mikeb,

Does it have anything to do with that you don't Import filename4?

hth,

Doug

"mikeb" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I'm running Office 2003, the code below causes Excel to crash. I tried to
> accomplish this with several workarounds but it still crashed Excel. Any
> ideas?
> It usually crashes at the "End With" statement
>
> filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
> filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
> filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
> filename4 = ThisWorkbook.Path & "\menuxxx.bas"
>
> ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
> ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export filename2
> ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
> ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export filename4
>
> Set vbp = ActiveWorkbook.VBProject
> On Error Resume Next
> With vbp.vbcomponents
> .Remove vbp.vbcomponents("moditerate")
> .Import filename1
> .Remove vbp.vbcomponents("runsfgcalcs")
> .Import filename2
> .Remove vbp.vbcomponents("sfcalcs")
> .Import filename3
>
> Lines = vbp.vbcomponents("thisworkbook").codemodule.countoflines
> vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, Lines
> vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4
> vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
> End With
>
> Kill filename1
> Kill filename2
> Kill filename3
> Kill filename4

[Peter T]

Serious problems can occur if you are changing code to the ThisWorbook
module or Sheet modules in ThisWorkbook, ie in same project as running the
code. Try the same code but out of ThisWorkBook, ie process some other
workbook.

Regards,
Peter T

"mikeb" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I'm running Office 2003, the code below causes Excel to crash. I tried to
> accomplish this with several workarounds but it still crashed Excel. Any
> ideas?
> It usually crashes at the "End With" statement
>
> filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
> filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
> filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
> filename4 = ThisWorkbook.Path & "\menuxxx.bas"
>
> ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
> ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export filename2
> ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
> ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export filename4
>
> Set vbp = ActiveWorkbook.VBProject
> On Error Resume Next
> With vbp.vbcomponents
> .Remove vbp.vbcomponents("moditerate")
> .Import filename1
> .Remove vbp.vbcomponents("runsfgcalcs")
> .Import filename2
> .Remove vbp.vbcomponents("sfcalcs")
> .Import filename3
>
> Lines = vbp.vbcomponents("thisworkbook").codemodule.countoflines
> vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, Lines
> vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4
> vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
> End With
>
> Kill filename1
> Kill filename2
> Kill filename3
> Kill filename4

[mikeb]

Peter, What I'm actually doing is adding or updating code to "ThisWokbook"
module for any workbook from a "template" workbook, if that makes sense. I'm
adding some events to "thisworkbook" from a different workbook. I want to
copy code from one "thisworkbook" to another "thisworkbook". Thanks!

"Peter T" wrote:

> Serious problems can occur if you are changing code to the ThisWorbook
> module or Sheet modules in ThisWorkbook, ie in same project as running the
> code. Try the same code but out of ThisWorkBook, ie process some other
> workbook.
>
> Regards,
> Peter T
>
> "mikeb" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I'm running Office 2003, the code below causes Excel to crash. I tried to
> > accomplish this with several workarounds but it still crashed Excel. Any
> > ideas?
> > It usually crashes at the "End With" statement
> >
> > filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
> > filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
> > filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
> > filename4 = ThisWorkbook.Path & "\menuxxx.bas"
> >
> > ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
> > ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export filename2
> > ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
> > ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export filename4
> >
> > Set vbp = ActiveWorkbook.VBProject
> > On Error Resume Next
> > With vbp.vbcomponents
> > .Remove vbp.vbcomponents("moditerate")
> > .Import filename1
> > .Remove vbp.vbcomponents("runsfgcalcs")
> > .Import filename2
> > .Remove vbp.vbcomponents("sfcalcs")
> > .Import filename3
> >
> > Lines = vbp.vbcomponents("thisworkbook").codemodule.countoflines
> > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, Lines
> > vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4
> > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
> > End With
> >
> > Kill filename1
> > Kill filename2
> > Kill filename3
> > Kill filename4
>
>
>

[mikeb]

Doug,
AFAIK you can't import a "ThisWorkbook" module, you can export it but if you
import it becomes a class module. All I want to do is copy some events from
one "thisworkbook" module to another "thisworkbook" module. Thanks!


"Doug Glancy" wrote:

> mikeb,
>
> Does it have anything to do with that you don't Import filename4?
>
> hth,
>
> Doug
>
> "mikeb" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I'm running Office 2003, the code below causes Excel to crash. I tried to
> > accomplish this with several workarounds but it still crashed Excel. Any
> > ideas?
> > It usually crashes at the "End With" statement
> >
> > filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
> > filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
> > filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
> > filename4 = ThisWorkbook.Path & "\menuxxx.bas"
> >
> > ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
> > ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export filename2
> > ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
> > ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export filename4
> >
> > Set vbp = ActiveWorkbook.VBProject
> > On Error Resume Next
> > With vbp.vbcomponents
> > .Remove vbp.vbcomponents("moditerate")
> > .Import filename1
> > .Remove vbp.vbcomponents("runsfgcalcs")
> > .Import filename2
> > .Remove vbp.vbcomponents("sfcalcs")
> > .Import filename3
> >
> > Lines = vbp.vbcomponents("thisworkbook").codemodule.countoflines
> > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, Lines
> > vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4
> > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
> > End With
> >
> > Kill filename1
> > Kill filename2
> > Kill filename3
> > Kill filename4
>
>
>

[Peter T]

> Peter, What I'm actually doing is adding or updating code to "ThisWokbook"
> module for any workbook from a "template" workbook, if that makes sense.
I'm
> adding some events to "thisworkbook" from a different workbook. I want to
> copy code from one "thisworkbook" to another "thisworkbook". Thanks!

So you're saying you are changing the ThisWorkbook module NOT in the project
with your code.

Looking again at your code again this stands out like a bad thing to do
(look at the top of the exported file) -

vbp.vbcomponents("thisworkbook").codemodule.addfromfile filename4

try writing/inserting line by line

find "Attribute VB_Exposed = True" and continue after that

Regards,
Peter T


> "Peter T" wrote:
>
> > Serious problems can occur if you are changing code to the ThisWorbook
> > module or Sheet modules in ThisWorkbook, ie in same project as running
the
> > code. Try the same code but out of ThisWorkBook, ie process some other
> > workbook.
> >
> > Regards,
> > Peter T
> >
> > "mikeb" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > I'm running Office 2003, the code below causes Excel to crash. I
tried to
> > > accomplish this with several workarounds but it still crashed Excel.
Any
> > > ideas?
> > > It usually crashes at the "End With" statement
> > >
> > > filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
> > > filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
> > > filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
> > > filename4 = ThisWorkbook.Path & "\menuxxx.bas"
> > >
> > > ThisWorkbook.VBProject.vbcomponents("modIterate").Export filename1
> > > ThisWorkbook.VBProject.vbcomponents("runsfgcalcs").Export
filename2
> > > ThisWorkbook.VBProject.vbcomponents("sfcalcs").Export filename3
> > > ThisWorkbook.VBProject.vbcomponents("ThisWorkbook").Export
filename4
> > >
> > > Set vbp = ActiveWorkbook.VBProject
> > > On Error Resume Next
> > > With vbp.vbcomponents
> > > .Remove vbp.vbcomponents("moditerate")
> > > .Import filename1
> > > .Remove vbp.vbcomponents("runsfgcalcs")
> > > .Import filename2
> > > .Remove vbp.vbcomponents("sfcalcs")
> > > .Import filename3
> > >
> > > Lines =
vbp.vbcomponents("thisworkbook").codemodule.countoflines
> > > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1,
Lines
> > > vbp.vbcomponents("thisworkbook").codemodule.addfromfile
filename4
> > > vbp.vbcomponents("thisworkbook").codemodule.deletelines 1, 4
> > > End With
> > >
> > > Kill filename1
> > > Kill filename2
> > > Kill filename3
> > > Kill filename4
> >
> >
> >