Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

This course is designed to introduce delegates to the use of macros and VBA programming to automate processes in Excel, this includes editing and scripting macros. It looks at basic coding rules and offers an overview of object models, properties and methods. Also includes repeating processes using loops.

At the end of this course you should have a good understanding of the basic structures and techniques available in the programming language of Visual Basic for Applications.

Prerequisite

Attendees should have a thorough working knowledge of Excel before joining this course. No previous programming experience is necessary.

Course outline

Introduction to Macros

  • Recording a Macro
  • Absolute vs. Relative Record
  • Assigning a macro to a button

Overview of VBA as an Object Based Language

  • Explanation of Objects
  • Definition of an Object Model
  • Use of objects, properties and methods

Editing Macros

  • Layout of the Visual Basic Editor
  • Structure of a procedure
  • Overview of colour coding in VBA
  • Basic coding rules

Interactive Functions

  • Display a message using MSGBOX
  • Ask a question using MSGBOX
  • Prompt for data using the INPUTBOX function
  • Using multiple criteria when looking up duplicate rows

Making Decisions using Conditional Logic

  • The IF statement
  • The SELECT CASE statement

Using Variables and Constants

  • Declaring a variable
  • Exploring variable data types
  • Scope of variables
  • Using OPTION EXPLICIT

Repeating Processes using Loops

  • The FOR .. NEXT loop
  • The DO UNTIL / DO WHILE loop
  • The FOR EACH .. NEXT loop