Course Description
This course is designed for first-time users of Excel Visual Basic, who are familiar with the Windows environment and have used Excel to an advanced level. VBA Programming is designed to guide the programmer in writing procedures using the VBA language. The procedures you will create in this course are standard using Excel VBA to enable developers to build user-defined functions, automate processes.
Target Student
This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).
Prerequisites
Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following courses or have equivalent knowledge:
- Microsoft® Office Excel® 2013: Levels 1 - 4
Delivery Method
Instructor-led, group-paced, classroom-delivery learning model with structured hands-on activities.
Performance-Based Objectives
Upon successful completion of this course, students will be able to:
- Develop a macro.
- Format worksheets using macros.
- Create an interactive worksheet.
- Work with multiple worksheets.
- Perform calculations.
Course Content
Macros
- What is a Macro?
- Absolute and Relative Reference
- Personal Macro Workbook
- Security Issues
- Do’s and Don’ts when recording a Macro
- Recording Macros
- Running Macros
- Editing a Macro
- Creating a Button
- Creating a Tab
- Assign a Shortcut Key
- Making Macros Do Complex Tasks
- Saving a Macro-Enabled Workbook
The Visual Basic Editor
- The Visual Basic Interface
- The Code Window
- Saving and Running a Procedure
- Printing a Procedure
- Create Event and Macro Procedures
- Coding the Open Event Procedure
Working with Object Model
- Using the Object Model
- Using the Immediate Window
Working with Object Variables
- Using Variables
- Using Object Variables
- Reserving a procedure-level variable
- Naming Variables
- Use the Set Statement
- Entering a Value & Formula into a Range Object
- Previewing and Printing a Worksheet Object
Working with String Variables
- Reserving a procedure-level string variable
- The Assignment Statement
- The InputBox Function
- Concatenating Strings
- The Val Function
- The Option Explicit Statement
Working with Date Variables
- Reserving a procedure-level date variable
- Using an Assignment Statement
- Converting Strings to Dates
- The OffsetProperty
Working with Numeric Variables
- Reserving a procedure-level numeric variable
- Using the Assignment Statement
- Assigning a numeric expression to a numeric variable
Using the Selection Structure
- Using the If..Then..Else Statement
- Nesting the If..Then Else Statement
- Ucase functions
- Logical Operators
Using the MsgBox Function
- The Select Case Statement
- The MsgBox Function
- Values returned by the MsgBox function
Working with Custom Dialog Boxes
- Creating Custom Dialog Boxes
- Setting the Tab Order /Keyboard access to a control
- Assigning accelerator keys
- Using Default and Cancel Properties
- Adding a Form
- Naming a form
- The Toolbox
- Using the Toolbox to add a control to the Form
- Control Properties
Error Trapping
- Intercepting and handling run-time errors
- The On Error statement and the error-handling routine
- The Resume Statement
