Office Productivity
Microsoft
Excel VBA
Ultimate Excel VBA
4.6 (2,926 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
11,132 students enrolled

Ultimate Excel VBA

Programming for the Business Professional
4.6 (2,926 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
11,132 students enrolled
Created by Mark Talbert
Last updated 8/2013
English
English, Indonesian [Auto-generated], 3 more
  • Polish [Auto-generated]
  • Romanian [Auto-generated]
  • Thai [Auto-generated]
Preview this course
Ultimate Excel VBA
Current price: $16.99 Original price: $24.99 Discount: 32% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 8.5 hours on-demand video
  • 34 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Course Goal: You will obtain practical fluency in Visual Basic for Applications (VBA) specifically as it applies to Microsoft Excel.
  • Course Objectives: 1) You will learn to automate and extend Excel by adding VBA projects to your workbooks. 2) You will learn to enhance, customize and extend the Excel user interface through modifications to the Excel environment, the creation of custom user forms, and the use of ActiveX controls. 3) You will learn how to respond to workbook, worksheet and application events to automate macro execution. 4) You will learn how it is possible to reach outside of Excel by way of VBA, in order to perform cross-application tasks.
Requirements
  • Microsoft Excel (2007-2013)
Description

This is an introductory course on how to program Excel using VBA macros. You will learn to automate and extend the Excel environment to supercharge your Excel models, automate routine tasks, customize your user interface, and manage large quantities of data. This course is based on the instructor's semester-long college courses which have produced thousands of graduates in the fields of business, finance, health care and public policy. Designed for busy professionals who need to take their Excel abilities to the next level, you will not waste time on topics which you are unlikely to use, and you will receive extra attention to the often confusing or overlooked details that will make the most difference to your ultimate understanding. Over 8 hours of instruction!

Who this course is for:
  • This is an introductory programming course, so no previous programming experience is assumed. The student should be at least moderately comfortable with Excel. The more prior exposure to Excel you have the more you will get out of the course, but you do not need to be an Excel expert to take the course.
Course content
Expand all 36 lectures 08:34:13
+ – Introduction
3 lectures 37:13
A brief overview of this course. What VBA really is and how it can be used. Also, what prior knowledge is recommended and tips for getting the most out of the course.
Course Overview
Preview 10:20
An introduction to using the macro recorder, adding the Developer tab to the ribbon, and Absolute versus Relative recording modes. Also, saving macro-enabled workbooks, and macro security.
Macro Recording Basics
13:52
An introduction to the VBA editor window, moving back and forth between editor and workbook, and managing VBA projects.
The VBA Editor
13:01
This quiz covers the topics presented in Section 1.
Macro Recorder and the VBA Editor
5 questions
+ – The Excel Object Library
9 lectures 02:10:21
A brief explanation of objects, properties and methods, and a look at the relationship between the VBA language and the Excel object library.
VBA & The Object Library
09:03
An example of using the macro recorder to set object properties. Further explanation of comments and an introduction to the WITH...END WITH construct.
Objects and Properties
15:10
An example of using the macro recorder to execute object methods, both with and without parameters, and an explanation of how named parameters work.
Objects and Methods
13:50
An explanation of the Application object and its properties, and how properties can contain other objects.
More about Objects and Properties
10:16
A look at the many different ways of referencing Range objects.
Referencing a Range
19:10
Manipulating ranges using some important Range properties that allow you to derive new Range objects based on existing Range objects.
Important Range Properties
17:35
A look at the different ways of referencing worksheet objects, most importantly, the Worksheets collection.
The Worksheets Collection
12:59
A look at the Workbooks collection, and other ways of referencing workbook objects.
The Workbooks Collection
18:05
Using Intellisense lists, the Object Browser, and VBA help to discover more about objects, and their associated properties and methods.
Exploring the Object Library
14:13

This quiz covers the topics presented in Section 2.

The Excel Object Library
5 questions
+ – The VBA Language
13 lectures 03:13:30
Using the MsgBox() and InputBox() VBA functions for simple macro input and output.
Simple Input & Output
11:39
Declaring and using simple variables and enforcing variable declaration with Option Explicit. Also a short survey of the most common data types.
Variables & Data Types
21:06
Using object type variables to reference and manipulate Excel objects.
Object Type Variables
16:00
How to use VBA and Excel worksheet functions in your macros.
VBA & Worksheet Functions
11:37
How to create custom functions in VBA that can be called from your macros or from the worksheet.
User-Defined Functions
15:22
Introduction to variations on IF...THEN, IF...THEN...ELSE and ELSEIF to perform conditional branching in your code. Also, combining logical conditions with AND.
Branching: IF...THEN
18:09
Introduction to the SELECT...CASE statement as an alternative to ELSEIF. Also, using the Typename() VBA function to determine the data type of a property or variable, and combining logical conditions with OR.
Branching: SELECT...CASE
13:02
Controlling program flow through repetition using 4 different variations of the Do...Loop. 
Looping: DO...LOOP
16:01

Controlling program flow through repetition using variations of the For...Next loop.

Looping: FOR...NEXT
11:29

Controlling program flow through repetition using variations of the For...Each loop.

Looping: FOR...EACH
17:51

Understanding variable scope: local, module and global, and when each should be used. How to declare variables of different scope.

Variable Scope
11:44

Trapping runtime errors in your code, and providing simple error handlers to report the error and gracefully exit.

Error Handling
15:05

This short project combines and reinforces some of the skills and techniques introduced so far in the course: looping, simple and object type variables, defining and passing parameters, and using important Range properties such as CurrentRegion and Resize.

Project 1 - The VBA Language
14:25

    • This quiz covers the topics presented in Section 3.

The VBA Language
5 questions
+ – Launching Macros: Events & Shortcuts
5 lectures 01:01:26

A look at additional techniques you can use to launch macros, by attaching them to objects on the worksheet or modifying the Quick Access Toolbar.

Macro Launching Shortcuts
13:01

Modifying the Ribbon by adding tabs and groups; attaching macros to the Ribbon.

Modifying the Ribbon
05:58

Hooking into Workbook and Worksheet events to run code automatically in response to user actions.

Workbook & Worksheet Events
19:17

Hooking into Application events to run code automatically in response to keyboard actions and system time.

Application Events
13:39

      • This short project combines and reinforces some of the skills and techniques introduced in this section, specifically using a Workbook event handler to modify Excel's save/close behavior.

Project 2 - Event Handling
09:31

        • This quiz covers the topics presented in Section 4.
    •

Launching Macros: Events & Shortcuts
5 questions
+ – Modifying the User Interface
5 lectures 01:15:12

Exploring additional features of MsgBox, including modifying visual characteristics such as title bar, icon and buttons. How to test for user choices, OK/Cancel, Yes/No, etc...

Enhanced Message Box
07:58

Enhancing the Excel user interface by adding ActiveX controls to the worksheet, without using any code.

ActiveX & The Worksheet (No Code)
17:19

      • Enhancing the Excel user interface by adding ActiveX controls to the worksheet, with code.

ActiveX & The Worksheet (With Code)
13:17

Presenting and gathering information through custom user forms: initializing controls, displaying and disposing of the form.

Basic User Forms
17:24
            • This short project combines and reinforces some of the skills and techniques introduced in this section, specifically creating interaction between ActiveX controls on user forms.
          •

Project 3 - User Forms & ActiveX
19:14

This quiz covers the topics presented in section 5.

Modifying the User Interface
5 questions
+ – What's Next?
1 lecture 16:31

A simple example of cross-application programming: reaching into Word from Excel to insert a chart and create a series of documents.

Reaching Beyond Excel
16:31