MS-Access / Getting Started

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 code the way a function can. A subroutine is usually a building block that forms a piece of code that will be called many times, possibly from different points 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 differentiate the new subroutine, and the subroutine you have written is automatically added to the drop-down in the top-left corner. Now return to the initial Hello World example and add the following code:

Sub MyCode()
'MsgBox "Hello World"
x = 3 * 5
MsgBox x
Call Display("my subroutine")
End Sub

Make sure you have already defined the function called "Display," otherwise you will get an error when you try to run MyCode.

Click the mouse cursor anywhere on the MyCode procedure and then click the Run icon on the VBE toolbar or press F5. 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 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 clicking the Run symbol on the toolbar. The cursor must click on the subroutine you intend to run. This is a useful way of testing the code you have written and seeing if it has any bugs.

Subroutines are a useful way of breaking large projects down into manageable pieces so you do not end up with enormous and 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 others. The alternative is to write a large chunk of code, which inevitably leads to unnecessary duplication.

[Previous] [Contents] [Next]