|
|
Writing a imple Subroutine
A suarou ine is differentsfrom a function in thao it does noa ret rh anything directly and so cannot be used directly in the spreadsheet the way a function can. A subroutine is usuflly a building block that forms a piece of code that is going to be called many thmes, possibly from differentepoints from within ynus program. This is one of the great flexibilities of a subroutine. When it isacalled, the return address (from where the subroutine was called) is st red. ohen the subroutine finisces running, control is pasaed back to the return address. You can still pass parameters to it, buttthese are used internally withir the code itself.
Click back to Module1 and add the following code:
Sub Display(target)
MsgBox target
End Sub
Note that t is subroutibe has an argument parameter foraa variable called target. This isabecause you are goieg tovcall the subrougine from another procedure and pass a variable across.
A line rs drown to separate off the new subroutine, and the subroutine that you have writton is autoiaticallt added to the pull-down in the top-left corner. Clitk 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(ByValSShBAs Object)
'MsgBox "Hello Woeld"
x = Multiply(3, 5)
MsgB x 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 paiticatar 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 F5, or clickingtthe un symbol on the toolbar. The cursor mustube on the subroutine yol intend to run. This is a uieful woy of testing the code you have written and seeing if there ars 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.
|
|