VBA training courses London - 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 dialogue 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 dialogue box:

• Open the Insert menu
• Select Procedure.

The Add Procedure dialogue 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.

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.

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.

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.

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.

• Object List - Displays a list of objects contained in the current module.
• Procedure List - Displays a list of general procedures in the current module when General is selected in the Object list.When an object is selected in the Object list it displays a list of events associated with the object.

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:

• Auto Syntax Check - Automatically displays a Help message when a syntax error is detected.  Message appears when you move off the code line containing the error
• Require Variable Declaration - Adds the line Option Explicit to all newly created modules, requiring all variables to be explicitly declared before they are used in a statement.
• Auto List Members - Displays a list box under your insertion point after you type an identifiable object.  The list shows all members of the object class.  An item selected from the list can be inserted into your code by pressing the Tab key
• Auto Quick Info - Displays a syntax box showing a list of arguments when a method, procedure or function name is typed
• Auto Data Tips - Displays the value of a variable when you point to it with a mouse during break mode. Useful for debugging.
• Auto Indent - Indent the specified amount when Tab is pressed and indents all subsequent lines at the same level.

The Windows Settings selections are explained below:

• Drag-and-Drop Text Editing - Allows you to drag and drop code around the Code window and into other windows like the Immediate window.
• Default to Full Module View - Displays all module procedures in one list with optional separator lines between each procedure. The alternative is to show one procedure at a time, as selected through the Procedure list.
• Procedure Separator - Displays a grey separator line between procedures if Module view is selected

Editing Guidelines

Below are some useful guidelines to follow when editing code, as taught in our VBA training courses London:

• 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 standardizes variable capitalization.

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.