.insertlines automation error excel crash

[perf]

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(ActiveWorkbook.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

 

[Barb Reinhardt]

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?

 

[perf]

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

 

[Peter T]

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]

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]

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

 

[Rene]

Hi,

New to the forum, so forgive me if I duplicate.

I also had similar problems, where excel crashes when starting to
build up text in a sheet.

I found out the reason for it not working is that Excel screens the
data you put in, and if not complete, it provides the error you would
get when you were to manual enter in.

So, starting with "Public sub xxxxxx ()" will cause an error, (missing
end sub).
(try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't
help us if we need code in the sub.

What I do now is start building the text from the back, and always do
an "InsertLines 1, text"
That seems to do the trick.

 

[Peter T]

That's a very interesting idea and makes perfect sense! It's also entirely
consistent with what I suggested in my first post here, about the project
attempting to recompile while other code was running.

Just one thing though, I would only expect the crash problem to occur when
code is writing changes to "self", if also the activeworkbook/sheet also may
make things even more prone for worse for some reason.

Thanks for posting.

Peter T

 

[perf]

oh thanks!!!!

this can help me a lot!

so, do you suggest to do this?

..InsertLines 1, "end sub"
..InsertLines 1, "code line n"
..InsertLines 1, "code line n-1"
....
..InsertLines 1, "private sub ()"


very clever. I'll try soon to do it and I let you know: )

mark

 

[Peter T]

I haven't tested myself but try something like this -

Dim i as Long
Dim LinesCount as Long
Dim arrLines() as String

Redim arrLines(1 to LinesCount)

' populate the string array with lines of code to be inserted in normal
order
arrLines(1) = "Public sub xxxxxx ()"
'' etc
arrLines(LinesCount) = "End Sub"

for i = ubound(arrLines) to 1 step -1
..InsertLines 1, arrLines(i)
next

' hold your breath!

Regards,
Peter T

 

[perf]

Hi peter!
I held my breath.. but it was not enough! I can write all the code lines but
the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I
obtain an automation error, like before.
I this doesn't happen always, expecially it's ok when I do it in debug mode,
proceeding line by line writing the code.


what a pity, this solution was very very clever...

thanks anyway for your support!

mark

 

[Peter T]

Explain clearly, is there any connection between the code workbook (the one
that's writing the changes) and the workbook that's having its project
modified, that includes the "modified workbook" calling code in some other
workbook.

If not, is there any problem to modify a brand new workbook.

Is there something else going on that we are not aware of.

If nothing above is of relevance post the ENTIRE code.

Regards,
Peter T

 

[perf]

Dear Peter,
Maybe I just solved the problem with a trivial solution. I noticed that
deleting the possible code present in sheet1, then save the file, close it,
reopen it and at that stage write the new code from down to up seems to work
well. at least so far.
I do not why, but the combination of saving, closing and writing in a
reverse way it's good.

answering to your question, posting all code would require too much time,
because the xla module are many. I can say that a connection between the xla
and the file is there, because the xla opens a dialog menu that searches in a
database and allows to open from the menu the selected file. and then it
modifies the code... it sounds a little complicated, I know...

but now it works, this is the most important thing!

thank to you all


mark

: )

 

[Peter T]

I don't understand what you are saying here

I can say that a connection between the xla
and the file is there, because the xla opens a dialog menu that searches
in a
database and allows to open from the menu the selected file.

Are you saying code in the file that will be modified calls the xla which in
turn modifies the code. If so that is the reason for the problem (as I have
been trying to say all along), and there is probably a better solution.

Regards,
Peter T

 

[perf]

Not exactly... better explanation:

I load an xla that manage excel file in some directories. the xla opens a
toolbar with some blocks to draw in these file and shows a menu where I can
create a new sheet (so, a new file), delete one of them ect ect. all these
file are listed in an access database, and everytime the xla shows in the
menu the files present it reads in the access database. all these files have
code in the sheet1 module, so when I create a new one, the xla creates the
code in the new sheet1 module.
but sometimes I have to modify the code in the sheet, so I open the menu via
xla and I delete the code and I rewrite it.

this last modification is what causes the automation error.

so, everything is managed by the xla, so it's not true that there is no link
between xla and excel file. but it's not true that the code added in the
sheet1 recall the xla or something like this.

I hope the structure is a little clearer for you now. : ) it's not very easy
to explain, i know.

thanks for your patience

mark