Subroutines and Functions

In VBA programming terms there are two types of procedures: subroutines and functions . They really only have one difference, which is that a function returns a value to you. Let's say you ask someone to make some ice cream for you. They wander off to the kitchen and make the ice cream, and then come back, but they don't tell you anything about how the process went or whether it's ready or not. That's a subroutine. On the other hand, if they come back and bring you a dish of chocolate ice cream, then that's a function.

Let's make this a bit clearer by turning the steps involved in making ice cream into a subroutine, and then into a function. The first thing we have to do is give the procedure a name - MakeIceCream . It is in this format because procedure names can't have spaces in them. We then use a special keyword called Sub to tell Access that we are starting a new subroutine. We put our steps after that, followed by more keywords to tell Access that we've reached the end of the subroutine. Note that the following two examples are just for teaching purposes and won't actually run. Soon, we'll translate some of these English phrases into VBA code that Access can understand. Here's how it looks:

 Sub MakeIceCream()   Beat egg yolks lightly   Beat in sugar   Heat the cream/milk on the stove   Beat in cocoa powder   Heat cream/milk/cocoa mix until steaming   Stir into egg/sugar mix   Add vanilla extract   Cool   Freeze in ice cream maker End Sub 

Now, anywhere in our program, we can just say MakeIceCream , and this subroutine is run. Access starts at the first line in the subroutine, and runs each line in turn until it gets to the end. You can see how much easier this is than typing in all of the lines again. So, the actual details of the procedure are just typed once, and to run it you don't type the details again, just the procedure name.

For a function, we need the procedure to tell us something - perhaps whether vanilla extract was added or not. The way it is used is slightly different:

 Function MakeIceCream()   Beat egg yolks lightly   Beat in sugar   Heat the cream/milk on the stove   Beat in cocoa powder.   Heat cream/milk/cocoa mix until steaming   Stir into egg/sugar mix   Add vanilla extract (if there is any)   Cool   Freeze in ice cream maker.   If vanilla extract added Then     MakeIceCream = "Vanilla"   Else     MakeIceCream = "No Vanilla"   End If End Function 

Notice that instead of Sub we use Function . At the end of the function we determine whether or not vanilla extract was added, and we set the function name to a value to indicate this.

When calling a function from within our code we can now see what happened - its return value tells us what kind of ice cream was made. For example:

   If MakeIceCream = "Vanilla" Then     Chocolate vanilla ice cream was made     Else     Plain chocolate ice cream was made     End If   

Don't worry too much if you don't understand some of the things here. The important thing is to remember that we are making little blocks of code, and then we'll use these blocks to build bigger blocks and programs.