Excel VBA Macros: Hyper-disambiguated Excel VBA Programming
4.5 (121 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,564 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA Macros: Hyper-disambiguated Excel VBA Programming to your Wishlist.

Add to Wishlist

Excel VBA Macros: Hyper-disambiguated Excel VBA Programming

Learn Excel VBA macros, using clean, clear VBA programming techniques and code that you fully understand
Best Seller
4.5 (121 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,564 students enrolled
Created by Grant Gamble
Last updated 8/2017
English
Current price: $10 Original price: $125 Discount: 92% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 11.5 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • By the end of this course, you will be confidently writing VBA macros which automate procedures in Microsoft Excel.
  • You will fully understand the purpose and syntax of each line of code that you write.
  • You will not simply be entering lines of code because you have been told they will produce a certain result.
  • You will gain a solid understanding of both the VBA and Excel object models which underly VBA programming.
View Curriculum
Requirements
  • Experienced Excel users who are new to Excel VBA programming.
Description

Excel VBA programming can seem baffling, especially when you don't fully understand the code you are asked to write. Learn to write Excel VBA macros, from scratch, to automate Microsoft Excel tasks and operations.

This course is aimed particularly at Excel users without much programming experience, who struggle to remember the syntax and structure of the VBA (Visual Basic for Applications) programming language.

During the course, we will use a style of programming which emphasizes the significance of each line of code that you write. This means that when you revisit the code, the meaning of each line is apparent from the syntax, rather than becoming more and more unfamiliar. The course also demonstrates how to get the maximum benefit from IntelliSense, Microsoft's code completion feature.

Whether you are looking to enhance your career prospects by adding Excel VBA to your C.V., save your company money or increase your productivity, this course will provide your with all the knowledge you need to get started.

You can download all of the materials used in the lectures, so that you can follow along. (Please, remember to unZIP the downloaded files.)

Who is the target audience?
  • Anyone wishing to learn professional Excel VBA programming techniques.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
75 Lectures
11:42:38
+
1. Getting Started
14 Lectures 01:24:19

Welcome to the course Hyper-disambiguated Excel VBA.

Preview 04:47

This video provides an overview of Excel VBA and what you will need to learn in order to start writing your own macro.

Preview 01:40

The Developer Tab is not visible on the Excel Ribbon by default. This video shows you how to make the Developer Tab visible in Excel.

Preview 02:52

For your protection, Excel displays a warning message each time you open a workbook containing macro code. This tutorial shows you how to prevent these messages being displayed when working with your own macros.

Preview 05:48

In this video, we begin our look at the Visual Basic Editor, the environment in which VBA code is edited, by examining the role of the Project Explorer window.

Preview 02:02

In this lesson, we continue our look at the Visual Basic Editor by discussing the function of the Excel Objects folder found in the Project Explorer.

Preview 07:59

This video demonstrates how you write a macro from scratch in the Visual Basic Editor.

7. Writing a macro in the VBE
03:57

In this lesson, we continue our look at the Visual Basic Editor by discussing the function of the Immediate window for testing lines of code.

8. Using the Immediate window
03:11

This video discusses how to correctly save a workbook containing VBA code.

9. Saving a macro-enabled workbook
01:30

In this lesson, we discuss the technique which you should use if you want to get the most value out of Excel's Macro Recorder utility.

10. The correct way to use the macro recorder
12:50

In this lesson, we examine code produced by the Macro Recorder and match each statement to the equivalent actions performed in the Excel user interface.

11. Analysing a recorded macro
06:58

In this video, we discuss ways of improving the code generated by the Macro Recorder. Making the code less specific to a single worksheet and cell ranges makes it more flexible and useful.

12. Improving a recorded macro
11:59

In this lesson, we look at attaching a macro to a button. We also discuss a technique for attaching the same macro to several buttons and having the macro produce different results based on the name of the button.

13. Assigning a macro to a button
12:52

To test our improvements to the code originally generated by the Macro Recorder, in this tutorial, we transfer our code to a different workbook and check that it still works fine.

14. Testing a macro on another worksheet
05:54
+
2. Key components of VBA code
11 Lectures 01:53:00

In this lesson, we examine the Excel object model; the programmatic representation of the elements within the Excel user interface.

Preview 12:07

In this lesson, we move from looking at the Excel Object Model to our first look at the VBA object model. We will be focusing on the VBA InputBox function which enables basic user input.

2. VBA classes and functions
11:28

This lesson discusses the two key types of syntax structures used with both Excel and VBA objects: properties and methods.

3. Properties and methods
12:54

This lesson introduces the use of variables in Excel and focuses on defining variables which hold data values.

4. Data variables
13:27

In this lesson, we move on to look at storing references to Excel objects in our variables.

5. Object variables
19:25

In this lesson, you will discover why the Option Explicit statement is so essential in VBA.

6. Option explicit
05:25

In this lesson, we contrast the use of constants in VBA code with the use of variables.

7. Declaring and using constants
05:49

In this lesson, we look at the use of the VBA MsgBox function for outputting messages to the user.

8. Using MsgBox for output
10:11

In this lesson, we look at the use of the VBA MsgBox function for capturing information from the user.

9. Using MsgBox for input
09:30

In this lesson, we discuss the benefits and limitations of using the VBA InputBox function to capture information from the user.

10. Using VBA InputBox
06:25

This lesson highlights the benefits of using Application.InputBox, in preference to the VBA InputBox function, when capturing information from the user.

11. Using Application.InputBox
06:19
+
3. VBA Control statements
10 Lectures 01:46:35

This lesson demonstrates the use of If statements, the key conditional structure in VBA programming.

Preview 11:36

This lesson builds on the topics covered in the previous video and demonstrates how to create more complex If statements, using the If ... ElseIf pattern.

2. If … ElseIf statements
13:18

This lesson highlights the occasions when it is preferable to use the Select Case statement, rather than If ... ElseIf.

3. Select Case
09:19

In this lesson, we begin our look at looping structures (an essential programming construct) with VBA's most common type of loop; the For ... Next loop.

4. For … Next loops
09:32

In this lesson, we examine the VBA For Each ... Next loop, a variant of the For ... Next loop which is specially designed for looping through object collections.

5. For Each … Next loops
06:46

In this tutorial, we begin our look at how loops can be controlled by logical tests by examining Do ... While loops.

6. Do … While loops
13:51

In this tutorial, we continue our look at conditional loops by examining the  Do ... Until loop (a variant of the Do ... While loop).

7. Do … Until loops
07:27

8. Creating arrays
10:16

In this lesson, we continue our look at VBA arrays by discussing dynamic arrays, whose dimensions can be redefined as your program runs.

9. Dynamic arrays
13:25

We end this section with some further practice on using array variables. We also discuss the use of Excel formulas to construct repetitive VBA code.

10. Utilising arrays
11:05
+
4. Procedures and Functions
5 Lectures 52:43

This video shows you how to modularize your code by splitting it into separate sub-procedures and how to define module-level variables whose contents can be accessed from any sub-procedure.

Preview 10:53

Normally, after a macro has been run, any values held in variables disappear. This lesson shows how the use of static variables can preserve values held in variables after a macro has stopped running.

2. Static variables
05:29

The use of parameters can make more flexible; since the values passed to parameters can change the behaviour of a sub-procedure.

3. Passing parameters
11:29

Functions are a useful subset of VBA procedures which can return a value. This video demonstrates their basic use.

4. Creating and calling functions
07:48

As well as being called from macros, VBA functions can be used to encapsulate logic which might be difficult to define using Excel's built-in functions. This video demonstrates how to create these user-defined functions.

5. Creating user-defined functions
17:04
+
5. Application Object
6 Lectures 01:02:46

If your macros need to change a user's Excel settings, it is good etiquette to restore them to their original values. This tutorial gives an example of this best practice technique.

Preview 13:32

Rather than specifying a file path in your code, it is often better to ask the user to choose a file. This two-part video shows how to use the Appliction.GetOpenFileName method to achieve this.

2. Application.GetOpenFilename Part 1
10:07

Rather than specifying a file path in your code, it is often better to ask the user to choose a file. This two-part video shows how to use the Appliction.GetOpenFileName method to achieve this.

3. Application.GetOpenFilename Part 2
13:19

This tutorial demonstrates show how to use Application.GetSaveAsFileName to allow the user to choose a location and file name when saving a file.

4. Application.GetSaveAsFilename
10:29

De-activating screen updating is a useful way of improving the user experience when your macros are running. This video demonstrates how to use this technique.

5. Controlling screen updating
09:28

Another way of improving the user experience while your macro is running is to de-activate Excel's user-friendly warning messages. This video shows how to implement this technique.

6. Suppressing warning messages
05:51
+
6. Code recognition clinic
7 Lectures 56:53

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

Preview 12:42

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

2. Example 2
05:28

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

3. Example 3
05:59

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

4. Example 4
04:20

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

5. Example 5
05:05

In this chapter, we practice our code recognition skills, categorizing each line of code according to the function it performs within the macro.

6. Example 6
09:01

We end this section by taking some code generated by the Macro Recorder and re-writing it in a hyper-disambiguated style to make the purpose of each line of code super clear.

07. Hyper-disambiguation practice
14:18
+
7. Files and Workbooks
7 Lectures 55:34

In this video, we will review the six different methods of referencing a workbook: by name, by position, the active workbook, the workbook which contains the macro, by creating a new workbook, and by opening an existing one.

1. Targeting workbooks efficiently
10:12

In this lesson, we will learn how to use a loop to verify if a workbook is currently open before we attempt to reference it.

2. Checking whether a workbook is open
08:03

In this lesson, we will learn how to use loops and arrays to verify whether a series of required workbooks are all currently open before we attempt to reference them.

3. Checking whether several workbooks are open
08:33

In this lesson, we will learn how to use the VBA Dir function to verify if an Excel file exists on disk before we attempt to open it.

4. Checking whether a file exists
05:40

In this lesson, we will learn how to use the VBA Dir function to verify if a folder exists on disk before we attempt to process the Excel files it contains.

5. Checking whether a folder exists
02:50

In this video, we will learn how use the VBA Dir, Kill and RmDir functions to target and delete files and folders.

6. Deleting files and folders
05:43

In this lesson, we will create a macro which allows the user to review the contents of all the Excel files within a specified folder. Our macro will use the VBA Dir function to loop through the files.

7. Processing all files in folder
14:33
+
8. Worksheets and charts
7 Lectures 01:22:22

In this video, we will look at the most efficient way of targetting worksheets in your VBA code (by referencing the hidden codename of the sheet) and verifying whether a worksheet exists before attempting to manipulate it.

1. Targeting worksheets efficiently
12:55

In this tutorial, we will create a macro which allows the user to create a new workbook and copy into it any worksheet from any of the workbooks within their chosen folder.

2. Copying worksheets
14:09

In this tutorial, we will create a macro which allows the user to move any worksheet from any of the workbooks within their chosen folder to an archive workbook.

3. Moving worksheets between workbooks
15:44

In this tutorial, we will write a macro which splits a workbook specified by the user into a series of separate new workbooks, each containing one worksheet from the original workbook.

4. Saving worksheets as separate workbooks
12:10

In this video, we will focus on the Sheets conllection, which allows you to target both Worksheet and Chart objects.

5. Working with the sheets collection
08:07

In this tutorial, we will examine the VBA code used to create a standalone chart, one which resides on a separate chart sheet.

6. Creating a standalone chart
11:49

In this tutorial, we will examine the VBA code used to create an embedded chart, one which resides on worksheet inside a ChartObject object.

7. Creating an embedded chart
07:28
+
9. The Range object
8 Lectures 01:28:26

In this video, we review the two key VBA techniques for copying both Excel data and cell attributes from one location to another.

1. Copying a range of cells
12:35

This video examines the use of the Range.Offset property to efficiently target cells adjacent to a given range.

2. Using the Offset property
10:59

This tutorial examines the Range.End property, the VBA equivalent of the Excel shortcut Control + Arrow key (up, down, left or right).

3. The End property
10:30

In this lesson, we practice the use of the Range.Resize property which allows us to expand or contract the number of rows and columns referenced by an already specified range.

4. The Resize property
14:32

Sooner or later, you will need to write code which enters formulas in an Excel worksheet. This tutorial shows you how to use the Range.FormulaR1C1 property to do this in the most efficient manner.

5. Entering formulas with VBA
10:34

If you reference a range of cells with a variable and then delete that range, your variable is "broken" and generates an error. This tutorial shows you how to efficiently delete ranges without destroying your range variables in the process.

6. Deleting ranges
06:05

This lesson demonstrates how to insert new ranges into a worksheet and then reference and populate the newly inserted cells.

7. Inserting ranges
13:55

This tutorial demonstrates the use of the Range.Group and Range.UnGroup commands to outline data.

8. Outlining ranges
09:16
About the Instructor
Grant Gamble
4.5 Average rating
494 Reviews
13,434 Students
8 Courses
Developer, trainer and author

Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes.

His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft Power BI, VBA, web development and Adobe Creative Suite automation.