Writing a Simple Subroutine

A subroutine is different from a function in that it does not return anything directly and so cannot be used directly in the spreadsheet the way a function can. A subroutine is usually a building block that forms a piece of code that is going to be called many times, possibly from different points from within your program. This is one of the great flexibilities of a subroutine. When it is called, the return address (from where the subroutine was called) is stored. When the subroutine finishes running, control is passed back to the return address. You can still pass parameters to it, but these are used internally within the code itself.

Click back to Module1 and add the following code:

 Sub Display(target) 
 MsgBox target 
 End Sub 

Note that this subroutine has an argument parameter for a variable called target . This is because you are going to call the subroutine from another procedure and pass a variable across.

A line is drawn to separate off the new subroutine, and the subroutine that you have written is automatically added to the pull-down in the top-left corner. Click the This Workbook object and return to the initial Hello World example from Chapter 1. On the Workbook_Newsheet event, add the following code:

 Private Sub Workbook_NewSheet(ByVal Sh As Object) 
 'MsgBox "Hello World" 
 x = Multiply(3, 5) 
 MsgBox x 
 Call Display("my subroutine") 
 End Sub 

Now, click the Excel worksheet and choose Insert Worksheet from the menu. You will see the message box showing 15 followed by a message box showing ‚“my subroutine. ‚½

The Call command calls your subroutine and passes any required parameters to it. It then executes the code in the subroutine and returns to the next instruction following the Call statement. In this particular case, it passes the string ‚“my subroutine ‚½ into the variable called target .

If the subroutine that you have written does not use parameters (arguments), you can run it from the code page by selecting Run Run Sub UserForm from the VBE (Visual Basic Editor) menu, pressing F 5, or clicking the Run symbol on the toolbar. The cursor must be on the subroutine you intend to run. This is a useful way of testing the code you have written and seeing if there are any bugs in it.

Subroutines are a useful way of breaking large projects down into manageable pieces so that you do not end up with enormous , cumbersome routines. It is far easier to break a problem into constituent parts and work separately on each section, making sure you get that section working properly before moving onto other parts . The alternative is to write a large chunk of code, which inevitably leads to unnecessary duplication.