Course Info
What Will I Learn From This Course?
Create recorded macros in Microsoft Excel
Use the macro recorder to create a variety of macros
Understand the Microsoft Excel object model and VBA concepts
Create command procedures
Create and use variables
Write code to manipulate Microsoft Excel objects
Use a range of common programming techniques
Pre-requisites
Participant required to have knowledge in Microsoft Excel Basic & Intermediate Level.
Course Outline for This Programme
1. Module: Recorded Macros
a. Understanding Microsoft Excel macros
b. Setting macro security
c. Saving a workbook as a macro enabled workbook
d. Recording a simple macro
e. Running a recorded macro
f. Viewing a macro
g. Editing a macro
h. Running a macro from the toolbar
i. Assigning a keyboard shortcut to a macro
j. Tips for developing macros
2. Module: Recorder workshop
a. Preparing data for an application
b. Recording a macro that updates opening balances
c. Recording a macro to open text files
d. Creating objects to run macros
e. Assigning a macro to an object
3. Module: Understanding VBA
a. VBA terminology
b. About objects
c. Accessing the Microsoft Excel object model
d. Using the immediate window
e. Working with object collections
f. Setting property values
g. Working with worksheets
h. Using the object browser
4. Module: Using the VBA editor
a. The VBA editor
b. Opening and closing the editor
c. Working with the project explorer
d. Working with the properties window
e. Working with a code module
f. Stepping through a procedure
5. Module: Procedures
a. About procedures
b. Creating a command procedure
c. Making sense of intellisense
d. Using the edit toolbar
e. Commenting statements
f. Indenting code
6. Module: Using variables
a. Understanding variables
b. Creating and using variables
c. The scope of variables
d. Procedure level scoping
e. Module level scoping
f. Passing variables by reference
g. Passing variables by value
h. Data types
i. Declaring data types
7. Module: Using Microsoft Excel objects
a. Application methods and properties
b. Workbook methods and properties
c. Viewing Microsoft Excel and the editor together
d. Using workbook objects
e. Worksheets methods and properties
f. Using worksheet objects
g. Range methods and properties
h. Using range objects
i. Using objects in a procedure
8. Module: Programming techniques
a. The msgbox function
b. Sending messages to the user
c. Inputbox techniques
d. Using the inputbox function
e. Using if for multiple conditions
f. Looping with known or specified iterations
g. The do…loop statement
h. Looping with unknown iterations
