Introduction to Excel VBA 

This course will show delegates how to use the Visual Basic for Applications (VBA) programming language to simplify and automate many of the repetitive tasks that are part of spreadsheet development.

Course Topics

Recording Macros

using the macro recorder • running macros via worksheet or toolbar buttons • relative and absolute cell references • options for storing a macro

Visual Basic Editor (VBE) Window

familiarisation with the VBE window • excel objects, user forms & modules • procedures, keywords, statements & comments • customising the VBE window

Creating and Modifying VBA Code

making long statements readable • using message boxes to interact with the user • using input boxes to get information from the user

Working with Worksheets and Cells

navigating around worksheets • editing & formatting cells • moving around lists of data • working with named ranges

Introducing Functions and Variables

variables and variable types • using a naming convention for your variables • using built-in worksheet functions

Decision Making within VBA Procedures

using If Then Else statements • using Select Case statements

Looping Through Data

using Do While or Do Until statements • exploring the use of the For Next statement

De-Bugging your Code

break mode • stepping through code • using auto data tips, call stack, expressions • using the immediate window, local window

On-sheet Controls

using “controls” on the worksheet • setting the properties of controls • linking controls to the worksheet 

Working with Workbooks 

opening other workbooks with re-usable code • navigating between different workbook • saving a workbook file

Creating Re-usable Code

passing arguments to a procedure • exploring the benefits of re-usable code

Handling Errors within your Code 

trapping errors • adding code to handle the errors • determining what error has occurred • exiting from the error handling code

Variables and their scope and lifetime

testing the content of a variable • local, module and static variables • global and private variables • using constants

Working with Data 

manipulating data contained within a single string • manipulating dates

Creating User Defined Functions 

creating a simple function • creating a function for a more complex formula you use frequently

User Forms

how to create simple custom forms in Excel • adding controls to a user form • adding code to respond to events

Good Coding Guide

 

 

Follow on Course

• 1-day Excel VBA Advanced