Leaner - Proper sub procedure calling technique?

[jdttek]

I am developing my 1st full fledged Excel program and have a question on proper coding etiquette for sub-procedure calls.

For example. I have a module that opens a workbook, formats it and then saves it. I have broken it down into 3 sub-procedures with appropriate code for each:
Sub Open ()
Sub Format ()
Sub Save ()

Question 1: Is it better to have one main procedure that calls the 3:
Sub Main ()
Call Open
Call Format
Call Save
End Sub

OR

Have each sub Procedure call the next.

Question 2:
The 1st option appears easier to follow HOWEVER, I find that if there is an error in, say Sub Open () (e.g. file does not exist) and I end the Open()procedure with an error handler in Open (), then Sub Main will still try to continue with Format()and Save(). If I use Option 1, how do I stop Main() entirely if there is an error in any of the called procedures.

An earlier posting had suggested switching the sub procedures to Functions to test completion of each step. I am still experimenting with how to do this but wonder if there is another alternative.

Any advice greatly appreciated.

Thanks
jdttek

 

[RobBroekhuis]

I wouldn't use the method #2. It confuses things, and allows almost-completed subs to remain dangling until all subs that are subsequently called are completed. If you need more help with the approach we discussed yesterday, let us know.
Rob

 

[jdttek]

Rob
Thanks for your reply and interest in helping nme learn.

I converted to:

Sub Main()
Function Open()
Function Format()
Function Save ()
End Sub

as you suggested.

I then put [xFunctionx] = True
On Error GoTo Handle
:
:
Handle:
MsgBox "Error " & Err & " " & Err.Description
Exit Function
in each of the functions.
Each of the functions work as they should (individually), if no error.

Then, if I delete the file required in Function Open ()(to force an error), the error is trapped and the function ends. BUT, since I called the function from Sub Main(), flow then takes me back to Sub Main() and starts to process Function Formatt(), but obviously can't find file to format, since file was never opened. That is where I get confused. Looks like I need something in Sub Main() to stop Sub Main()in it's tracks if error is found in Function Open() that ended Function Open().

What do I put in Sub Main() to test successful completion of each Function xxxxx ()so, if false, it ends Sub Main()?


Thanks again

jdttek

 

[RobBroekhuis]

In its most simple implementation, if you just have the three subs, you could do

Sub Main()
if not Open(...) then exit sub
if not Format(...) then exit sub
Save(...)
End Sub

Note that Save, in this case, doesn't need to be a function, since your main sub doesn't care whether it was successfully completed. If there are some things you need to take care of at the end of the sub regardless of whether the open sub was successful, you may need something like:

Sub Main()
if Open(...) then
if Format(...) then Save ()
end if
...cleanup code here...
End Sub

As I said before, there are many ways to do this kind of thing, and the best way is both a matter of preference and a function of exactly what you need to accomplish.
I hope that helps.
Rob

 

[jdttek]

Thanks Rob.

One last (and probably dumb) question. What (type of) info goes in the (...)? True? False? as return from functions?

jdttek

 

[RobBroekhuis]

No, if your functions have no arguments, then you'd simply use Open(). I was leaving the possibility of the main sub passing arguments to your other functions.
Rob