Introduction to Excel Power Macros (VBA)

Course duration

2 das

Course timings

9:30-16:30

Prerequisites

A thorough knowledge of Excel is essential: however, no knowledge of Visual Basic for Applications is needed.

Training formats

Scheduled course · private course · tailored · 1 to 1 (all instructor led)

Manuals

A comprehensive course manual provides support throughout the course and upon your return to the office.

Calendar

Click here to see the Excel courses calendar

Click here to see the Excel VBA courses calendar

 

• Print
• Download pdf

Course Topics

Recording Macros

using the macro recorder · running macros via worksheet buttons 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 and modules · reviewing VBA code · procedures, keywords, statements and comments · Indenting code · customising the VBE window.

Creating and Modifying VBA Code

deleting code · making long statements readable · using message boxes to interact with the user · using input boxes to get information from the user · copying code in the VBE window.

Working with Worksheets and Cells

navigating around worksheets · editing and formatting cells · copying and pasting information · inserting worksheets, rows, columns and cells · deleting worksheets, rows, columns and 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 (Part 1)

break mode · stepping through code · using auto data tips.

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.

De-Bugging (Part 2)

using the immediate window · using the locals window · using watch expressions · using the call stack.

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 user defined functions.

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

summary of the principles to be adopted when creating VBA procedures.