Working with Microsoft Excel Introduction to VBA

About the Course

Designed for
This Microsoft Excel Introduction to VBA Programming course is aimed at people who wish to make a start at learning the Excel VBA programming language, familiarising themselves with the concepts and practice of VBA programming, and understanding and using the relevant techniques. This course provides candidates with practical experience of the topics listed in the course outline.

Requirements
Participants who wish to attend this Microsoft Excel VBA course need to have significant experience and ability at understanding and using Excel’s advanced functionality. No prior VBA experience is required.

Course Objectives
On completion of this Excel VBA training course participants will be able to:

  • Use the Macro Recorder
  • Understand how VBA code is stored and how to access it using the Visual Basic Editor
  • Launch the Visual Basic Editor and use it to manage module sheets and code
  • Get macros to run via shortcut keys, menus and buttons
  • Write simple sub procedures with frequently used Excel objects, methods and properties
  • Create decision-making code
  • Use the Do and While loop to repeat the execution of code
  • Use simple variables to store and return information in a sub procedure

Teaching Approach:
The teaching approach contains a blend of lectures, practical, and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.

Course Material:
The majority of this course is spent with a set of prepared exercises and projects so that those attending gain a firm foundation of Microsoft Excel VBA capabilities. Professionally presented course notes are provided and these may be retained for reference afterwards.

Duration:
This training program is one day in duration.

Course Outline

Recording Macros
Defining Macros
Recording a Macro
Running a Macro
Recording a Macro Using Relative References
Saving a Workbook Containing a Macro
Opening a Workbook Containing a Macro
Saving the Personal Macro Workbook

The Visual Basic Editor
Launching the Visual Basic Editor
Navigating the Visual Basic Editor
Finding a Macro in the Visual Basic Editor
Inserting a Module into a Workbook
Copying a Module between Workbooks
Renaming a Module
Deleting a Module

Assigning Macros
Assigning a Shortcut Key
Creating a Macro Button
Using a Macro Button
Copying a Worksheet Macro Button
Editing a Worksheet Macro Button
Deleting a Macro Button

Writing Procedures
Introduction to Excel Objects
Methods and Properties
Returning Methods and Properties
Adding Arguments to Methods
Properties that are also Objects
Using Selection versus ActiveCell
Writing VBA Sub Procedures
The With / End With Block
The Offset Property
Dealing With a Runtime Error
Using the Object Browser

Control Structures
The If-Then Control Structure
The If-Then-Else Control Structure
Using ElseIf in a Control Structure
The Select Case Control Structure
Using a Do Loop Statement

Data Variables
Data Variables
Creating Variables and Assigning Values
Variable Types

Using the For Loop
Using a For Next Loop
Using a For Each Next Loop

Adding Interactivity to Macros
The MsgBox Function
Creating an Interactive Message Box
Creating a Custom Input Box