Hi,
I'm having a quite strange problem in a VBA macro that I made that is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1", I always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set line:
useless


Can you help me somehow?

Thank you so much right now


Mark

You may find something here.

http://www.cpearson.com/excel/vbe.aspx

I've not done anything like what you are doing, but I wonder if you already
have some lines in the code module and are trying to insert lines where some
already exist. Have you checked that?
--
HTH,

Barb Reinhardt



"perf" wrote:

Hi,
I'm having a quite strange problem in a VBA macro that I made that is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1", I always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set line:
useless


Can you help me somehow?

Thank you so much right now


Mark

Hi,
yes yes, I only write lines if the module is empty, otherwise I delete all
lines and then I write them.
your link was useful, and I made some change in the code, that (apparently
without reason) allowed me to write in the sheet1 module if the module is
originally empty.
But I still have that problem if the module is not empty: after deleting the
rows, as soon as I try to write in the module, there is the automation error.

moreover, I am noticing that the SAVE command is not working properly with
..xls. Sometimes it saves the fresh code in the module, sometimes not.
randomly.


this is sooooo unpredictable... not good at all : (

thanks
"Barb Reinhardt" wrote:

You may find something here.

http://www.cpearson.com/excel/vbe.aspx

I've not done anything like what you are doing, but I wonder if you already
have some lines in the code module and are trying to insert lines where some
already exist. Have you checked that?
--
HTH,

Barb Reinhardt



"perf" wrote:

Hi,
I'm having a quite strange problem in a VBA macro that I made that is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1", I always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set line:
useless


Can you help me somehow?

Thank you so much right now


Mark

There can be unpredictable problems writing to worksheet modules in the same
workbook that contains the code that's writing the changes. This can lead to
crashing Excel. It might depend on what changes are being made and possibly
if the sheet that's being modified is also the active-sheet. Whatever the
reason, the underlying cause is probably due to the project trying to
recompile while code is running.

Regards,
Peter T

"perf" wrote in message
...
Hi,
yes yes, I only write lines if the module is empty, otherwise I delete
all
lines and then I write them.
your link was useful, and I made some change in the code, that (apparently
without reason) allowed me to write in the sheet1 module if the module is
originally empty.
But I still have that problem if the module is not empty: after deleting
the
rows, as soon as I try to write in the module, there is the automation
error.

moreover, I am noticing that the SAVE command is not working properly
with
.xls. Sometimes it saves the fresh code in the module, sometimes not.
randomly.


this is sooooo unpredictable... not good at all : (

thanks
"Barb Reinhardt" wrote:

You may find something here.

http://www.cpearson.com/excel/vbe.aspx

I've not done anything like what you are doing, but I wonder if you
already
have some lines in the code module and are trying to insert lines where
some
already exist. Have you checked that?
--
HTH,

Barb Reinhardt



"perf" wrote:

Hi,
I'm having a quite strange problem in a VBA macro that I made that is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1", I
always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for
remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the
first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub
Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I
tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set
line:
useless


Can you help me somehow?

Thank you so much right now


Mark

yes, it can be as you say.
do you think there can be a solution to this? for istance, disabling each
type of operation when writing code? I do not know if you can do it, though.

by the way, the code in the worksheet is written by a macro xla, so
something 'outside' the workbook itself.

you know... this unpredictability makes the product completely useless.
would be almost better to have a product that always crashes under certain
conditions, rather than have one that crashes sometimes. I can't give it
around to customers, under these conditions!


thanks,

regards

mark


"Peter T" wrote:

There can be unpredictable problems writing to worksheet modules in the same
workbook that contains the code that's writing the changes. This can lead to
crashing Excel. It might depend on what changes are being made and possibly
if the sheet that's being modified is also the active-sheet. Whatever the
reason, the underlying cause is probably due to the project trying to
recompile while code is running.

Regards,
Peter T

"perf" wrote in message
...
Hi,
yes yes, I only write lines if the module is empty, otherwise I delete
all
lines and then I write them.
your link was useful, and I made some change in the code, that (apparently
without reason) allowed me to write in the sheet1 module if the module is
originally empty.
But I still have that problem if the module is not empty: after deleting
the
rows, as soon as I try to write in the module, there is the automation
error.

moreover, I am noticing that the SAVE command is not working properly
with
.xls. Sometimes it saves the fresh code in the module, sometimes not.
randomly.


this is sooooo unpredictable... not good at all : (

thanks
"Barb Reinhardt" wrote:

You may find something here.

http://www.cpearson.com/excel/vbe.aspx

I've not done anything like what you are doing, but I wonder if you
already
have some lines in the code module and are trying to insert lines where
some
already exist. Have you checked that?
--
HTH,

Barb Reinhardt



"perf" wrote:

Hi,
I'm having a quite strange problem in a VBA macro that I made that is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1", I
always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for
remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the
first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub
Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I
tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set
line:
useless


Can you help me somehow?

Thank you so much right now


Mark


.

If the code writing the changes is in a different project I don't have an
explanation for your problems, not the same as I had in mind.

Post enough code to replicate the problem with details how you are call in
the code. On which point, if you are calling the code in the xla from the
workbook that the xla will change, that in effect as the same as the code
being in the same project. So, give details.

Regards,
Peter T


"perf" wrote in message
...
yes, it can be as you say.
do you think there can be a solution to this? for istance, disabling each
type of operation when writing code? I do not know if you can do it,
though.

by the way, the code in the worksheet is written by a macro xla, so
something 'outside' the workbook itself.

you know... this unpredictability makes the product completely useless.
would be almost better to have a product that always crashes under certain
conditions, rather than have one that crashes sometimes. I can't give it
around to customers, under these conditions!


thanks,

regards

mark


"Peter T" wrote:

There can be unpredictable problems writing to worksheet modules in the
same
workbook that contains the code that's writing the changes. This can lead
to
crashing Excel. It might depend on what changes are being made and
possibly
if the sheet that's being modified is also the active-sheet. Whatever the
reason, the underlying cause is probably due to the project trying to
recompile while code is running.

Regards,
Peter T

"perf" wrote in message
...
Hi,
yes yes, I only write lines if the module is empty, otherwise I delete
all
lines and then I write them.
your link was useful, and I made some change in the code, that
(apparently
without reason) allowed me to write in the sheet1 module if the module
is
originally empty.
But I still have that problem if the module is not empty: after
deleting
the
rows, as soon as I try to write in the module, there is the automation
error.

moreover, I am noticing that the SAVE command is not working properly
with
.xls. Sometimes it saves the fresh code in the module, sometimes not.
randomly.


this is sooooo unpredictable... not good at all : (

thanks
"Barb Reinhardt" wrote:

You may find something here.

http://www.cpearson.com/excel/vbe.aspx

I've not done anything like what you are doing, but I wonder if you
already
have some lines in the code module and are trying to insert lines
where
some
already exist. Have you checked that?
--
HTH,

Barb Reinhardt



"perf" wrote:

Hi,
I'm having a quite strange problem in a VBA macro that I made that
is
driving me crazy. First of all, I am using Excel 2003.
Well, when I try to insert lines of code in vba worksheet "sheet1",
I
always
get an Automation Error that causes the restart of excel.
The precise error is:
"Automation Error. connection to type library or object library for
remote
process has been lost. Press OK for dialog ot remove reference."


The error always happens at the SECOND insertline instruction (the
first
line in sheet1 is written correctly) in the following code:

----...----

Set VBCodeModmej =
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule
VBCodeModmej.InsertLines 1, "Private Sub
Worksheet_SelectionChange(ByVal
Target As Range) "
VBCodeModmej.InsertLines 2, "Dim Touched As Range"
VBCodeModmej.InsertLines 3, "Dim void As String"
etc etc

---...---


Note that with Office 2007 this never happens ...!

I tried the following things, read here and there for the network (I
tried
them one at a time ...):
-Put option explicit: useless
-Put on error resume next: useless
-Put Instead of 'private sub' the string 'sub': useless
-Put Application.EnableEvents = True: useless
-Put Application.OnTime Now + TimeValue("00:00:10") before the Set
line:
useless


Can you help me somehow?

Thank you so much right now


Mark


.