Universal Class: Over 500 Online Certificate Courses

Course Syllabus: Visual Basic for Excel

Computer Programming Picture
no certificate
- OR -
with CEU Certificate*
Start Right Now!
Taking multiple courses? Save with our platinum program.

Course Description

Developer tools in Microsoft Office give you the functionality to create custom applications in Excel. These tools require Visual Basic for Applications (VBA). The developer can create custom spreadsheets, workbooks, controls, and even toolbar add-ins for users.

Visual Basic is a specific Microsoft language, so it integrates into any Microsoft productivity tool. This course focuses on Excel customizations and applications. To create these customizations, you first need to learn macros and what they can do for your projects. We then go into modules and custom coded macros. Visual Basic is an object-oriented language, so we show you how to create custom classes that plug into your Excel modules.

This course not only focuses on Visual Basic syntax, but we also focus on working with classes, properties and methods specific to the Visual Basic version for Excel. We show you how to data mine from external source, connect to databases, and even scrape Internet content to include in a local spreadsheet.

We also work with pivot tables, charts and graphs that you can automatically draw and render within an Excel spreadsheet. Later chapters include instructions and tutorials on sparklines and working with external files. We help you develop skills in error handling to provide better ways to work with errors in your code.

Whether you're new to Excel or just to the Visual Basic for Application language, this course will help you develop the skills necessary to build custom applications that work with a variety of industries and users.

Course Requirements

There are no pre requisites. Anyone over the age of 13 is welcome to join this course. It is recommended though that individuals have some basic computer programming knowledge. 

Course Topics

1. Getting Excel ready

a.    Enabling the developer toolbar

b.    Understanding file types supported by macros

                                               i.     Excel Workbook (.xls) – Macros supported

                                             ii.     Excel Workbook (.xlsx) – Not supported

                                            iii.     Excel Macro-Enabled Workbook (.xlsm) – Macros supported

                                            iv.     Excel Binary Workbook (.xlsb) – Macros supported

c.     Configure Excel to save as .xlsm by default

d.    Macro security

                                               i.     Setting trusted locations

                                             ii.     Enabling Macros by clicking button on toolbar

                                            iii.     Disable notifications for other locations

2.    Using the macro recorder

a.    Give macro a name

b.    Add a shortcut key

c.     Store macro in workbook vs. separate file

d.    Adding a description

3.    Running a macro

a.    Adding a macro to the quick access toolbar

b.    Button form control

c.     Clipart

d.    Shapes and Word Art

e.    SmartArt Graphics

4.    Visual Basic Editor (VBE)

a.    How to access (Alt + F11)

b.    Layout of the window

                                               i.     Project explorer

                                             ii.     MSE Objects

c.     How to rearrange the display window

d.    Debugging VBA code

                                               i.     Add breakpoint

                                             ii.     Step into, over, continue

5.    Problems with the recorder

a.    Unnecessary code added

b.    Difficult to troubleshoot

c.     Uses absolute references to cells

6.    Introduction to Object-Oriented Programming (OOP)

7.    Using Message Boxes

8.    Ranges and Selections

a.    Named ranges

b.    Range references

c.     Relative ranges

d.    Cells, offset, resize, columns, rows, union, intersect, regions

e.    Referencing tables

9.    Looping / Flow Control

a.    For / Next loops

b.    Do loops

c.     While loops

d.    For / Each loops

10. Selection Statements

a.    If / Then / Else

b.    Select / Case

11. A1 vs. R1C1 references

a.    Toggling style references

b.    Using Excel formulas

c.     Using Arrays

12. Excel Names

a.    Global vs. local variables

b.    Adding / Hiding / Deleting Names

c.     Checking Name existence

d.    Comments

e.    Types of Names

13. Event Programming

a.    Event levels

b.    Using Events

c.     Workbook / Worksheet / Chart events

d.    Application-Level events

14. Arrays

a.    Declaring single-dimension and multi-dimension arrays

b.    Propagating an array

c.     Retrieving data from an array

d.    Reasons and examples of why to use arrays

e.    Dynamic arrays

f.      Passing arrays

                                               i.     Reference

                                             ii.     Argument

15. Classes, Records, and Collections

a.    Class Modules

b.    Trapping Events

c.     Custom Objects

d.    Using collections

e.    User-Defined Types

f.      Custom Properties

16. User Forms

a.    Interaction methods

b.    Creating / Calling / Hiding a Userform

c.     Programming a Userform / Controls

d.    Verifying Data Entry

e.    Filenames

17. Data Mining with Advanced Filter

a.    Replacing Loops

b.    Extracting lists of values

c.     Criteria Ranges

18. Pivot Tables

a.    Introduction and understanding Pivot Tables and versions

b.    Examples

c.     Filtering Datasets

d.    Using the Data Model

19. Charts

a.    Creating and customizing charts

b.    Combo charts

c.     Advanced charts

d.    Exporting charts as graphics

e.    Pivot charts

20. Conditional Formatting

a.    Data Visualization

b.    Data Bars, Color Scales, Icon Sets

21. Sparklines

22. Reading from and writing to the Web

23. Text file processing

24. Automating Word functions

25. Handling errors

26. Customizing the ribbon

27. Creating Add-Ins

Course Materials

No additional course materials required to complete this course.

Grading Policy

A brief 10 pt. quiz will follow each lesson. Students will successfully complete this course by mastering all learning outcomes with 70% or higher overall grade.

Assessment Guide

Exam 110
Exam 210
Exam 310
Exam 410
Exam 510
Exam 610
Exam 710
Exam 810
Exam 98
Exam 1010
Exam 1110
Exam 1210
Exam 139
Exam 1410
Exam 1510
Exam 1610
Exam 1710
Exam 1810
Exam 1910
Exam 2010
Exam 2110
Exam 2210
Exam 2310
Exam 2410
Exam 2510
Exam 2610
Exam 2710
Final Exam111
Total Points:378
Show More

Related Articles