Hyper Disambiguated Excel Vba Programming

1. The Excel object model Preview 12:07

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

2. VBA classes and functions Preview 11:28

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.

3. Properties and methods Preview 12:54

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

4. Data variables Preview 13:27

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

5. Object variables Preview 19:25

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

6. Option explicit Preview 05:25

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

7. Declaring and using constants Preview 05:49

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

8. Using MsgBox for output Preview 10:11

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

9. Using MsgBox for input Preview 09:30

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

10. Using VBA InputBox Preview 06:25

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

11. Using Application.InputBox Preview 06:19

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

1. If statements Preview 11:36

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

2. If … ElseIf statements Preview 13:18

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.

3. Select Case Preview 09:19

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

4. For … Next loops Preview 09:32

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.

5. For Each … Next loops Preview 06:46

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.

6. Do … While loops Preview 13:51

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

7. Do … Until loops Preview 07:27

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

8. Creating arrays Preview 10:16

9. Dynamic arrays Preview 13:25

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

10. Utilising arrays Preview 11:05

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.

1. Sub Procedures and module level variables Preview 10:53

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.

2. Static variables Preview 05:29

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.

3. Passing parameters Preview 11:29

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

4. Creating and calling functions Preview 07:48

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

5. Creating user-defined functions Preview 17:04

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.

1. Changing and restoring user settings Preview 13:32

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.

2. Application.GetOpenFilename Part 1 Preview 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 Preview 13:19

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.

4. Application.GetSaveAsFilename Preview 10:29

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

5. Controlling screen updating Preview 09:28

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.

6. Suppressing warning messages Preview 05:51

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.

1. Example 1 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 Preview 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 Preview 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 Preview 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 Preview 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 Preview 09:01

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

07. Hyper-disambiguation practice Preview 14:18

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.

1. Targeting workbooks efficiently Preview 10:12

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.

2. Checking whether a workbook is open Preview 08:03

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.

3. Checking whether several workbooks are open Preview 08:33

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.

4. Checking whether a file exists Preview 05:40

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.

5. Checking whether a folder exists Preview 02:50

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.

6. Deleting files and folders Preview 05:43

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

7. Processing all files in folder Preview 14:33

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.

1. Targeting worksheets efficiently Preview 12:55

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.

2. Copying worksheets Preview 14:09

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.

3. Moving worksheets between workbooks Preview 15:44

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.

4. Saving worksheets as separate workbooks Preview 12:10

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.

5. Working with the sheets collection Preview 08:07

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

7. Creating an embedded chart Preview 07:28

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

6. Creating a standalone chart Preview 11:49

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

1. Copying a range of cells Preview 12:35

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

2. Using the Offset property Preview 10:59

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

3. The End property Preview 10:30

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

4. The Resize property Preview 14:21

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.

5. Entering formulas with VBA Preview 10:34

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.

6. Deleting ranges Preview 06:05

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.

7. Inserting ranges Preview 13:55

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

8. Outlining ranges Preview 09:16

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