VBA and Excel for Engineers and Scientists – Part 2

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

While writing a code, make liberal use of comments in your code or you’ll never be able retrace your steps when you come back to it a few days, weeks or months later. In VBA, a comment line begins with a single quote mark. In addition to annotating your code, you can use comments to "comment out” lines that you suspect may be causing errors when you are debugging.

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.