98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel VBA Introduction Courses UK Wide
Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
(547 reviews, see all 99,943 testimonials) |
From £300 List price £350
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
This course is suitable for advanced users of Microsoft Excel. If they are responsible for very large and variable amounts of data, or teams, who need to learn how to program features and functions to develop the accessibility and usability of their data.
Prerequisites
A thorough knowledge of Microsoft Excel is required, or completion of our Microsoft Excel Advanced course.
Benefits
- By the end of the course the delegate will be competent in the fundamentals of VBA, including recording macros, working with the Visual Basic Editor and writing their own code.
- In order to ensure minimal downtime while building and rolling out the program, the course also includes advice on debugging code and handling errors.
You may also wish to consider our Excel forecasting and data analysis training course.
Course Syllabus
Introducing Visual Basic for Applications
Why use VBA?
Recording and running macros
Absolute v relative cell selection
Working with the Visual Basic Editor
Creating your own code
Understanding and creating modules
Defining procedures
Calling procedures
Where to store macros
Making decisions in code
Using logical comparisons
The IF...ENDIF structure
The SELECT CASE...END SELECT structure
When to use IF v SELECT CASE
Repeating code with loops
The DO... LOOP structure
The FOR... NEXT structure
The FOR EACH... NEXT structure
How to debug problems with loops
Debugging errors
Defining errors
Setting breakpoints to pause execution
How to step through code
Working with break mode to spot errors
Identifying the value of expressions
Understanding Visual Basic
What is an Object?
Examining the Excel object hierarchy
Using the object browser
How to explore methods and properties
Getting help in VBA
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
Thales UK LTD
Chris Brodie,
Finance Graduate
Yes it was thoroughly enjoyable. I think maybe getting a brief off us beforehand may have been useful for the instructor just so he knew what we wanted from the session and could tailor the session to our needs
Excel VBA Introduction
Government Actuary's Department
Manoj Mistry,
Actuary
User Form was very handy
Overall course was very good and interactive
Excel VBA Introduction
Motor Insurers' Bureau
Stuart Barnard,
MI Analyst
Trainer was very patient and explained the subject well
Excel VBA Introduction
Training manual sample
Below are some extracts from our Excel training manuals.
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.
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. |
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:
- 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.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...