Excel VBA Macro Programming
Overview
Visual Basic for Applications is the integrated programming language used in Excel to create Excel Macros. In this class we take an in-depth look at this language starting from the ground up, and how it is applied to developing programs & automating operations in Excel.
Price
$1,295 per Student
CPE Credits
16 CPE Credits
Prerequisites
For the best results, participants should be familiar with the topics covered in the Excel Introduction, Excel Intermediate Part 1, and Excel Intermediate Part 2 classes.
Duration
2 Days
Class Outline
Introduction to VBA
- Why use VBA if I can record macros in Excel?
- Recoding a Simple macro
- Reviewing the code
- Familiarisation with the VBA environment
- Running Code from the VB Editor window
- Getting help on code
- Stepping through a procedure
- Using a Break point
- Communicating with the User
VBA Terminology
- Modules and procedures
- Components of your code
- Objects, Collections, Properties and methods
- Using the Excel Object model
Data Types, Variables and Operators
- Working with variables and constants
- Using Data types
- Working with operators and expressions
- Implicit and Explicit Declarations
- Variable Scope – Procedural, Modular or Public
- Passing variables by Value
- Passing variables by Reference
- Using Arrays
Workbooks and Worksheets
- Workbook objects and methods
- Creating a new workbook
- Adding and Renaming Sheets
- Saving and Closing a Workbook
Functions
- Mathematical functions
- Date and time functions
- String functions
- Using Excel functions in VBA code
User Defined Function Procedures
- Creating User Defined functions
- Using user-defined functions in a worksheet
- Declaring Multiple arguments in a function
- Creating a Function Library
Loops
- Do Until and Do While loops
- Looping at least once
- For Next Loop
- For Each Loops with collections
Decision Structures
- Using IF to make decisions
- Testing for multiple conditions
- Establishing Flow control
- Branching
- Call other procedures
Working with Names
- Adding Names
- Deleting Names
- Creating Hidden Names
- Checking for the Existence of a Name
Manipulating data
- Working with the Ranges and Selections
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Columns and Rows Properties
- Using the Union Method to Join Multiple Ranges
- Using the IsEmpty Function
- Using the CurrentRegion Property
- Using the Areas Collection
File management techniques in VBA
- Open and Save files
- Copy, move and delete files
- Changing folders
- Using Excel Open and SaveAs dialog boxes in code
Custom Forms / Dialog boxes
- Creating a user form
- Labels, text boxes, combo boxes and list boxes
- Setting properties for the form and controls
- Assigning data to combo boxes and list boxes
- Option buttons, Groups, checkboxes and buttons
- Creating the event code for controls
- Initialising the form
- Closing the form
- Using RefEdit to allow user interaction
Managing Information with VBA
- Linking Excel to an Access database
- Adding a Record to the Database from Excel
- Retrieving Records from the Database
- Updating an Existing Record
Excel VBA Macro Programming Schedule
In-Person Classes Schedule
| Date | Location | Price | Registration |
Live Online Classes Schedule
| Date | Location | Price | Registration |
Don't see a Class Date that works for you?
Let us know when you would like to take the class and we will see if we can add a new class date to our schedule.
Oklahoma City Public Classes
Contact Us
Phone
405-438-6287
Office Address
100 NE 5th Street
Oklahoma City, OK 73104
Mailing Address
PO Box 712
Aledo, TX 76008
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.