Use Visual Basic code to automate tasks and create procedures to run in Microsoft Excel spreadsheets.

Open participant course: £590 per person - maximum 10 people

Closed company course: £3,000 per course - maximum 10 people

Those who need to exploit Microsoft Excel Visual Basic for Applications to automate tasks and create functionality not found in the standard Microsoft Excel interface.

By the end of the course, you will be able to understand and create VBA code. You will be able to create procedures, user-defined functions and forms to help automate Microsoft Excel spreadsheets for yourself and others.

Macros Revision:

• Recording and running macros.
• Adding macros to the Microsoft Excel interface.
• Setting macro protection levels.

Getting Started with Visual Basic Editor:

• Exploring the Visual Basic editor.
• Understanding VBA procedures.
• Commenting and editing VBA code.

Objects and Object-Oriented Programming:

• Understanding objects and dot syntax.
• Referencing cells with the range object.
• Organising code using “with...end with”.

Editing Features:

• Running code in the immediate window.
• Completing code with auto lists.

Message Boxes and Input Boxes:

• Creating and customising message boxes.
• Receiving information from message boxes.
• Creating input boxes to receive user data.
• Understanding and customising input boxes.

Variables:

• Understanding and naming variables.
• Methods of declaring variables.
• Defining variable data types.
• Declaring and using constants.
• Assigning values and objects to variables.

Writing Procedures and Function Procedures:

• Calling procedures from different locations.
• Understanding and using intrinsic functions.
• Writing and using custom functions in VBA and the Microsoft Excel interface.

Conditional Control Structures:

• Controlling program flow using if structures.
• Using select case structures.
• Looping control structures.
• Repeating code using loops.
• Definite loops.
• Indefinite loops

Controls Dialog Boxes and Forms:

• Creating user forms.
• Using form controls.
• Adding VBA code to user forms.

Using Pivot Tables:

• Creating and manipulating pivot tables with VBA.

 Debugging and Error Trapping:

• Identifying error types.
• Reducing errors in code.
• Checking code with debugging tools.
• Writing code to handle errors.

This course can be delivered remotely or in a classroom environment, to suit your requirements.

Attendees should have an advanced understanding of creating spreadsheets and manipulating data in Microsoft Excel. 

Attendees must have the following equipment to attend this training remotely:

• PC or laptop with a microphone and speakers (webcam is preferred but not essential).
• Access to the internet and web browser application (e.g. Google Chrome).
• Microsoft Excel installed on the PC or laptop.
• An email account for file sharing.
• A second screen is ideal but not essential.