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" wrote:

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" wrote in message
...
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" wrote:

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


"Rene" wrote in message
...
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.


.



.