Subroutines and Functions

Pretty much every programming language has subroutines and functions. Both hold pieces of your overall code and work essentially the same except that with a function, you typically want to return a value or an array of values. There are ways of returning values with Subroutines also, but I don’t want to get ahead of myself. Another difference is that you can define a function in your code module that will act just like a built-in Excel formula. More on that later.

To help you understand the difference between Subroutines or “Sub” procedures and Functions, I borrow the following paragraph from Walkenbalk (2004a):

“You can think of a Sub procedure as a command that can be executed either by the user or by another procedure. Function procedures, on the other hand, usually return a single value (or an array), just like Excel worksheet functions and VBA built-in functions do. Like with built-in functions, your Function [and Sub] procedures can use arguments.”

Clear as mud? That didn’t help explain it as much as I though it would. I’ll try to illustrate the basic structure of a subroutine or a function with a couple simple examples.

Listing 1: Subroutines vs. Functions

Sub NameOfSubroutine(Arguments)







'Code goes here







End Sub















Function NameOfFunction(Arguments) AS ReturnDataType







'Code goes here







NameOfFunction = ReturnValue







'The previous line sends the value back to the calling cell or procedure







End Function

Where you see “Arguments” above, it can be left blank for no arguments, or you can have one or multiple arguments. Each argument is essentially a variable name and you declare it in a similar way to a variable as well. These variables will be available to use within the subroutine or function but not outside of it. Take a look at Listing 2 below to see what I mean. The “Single” means single precision floating point number, the data type. We will discuss data types in a moment.

Listing 2: Function with multiple arguments

Function MultiplyExample(Value1 As Single, Value2 As Single) As Single

Now let me show you how to call a Sub Procedure from another function or Sub procedure. Below that, I will show how to call a function and get a value back.

Listing 3: Calling functions or subroutines

Sub TestFunctionCall()







'Note how there are no arguments to this TestFunctionCall Sub























'Here is the Sub Call on the next line







Call NameOfSubToCall(Arguments)















'Here is a call to a function







ResultVariable = NameOfFunctionToCall(Arguments)







'We will discuss variables in the next section







End Sub

One other big difference between Subs and Functions is that if you have a Subroutine with no arguments, you can “Play” that subroutine just like you would a Macro that you recorded. In Excel 2003 and prior, you would play it using the Macro Toolbar or from the Tools Menu. In Excel 2007, you click on the Developer tab on the ribbon and then click the “Macros” button.

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment