Hi Frederick
If you call your ChildProc with OnTime the calling proc will complete before
ChildProc starts.
If I understand your question you want to initiate deletion of all VBA in a
workbook from within that workbook by calling code in another book. Perhaps
something like this -
' in the wb with the VBA to delete
Sub DelMyVBA()
Application.Run "Book3.xls!module1.Test", ThisWorkbook.Name
End Sub
' in Book3.xls
Dim msWBname As String
Sub Test(s As String)
msWBname = s
Application.OnTime Now, " DeleteAllVBA "
End Sub
Sub DeleteAllVBA()
'http://www.cpearson.com/excel/vbe.htm
' adapted for Late Binding
Dim VBComp As Object 'VBIDE.VBComponent
Dim VBComps As Object 'VBIDE.VBComponents
On Error Resume Next
Set VBComps = Workbooks(msWBname).VBProject.VBComponents
If VBComps Is Nothing Then Exit Sub
On Error GoTo 0
For Each VBComp In VBComps
Select Case VBComp.Type
'Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
Case 1, 3, 2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
' Workbooks(msWBname).Save
End Sub
I'm sure it should be possible to pass a string variable with OnTime. No
problem to pass numbers, number variables or literal strings but I can't get
the syntax to pass a string var, hence use the module var msWBname.
You say you want to call this from the BeforeSave event so I imagine you
will want to set Cancel = true. You might also want to cater for the
possibility of user wanting to cancel the Save.
Regards,
Peter T
"Frederick Chow" wrote in message
...
Hi Peter,
Mind elaborating on the relevance of Application.OnTime method to my
problem? Thanks.
Frederick Chow
Hong Kong
"Peter T" <peter_t@discussions wrote in message
...
Hi Fredrick,
Have a look at Application.OnTime
Regards,
Peter T
"Frederick Chow" wrote in message
...
Hi Niek,
Glad to tell you more detail about this.
ProcParent is a WorkBook_BeforeSave which will call a subroutine,
ProcChild,
located in another workbook, whose job is to destroy all codes in the
workbook where ProcParent is located.
So at the time of finishing running ProcChild, the ProcParent will
never
exist, and that's why I don't want control to be returned to a
non-existent
ProcParent.
Any advise from this?
Frederick Chow
Hong Kong
"Niek Otten" wrote in message
...
Hi Frederick,
Maybe you should tell us a bit more about what you're trying to
achieve;
there may be alternatives that are acceptable to you.
Not returning to immediately after the call is generally considered
(very)
bad practice and is even impossible in many programming languages.
--
Kind regards,
Niek Otten
"Frederick Chow" wrote in message
...
Hi all,
Suppose I have to subroutines, ProcParent and ProcChild. ProcParent
calls
ProcChild. For some reasons, I don't want the control to be passed
back
to ProcParent after Executing ProcChild. I have searched through the
VBA
help and I found that both END statement and STOP statement placed
in
the
ProcChild could do the job, but they have side effects: END will
reset
any module-level variables, which certainly I don't want, and the
STOP
statement will just leave the VB editor in (undesired) break mode.
Are
there any other options? Please advise.
Frederick Chow
Hong Kong.