Excel crashes when adding code using vbproject object?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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

hth,

Doug
 
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
 
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!
 
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!
 
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!
Click to expand...

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