SKIP TO CONTENT
The portal for lifelong-learning
LU
DE
EN
FR
PT

Programming in VBA Excel

3 day(s)

Objectives

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.

Content

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 covered
  • Programming in VBA Excel
  • Microsoft Office System
  • Use Visual Basic to control Excel

Target group

Who is the course aimed at?

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.

 
Prerequisites

Excel Advanced, or equivalent knowledge.

Assessment

Certficate, diploma

Certificate

Contact the training provider
Share this content

 



Responsibility for the content of this training description lies solely with its author, the training provider Business Training Luxembourg.

Print Expand/Collapse all Send to a friend
Contact the training provider
Characteristics
Level Intermédiaire
Organisation Formation inter et intra-entreprise

Are included in the price:

  • Coffee break
  • Lunch
  • Car park
Languages
Next sessions
From/To Location Price
06 - 08/02/23
  Business Training Luxembourg   1200.00 €
See details
19 - 21/04/23
  Business Training Luxembourg   1200.00 €
See details
12 - 14/06/23
  Business Training Luxembourg   1200.00 €
See details
07 - 09/08/23
  Business Training Luxembourg   1200.00 €
See details
09 - 11/10/23
  Business Training Luxembourg   1200.00 €
See details
11 - 13/12/23
  Business Training Luxembourg   1200.00 €
See details
Use of cookies

lifelong-learning.lu uses cookies to offer you a quality user experience, measure audiences, optimise functions on social media, and offer you personalised content.

By continuing to browse this website, you accept the use of cookies subject to the conditions provided for in our policy on the subject. Find out more.

Essential cookies
These cookies make it possible to use the main functions of our website (including access to your personal space). Normal use of our website is not possible unless these cookies are enabled.
Statistics
These cookies make it possible to compile statistics on visits to our website. Disabling them stops us monitoring and improving the quality of the services we provide.
Targeted advertising
These cookies make it possible to provide you with information via the Internet and the social media, with offers of training that match your needs.

Read our policy on the use of cookies.

Accept all Manage your cookie settings
Veuillez patienter...