Realdolmen Education

Details

VBA Programming for Excel

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

3 day(s)

Audience

This course is intended for experienced Excel users who want to develop their own applications in Excel.

Prerequisites

A thorough knowledge of Excel is required. Experience in programming is an asset but is not required.

Objectives

Exploring Visual Basic for Excel, from the creation of simple macros to the development of an application providing interaction with the user.

Methods

Classroom training with attention to individual guidance and possibility to ask questions. The participant can immediately put the features of the program into practice with various exercises. Each participant is provided with his own workstation.

Description

VBA stands for Visual Basic for Applications, and is the programming language used by the MS Office suite applications. Each application has its own VBA, but naturally, some elements are common to all applications.

We will start the course with recording and editing macros but very soon, we will use Visual Basic Editor in which code is created.

We will first study some elements specific to the VBA programming language in general like: Decision structures (e.g.: IF … THEN), Loop structures (e.g.: DO … WHILE), working with variables, constants, operators, ranges. We will also explain how to use the help as well as the testing, the debugging and the error handling so that we can avoid or resolve mistakes in our own created programs.

We will then take a look at the most important Excel objects: Application, Workbook, Worksheet, and the objects and properties allowing us to work with cells. We will also learn to react to certain workbook and worksheet events.

Besides the use of dialog box items in a worksheet, we will study the creation of user forms.

In the User Defined Functions part, you will learn how to create your own functions.

Depending on the interests or questions of the participants, the following topics could also be covered: Automation, Excel Add-ins, access to data sources via ADODB, …

Contents

  • Introduction
  • Macros
    • Preparing macros
    • Creating macros via the Macro Recorder
    • Executing macros
  • VBA: What, why and when?
    • The structure of an Excel project
  • Using the Visual Basic Editor
    • Activating the VBE environment
    • Exploring the screen
      • Project explorer
      • Properties window
      • Code window
    • Management module
    • Management procedures
  • Code
    • Entering and structuring code
    • Variables
    • Constants
    • Working with text
    • Working with numbers
    • Working with dates and time
    • Interaction with users (MsgBox, InputBox)
    • Declaring procedures and functions
    • Program structures
      • Conditional structures
        • If … Then … Else
        • Select Case
      • Loops
        • For … Next
        • Do … Loop
    • Debugging and handling errors
  • The Excel object model
    • Collections and objects
    • Properties, methods and events
    • Using the object browser
    • Object variables
    • Program structures
      • With … End With
      • For Each … Next
  • A look at the application object
  • The collection Workbooks and the Object Workbook
    • Referring to a workbook
    • Workbooks: methods and properties
    • Workbook: methods and properties
  • The collection Worksheets and the object Worksheet
    • Referring to a worksheet
    • Worksheets: methods and properties
    • Worsheets: methods and properties
  • Working with cells
    • ActiveCell, Selection and CurrentRegion
    • The property range
    • The property Cells
    • The property Offset
  • Events
    • Workbook events
    • Worksheet events
  • Using Dialog box items in a worksheet
  • User Forms
    • Adding controls
    • Properties
    • Code
  • Automation: automating another Office program via Excel
  • Creating User Defined Functions
  • Excel Add-ins
  • Accessing data via ADODB