Excel 2013 VBA and Macros
CLASS DATE(s):
Request a Class

COURSE LENGTH: 3 Days

COURSE COST: $1195.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print

COURSE OVERVIEW

This three-day course takes you through Visual Basic for Application in Excel, all the way from recording Excel VBA macros through to designing your user forms. On the way you will learn what variables are, how to write object-oriented programs, how to create loops and much more besides. VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. Without your waiting for resources from IT, VBA helps you figure out houw to import data and produce reports in Excel.

AUDIENCE AND PREREQUISITES

You must be an Experienced Excel user who wish to automate repetitive tasks or simplify more complex tasks using Excel 2013 Macros and Visual Basic for Applications (VBA) programming language. You must have good Excel skills and understand key concepts of spreadsheets or equivalent. It is not necessary to have prior programming knowledge. We cover VBA Macro programming concepts from the very basic level. Excel 2013 Level 1, 2 & 3.

  

*Course cost listed does not include the cost of courseware (required) or lunch (optional). Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to minimum enrollment. Course may run as a live distance learning class if minimum is not met.

COURSE TOPICS:


Introduction
Getting Results with VBA

Unleash the Power of Excel with VBA
The Power of Excel
Barriers to Entry
Knwoing your Tools: The Developer Tab
Understand Which File Types Allow Macros
Macro Security
Overview of Recording, Storing, and Running a Macro
Running a Macro
Understanding the VB Editor
Understanding Shortcomings of the Macro Recorder

This Sounds like BASIC, So Why Doesn't It Look Familar?
I Can't Understand This Code
Understanding the Parts of VBA "Speech"
VBA is Not Really Hard
Examining Recorded Macro Code: Using the VB Editor and Help
Using Debugging Tools to Figure Out Recorded Code
Object Browser
Seven Tips for Cleaning Up Recorded Code

Referring to Ranges
The Range Objects
Syntax to Specify a Range
Named Ranges
Shotcut for Referencying in Other Sheets
Referencing a Range Related to Another Range
Use the Cells Property to Select a Range
Use the Resize Property to Change the Size of a Range
Use the Columes and Rows Properties to Specify a Range
Use the Union Method to Join Multiple Ranges
Use the Intersect Method to Create a New Range from Overlapping Ranges

Looping and Flow Control
For Next Loops
Do Loops
the VBP Loop For Each
Flow Control Using If.. Then..Else and Select Case

R1C1-Style Formulas
Referring to Cells A1 Versus R1C1 References
Toggling to R1C1-Style References
The Miracle of Excel Formulas
Explanation of R1C1 Reference Syle
Array Formulas Require R1C1 Formulas

Create and Munipulate Names in VBA
Excel Names
Global Versus Local Names
Adding Names
Deleting Names
Adding Comments
Types of Names
Hiding Names
Checking for the Existence of a Name

Event Programming
Levels of Events
Using Events
Workbook Events
Worksheet Events
Chart Sheet Events
Application-Level Events

Arrays
Declare an Array
Declare a Multidimensional Array
Fill an Array
Retrieve Data from an Array
Use Arrays to Speed Up Code
Use Dynamic Arrays
Passing an Array

Creating Classes, Records, and Collections
Inserting a Class Module
Trapping Applicat5ion and Embedded Chart Events
Creating a Custom Object
Using a Custom Object
Using Property Led and Property Get to Control How Users Utilize Custom Objects
Using Collections to Hold Multiple Records
Using User-Defined Types to Create Custom Properties

Userforms An Introduction
User Interaction Methods
Creating a Userform
Calling and Hiding a Userform
Programming the Userform
Programming Controls
Using Basic Form Controls
Verifying Field Entry
Illegal Window Closing
Gettinga Filename

Data Mining with Advanced Filter
Replacing a Loop with AutoFilter
Advanced Filteris Easier in VBA Than in Excel
Using Advanced Filter to Extract a Unique of Values
Using Advanced Filter with Criteria Ranges
Using Filter in Place in Advanced Filter
The Real Workhorse xlFilterCopy with All Records Rather Than Unique Records Only

Using VBA to Create Pivot Tables
Introducing Pivot Tables
Understanding Versions
Building a Pivot Table in Excel VBA
Using Advanced Pivot Table Features
Filteringa Dataset
Using the Data Model in Excel 2013
Using Other Pivot Table Features

Excel Power
File Operations
Combining and Seperating Workbooks
Working with Cell Comments
Utilities for VBA Pros
Cool Applications

Sample-User Defined Functions
Creating User-Defined Functions
Sharing UDFs
Useful Custom Excel Functions

Creating Charts
Charting in Excel 2013
Understanding the Global Settings
Creating a Chart in Varios Excel Versions
Customizing a Chart
Creating a Combo Chart
Creating Advanced Charts
Exporting a Chart as a Graphic
Creating a Chart as a Graphic

Data Visualizations and Conditional Formatting
Introduction to Data Visualizations
VBA Methods and Properties for Data Visualizations
Adding Data Bars to a Range
Adding Color Scales to a Range
Adding Icon Sets to a Range
Using Visualization Tricks
Using Other Conditional Formating Methods

Dashboarding with Sparklines in Excel 2013
Creating Sparklines
Scaling Sparklines
Formating Sparklines
Creating a Dashboard

Text File Processing
Importing from Text Files
Writing Text Files

Automating Word
Using Early Binding to Reference the Word Object
Using Late Binding to Reference the Word Object
using the New Keyword to Reference the Word Application
using the CreateObject Function to Create a New Instance of an Object
Using the GetObject Function to Reference an Existing Instance of Word
Using Constant Values
Using Word's Objects
Controlling Form Fields in Word

Using Access as a Back End to Enhance Multiuser Access to Data
Adding and Retrieving a Record to the Database
Updating an Existing Record
Deleting Records via ADO
Other Utilites via ADO
SQL Server Examples

Advanced Userform Techniques
Using the UserForm Toolbar in the Design of Controls on Userforms
More Userform Controls
Controls and Collections
Modeless Userforms
Using Hyperlinks in Userforms
Adding Controls at Runtime
Adding Help to the Userform

Windows APi

Handling Errors

Customizing the Ribbon to Run Macros