Thread: Sub handling in VBA

Does VBA create a Sub Main() the way VB does if you don't include it in your code? I ask because I've been using a procedure called MainProcedure to call all my other subs from, and the program seems to run fine, but since I have no Main shouldn't the program run all my procedures twice? Once when called from MainProcedure, and again when going top-to-bottom through each sub as entered?

Originally Posted by scudder12

I've been using a procedure called MainProcedure to call all my other subs from, and the program seems to run fine, but since I have no Main shouldn't the program run all my procedures twice?

No, VBA appears to run the code in the order it is encountered. Subroutines inside other subs will complete then return to the same point in the original sub and proceed logically and in order until the code ends or aborts.

Originally Posted by scudder12

So, if my code looks something like this:

Sub Main()

   Call Sub1()
   Call Sub2()
   End

End Sub

Sub Sub1()
   ...
End Sub

Sub Sub2()
   ...
End Sub

Then the Main() will call the two subs in order and the end statement will terminate the program, preventing it from running sub1 and sub2 again, correct?

That is correct. Listing the related subs in the same module is completely safe.

The End statement is unnecessary (and should be avoided on its own). The main sub will simply stop at end sub anyway.

Originally Posted by parrynz

IndHowever, from an error handling perspective there may be times when you want to use End in called procedures to prevent further processing.

I would tend to disagree with that. Either use a Function (or passed variable) that indicates success, or don't error trap in the called sub and allow the calling sub to do it. End is too brutal for my liking as it kills your variables.

The way I think about it is : if I were designing a (simple) spreadsheet program I would need a form with a grid control (in lieu of full spreadsheet functionality) and some buttons.

Main displays my form with the grid control and the user doesn't need access to that. But I will allow them to write macros to tie into events related to the form eg buttonClick, formClose.

So in Excel main starts excel provides the UI etc. Whereas VBA just provides user defined code for methods/events. You can have macros that run on Worksheet_Open, Cell_Change, Before_Save etc When these terminate they don't terminate the program they just return to "waiting for user input". The oddity appears to be when you directly run a sub. But this is still just running an event created by user input.

The END only occurs when you close down Excel.

I agree with RomperStomper you should never need to use END (on its own). If you think you do it is nearly always poor coding elsewhere.

That's correct. VB behaves the same way, incidentally, as I believe does C++.

Originally Posted by parrynz

Yes I agree. My intention was to demonstrate what happens when you use it as the Op has a VB background. I use error handling within my main procedures myself not End but nevertheless the option is still available.

A bit like closing Excel by killing its process though.

Are you on holiday from MrExcel?

Originally Posted by parrynz

I created a FIFA World Cup spreadsheet which I posted in the Development forum so I thought it would be a tad rude to just post that and not contribute to the site.

I saw (and downloaded) that in the Lounge yesterday - along with Richard's impressive optimism... Looks very good, so thanks!