A question on procedures

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.

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" <[email protected]> wrote in message news:[email protected]...
> 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.
>

I would declare ProcChild as a Function, not a Sub, and have it
return a value indicating whether ProcParent should continue
running. For example,

Function ProcChild() As Boolean
' code
ProcChild = False
End Function

Sub ProcParent()
' code
If ProcChild = False Then
Exit Sub
End If
' code
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Frederick Chow" <[email protected]> wrote in
message news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>
>

Hi Frederic

If you want ProcChild to consider something of global interest (like "cancel
the rest of the operation" or "do this sinstead") then make it a function
instead of a sub and let the caller do the decision. A function will return
a value of the kind youy declare it as (here Boolean True/False). See if
this little demo makes sense:

Sub ProcParent()
MsgBox "Starting now"
If ProcChild = True Then
MsgBox "We do this"
Else
MsgBox "We do that"
End If
End Sub

Function ProcChild() As Boolean
If Weekday(Date) > 4 Then
MsgBox "Weekday too big"
ProcChild = False
Else
MsgBox "Weekday is fine"
ProcChild = True
End If
End Function

HTH. Best wishes Harald

"Frederick Chow" <[email protected]> skrev i melding
news:[email protected]...
> 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.
>
>

Thanks for your advice; I know this trick, but my case is really special,
for the job of ProcChild is to destroy the all the codes where ProcParent is
in! Needless to say, by the time ProcChild has finished, ProcParent will
cease to exit, so how can I allow control to be returned to a non-existent
parent?

Wish you could advise on my issue futher.

Frederick Chow
Hong Kong.

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
>I would declare ProcChild as a Function, not a Sub, and have it return a
>value indicating whether ProcParent should continue running. For example,
>
> Function ProcChild() As Boolean
> ' code
> ProcChild = False
> End Function
>
> Sub ProcParent()
> ' code
> If ProcChild = False Then
> Exit Sub
> End If
> ' code
> End Sub
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Frederick Chow" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>
>

Doesn't sound like child work. Sounds like god work.

HTH. Best wishes Harald

"Frederick Chow" <[email protected]> skrev i melding
news:[email protected]...
> Thanks for your advice; I know this trick, but my case is really special,
> for the job of ProcChild is to destroy the all the codes where ProcParent
is
> in! Needless to say, by the time ProcChild has finished, ProcParent will
> cease to exit, so how can I allow control to be returned to a non-existent
> parent?

Hi Fredrick,

Have a look at Application.OnTime

Regards,
Peter T

"Frederick Chow" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >
> >
>
>

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
news:[email protected]...
> Hi Fredrick,
>
> Have a look at Application.OnTime
>
> Regards,
> Peter T
>
> "Frederick Chow" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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.
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
> > Hi Fredrick,
> >
> > Have a look at Application.OnTime
> >
> > Regards,
> > Peter T
> >
> > "Frederick Chow" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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.
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>