Excel VBA Introduction Courses UK Wide

Excel VBA Introduction

Unit 2: Developing with Procedures and Functions

Procedure is a term that refers to a unit of code created to perform a specific task.  In Excel, procedures are stored in objects called Modules. In this unit we will look at both Modules and Procedures.

Understanding and Creating Modules

Standard modules can be used to store procedures that are available to all forms, worksheets and other modules.  These procedures are usually generic and can be called by another procedure while the workbook is open.

Within a project you can create as many standard modules as required.  You should store related procedures together within the same module.

Standard modules are also used to declare global variables and constants.  To create a standard module in the VB Editor:

• Open the Insert menu
• Select Module.
• A new Module appears:
• Display the Properties window if necessary
• In the Properties window change the name of the module

Defining Procedures

A procedure is a named set of instructions that does something within the application.

To execute the code in a procedure you refer to it by name from within another procedure.  This is known as Calling a procedure.  When a procedure has finished executing it returns control to the procedure from which it was called.

There are two general types of procedures:

Sub procedures: perform a task and return control to the calling procedure

Function procedures: perform a task and return a value, as well as control, to the calling procedure

If you require 10 stages to solve a problem write 10 sub procedures.  It is easier to find errors in smaller procedures than in a large one.

The procedures can then be called, in order, from another procedure.

Naming Procedures

There are rules and conventions that must be followed when naming procedures in Visual Basic.

While rules must be followed or an error will result, conventions are there as a guideline to make your code easier to follow and understand.

The following rules must be adhered to when naming procedures:

• Maximum length of the name is 255 characters
• The first character must be a letter
• Must be unique within a given module
• Cannot contain spaces or any of the following characters: . , @ & $ # ( ) !

You should consider these naming conventions when naming procedures:

• As procedures carry out actions, begin names with a verb
• Use the proper case for the word within the procedure name
• If procedures are related try and place the words that vary at the end of the name

Following these conventions, here is an example of procedure names:

• PrintClientList
• GetDateStart
• GetDateFinish

Creating a Sub-Procedure

Most Excel tasks can be automated by creating procedures.  This can be done by either recording a macro or entering the code directly into the VB Editor's Code window.

Sub procedures have the following syntax:

[Public/Private] Sub ProcedureName ([argument list])

Statement block

End Sub

Public indicates procedure can be called from within other modules.  It is the default setting

Private indicates the procedure is only available to other procedures in the same module.

The Sub... End Sub structure can be typed directly into the code window or inserted using the Add Procedure dialog box.

To create a sub procedure:

• Create or display the module to contain the new sub procedure
• Click in the Code window
• Type in the Sub procedure using the relevant syntax
• Type in the word Sub, followed by a space and the Procedure name
• Press Enter and VB inserts the parenthesis after the name and the End Sub line.
• OR
• Use Add Procedure. To display the Add Procedure dialog box:
  • Open the Insert menu
  • Select Procedur

  The Add Procedure dialog box appears:

• Type the name of the procedure in the Name text box
• Select Sub under Type, if necessary
• Make the desired selection under Scope
• Click OK.

Creating a Function Procedure

Function procedures are similar to built-in functions such as Sum().  They are sometimes called user-defined function.

A function returns a value to the procedure that calls it.  The value the function generates is assigned to the name of the function.

Function procedures have the following syntax:

[Public/Private] Function FunctionName ([argument list]) [As ]

[Statement block]

[FunctionName = ]

End Function

Public indicates procedure can be called from within other modules. It is the default setting

Private indicates the procedure is only available to other procedures in the same module.

The As clause sets the data type of the function's return value.

To create a function procedure:

• Create or display the module to contain the new Function procedure
• Click in the Code window
• Type in the Function procedure using the relevant syntax or use Add Procedure
• Type in the word Function followed by a space and the Function name
• Press Enter and VB places the parenthesis after the name and inserts the End Function line.

Display the Add Procedure dialog box (as in Creating a Sub Procedure):

• Open the Insert menu
• Select Procedure.

The Add Procedure dialog box appears (as seen in Creating a Sub Procedure):

• Type the name of the procedure in the Name text box
• Select Function under Type
• Make the desired selection under Scope
• Click OK.

Below is an example of a basic function procedure:

Calling Procedures

A sub procedure or function is called from the point in another procedure where you want the code to execute.  The procedure being called must be accessible to the calling procedure.  This means it must be in the same module or be declared public.

Below is an example of calls to Sub and Function procedures:

When passing multiple arguments (as in the function procedure above) always separate them with commas and pass them in the same order as they are listed in the syntax.

Auto Quick Info is a feature of the Visual Basic that displays a syntax box when you type a procedure or function name.

The example below shows the tip for the Message Box function:

Arguments in square brackets are optional.

Values passed to procedures are sometimes referred to as parameters.

Using the Immediate Window to Call Procedures

The Immediate window is a debugging feature of Visual Basic.  It can be used to enter commands and evaluate expressions.

Code stored in a sub or function procedure can be executed by calling the procedure from the Immediate window.

To open the Immediate window:

• Open the View menu
• Select Immediate window
• OR
• Press Ctrl+G.

The Immediate window appears.

To execute a sub procedure:

• Type SubProcedureName ([Argument list])
• Press Enter.

To execute a function and print the return value in the window:

• Type ? FunctionName ([Argument list])
• Press Enter.

To evaluate an expression:

• Type ? Expression
• Press Enter.

Within the code, especially in loops, use the Debug.Print statement to display values in the Immediate window while the code is executing.  The Immediate window must be open for this.

Working Using the Code Editor

The Code editor window is used to edit Visual Basic code.  The two drop down lists can be used to display different procedures within a standard module or objects' event procedures within a class module.

Setting Code Editor Options
The settings for the Code Editor can be changed. To do this:

• Open the Tools menu in the VB Editor
• Select Options.

The Options dialog box appears.

The following are explanations of the Code Setting selections:

The Windows Settings selections are explained below:

Editing Guidelines

Below are some useful guidelines to follow when editing code:

• If a statement is too long carry it over to the next line by typing a space and underscore ( _ ) character at the end of the line.  This also works for comments. 
• Strings that are continued require a closing quote, an ampersand (&), and a space before the underscore. This is called Command Line Continuation.
• Indent text within control structures for readability. To do this:
• Select one or more lines
• Press the Tab key  OR Press Shift + Tab to remove the indent.
• Complete statements by pressing Enter or by moving focus off the code line by clicking somewhere else with the mouse or pressing an arrow key.
• When focus is moved off the code line, the code formatter automatically places key words in the proper case, adjusts spacing, adds punctuation and standardises variable capitalisation.

It is also a good idea to comment your code to document what is happening in your project. Good practice is to comment what is not obvious.

Start the line with an apostrophe ( ‘ ) or by typing the key word Rem (for remark).  When using an apostrophe to create a comment, you can place the comment at the end of a line containing a code statement without causing a syntax error.