Code into MS Object Sheet Hangs up

[donbowyer]

Excel 2003. Win XP
The subroutine below is called to programatically write code into a
worksheet object sheet that is added to the sheets collection with an add
sheet macro.
On first use, it hangs at the second line Set SheetCodeModule.
If I delete the new sheet and run the add sheet routine again, the
WriteCode() subroutine works fine and the code now appears in the sheet
object.

Sub WriteCode()
MySh = ActiveSheet.CodeName
Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModule
With SheetCodeModule
CodeLine = .CountOfLines + 1
.InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _
"Application.ScreenUpdating = False" & Chr(13) & _
"If MyNewSheet = ""Running"" Then" & Chr(13) & _
"MyNewSheet = ""NotRunning""" & Chr(13) & _
"Go To Line2" & Chr(13) & _
"End If" & Chr(13) & _
"Call Load" & Chr(13) & _
"Line2:" & Chr(13) & _
"End Sub"
End With
Set SheetCodeModule = Nothing
End Sub

Any suggestions as to what is wrong would be welcome.

 

[Per Jessen]

Hi

What do you mean saying that the macro hangs?
Does it comes up with an error, and what does it says?

I tried your code in excel 2000, even in a new sheet that i just added by
macro and then activated before ending the macro. It never caused any
trouble.


Btw: You might want to set screenupdating=true in the macro you add by the
first macro.

//Per

 

[donbowyer]

Hi Per
The macro stops running at the line Set SheetCodeModule. and the error
message <<Runtime error 9>> and <Subscript out of range>> is displayed.
If I run my add worksheet routine on its own, without the routine which adds
code to the worksheet object, then the new sheet is created without error.
If I then run the add code routine separately, it runs ok and the required
code is added.
But if the add code routine is called from the add worksheet routine, then
the macro stops as above.
Adding screenupdating=true to the second macro had no effect.
I'm still baffled!!

 

[Per Jessen]

Hi

I can not reproduce the error with the code below. Try to make a
breakepoint at the line " Set SheetCodeModule =..." and check the
value of MySh. Is the value what you expect it to be?


Sub AddSheet()
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Call WriteCode

End Sub

Sub WriteCode()
MySh = ActiveSheet.CodeName
Set SheetCodeModule =
ThisWorkbook.VBProject.VBComponents(MySh).CodeModule
With SheetCodeModule
CodeLine = .CountOfLines + 1
.InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _
"Application.ScreenUpdating = False" & Chr(13) & _
"If MyNewSheet = ""Running"" Then" & Chr(13) & _
"MyNewSheet = ""NotRunning""" & Chr(13) & _
"Go To Line2" & Chr(13) & _
"End If" & Chr(13) & _
"Call Load" & Chr(13) & _
"Line2:" & Chr(13) & _
"Application.ScreenUpdating = True" & Chr(13) & _
"End Sub"
End With
Set SheetCodeModule = Nothing

End Sub

Regards,

Per

 

[donbowyer]

Hi Per
I put a BP on the line Set SheetCodeModule.......
The value of MySh was "sheet1" as expected.
On reaching this break point the new sheet1 has been created OK.
If I then hit Run, to complete execution of the macro, it runs to the end
without error, and the required code is written into the sheet.
??

 

[Per Jessen]

Hi donwb

Your error will (only) occure when the value of MySh isn't referring
to at sheet in the active workbook. If i set MySh = "Dummy" then I get
the error you mentioned earlier.

//Per

 

[donbowyer]

Hi Per
Yes - I agree with you.
But since MySh gives the expected result, what I am wondering is something
like this:-
I run the macro that generates the new sheet, and even though the new sheet
appears immediately in the workbook, maybe its information or data is not
actually available for further use until the generating macro finishes. If
that was the case, then the code loading subroutine, if run as a Call from
the sheet generating subroutine, would not <<Find>> MySh. Whereas, if it is
started separately as a Subroutine in its own right, then it works.
Note that I do have MySh declared as a Public variable.
The problem with this is that I cannot believe this is how Excel VBA works.

 

[Per Jessen]

Hi donwb
I just don't get it. As you can get the name of the new sheet, I am sure
that you have the full control of it.

Try to add this line after mySh = Active...

Debug.Print MySh

then open the Immediate window (ctrl+G) before you run the subroutine
generating the new sheet.

Is the value in the Immediate window "Sheet1" as expected? If it is the
subroutine adding the code should work for sure.

Have you tried to make the code loading Subroutine a part of the sheet
generating subroutine.

It shouldn't be nessecary to declare MySh as a Public variable, as you
assign a value to MySh in the Subroutine where you use it.
I agree with you Excel VBA shoulden't work this way.

//Per

 

[donbowyer]

Hi Per
I did try all you suggested in your last posting, but results were the same.
However think I have found the problem.
In the declarations before the code adding subroutine there were two missing:-
Dim VBProj As VBProject
Set VBProj = ThisWorkbook.VBProject
With these in it now works fine - take them out and I get the error message.
Thanks for all your inputs.
It serves to show that declarations are important!!