Euro-training Center
 MS Office: Macro Programming Using VBA QR Code

Business Information Technology Planning, Deployment & Management

MS Office: Macro Programming Using VBA


REF : B6078 DATES: 14 - 18 Dec 2020 VENUE: Trabzon (Turkey) FEE : 4950 

Overview:

Introduction:

In this course, you will learn the skills to enable you to use the Visual Basic for Applications (VBA) programming language to simplify or automate activities and tasks using the Microsoft Office 2007 - 2019 (365) applications. The course covers examples in Excel, Access, Word, and PowerPoint 2007 - 2019 (365).

Course Objectives:

At the end of this course the participants will be able to:

  • Understand MS Office Object Hierarchies and the Object Browser
  • Work within an MS Office Application
  • Use User Defined Types, Classes and Arrays
  • File and Folder Management using VBA
  • Interaction between MS Office Applications
  • Work with the Excel Range Object
  • Work with Data in Excel
  • Present Data in Excel
  • Programming in Access with VBA

Targeted Audience:

Delegates looking to gain enhanced skills to apply VBA to develop macros to work with Office applications. You will work with the application object models, manipulate data across applications, and manage files and folders using VBA.

Course Outlines:

Unit 1: MS Office Object Hierarchies and the Object Browser:

  • Objects, Collections, and Hierarchies
  • Properties, Methods, and Events
  • Using the Object Browser
  • MS Office Application Reference Libraries

Unit 2: Working within an MS Office Application:

  • VBA or Macro Recorder
  • Special Macros
  • Object Variables
  • Binding
  • Application Object Examples

Unit 3: User-Defined Types, Classes and Arrays:

  • Introduction to User-Defined Types (UDTs)
  • Creating a User-Defined Type
  • Creating a Class
  • Creating Array Variables
  • Using Arrays in Code

Unit 4: File and Folder Management Using VBA:

  • Using the FileSystemObject
  • Handling Folders using VBA
  • Create a Folder
  • Move, Rename, Copy or Delete a File
  • File or Folder Properties and Attributes

Unit 5: Interaction Between MS Office Applications:

  • Classes and References
  • Working with Objects in another Application
  • Editing Documents across MS Office

Unit 6: Working With the Excel Range Object:

  • Range Objects Defined
  • Referencing Ranges using VBA
  • Dynamic Range Handling
  • Copying or Moving Range Data

Unit 7: Working with Data in Excel:

  • Importing Data from a Delimited File or Web Page
  • Excel and ADO
  • Data Types or Formatting
  • Validating Data
  • Filtering and Sorting Data with VBA
  • Using Worksheet Database Functions
  • Excel Tables

Unit 8: Presenting Data in Excel

  • Apply Conditional Formatting with VBA
  • Working with Charts
  • Pivot Tables

Unit 9: Programming Access with VBA

  • Navigating the Access Object Hierarchy
  • Understanding Access Collections