Excel VBA Macros: Hyper-disambiguated Excel VBA Programming

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

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.

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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.

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

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

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

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.

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.

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.

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

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

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.

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.

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.

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.

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

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.

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.

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

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

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

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

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

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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

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

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

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

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

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.

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.

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.

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

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