Duration

2 day(s)

MS Office: Start to program

Audience

This course is aimed at all advanced MS Office users (Word, Excel and/or Access) who’d like to automate tasks using the VBA programming language.

Prerequisites

A thorough knowledge of MS Office (MS Word, MS Excel and possibly MS Access) is required. A programming experience is a plus but is not essential.

Objectives

Learn to create simple and repetitive tasks using « Macros ».

Learn the VBA general programming language.

Explore the object library and learn how to use it in VBA code, depending on the used MS Office application.

Methods

Classroom training with numerous exercises.

Description

VBA stand for Visual Basic for Applications and is the standard programming language for all MS Office applications. Every application disposes of its own VBA, but in this object-oriented language, a lot of elements are common to all applications.

We start off with creating macros. MS Word and Excel can record actions and convert them immediately into macros.

Access has a macro object where macro actions can be entered.

Furthermore, we’ll go over some properties that are typical to the VBA language like decisions structures (e.g.: IF…THEN), loop structures (e.g.: DO…WHILE), variables and constants,…

We’ll also go over the use of the help function, the testing and debugging and the error handling. In short: avoid and solve programming errors

We’ll have a look at the most important objects in the object library for each application and how to integrate them in the VBA code using properties, methods and events

Finally, we’ll have an overview of the Automation objects and ADODB.

Contents

• Introduction
• Macros
  • MS Excel and MS Word
    • Prepare macros
    • Create macros using the macro recorder
    • Test and execute macros
  • MS Access
    • Create macros
    • Test and execute macros
    • Convert macros to VBA
• How to use the Visual Basic Editor
  • Access the VBE environment
  • Overview of the screen
  • Options
• VBA in general
  • Structure of a VBA project
  • Manage modules
  • Manage procedures
  • Insert and format code
  • Variables
  • Constants
  • Working with strings
  • Working with numeric values
  • Working with date and time
  • User input (MsgBox, InputBox)
  • Call procedures and functions
  • Programming structure
    • Decisions
      • If ... Then ... Else
      • Select case
    • Loops
      • For ... Next
      • Do ... Loop
  • Debugging and error handling
• Objects - General
  • Collections and objects
  • Properties, methods and events
  • Object explorer
  • Object variables
  • Programming structure
    • With ... End With
    • For Each ... Next
• Excel object models
  • Overview of the Application object
  • The Workbook collection and object
  • The Worksheet collection and object
  • Working with cells
    • ActiveCell, Selection et CurrentRegion
    • Range property
    • Cells property
    • Offset property
  • Events
    • Workbook-events
    • Worksheet-events
• Word object models
  • Overview of the Application object
  • Document collection and object
  • Working in documents
  • Document events
• Working with objects in Access
  • React to events Réagir à des évènements
  • DoCmd
  • Working with forms
  • Working with reports
  • Working with control elements
• Automation : automate another Office application using the active Office application
• Access to data using ADODB