Microsoft Excel VBA Introduction

To create and edit VBA code to automate elements of MS Excel.

• Record and Edit Excel macros
•  Make informed decisions on when to Record macros and when to write VBA programs
•  Run macros or VBA programs from a worksheet using command and/or toolbar buttons
•  Open, Close, Save Workbook(s)
•  Insert, Delete, Rename Worksheet(s)
•  Close/save all Open workbooks and Quit Excel
•  Interact with the PC user through messages and keyboard input including selections from dropdown lists
•  Construct efficient and easily maintainable VBA Programs
•  Identify beginning and end of a range of worksheet cells
•  Extract user specified data from a range of cells (similar to but beyond filtering)
•  Build multiple worksheets from data extracted from other sheets/workbooks
•  Format ranges of cells
•  Carry out arithmetic within VBA or insert Excel functions into a sheet
•  Trap and handle user generated errors
•  Develop your own functions similar to those offered by Excel

Content

Introduction

What is a Macro

Recording & Running a Macro

Relative References

 

Assigning Macros to Buttons and Objects

Assigning a Macro to a Button or a Drawn Object on a Sheet

Assigning a Macro to a Button on a Toolbar

 

Editing Macros

Visual Basic Editor

Project Explorer

Procedures; Inserting Comments

Printing a Visual Basic Module

 

Improving macro performance

Aesthetic improvements

Increasing speed

 

Creating VBA Projects

Structuring an application

Identifying Modules and procedures

Writing and testing tight and concise VBA code

Adopting good programming practices

 

Developing the course project

Collecting user input

Verifying success of each procedure

Understanding an application’s requirements

 

The Range Object

Cells & Range Method; Flexible Ranges

Virtual Worksheets; Range Contents

 

Workbooks and Sheets

Activating a Workbook & Worksheet

This Workbook

 

Objects, Properties & Methods

Controlling Objects with their Properties

Performing Actions with Methods

Performing Multiple Actions on an Object

 

Variables

Variables and associated Data Types

Object Variables

 

Control Structures

Conditional & Looping Control Structures

Non-Structured Statements

Sub Procedures

 

Using Controls

Adding Worksheet Controls

Worksheet Control Properties

 

Designing VBA Forms

Using Drop Down Lists

 

Debugging & Error trapping

Errors, Break Mode; Instant Watch

Stepping & Error Handling

 

Function Procedures

User Defined Functions

 

Course summary

Save VBA project to disc