VBA Programming with Microsoft Excel

4 Day Course
Hands On
Code WNXLPG6

Book Now - 1 Delivery Method Available:

Scheduled Online Onsite

Overview

Many companies have made a major investment in Excel spreadsheets to support their business. However, few organisations realise the potential for improved efficiency and effectiveness that can be realised by programming with Excel and Visual Basic for Applications (VBA). This combination allows for the development of custom spreadsheet applications, which can extend the functionality of Excel and its user interface.

Through the mixture of lectures and practical sessions you will practice all the significant aspects of Excel-based application development with Visual Basic for Applications. Delegates will learn how to create complete, robust applications.

Training Partners

We work with the following best of breed training partners using our bulk buying power to bring you a wider range of dates, locations and prices.

Modules

Hide all

Introduction (2 topics)

  • Using the Excel macro recorder and the Visual Basic Editor to create and modify macros
  • Customising menus and toolbars to run macros

The VBA Language (4 topics)

  • Simple input and output
  • Declaring and using variables of appropriate data types
  • Controlling the flow of the program
  • Debugging techniques and aids

Using Excel (7 topics)

  • Objects, properties and methods
  • Collections of Objects
  • Excel's Object Hierarchy
  • Object browser and online help
  • Useful properties and methods of the Application Object
  • Working with workbooks and sheets
  • Special VBA statements for working with Objects

Using Procedures (7 topics)

  • Program Design
  • Scope and lifetime of variables
  • Passing parameters to procedures
  • Event procedures
  • Functions
  • Writing functions to be used in worksheet formulas
  • Error handling

Working with Worksheet Ranges (5 topics)

  • Identifying a range
  • Range object properties and methods
  • Formatting
  • Navigating around a worksheet
  • Improving on the macro recorder

Designing User Forms (4 topics)

  • Uses of different control types
  • Displaying a user form
  • Validating user input
  • Using built-in dialogs

The User Interface (7 topics)

  • Using controls on worksheets
  • Creating and modifying CommandBars by program
  • Using Event Procedures to set up the desired environment
  • Protection
  • Hiding Excel's interface
  • Providing Help
  • Making an Add-In

Getting Data into Excel (6 topics)

  • Using Microsoft Query
  • QueryTables and Parameters
  • Use of ADO objects to access databases
  • Importing from text files
  • Importing data from Web Pages
  • Using links

Analysing Data (4 topics)

  • Sorting
  • Filtering
  • Excel's data analysis functions
  • Pivot Tables

Presenting Results (7 topics)

  • Charting
  • Chart object model
  • Creating generic charts
  • PivotCharts
  • Report generation by controlling Microsoft Word
  • Sending results by email
  • Publishing to the Web

Prerequisites

Delegates must have used Excel for at least three months. An understanding of programming concepts, such as looping, conditional statements and variables, is essential. Required programming skills can be obtained by attending the 2-day course "Introduction to Programming with Visual Basic for Applications" (QAVBAINT). Programmers, systems analysts and support professionals who want to create and support custom spreadsheet applications using Microsoft's Visual Basic for Applications. This course is not suitable for Excel users without programming experience (see Prerequisites section). This course is relevant for any version of Microsoft Office from 97 to 2003

Additional Learning

The courses below may help you meet the knowledge level required to take this course. If you are unsure please ask a training advisor .

  • Introduction to Programming with VBA

    The two-day Introduction to Programming with VBA training course introduces programming fundamentals as a lead-in to other courses.

    2 Day Course Hands On Training Course Code QAVBAINT
    Scheduled Online Onsite

or call:408-759-5074

Course PDF

Print

Share this Course

+1
Share

Recommend this Course

Sections