<< Back to Courses

Excel VBA 2010


Cost: Contact us for quote.   Duration: 2 days


Contact Us to book.

Overview

The skills and knowledge acquired in this course are sufficient to be able to create real life working VBA applications within Excel. The learner will be able to work with VBA within the Excel environment to program and automate worksheet operations.

 

Learning Outcomes

At the completion of this course you should be able to:

 ·         create recorded macros in Excel

·         use the macro recorder to create a variety of macros

·         understand the Excel object model and VBA concepts

·         work effectively with the main features of the VBA Editor window

·         create procedures in VBA

·         create and use variables

·         create and work with user-defined functions in VBA

·         write code to manipulate Excel objects

·         use a range of common programming techniques

·         create a custom form complete with an assortment of controls

·         create code to drive a user form

·         create procedures that start automatically

·         write a variety of error handling routines

 

Target Audience

This course is designed for users who wish to learn how to use the inbuilt VBA programming language in Excel to enhance their worksheets and automate processes.

 

Prerequisites

This publication assumes the learner has a good knowledge of working with Excel. The learner should at least be able to create and edit workbooks, enter

formulas, copy, paste, and format data. The learner must also have a general understanding of personal computers and the Windows operating system

environment and be able to use Windows Explorer to locate and copy files.

        
         Duration

         Scheduled classroom training (2 Days)

 
Contents

Ø Recorded Macros

 Understanding Excel Macros

 Setting Macro Security

 Saving A Document As Macro Enabled

 Recording A Simple Macro

 Running A Recorded Macro

 Relative Cell References

 Running A Macro With Relative References

 Viewing A Macro

 Editing A Macro

 Assigning A Macro To The Toolbar

 Running A Macro From The Toolbar

 Assigning A Macro To The Ribbon

 Assigning A Keyboard Shortcut To A Macro

 Deleting A Macro

 Copying A Macro

Ø Recorder Workshop

 Preparing Data For An Application

 Recording A Summation Macro

 Recording Consolidations

 Recording Divisional Macros

 Testing Macros

 Creating Objects To Run Macros

 Assigning A Macro To An Object

Ø Understanding Excel VBA

 Programming In Microsoft Excel

 VBA Terminology

 Understanding Objects

 Viewing The Excel 2010 Object Model

 Using The Immediate Window

 Working With Object Collections

 Setting Property Values

 Working With Worksheets

 Using The Object Browser

 Programming With The Object Browser

 The Best VBA Help Available

Ø The VBA Editor

 The VBA Editor Screen

 Opening And Closing The Editor

 Using The Project Explorer

 Working With The Properties Window

 Using The Work Area

 Viewing Other Panes

 Working With Toolbars

 Working With A Code Module

 Running Code From The Editor

 Setting Breakpoints In Code

 Stepping Through Code

Ø Procedures

 Understanding Procedures

 Where Procedures Live

 Creating A New Sub Routine

 Making Sense Of IntelliSense

 Using The Edit Toolbar

 Commenting Statements

 Indenting Code

 Bookmarking In Procedures

Ø Using Variables

 Understanding Variables

 Creating And Using Variables

 Explicit Declarations

 The Scope Of Variables

 Procedure Level Scoping

 Module Level Scoping

 Passing Variables

 Passing Variables By Reference

 Passing Variables By Value

 Data Types For Variables

 Declaring Data Types

 Using Arrays

Ø Functions In VBA

 Understanding Functions

 Creating VBA Functions

 Using A VBA Function In A Worksheet

 Setting Function Data Types

 Using Multiple Arguments

 Modifying A VBA Function

 Creating A Function Library

 Referencing A Function Library

 Importing A VBA Module

 Using A Function In VBA Code

Ø Using Excel Objects

 The Application Object

 The Workbook Objects

 Program Testing With The Editor

 Using Workbook Objects

 The Worksheets Object

 Using The Worksheets Object

 The Range Object

 Using Range Objects

 Using Objects In A Procedure

Ø Programming Techniques

 The MsgBox Function

 Using MsgBox

 

 InputBox Techniques

 Using The InputBox Function

 Using The InputBox Method

 The IF Statement

 Using IF For Single Conditions

 Using IF For Multiple Conditions

 The Select Case Statement

 Using The Select Case Statement

 For Loops

 Looping With Specified Iterations

 The Do…Loop Statement

 Looping With Unknown Iterations

Ø Creating Custom Forms

 Understanding VBA Forms

 Creating A Custom Form

 Adding Text Boxes To A Form

 Changing Text Box Control Properties

 Adding Label Controls To A Form

 Adding A Combo Box Control

 Adding Option Buttons

 Adding Command Buttons

 Running A Custom Form

Ø Programming UserForms

 Handling Form Events

 Initialising A Form

 Closing A Form

 Transferring Data From A Form

 Running Form Procedures

 Creating Error Checking Procedures

 Running A Form From A Procedure

 Running A Form From The Toolbar

Ø Automatic Startup

 Programming Automatic Procedures

 Running Automatic Procedures

 Automatically Starting A Workbook

Ø Error Handling

 Understanding Error Types

 The On Error Statement

 Simple Error Trapping

 Using The Resume Statement

 Using Decision Structures In Error Handlers

 Working With Err Object

 Error Handling In Forms

 Coding Error Handling In Forms

 Defining Custom Errors

Ø Concluding Remarks



Zip File
Download Exercise Files