Font size:

Introduction

The advantages of VBA

  • An easy-to-use and versatile language
  • Integrated with off-the-shelf products

Macros: the first step

  • Recording and executing macros
  • Limitations
  • Enhancing with VBA code

Getting to Know the VBA Environment

The code design tools

  • Project Explorer
  • Help system
  • Code Window
  • IntelliSense
  • Properties Window
  • Object Browser

The debugging tools

  • Code stepping
  • Immediate Window
  • Breakpoints

Writing VBA Code

Event-driven programming

  • The role of events in Windows
  • 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

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 with the Object Browser and Help
  • 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 using ActiveX Data Objects

Building Intuitive User Interfaces

Using intrinsic dialogs

  • Communicating with the user through the message box
  • Gathering user information with the input box
  • Utilizing the FileDialog object and Dialogs collections

Creating customized dialogs with UserForm objects

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

Modifying menus and toolbars

  • Simplifying user interaction with the CommandBar object
  • Adding and removing CommandBarControl objects

Providing a Safe and Secure Environment

Handling runtime errors

  • The On Error GoTo structure
  • Classifying errors with the Err object
  • Executing 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

You 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
  • Troubleshoot code using VBA's debugging tools
  • Secure your code and protect your users from macro viruses

Course 209 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
  • Block commenting and uncommenting

The debugging tools

  • Code stepping
  • Immediate Window
  • Breakpoints

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 using ActiveX Data Objects

Building Intuitive User Interfaces

Using intrinsic dialogs

  • Communicating with the user through the message box
  • Gathering user information with the input box
  • Utilizing the FileDialog object and Dialogs collections

Creating customized dialogs with UserForm objects

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

Modifying menus and toolbars

  • Simplifying user interaction with the CommandBar object
  • Adding and removing CommandBarControl objects

P
Hands-On Training
Extensive hands-on exercises provide you with practical experience automating Office applications, such as Word, Excel and Outlook, with VBA programming. Exercises include:
  • Building user interfaces with standard and custom controls
  • Adding powerful subtotaling to an Excel workbook
  • Creating form letters and Outlook mail with customized userforms
  • Protecting users with digital signatures
  • Retrieving Access and SQL Server data for use in Word and Excel
  • Integrating the powerful features of Word and Excel


Find a class and register in three easy steps:
  • 1. Select Location:
  • 2. Select Date Range:
    to
  • 3. Select Class Type:
    • All Types
    • C Classroom
    • V Virtual Live
    • Cv In-Class Virtual
    • O Online
MD Rockville12/19/2017 9:00:00 AM<span class="course-type type-c" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'ILT')" onmouseout="clearHoverInfo()">C</span>4 days2990.001803883
Virtual Live12/19/2017 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805812
VA SAINT STEPHENS CHURCH12/19/2017 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808839
Virtual Live1/23/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805815
NJ Princeton1/23/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808854
Virtual Live2/20/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805820
VA WINCHESTER2/20/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808944
Virtual Live3/27/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805824
MD Rockville3/27/2018 9:00:00 AM<span class="course-type type-c" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'ILT')" onmouseout="clearHoverInfo()">C</span>4 days2990.001803887
VA SAINT STEPHENS CHURCH3/27/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808973
VA ALEXANDRIA4/10/2018 9:00:00 AM<span class="course-type type-c" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'ILT')" onmouseout="clearHoverInfo()">C</span>4 days2990.001803882
Virtual Live4/10/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805813
NY New York4/10/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808981
Virtual Live7/24/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805817
MD Rockville7/24/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808887
Virtual Live8/7/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805821
MD Rockville8/7/2018 9:00:00 AM<span class="course-type type-c" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'ILT')" onmouseout="clearHoverInfo()">C</span>4 days2990.001803886
MD LAUREL8/7/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808849
Virtual Live8/21/2018 9:00:00 AM<span class="course-type type-v" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'VILT')" onmouseout="clearHoverInfo()">V</span>4 days2990.001805825
VA ALEXANDRIA8/21/2018 9:00:00 AM<span class="course-type type-cv" style="cursor:default;word-wrap:normal;display:inline-block;" onmouseover="showHoverInfo(this, 'ProductTypeDescription' ,'HVILT')" onmouseout="clearHoverInfo()">Cv</span>4 days2990.001808884
seek-warrow-w
  • 1
arrow-eseek-eitems per page1 - 20 of 20 items