VBA for the power user and developer

VBA for Access and Excel
Versions 2010, 2013 and 2016

VBA (Visual Basic for Applications) is an extensive programming language which has seen additions and changes to its vocabulary and syntax over the years. Microsoft Access and Excel share elements of the language and each have extensions to it of their own.

Advantages in opting for DMW's on-site training, as an alternative to scheduled public courses, are that all your time is spent on your interests alone and that you are not tied to a syllabus. Additionally, with DMW you are tapping the skills of programmer built up during development of scores of databases and spreadsheets for clients since both Access and Excel arrived in the UK.


Access VBA Courses

MSA VBA

These courses are aimed at Access developers who want to get serious about VBA in order to enhance their databases' operability.

A common request is for training on the application of VBA to a database that a client has already created but which can only be made fully operational with the inclusion of some specialised code.

Topics may include the following and you can specify others to suit your objectives:

Macros and VBA (Visual Basic for Applications) Why use, and when to use, VBA; why not use macros.

Access Programming Environment Modules; Using the VB Editor; using VB Help.

Procedures and Functions Sub procedures; Function procedures; calling procedures; use of Immediate window to test code; organizing code and procedures.

References Relevance of References; managing References.

Objects Object model; object classes; object hierarchy; object collections; using the Object Browser; the Application object; the Form object.

Working on Properties Distinguishing properties; using the With...End With statements; working with events.

Working with Methods Distinguishing methods; understanding the DoCmd object.

Coding Expressions; statements.

Variables and Constants Declaring variables; variable data types; variable scope and lifetime; local and global constants.

Functions Intrinsic functions; message boxes; input boxes; custom functions.

Program Execution Control Program flow; boolean expressions; If...Else...End statements; Select Case...End Select statements; For...Next statements; For Each...Next statements; Do...Loop statements.

Good Programming Style Structured programming; applying structure to your VBA code.

Recordsets DAO; managing object variables; DAO Recordset Object; processing data sets programmatically.

Debugging Code Understanding errors; using debugging tools; testing and evaluating expressions.

Error Handling Purpose of error handling; essential error handling; VBA error object; trapping and responding to errors; general-purpose error-handling code.

Applying Code to Forms Event code; controls code; form code.

Applying Code to Reports Controls code; report code.


Excel VBA Courses

Excel VBA

The courses suit anyone who has been recording macros and now wants to gain more control over automation of their spreadsheets than recording alone provides.

If you are already into writing your own VBA code, DMW will adapt and extend course content to suit your interests.

Power users already into hard-coding VBA know there is a lot more to VBA than is suggested by the list below. For them DMW will cover in depth syntax of relevance to their particular projects.

Topics may include the following and you can specify others to suit your objectives:

Recording Macros Why and when to record; editing recorded macros

Excel VBA Using the Visual Basic Editor; VBA syntax; VBA help; projects; modules; objects and object hierarchy.

Procedures and Functions Sub procedures; function procedures; calling procedures; use of Immediate window to test code; organizing code and procedures.

References Relevance of references; potential pitfalls; managing references.

Working on Properties Distinguishing Properties; influencing properties; using With...End With statements.

Working with methods distinguishing methods; applying methods.

Coding Style and conventions; setting up modules; expressions; statements.

Variables and Constants Declaring variables; variable data types; variable scope and lifetime; local and global constants.

Functions Creating a function; using a function

Capturing User Input Message boxes; input boxes; custom dialog boxes.

Program Execution Control Program flow; boolean expressions; If...Else...End statements; Select Case...End Select statements; For...Next statements; For Each...Next statements; Do...Loop statements.

Range Object Addressing cells; the active cell; range offsets; doing things to cells; doing things with cells.

Worksheets and Workbooks Addressing worksheets; addressing workbooks.

Ribbon Customisation Introduction to the skills; how far you can get with VBA.

Debugging Code Understanding Errors; using debugging tools; testing and evaluating expressions.

Error Handling Purpose of error handling; essential error handling; VBA error object; trapping and responding to errors; general-purpose error handling code.


Course Duration

Though one day can get you started on VBA, DMW recommends two days as the best option to start with. After two days, you can assess whether additional days would be an advantage — DMW will not put you under any obligation to commit when you make your initial booking.

Courses normally run between 9:30 AM and 4:30 PM. Starts and finishes can be changed to suit your diary.

Not having to travel to a training centre, and not being far from your desk, are advantages to you of receiving training on-site.


Course Prerequisites

You'll need a thorough knowledge of Microsoft Access and Excel — to the levels of DMW's Access Advanced and Excel Advanced training courses.