Programming Microsoft Excel with Visual Basic for Applications (VBA)
- Open Schedule
This course has been developed for advanced Microsoft Excel users, who
need to automate cell, worksheet and workbook handling using Word Visual
Basic for Applications (VBA).
Course Duration: 2 Days
Course Pre-requisites: Competence with Microsoft Windows. Experienced
user of the many features covered on the Introduction, Intermediate and
Advanced Microsoft Excel courses.
At the end of this course you will be able to:
-
Appreciate the benefits of using Visual Basic (VBA) macros.
-
Record, run and edit basic Excel macros.
-
Interpret and edit VBA code.
-
Apply programming techniques such as conditional branching and loops
to your macros.
-
Acquisition of user data and the display of messages.
-
Create User Forms to manage complex user input.
Course content:
Introducing MacrosExplaining the purpose and potential of Excel
macros.
VBA (Macro) Basics
Recording simple macros
to automate routines such as document formatting.
Using convenient ways
of running macros - menus, toolbars, buttons within documents, and
shortcut keys.
Cell Referencing
Using Absolute and
Relative referencing in macros.
Methods for Excel navigation and
selection.
Working with Excel Objects.
Editing VBA
Program Code
An introduction to the programming behind recorded
macros.
Understanding the syntax of VBA code.
Making changes and
additions to code.
Adding comments.
Using the Basic Help system.
Extending
Control
Introducing some commonly used commands to give more
power and flexibility to your macros, such as making the actions of your
macro conditional on some activity.
Creating non-recordable code for:
Toggling
settings.
Managing user-input and displaying messages.
Declaring and
using variables.
Use of conditional statements.
Responding to
'Events'.
User Forms
Creating and utilising custom
User Forms to manage user interaction.
Processing data returned from
user forms.
Troubleshooting Problems
Techniques to
help find and resolve problems:
Identifying types of problem.
Interrupting
programs.
Monitoring values while programs are running.
Using the
De-bug and watch windows.
