Become a Professional Excel VBA developer. Go beyond Macros

The Visual Basic Editor (VBE) is an application that launches in the background when Excel starts up. It is brought to the foreground when the Visual Basic button is clicked in the Developer tab's Code group of the Excel Ribbon. The VBE has several component windows: Properties, Immediate, Code, Toolbar, Project Explorer and Menu Bar. This lecture will examine in detail the functions of each window

We will manually create a VBA code in a module instead of using the macro recorder. We explore the two main ways of adding modules into the Project Explorer window

We learn to add toolbar buttons by using the Quick Access Toolbar (QAT) for demonstration. We add a button to the QAT and assign it to a  macro. The macro can then be executed by clicking on the button. We look at how to place a button on the AQT that is for the host workbook only or global (available to all workbooks, including future workbooks)

Excel provides a variety of graphical interface components known as Form Controls. We demonstrate how to place controls such as a CommandButton control on UserForms and set their properties

Everything in Excel is an object, within hierarchical relationship: Application object, Range Object etc. We look at how to traverse along hierarchies to reference particular objects. We learn about the concepts of Collections and Activities. We learn about how to retrieve object properties and assign values to changeable properties

As a VBA programmer, you will be working a lot with data on a worksheet, whether this data is entered directly into the sheet or imported. We learn of the different ways to reference cells and ranges based on how the argument to the VBA Range() method of the Worksheet object or the Range object is passed to the object

VBA worksheet Cells object represents all cells on the worksheet. It has a Clear property and many other properties to help format cells on a sheet such as alignments. We learn how to address cells by only row number and both row number and column column letter. The following is an example of cell reference:

Range(Cells(2, "D"), Cells(2, "F")).Cells(1) = "A"

A variable is a container in a VBA code in which data is stored inside the computer's memory. We learn of the data types that can be assigned to variables. You learn about the naming rules for variables and the list of VBA keywords that cannot appear in variables. We explain the purpose of the Option Explicit statement. Under Managing strings, we see how to assign strings and dates to variables. We explore the Date and Time format specifiers. We learn about arrays and the array dimensions and how to populate array elements and assign/retrieve data. We look at object variables in addition to regular variables and how to Set values to object. variables. We learn how to declare constants and assign values to them

Variables can store different types of data such as integers, decimals, Boolean etc. You learn about the idiosyncrasies of the variant data type. You become familiar with the list and properties of VBA data types. We look at the significance of the Option Explicit statement

VBA has several built-in functions that can be used to manage strings. We look at the most common of these functions such as Trim(), LCase(), UCase(), inStr() and how to concatenate strings

We explain the fundamentals of arrays and VBA terms such as array elements, upper bounds and lower bounds of arrays. We use the Lbound() and Ubound() to create looping to access elements of arrays to make our code handle any size array. You learn to write code to populate spreadsheet with array data and vice versa

Side-by-side declaration of regular variables and arrays. We explore further dynamic and static arrays

Learn how to reference individual elements in arrays; Use the Split() function to automatically split a string based on a specified delimiter and populate an array with the components of the string

Populate array with values; perform arithmetic on arrays on the fly without modifying the original array values

Use the Erase() function on arrays and analyze the impact of the function on the different types of arrays: Static, Dynamic, and String

Understanding the Redim Preserve statement; Defining a two-dimensional array; Using nested loops to manipulate a two-dimensional array

Function versus Sub procedures explained; passing arguments to functions; debugging function procedures; using the Insert Function dialog box

After creating a custom function, we test it in a worksheet formula using the Insert Function dialog box

Detailed discussion of function syntax declaration, arguments and return values; function scope; setting reference to workbooks that contain functions we want to use in other workbooks

We discuss three ways we can execute/call custom functions: 1. calling from other procedures    2. using in worksheet formulas and 3. using in conditional formatting formulas

Explaining how to set reference to a workbook containing a function from another workbook to make a function available in the client workbook. Discuss other ways of making a function available to other workbooks without setting references

With demonstration, you learn how to create a custom function and use it in a conditional formatting rule to highlight cells that meet a  specific condition

We discuss Wrapper Functions and how to use them

A detailed example of a function that accepts a single argument

A detailed example of a function that accepts two arguments and returns a single value

With examples, we discuss the concept of optional parameters for functions and how to declare optional parameters

With examples, we learn how to create a function that returns an array (e.g. the names of the month)

We learn how to create functions that accept infinite number of arguments using the ParamArray keyword

We learn how to provide descriptions for a function and its argument using the MacroOptions Method of the Application object

This is an introduction to the section. You learn about VBA event types, figure out what you need to know to work with events. Work through examples of worksheet and workbook events. Learn about the Application Events to monitor open workbooks

Learn about where to put event-handler procedures; become familiar with different objects that have event handlers such as Sheet object events, Chart object events, ThisWorkbook object events and the UserForm events

You learn how to call procedures in a standard module from an event handler

We discuss event handlers that have arguments such as the one below

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

We look at how we can use these arguments to achieve a purpose

You learn about how to program some workbook-level event such as closing the workbook

We use the Workbook_Activate event handler to discuss some programming concepts

We use the Worksgeet_Activate event to discuss some programming concepts

We look at more examples of the Worksheet_Activate event handler

We learn about the NewSheet Event procedure which is fired when a new sheet is added to the workbook

We learn about the BeforeSave workbook event

The Deactivate event handler which is fired when a worksheet is deactivated (such as when a new sheet is selected)

The BeforePrint event which is fired before a worksheet data is printed

The WorksheetChange event which is fired when a cell/range in the worksheet is altered. We examine the difference between WorksheetChange (which is a workbook event) and the SelectionChange event (which is a worksheet event)

The BeforeRightClick event procedure with examples

Introduction to section. Explains what error handling means. Configure the interface to allow VBA to handle errors

Discuss the two main VBA error categories: Syntax errors and Runtime errors

Be able to use the ON ERROR statement to handle errors. A look at the ERR object and its methods such as error number, error description etc.

An example of the use of the Err object

Examples of VBA error handling techniques, detailed explanation of the ON ERROR GOTO 0 statement. The use of error labels to handle errors

A review of the previous lecture and an introduction to this one

Copying ranges within and between worksheets

Further discussions on copying ranges

If you need to periodically import data from a constantly-varying range size, you need extra coding to import such data. We look at the CurrentRegion method of the Range object to help handle such situation

We describe some tips for working with ranges using VBA. For example, we explain why it is not necessary to select a cell before you can work with it, as well as talk about named ranges and why named ranges will make you VBA codes flexible

A look at the CurrentRegion method of the Range object, the xlDown, and xlUp ect. to identify ranges

Code samples of some common range selection processes, such as selection a whole column of current cell

Use of the Resize property of the Range object to handle data ranges that constantly change

The use of the InputBox function to pause for user to enter a value for the VBA code to process

A mode advanced use of the InputBox function, providing branching depending on user input

Populating the last empty cells with values of the InputBox function

Pausing a macro for user to select target range

Counting cell properties such as number of columns or rows across a selected range

Your VBA code can tell you the type of cell selection so you can take appropriate action: A single cell; a contiguous range of cells (using the Selection.Area method); one or more entire rows/colums; entire worksheet

A VBA code to check if a sheet exists in a workbook so that the appropriate action can be taken such as deleting the worksheet etc.

Learn to traverse the object hierarchy to retrieve parents/grandparents etc. of any object. This arrows code to be flexible and portable since object references (such as parent's name) are not hard coded

It is often necessary to use VBA to open a file to extract data such as CSV files into a spreadsheet

We look at how to use the Application.GetSaveAsFileFileName to save a file

Generate Data Forms from range data with column heading as textbox labels. This allows entering data in an input form into textboxes that are transferred automatically to cells in a worksheet

Introduction to the lessons in this section: Inserting UserForms in a VBA project, Inserting controls on UserForms, A look at ActiveX versus Form controls, Setting properties for controls, Programming Form Events, Managing Lists

We demonstrate how to insert UserForm in a project and place controls on the UserForm. We examine the different types of controls at our disposal. We display how to show/hide the Toolbox

We provide demonstration about some of the common controls. You learn how to place them on forms and set properties and see them in action: CommandButton, ListBox, Frame etc.

Every control on a UserForm has many properties that can be set at design time on the Properties Window. You learn how to set some of these properties

We look at the acceptable standards that many developers use to assign names to controls such as txtFirstName (for a text box that will contain a first name). We look at referencing controls by name in a VBA code

In this lesson we look at how to display UserForms using VBA. We look at the two modes in which a form can be displayed: Modal and Modeless

We provide a detailed discussion about the ListBox and the ComboBox controls. You lean how to control these two controls with VBA such as populating the controls, looping through each item in the controls and how to remove items from them

Introduction to the section. A description of the VBA application we are going to develop in this last section of the course

We develop a short application to demonstrate the features of the two modes in which UserForms can be displayed: Modal and Modeless

We develop an application to insert values into cells on a worksheet and provide a visual indication of the progress of the task using the ProgressBar control

We utilize the MultiPage control to develop an application that allows the user to select from two categories of books: Fiction and Computing then display the selection in a shopping basket (list box). We provide utilities to cancel and empty the basket and to display how many items are in the basket as the user selects/deselect items. We provide an assignment for you to extend the application by adding the book type of the selected book such as EBook or Paperback. Finally, we give you a Word document that describes a project you must perform