CreateEventProc for OLE button crashes Excel

[Jag Man]

I have been having crashes and finally narrowed down the problem to
something
specific enough to post the question.

The procedure below is intended to be called from a menu choice I've added
to the Excel tool bar.
It adds a worksheet with a button, and clicking the button is supposed to do
something, here
just throw up a message box. It works fine if I execute it in Debug by
placing the cursor
at the end and doing ctrl-F8 (run to cusor). The sheet is added and button
works. However,
If I execute it from the menu choice on the tool bar it crashes Excel
immediately
upon return from the call to BuildWorksheet. To be more specific, in the
code for a form
that gets invoked by the menu choice there is:
....
Call BuildWorksheet
MsgBox ("Ret from BuilsWorksheet")
.....
The message gets printed, and it crashes.

Sometimes it even corrupts the Windows kernel so I have to reboot and suffer
through a disk scan.

To further narrow the problem, I have discovered that it will not crash if I
omit the code where
the CodeModule is modified (see "Create the code" at the end).

Any suggestions greatfully received.

TIA

Ed

Public Sub BuildWorksheet()
Dim WSName As String
Dim WSType As String
WSName = "xyz"
WSType = "type1"

Dim wks As Worksheet
Dim Present As Boolean
Present = False
For Each wks In Worksheets
If wks.name = WSName Then
Present = True
Exit For
End If
Next wks

If Present Then ActiveWorkbook.Worksheets(WSName).Delete
ActiveWorkbook.Sheets.Add Type:=xlWorksheet
Debug.Print ActiveWorkbook.ActiveSheet.CodeName
ActiveWorkbook.ActiveSheet.name = WSName
Names.Add name:=WSName & "!WSType", RefersTo:=WSType
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ActiveWorkbook.ActiveSheet
' Create The Button
Set Btn = WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=WS.Range("D3").Left, Top:=WS.Range("D3").Top, _
Width:=95, Height:=40)

Btn.Object.Caption = "Calculate " & WSType
' Create the code
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
StartLine = .CreateEventProc("Click", Btn.name) + 1
.InsertLines StartLine, " MsgBox(" & Chr(34) & "calc here" &
Chr(34) & ")"
End With
End Sub

 

[onedaywhen]

Usually, when something works when you step through the code but
doesn't under normal execution, then you have a timing issue. Stepping
through code, even just entering break mode, slows processes down,
allowing things to complete before execution continues to the next
task. In theory, a liberal smattering of DoEvents should take care of
things but you can incorporate deliberate pauses in your code using
the Sleep API. Another approach is to use Excel's OnTime method, which
reminds me...

I'm not sure if this is *directly* relevant but a while ago I had code
which added a ActiveX optionbutton to a worksheet and assigned it to a
withevents property in a custom class, but the event subsequently
failed to fire in the class. Hard work by Excel MVP Dick Kusleika came
up with one resolution: have two subs, the first created the
optionbutton, the second assigned it to the class. The second sub was
called from the first using OnTime method. IIRC it was the fact they
were in separate subs that was relevant, rather than the delay the
OnTime introduced. Here's the thread:

http://groups.google.com/[email protected]

--

 

[Jag Man]

Thanks. I've read over Dick's saga, and will read it again until I
understand it.
One thing that caught my attention is his mention of the loss of ability to
single step the program once something has been added. I noticed that,
and after reading the associated Help concluded that it was a consequence of
the
debugger not being able to deal with the "ground shift" of changing code...
not
too surprising.

I'm not sure my problems are with timing, but will investigate.

Thanksfor your thoughtful reply.

Ed

 

[Jag Man]

I have "fixed" the problem, at least in the sense that it doesn't crash
anymore.
The quotes are because I'm not sure why what I did made it stop crashing,
so if there is something more subtle still there it may come back to bite me
later.

To explain the "fix" I have to elaborate on how I was calling the affected
procedure
from a Menu.

I added a menu to the Excel menu bar, and with choices AddLibrary and
AddComponent.
The VBA code that adds the menu is in a standard module:

With muCustom
.Caption = "&SparkEx"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Add Library"
.OnAction = "AddLibrary"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Add component"
.OnAction = "AddComponent"
End With
End With


Under the AddComponent choice is code (in the same module as above) that
loads a form to gather data:

Private Sub AddComponent()
Load fmAddComponent
fmAddComponent.doTheForm
fmAddComponent.Hide
' Unload fmAddComponent 'this causes a crash!
End Sub

As you can see, the code that processes the form is in a procedure called
doTheForm. In that
procedure my other procedure BuildWorksheet (shown in the original post)
gets called.

The crashed was being caused by the Unload of the fmAddComponent form. If I
hide it instead,
no crash.

Curiously, the other menu choice, AddLibrary, is structured the same way,
but it DOES NOT
crash if I unload the form! And yes, I do create a code module in it too.
The major difference
is that in that case the code module created is a standard module, having
nothing to do with
OLE or button events.

My guess is that when the code module for control action events is created
some use is made of the stack
rather than the heap, so the pointer to the code module is pointing at
nothing after the form is closed.

Ed