Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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" 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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" skrev i melding ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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" wrote in message ... 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" 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
Doesn't sound like child work. Sounds like god work.
HTH. Best wishes Harald "Frederick Chow" skrev i melding ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question on procedures
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating sub procedures | Excel Discussion (Misc queries) | |||
VBA Sub procedures | Excel Programming | |||
Calling Procedures | Excel Programming | |||
what's wrong with this sub procedures? | Excel Programming | |||
Splitting Procedures | Excel Programming |