Primer in Excel VBAWhat’s New in Excel 2003Visual Basic for Applications or VBA is a common programming language, which is used to control applications like Microsoft Excel, Microsoft Access or any application that meets its standards.

Who is it for?

This course is aimed at delegates who have an extensive knowledge of Excel and Access and who need to automate repetitive or complex tasks within a spreadsheet or wanting to create database application.

After completing this course you will be able to:

Macros:

  • Record macros
  • Assign macros to toolbar buttons, menus and objects
  • Understand the limitations of recorded macros
  • Edit recorded macros

Visual Basic for Applications:

  • Introduce the VBA editor window
  • Create procedures
  • Basic principles – syntax, commenting, naming conventions
  • Getting help

VBA Fundamentals:

  • Use objects, properties and menus
  • Manipulate ranges and cells
  • Use variables
  • Call procedures
  • Use the With statement
  • Create Looping statement
  • Assign procedures to workbook events

Debug code and error trap:

  • Step through code
  • Interrupt / break code
  • Include simple error trapping code

Dialog boxes / user forms:

  • Create message boxes
  • Create user forms
  • Use the control toolbox

What does the course consist of?

Beginning Access 2003
  • Introduction to Access 2003 VBA.
  • The Basics of Writing and Testing VBA Code.
  • Programming Applications Using Objects.
  • Creating Your Own Objects.
  • Interacting with Data Using ADO and SQL.
  • Building Interactive Forms.
  • Importing, Linking, and Exporting Using External Data Sources.
  • Creating Reports and Web-Enabled Output.
  • Building SQL Server Applications with Access Projects.
  • Advanced Access Programming.
  • Finishing the Application.
  • Case Study 1: Project Tracker Application.
  • Case Study 2: Customer Service Application.
  • Appendix A: Exercise Answers.
VBA Excel 2003
  • What’s New in Excel 2003
  • Exploring Excel
  • Exploring Visual Basic for Applications
  • VBA Programming Starter Kit
  • Creating Sub and Function Procedures
  • The Application Object
  • Workbooks and Worksheets
  • Ranges and Cells
  • Manipulating Data with VBA
  • Formatting Excel Objects
  • Creating Add-Ins and COM Add-Ins
  • Understanding and Using Events
  • Manipulating Files
  • Developing Class Modules
  • Manipulating Excel Objects
  • PivotTables and PivotCharts
  • Command Bars
  • Customizing Dialog Boxes
  • Creating User Forms
  • Creating Advanced User Forms
  • Excel and Other Office Applications
  • Excel and Other Structured Query Language
  • Introducing ADO
  • Excel Query Program
  • Excel and the Web
  • Excel and the Extensible Markup Language(XML)
Access 2007 VBA
  • Introduction to Microsoft Access 2007
  • Access, VBA, and Macros
  • New Features in Access 2007
  • Using the VBA Editor
  • VBA Basics
  • Using DAO to Access Data
  • Using ADO to Access Data
  • Executing VBA
  • VBA Error Handling
  • Using VBA to Enhance Forms
  • Enhancing Reports with VBA
  • Customizing the Ribbon
  • Creating Classes in VBA
  • Extending VBA with APIs
  • SQL and VBA
  • Working with Office Applications
  • Working with SharePoint
  • Database Security
  • Understanding Client-Server Development with VBA
  • Working with the Win32 Registry
  • Using the ADE Tools
  • Protecting Yourself with Access 2007 Security
Excel 2007 VBA
  • Primer in Excel VBA
  • The Application Object
  • Workbooks and Worksheets
  • Using Ranges
  • Using Names
  • Data List
  • Pivot Tables
  • Charts
  • Event Procedures
  • Adding Controls
  • Text Files and File Dialog
  • Working with XML and the Open XML File Formats
  • UserForms
  • RibbonX
  • Command Bars
  • Class Modules
  • Add-ins
  • Automation Add-Ins and COM Add-Ins
  • Interacting with Other Office Applications
  • Data Access with ADO
  • The Trust Center and Document Security
  • Browsing OLAP Data Sources with Excel
  • Excel and the Internet
  • International Issues
  • Programming the VBE
  • Programming with the Windows API