Introduction

This Microsoft Excel course demonstrates how you can use a combination of Microsoft Excel and VBA to increase your productivity, adding power and flexibility to spreadsheets, automate processes and improve user interaction. It requires intermediate to advance knowledge of Microsoft Excel for participants to attend this course. Programming knowledge, however, is NOT required.

This Microsoft Excel VBA training course is designed for users who wish to learn how to use the built-in macro programming language in Microsoft Excel to enhance their worksheets and automate processes to increase productivity.

Prerequisites

Intermediate to advance knowledge of Microsoft Excel is required.

Course Objectives

Upon completion of this Microsoft Excel VBA training course, participants should have learned Microsoft Excel VBA sufficiently to be able to:

1. Create recorded macros in Microsoft Excel
2. Use the macro recorder to create a variety of macros
3. Understand the Microsoft Excel object model and VBA concepts
4. Create command procedures
5. Create and use variables
6. Write code to manipulate Microsoft Excel objects
7. Use a range of common programming techniques

Course Outline

RECORDED MACROS

1. Understanding Microsoft Excel macros
2. Setting macro security
3. Saving a workbook as a macro enabled workbook
4. Recording a simple macro
5. Running a recorded macro
6. Viewing a macro
7. Editing a macro
8. Running a macro from the toolbar
9. Assigning a keyboard shortcut to a macro
10. Tips for developing macros

RECORDER WORKSHOP

1. Preparing data for an application
2. Recording a macro that updates opening balances
3. Recording a macro to open text files
4. Creating objects to run macros
5. Assigning a macro to an object

UNDERSTANDING VBA

1. VBA terminology
2. About objects
3. Accessing the Microsoft Excel object model
4. Using the immediate window
5. Working with object collections
6. Setting property values
7. Working with worksheets
8. Using the object browser

USING THE VBA EDITOR

1. The VBA editor
2. Opening and closing the editor
3. Working with the project explorer
4. Working with the properties window
5. Working with a code module
6. Stepping through a procedure

PROCEDURES

1. About procedures
2. Creating a command procedure
3. Making sense of intellisense
4. Using the edit toolbar
5. Commenting statements
6. Indenting code

USING VARIABLES

1. Understanding variables
2. Creating and using variables
3. The scope of variables
4. Procedure level scoping
5. Module level scoping
6. Passing variables by reference
7. Passing variables by value
8. Data types
9. Declaring data types

USING MICROSOFT EXCEL OBJECTS

1. Application methods and properties
2. Workbook methods and properties
3. Viewing Microsoft Excel and the editor together
4. Using workbook objects
5. Worksheets methods and properties
6. Using worksheet objects
7. Range methods and properties
8. Using range objects
9. Using objects in a procedure

PROGRAMMING TECHNIQUES

1. The msgbox function
2. Sending messages to the user
3. Inputbox techniques
4. Using the inputbox function
5. Using if for multiple conditions
6. Looping with known or specified iterations
7. The do…loop statement
8. Looping with unknown iterations

 Register Now