Sub Procedures versus Function Procedures Part-1

Sub Procedures versus Function Procedures

A function is a VBA procedure that performs calculations and returns a value. You can use these functions in your Visual Basic for Applications (VBA) code or in formulas. Function procedures, on the other hand, usually return a single value (or an array), just like Excel worksheet functions and VBA built-in functions. As with built-in functions, your Function procedures can use arguments.

Function procedures are quite versatile, and you can use them in two situations:

·         As part of an expression in a VBA procedure

·         In formulas that you create in a worksheet

You’re undoubtedly familiar with Excel worksheet functions; even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. Excel 2007 includes more than 300 predefined worksheet functions that you can use in formulas. If that’s not enough, however, you can create custom functions by using VBA.

To shorten the lengthy formula is the need of creation a custom function. And shorter formulas are more readable and easier to work with.

Point to be note that custom functions used in formulas are usually much slower than built-in functions. And, of course, the user must enable macros in order to use these functions.

A Function Example

Sometimes in a spreadsheet a cell will contain both numeric characters and letter characters. You may wish to separate out the numeric part of this text.

‘Extract Number Function

Function ExtractNumber(cell_ref As Object)

Application.Volatile

t = ""

For Each cell In cell_ref

    temp = cell.Value

For n = 1 To Len(temp)

    If IsNumeric(Mid(temp, n, 1)) Then

        t = t & Mid(temp, n, 1)

    End If

    Next n

Next cell

ExtractNumber = Val(t)

End Function

This function certainly useful one I’ve written, and it demonstrates some key concepts related to functions.

When you enter a formula that uses the ExtractNumber function, Excel executes the code to get the value. Here’s an example of how you’d use the function in a formula:

= ExtractNumber (A1)

See the below Figure for examples of this function in action. The formulas are in column B, and they use the text in column A as their arguments. As you can see, the function returns the single argument, but with the all alphabet removed.

Analysis of the custom function

Function procedures can be as complex as you need them to be. Most of the time, they’re more complex and much more useful than this sample procedure. Nonetheless, an analysis of this example may help you understand what is happening.

Here’s the code, again:

Function ExtractNumber(cell_ref As Object)

Application.Volatile

t = ""

For Each cell In cell_ref

    temp = cell.Value

For n = 1 To Len(temp)

    If IsNumeric(Mid(temp, n, 1)) Then

        t = t & Mid(temp, n, 1)

    End If

    Next n

Next cell

ExtractNumber = Val(t)

End Function

Notice that the procedure starts with the keyword Function, rather than Sub, followed by the name of the function (ExtractNumber). This custom function uses only one argument (cell_ref as Object), enclosed in parentheses. A cell defines the data type of the function’s return value. Excel uses the Variant data type if no data type is specified.

The third line describes a variable “t” with the value as blank. From fourth line a “for loop” is created for each cell in cell_ref which is taken as object. Fifth line holds the value of cell of cell reference in a “temp” variable. Sixth line is again a for loop taken for length of string after that there is if statement to check if there is any numeric value or not if there is numeric value then it CONCATENATE with the value of “t” the loop continue till each and every numeric value added in that “t” variable. In second last line we get value of “t” as a return of function.

 Function Procedures

A custom Function procedure has much in common with a Sub procedure

The syntax for declaring a function is as follows:

[Public | Private][Static] Function name ([arglist])[As type]

[instructions]

[name = expression]

[Exit Function]

[instructions]

[name = expression]

End Function

The Function procedure contains the following elements:

·         Public: (Optional) indicates that the Function procedure is accessible to all other procedures in all other modules in all active Excel VBA projects.

·         Private: (Optional) indicates that the Function procedure is accessible only to other procedures in the same module.

·         Static: (Optional) Indicates that the values of variables declared in the Function procedure are preserved between calls.

·         Function: (Required) Indicates the beginning of a procedure that returns a value or other data.

·         Name: (Required) represents any valid Function procedure name, which must follow the same rules as a variable name.

·         arglist: (Optional) Represents a list of one or more variables that represent arguments passed to the Function procedure. The arguments are enclosed in parentheses. Use a comma to separate pairs of arguments.

·         Type: (Optional) Is the data type returned by the Function procedure.

·         Instructions: (Optional) Are any number of valid VBA instructions.

·         Exit Function: (Optional) Is a statement that forces an immediate exit from the

·         Function procedure prior to its completion.

·         End Function: (Required) Is a keyword that indicates the end of the Function procedure.

A function’s scope

A few things to keep in mind about a function’s scope:

·         If you don’t declare a function’s scope, its default is Public.

·         Functions declared As Private don’t appear in Excel’s Insert Function dialog box. Therefore, when you create a function that should be used only in a VBA procedure, you should declare it Private so that users don’t try to use it in a formula.

·         If your VBA code needs to call a function that’s defined in another workbook, set up a reference to the other workbook by choosing the Visual Basic Editor (VBE) ToolsàReferences command.

Executing function procedures

Although you can execute a Sub procedure in many ways, you can execute a Function procedure in only four ways:

·         Call it from another procedure.

·         Use it in a worksheet formula.

·         Use it in a formula that’s used to specify conditional formatting.

·         Call it from the VBE Immediate window.

From a procedure

You can call custom functions from a VBA procedure the same way that you call built-in functions.

For example, after you define a function called ExtractNumber, you can enter a statement like the following:

Nmbr = ExtractNumber(“A1”)

This statement executes the ExtractNumber function with Cell A1 as its argument, returns the function’s result, and assigns it to the Nmbr variable.

You also can use the Run method of the Application object. Here’s an example:

Nmbr = Application.Run (“ExtractNumber”, “A1”)

The first argument for the Run method is the function name. Subsequent arguments represent the argument(s) for the function. The arguments for the Run method can be literal strings (as shown above), numbers, or variables.

In a worksheet formula

Using custom functions in a worksheet formula is like using built-in functions except that you must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook, you don’t have to do anything special. If it’s in a different workbook, you may have to tell Excel where to find it.

You can do so in three ways:

·         Precede the function name with a file reference. For example, if you want to use a function called ExtractNumber that’s defined in an open workbook named Myfunctions.xlsm, you can use the following reference:

=Myfunctions.xlsm!ExtractNumber(A1:A1000)

If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically.

·         Set up a reference to the workbook. You do so by choosing the VBE Tools➜References command. If the function is defined in a referenced workbook, you don’t need to use the worksheet name. Even when the dependent workbook is assigned as a reference, the Paste Function dialog box continues to insert the workbook reference (although it’s not necessary).

·         Create an add-in. When you create an add-in from a workbook that has Function procedures, you don’t need to use the file reference when you use one of the functions in a formula. The add-in must be installed.

Function Arguments

Keep in mind the following points about Function procedure arguments:

·         Arguments can be variables (including arrays), constants, literals, or expressions.

·         Some functions don’t have arguments.

·         Some functions have a fixed number of required arguments (from 1 to 60).

·         Some functions have a combination of required and optional arguments.

Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don’t use arguments, including RAND, TODAY, and NOW. You can create similar functions.

Function User()

‘ Returns the name of the current user

User = Application.UserName

End Function

When you enter the following formula, the cell returns the name of the current user (assuming that it’s listed properly in the Registry):

=User()

The User function demonstrates how you can create a wrapper function that simply returns a property or the result of a VBA function. Following are three additional wrapper functions that take no argument:

Function ExcelDir() As String

‘ Returns the directory in which Excel is installed

ExcelDir = Application.Path

End Function

Function SheetCount()

‘ Returns the number of sheets in the workbook

SheetCount = Application.Caller.Parent.Parent.Sheets.Count

End Function

Function SheetName()

‘ Returns the name of the worksheet

SheetName = Application.Caller.Parent.Name

End Function

 Controlling function recalculation

Custom functions behave like Excel’s built-in worksheet functions. Normally, a custom function is recalculated only when it needs to be — which is only when any of the function’s arguments are modified. You can, however, force functions to recalculate more frequently. Adding the following statement to a Function procedure makes the function recalculate whenever the sheet is recalculated. If you’re using automatic calculation mode, a calculation occurs whenever any cell is changed.

Application.Volatile True

The Volatile method of the Application object has one argument (either True or False). Marking a Function procedure as volatile forces the function to be calculated whenever recalculation occurs for any cell in the worksheet. For example, the custom StaticRand function can be changed to emulate Excel’s RAND function using the Volatile method:

Function NonStaticRand()

‘ Returns a random number that

‘ changes with each calculation

Application.Volatile True

NonStaticRand = Rnd()

End Function

Using the False argument of the Volatile method causes the function to be recalculated only when one or more of its arguments change as a result of a recalculation. (If a function has no arguments, this method has no effect.)

Check out Sub Procedures versus Function Procedures Part-2