Working with VBA Sub Procedures

A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor (VBE). A module can hold any number of procedures. A procedure holds a group of VBA statements that accomplishes a desired task. Most VBA code is contained in procedures.

You have a number of ways to call, or execute, procedures. A procedure is executed from beginning to end, but it can also be ended prematurely.

A procedure can be any length; I prefer to avoid creating extremely long procedures that perform many different operations. You may find it easier to write several smaller procedures, each with a single purpose. Then, design a main procedure that calls those other procedures. This approach can make your code easier to maintain.

An argument is simply information that is used by the procedure and that is passed to the procedure when it is executed. Procedure arguments work much like the arguments that you use in Excel worksheet function.

Declaring a Sub procedure

A procedure declared with the Sub keyword must adhere to the following syntax:

[Private | Public][Static] Sub name ([arglist])

[instructions]

[Exit Sub]

[instructions]

End Sub

Here’s a description of the elements that make up a Sub procedure:

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

·         Public: (Optional) Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside the project.

·         Static: (Optional) Indicates that the procedure’s variables are preserved when the procedure ends.

·         Sub: (Required) The keyword that indicates the beginning of a procedure.

·         name: (Required) Any valid procedure name.

·         arglist: (Optional) Represents a list of variables, enclosed in parentheses, that receive arguments passed to the procedure. Use a comma to separate arguments. If the procedure uses no arguments, a set of empty parentheses is required.

·         instructions: (Optional) Represents valid VBA instructions.

·         Exit Sub: (Optional) A statement that forces an immediate exit from the procedure prior to its formal completion.

·         End Sub: (Required) Indicates the end of the procedure.

Scoping a procedure

I note that a variable’s scope determines the modules and procedures in which you can use the variable. Similarly, a procedure’s scope determines which other procedures can call it.

Public procedures

By default, procedures are public — that is, they can be called by other procedures in any module in the workbook. It’s not necessary to use the Public keyword, but programmers often include it for clarity. The following two procedures are both public:

Sub First()

‘ ... [code goes here] ...

End Sub

Public Sub Second()

‘ ... [code goes here] ...

End Sub

Private procedures

Private procedures can be called by other procedures in the same module but not by procedures in other modules.

The following example declares a private procedure named MySub:

Private Sub MySub()

‘ ... [code goes here] ...

End Sub

Executing Sub Procedures

In this section, I describe the various ways to execute, or call, a VBA Sub procedure:

·         With the Run➜Run Sub/UserForm command (in the VBE menu). Or you can press the F5 shortcut key, or click the Run Sub/UserForm button on the Standard toolbar.

·         From Excel’s Macro dialog box.

·         By using the Ctrl key shortcut assigned to the procedure (assuming that you assigned one).

·         By clicking a button or a shape on a worksheet. The button or shape must have the procedure assigned to it.

·         From another procedure that you write. Sub and Function procedures can execute other procedures.

·         From a custom control in the Ribbon. In addition, built-in Ribbon controls can be “repurposed” to execute a macro.

·         From a customized shortcut menu.

·         When an event occurs. These events include opening the workbook, saving the workbook, closing the workbook, changing a cell’s value, activating a sheet, and many other things.

·         From the Immediate window in the VBE. Just type the name of the procedure, including any arguments that may apply, and press Enter.

Passing Arguments to Procedures

A procedure’s arguments provide it with data that it uses in its instructions. The data that’s passed by an argument can be any of the following:

·         A variable

·         A constant

·         An array

·         An object

The use of arguments by procedures is very similar to their use of worksheet functions in the following respects:

·         A procedure may not require any arguments.

·         A procedure may require a fixed number of arguments.

·         A procedure may accept an indefinite number of arguments.

·         A procedure may require some arguments, leaving others optional.

·         A procedure may have all optional arguments.

You can pass an argument to a procedure in two ways:

·         By reference: Passing an argument by reference simply passes the memory address of the variable. Changes to the argument within the procedure are made to the original variable. This is the default method of passing an argument.

·         By value: Passing an argument by value passes a copy of the original variable. Consequently, changes to the argument within the procedure are not reflected in the original variable.

The following example demonstrates this concept. The argument for the Process procedure is passed by reference (the default method). After the Main procedure assigns a value of 10 to MyValue, it calls the Process procedure and passes MyValue as the argument. The Process procedure multiplies the value of its argument (named YourValue) by 10. When Process ends

and program control passes back to Main, the MsgBox function displays MyValue: 100.

Sub Main()

Dim MyValue As Integer

MyValue = 10

Call Process(MyValue)

MsgBox MyValue

End Sub

Sub Process(YourValue)

YourValue = YourValue * 10

End Sub

If you don’t want the called procedure to modify any variables passed as arguments, you can modify the called procedure’s argument list so that arguments are passed to it by value rather than by reference. To do so, precede the argument with the ByVal keyword. This technique causes the called routine to work with a copy of the passed variable’s data — not the data itself. In the following procedure, for example, the changes made to YourValue in the Process procedure do not affect the MyValue variable in Main. As a result, the MsgBox function displays 10 and not 100.

Sub Process(ByVal YourValue)

YourValue = YourValue * 10

End Sub

In most cases, you’ll be content to use the default reference method of passing arguments. However, if your procedure needs to use data passed to it in an argument — and you must keep the original data intact — you’ll want to pass the data by value.

Error-Handling Techniques

When a VBA procedure is running, errors can and probably will occur. These include either syntax errors (which you must correct before you can execute a procedure) or runtime errors (which occur while the procedure is running).

Runtime Error

A runtime error causes VBA to stop, and the user sees a dialog box that displays the error number and a description of the error. A good application doesn’t make the user deal with these messages. Rather, it incorporates error-handling code to trap errors and take appropriate actions. At the very least, your error-handling code can display a more meaningful error message

than the one VBA pops up.

Trapping errors

You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices:

·         Ignore the error and let VBA continue. Your code can later examine the Err object to determine what the error was and then take action, if necessary.

·         Jump to a special error-handling section of your code to take action. This section is placed at the end of the procedure and is also marked by a label.

To cause your VBA code to continue when an error occurs, insert the following statement in your code:

On Error Resume Next

You also use the On Error statement to specify a location in your procedure to jump to when an error occurs. You use a label to mark the location. For example:

On Error GoTo ErrorHandler

Sometimes, you can take advantage of an error to get information. The example that follows simply checks whether a particular workbook is open.

Sub CheckForFile()

Dim FileName As String

Dim x As Workbook

FileName = “BUDGET.XLX”

On Error Resume Next

Set x = Workbooks(FileName)

If Err = 0 Then

MsgBox FileName & “ is open.”

Else

MsgBox FileName & “ is not open.”

End If

On Error GoTo 0

End Sub

Example That Uses Sub Procedures

The Example is about to develop a utility that rearranges a workbook by alphabetizing its sheets (something that Excel can’t do on its own). If you tend to create workbooks that consist of many sheets, you know that locating a particular sheet can be difficult. If the sheets are ordered alphabetically, however, it’s easier to find a desired sheet.

Project requirements list

1.       It should sort the sheets (that is, worksheets and chart sheets) in the active workbook in ascending order of their names.

2.       It should be easy to execute.

3.       It should always be available. In other words, the user shouldn’t have to open a workbook to use this utility.

4.       It should work properly for any workbook that’s open.

5.       It should not display any VBA error messages.

Below is the program for the purpose

Option Explicit

Sub SortSheets()

' This routine sorts the sheets of the

' active workbook in ascending order.

Dim SheetNames() As String

Dim i As Long

Dim SheetCount As Long

Dim OldActiveSheet As Object

If ActiveWorkbook Is Nothing Then Exit Sub ' No active workbook

SheetCount = ActiveWorkbook.Sheets.Count

' Check for protected workbook structure

If ActiveWorkbook.ProtectStructure Then

MsgBox ActiveWorkbook.Name & " is protected.", _

vbCritical, "Cannot Sort Sheets."

Exit Sub

End If

' Make user verify

If MsgBox("Sort the sheets in the active workbook?", _

vbQuestion + vbYesNo) <> vbYes Then Exit Sub

' Disable Ctrl+Break

Application.EnableCancelKey = xlDisabled

' Get the number of sheets

SheetCount = ActiveWorkbook.Sheets.Count

' Redimension the array

ReDim SheetNames(1 To SheetCount)

' Store a reference to the active sheet

Set OldActiveSheet = ActiveSheet

' Fill array with sheet names

For i = 1 To SheetCount

SheetNames(i) = ActiveWorkbook.Sheets(i).Name

Next i

' Sort the array in ascending order

Call BubbleSort(SheetNames)

' Turn off screen updating

Application.ScreenUpdating = False

 'Move the sheets

For i = 1 To SheetCount

ActiveWorkbook.Sheets(SheetNames(i)).Move _

Before:=ActiveWorkbook.Sheets(i)

Next i

' Reactivate the original active sheet

OldActiveSheet.Activate

End Sub

Sub BubbleSort(List() As String)

' Sorts the List array in ascending order

Dim First As Long, Last As Long

Dim i As Long, j As Long

Dim Temp As String

First = LBound(List)

Last = UBound(List)

For i = First To Last - 1

For j = i + 1 To Last

If List(i) > List(j) Then

Temp = List(j)

List(j) = List(i)

List(i) = Temp

End If

Next j

Next i

End Sub

####################################################################################################################