Microsoft Excel: Visual Basic for Applications (VBA): Level 1, 2 and 3 in one Week

Course Overview

Our Excel VBA Intensive 1 Week Training Course is for anyone about to embark on live projects involving the creation of Excel macros and VBA applications. The course covers introductory, intermediate and advanced topics and aims to equip delegates with the range of skills necessary to start tackling live VBA projects with confidence.

On completion of the Microsoft training course, Microsoft users will be well equipped to use Excel

Related Courses

• Microsoft Excel: Introduction for Beginners (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (1 day)
• Microsoft Excel: Visual Basic For Applications (VBA) Programming (2 day)
• Microsoft Excel: Formulae & Functions (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (2 day)
• Microsoft Excel: Visual Basic for Applications (VBA) Programming (4 day)
• Microsoft Excel: Charts, Pivot Tables & Macros (1 day)
• Microsoft Excel: Financial Modelling (2 day)
• Microsoft Excel: Business Modelling for Telecoms (2 day)
• Microsoft Office: Word, Excel & PowerPoint: Introduction for Beginners (1 day)

Audience

Designed for experienced Excel users familiar with both basic and advanced features and functions. This course provides an overview of VBA and introduces users to basic VBA programming techniques.

Course Outline

Microsoft Excel: Level 1

Basics

• Excel VBA overview
• Understanding macro steps
• Modifying macro steps
• Storing values in variables
• Adding comments to your code
• Stepping through macros
• Getting help on macros
• Objects, properties and methods
• About Excel objects

Range object

• The Selection property
• The Range property
• Targetting cells
• Counting the cells in a selection
• Using the Offset statement
• Retrieving the value of a cell
• Retrieving a formula from a cell
• Setting the value of cells

Using Visual Basic

• Language structure
• Using variables
• Declaring variables
• Data types
• Making decisions
• If statements
• Looping structures

Interactivity

• Assigning a macro to a menu
• Assigning a macro to a toolbar
• Assigning macros to other objects
• Built-in dialogue boxes
• Using form controls

Microsoft Excel: Level 2

Designed for experienced Excel users who have already begun to create macros and know their way around Excel's Visual Basic Editor.

Excel object model

• About classes and objects
• Objects and collections
• The hierarchy of objects
• Properties, methods and events
• The Locals Window
• The Immediate Window

Function procedures

• Sub versus function
• User-defined functions
• Declaring a function
• Calling a function
• Private and public scope
• Defining arguments
• Optional arguments

The Application object

• When to use
• ScreenUpdating property
• DisplayAlerts property
• Setting CutCopyMode to false

Dialogue boxes

• MsgBox function
• InputBox function
• Application.Dialogs

UserForms

• Creating a userform
• Displaying a userform
• Adding Label controls
• Adding a TextBox control
• The CheckBox control
• ComboBox and ListBox
• Adding CommandButtons
• Setting control properties

Workbooks and worksheets

• The Workbooks collection
• The Sheets collections
• The Window objects
• Workbook events

Ranges and names

• Column and Row properties
• CurrentRegion property
• The Union method
• The Intersect method
• Using the Name property
• Working with named ranges

Microsoft Excel: Level 3

Designed for experienced Excel VBA developers who are familiar with working with the Excel object model.

Developing applications

• Types of applications
• Types of end-user
• Analysing end-user needs
• Planning an application
• Planning the user interface

Working with events

• Overview of event types
• Event-handler procedures
• Workbook events
• Worksheet events
• Chart events
• UserForm events
• Activate event
• Save events
• Change events
• Focuse events
• Click events

Pivot tables

• Pivot table code overview
• Creating a pivot table
• PivotField object
• PivotItems collection
• Modifying a pivot table
• Using external data

Charts and drawings

• About programming charts
• Working with embedded charts Working with independent charts
• Controlling chart formatting
• Assigning a range to a chart
• Positioning a chart
• Making a chart active/inactive
• Editing data series
• Controlling chart labels
• Drawing shapes
• Setting shape attributes

Custom menus

• Testing to see if a menu exists
• Creating a new menu
• Adding items to a menu
• Adding items to shortcut menus

File manipulation

• Opening a text file
• Writing a text file
• Importing data from a text file
• Searching for text in files
• Verifying if a file exists
• Listing all files in a given folder

Class modules

• Class modules defined
• When to use class modules
• Creating a class module
• Property procedures
• Method procedures

 

Microsoft Excel: Visual Basic for Applications (VBA): Level 1, 2 and 3 in one Week / SP-EXL1

Request availability or enquire by clicking a date: