Le portail de la formation tout au long de la vie
LU
DE
EN
FR
PT

Programming in VBA Excel

3 jour(s)

Objectifs

Objectives:

In this hands-on course, you learn the fundamentals of programming and VBA from the ground up. You gain the skills you need to automate repetitive tasks, build user interfaces and increase performance in order to improve user productivity.

Participants will learn how to:
  • Develop customized solutions using Visual Basic for Applications (VBA)
  • Apply the object models to program effectively in the Microsoft Office System
  • Take full advantage of extensive built-in VBA functionality
  • Exploit ActiveX controls to create functional and familiar user interfaces
  • Retrieve data from PC and server databases
  • Troubleshoot code using VBA's debugging tools
  • Secure your code and protect your users from macro viruses
  • Use Visual Basic to control Excel, and to automate processes to speed up and simplify their work with Excel data.
Course benefits

They will also learn how to create interactive procedures using message boxes, and custom dialog boxes using Visual Basic controls. By the end of the course participants will have a good understanding of the Visual Basic language and the main elements of Excel that are referenced when using VBA.

Contenu

Introduction
The advantages of VBA

An easy-to-use and versatile language
Integrated with off-the-shelf products
Minimizes development time and costs
Macros: the first step

Recording and executing macros
Limitations
Enhancing with VBA code
Getting to Know the VBA Development Environment

The code design tools

Project Explorer
Help system
Code Window
IntelliSense
Properties Window
Object Browser
The debugging tools

Code stepping
Immediate Window
Breakpoints
Manipulating Objects

Methods
Properties
Events
Accessing and using individual objects
Applying the Excel Object Model
Programming Excel objects

Application
Sheets collection
cell object
Controls collection
Building Intuitive user interfaces

Using intrinsic dialogs

Communicating with the user through the message box
Gathering user information with the input box
Creating customized dialogs with UserForm objects

Command buttons
Text boxes
List and Combo boxes
Option buttons
Check boxes
Labels
Frames
Adding more functionality with advanced ActiveX controls
Functions in Excel

Data Programming
Data Access Objects (DAO)

Manipulating data using Recordsets
Automation

With Outlook and Word

Writing VBA Code

Event-driven programming

The role of events in Windows
How events are triggered
Responding to events through event procedures
Putting objects to work

Properties
Methods
Events
Collections
Using With...End With
Storing data in variables

How and when to declare variables
Selecting data types
Fixed and dynamic arrays
Constants
The scope and lifetime of variables
Conditional branching

If...Then...Else
Select...Case
Looping through code

Do...Loop, While and Until
For...Next and For Each...Next
Building procedures

Creating Sub and Function procedures
Calling procedures
Passing arguments to procedures
Empowering VBA through the Application Object Models

Understanding object model structures

Exploring the hierarchy using the Object Browser and Help system
Referencing specific objects
Writing application-specific procedures

Key objects within Excel, Word and Outlook
Creating templates and add-ins
Exploiting the power of Automation

Controlling one Office application while working in another application
Accessing data in databases
Building Intuitive User Interfaces

Using intrinsic dialogs

Communicating with the user through the message box
Gathering user information with the input box
Creating customized dialogs with UserForm objects

Command buttons
Text boxes
List and Combo boxes
Option buttons
Check boxes
Labels
Frames
Adding more functionality with advanced ActiveX controls
Providing a Safe and Secure Environment

Handling runtime errors

The On Error GoTo structure
Classifying errors with the Err object
Continuing execution with Resume, Resume Next or Resume label
Implementing security

The dangers of macro viruses
Macro security levels in Office
Applying digital signatures to macros
Password protecting your VBA code

Points abordés
  • Programming in VBA Excel
  • Microsoft Office System
  • Use Visual Basic to control Excel

Public cible

A qui s'adresse la formation?

This course is valuable for anyone who wants to automate tasks through VBA programming. Some experience using the products in the Microsoft Office System is assumed. No previous programming experience is required.

 
Prérequis

Excel Advanced, or equivalent knowledge.

Evaluation

Certificat, diplôme

A certificate of formation.

Voir les coordonnées de l'organisme


Le contenu de ce descriptif de formation est de la seule responsabilité de son auteur, l'organisme de formation Business Training Luxembourg.

Imprimer Tout voir/cacher
Partager sur Facebook Partager sur Twitter Partager sur LinkedIn Partager sur Google+ Envoyer à un ami
Voir les coordonnées de l'organisme
Caractéristiques
Niveau Intermédiaire
Organisation Formation inter et intra-entreprise

Are included in the price:

  • Coffee break
  • Lunch
  • Car park
Langues de prestation
lifelong-learning.lu utilise des cookies

En consultant ce site, vous acceptez l'utilisation des cookies nécessaires à la navigation et permettant de réaliser des statistiques.

Vous pouvez modifier les paramètres des cookies à tout moment dans votre navigateur.

OK En savoir plus