About Microsoft Excel VBA Macro Advanced • Level 2
Microsoft Excel VBA 2 – “robust and user friendly applications”
This Microsoft Excel advanced VBA course follows on from our VBA Introduction course. The focus of the course is to develop robust and user friendly applications. Issues surrounding performance are discussed and implemented. Integration with both Word, Access and Outlook files are built into the case study which forms a mini project to be completed during the course. User Forms and related controls are used extensively as well as an introduction to the development of Class Modules. You are encouraged to consider the development of your own Functions and several examples are written during the course. Adopting good programming practices will be encouraged throughout the course.
Is this course for you?
You can already create and edit macro’s using Excel VBA programming techniques, but want to fill in gaps and expand your VBA capabilities.
Join this scheduled course at our training centre in Whiteley, near Fareham, Portsmouth and Southampton.
Or, consider onsite training using our computers at no extra cost. £400/delegate or £2,000 for up to eight delegates on the same course. Minimum charge: £1,600 plus vat for two days training.
- Lunch and Refreshments – let us know if you have any special dietary needs. [Excluding London]
- Microsoft Training Manual
- Post course training downloads
- Certificate on completion
- Post course support relating to course content
Aim & Objectives
- Complete advanced elements of VBA
- Adopt programming best practice
By the end of this level you will be able to:
Conduct Error Trapping
Employ a range of additional variables
Use data from a Word document
Create forms with controls
Use built-in dialog boxes
Add a calendar for data entry
Create runtime applications and add-ins
Run Access from VBA
Build a function library
Review VBA 1 course key elements
Error Trapping and the CASE statement – Building a generic Error handler – Testing for Errors with “Raise”.
Single and Multi-Dimension Array Variables
Using Data from MS Word
Building an Array from a Word document
Using Object Variables – Creating Object Variables – Setting Object Variables
Controls Dialogue Boxes and Forms
Built-In & Predefined Dialogue Boxes – Adding Form Controls, including Calendar – Multi-choice Controls – Control & Dialogue Box Events
Using VBA References
Creating Runtime applications and Add-Ins – Running Access from VBA – Processing Access Recordsets
Building a Function Library
Advantages of writing generic Functions – Open any Workbook – Open all Workbooks – Manipulate String Data
Procedures, Parameters, Variables
Passing Parameters by Reference and by Value (ByRef ByVal) – Optional and Default Parameters
Dealing with Files
Reading Text Files – Using CSV files
Creation of a Chart as a Sheet or as an Object within a Sheet – Maintenance of Key Attributes of a Chart
Create a Pivot Table and Definition of its source data – Maintenance of Key Attributes of a Pivot Table
Working with Events
Overview of event types – Workbook events – Worksheet events – UserForm events – Activate events – Save events