Excel VBA: The VBA Beginner's Blueprint to Programming Excel

Some people write VBA, some record macros, and some do both.
But why?

Learn about Object Oriented Programming, your key to VBA.

Learn about Container objects in VBA

Before you can write VBA, you need to know how VBA refers to objects.

Get familiar with the different security settings that that dictate when your code will run.

Key Points for Part One: Introduction to Visual Basic for Applications

Learn the steps to successfully record macros in Excel

Now that you have recorded a macro, let's make sure it works, and take a look at the VBA code behind it.

There are two different modes that you can record you macros in: Absolute and Relative.

Now that you know about Relative mode, let's see it in action.

What do you do if you need to make a change to you macro?

Learn how to create custom keyboard shortcuts for your Excel macros

Assign your macro to a button on the Quick Access Toolbar.

Assign your macro to a button on the Ribbon.

Assign your macro to a shape or to an image.

Assign your macro to a button in the Developer toolbox.

A macro project for quickly navigating through your Excel workbook

Key Points for Part Two: Understanding Macros

Time to write code. Time to get familiar with the Visual Basic Editor.

Learn about the Project Explorer in the VBE

Learn about the Properties Window in the VBE

Learn about the Code Window in the VBE

Learn about other important windows in the VBE

Windows in the VBE can float, or be docked.

Modules are where you will write your VBA. Learn how to add more modules, or remove the modules you know longer need.

Knowing how to Export and Import your modules is an important step to maintaining your VBA code.

Change the default module name into something more meaningful.

To be comfortable writing VBA, you need to be comfortable in the VBE.

Learn how to customize the VBE to meet your needs.

Browse through your project's available objects, and see their properties, methods, and events.

If you want to lock down your code, here is how you do it.

Key Points for Part Three: Getting to Know the Visual Basic Editor

An Introduction to Writing VBA

A couple of tips that will help with the speed and accuracy of writing your VBA.

Oh oh! Your code has an error in it, and a yellow arrow has appeared - what can you do to keep following along with the exercises?

Taking a look at the structure of SubProcedures

Using one SubProcedure inside of another.

Creating Functions

Using one Function inside of another.

How can you call one procedure with the same name as another?

Comments can do more than just add notes to your code. Learn another import feature of COmments.

Using Objects in Excel VBA

Understanding and using Methods in VBA.

Understanding and using Events in VBA

Key Points for Section: Getting Started with Programming VBA

Learn about Variables, and how to declare them.

More than likely you are going to be declaring multiple Variables in a procedure, learn how.

Learn about Object Variables in Excel VBA and how it can help you when writing your code.

What are data types, and how can they be used in VBA?

You can also declare variable's data types with Identifier Type Characters

Learn how to use Constants

A refresher on Arithmetic operators.

An exercise using Byte

A description and example of Strings

A description and example of Currency Variables

A description and example of Date variables

A refresher on Logical operators.

Learn how to use the Colon operator in VBA

Bring multiple strings together with String Contatenation

When you want to wrap your VBA code around to multiple lines, use the Line Continuation Operator

When you want to wrap the text output to multiple lines, use the CRLF.

Using the With statement is a real time saver, find out why.

Scope is an important concept to understand. Learn about the three different kinds of Scope.

Key Points for Section: Getting to Know Variables, Data Types, and Operators

Creating an Excel workbook using VBA is easy, but there are a few things you should know.

Learn the simple lines of VBA code that will open workbooks, close current workbooks, or other workbooks.

Find out the difference between Worksheets and Sheets.

Represents the active sheet in the active workbook, or specified workbook.

Learn to add worksheets to your workbooks.

Learn to copy your worksheets.

Learn to use the Move method to organize your Excel workbooks.

Learn to use the Delete method you remove worksheets from your Excel workbooks.

Learn how to refer to cells in your workbooks

Follow along with this exercise on using operator and the cells object.

ActiveCell is an important property to know. Learn how you can use it in Excel VBA.

Learn how to use the Rows property to select areas of your Excel workbook.

Learn how to insert columns and rows into workbooks

Automatically adjust the width of the columns, or the height of the rows for the best fit.

Range is one of the most important, and most commonly used Excel VBA objects.

If you have named ranges in your Excel workbook, you can use VBA to change their properties.

The Select method is used to select a part of a worksheet.

CurrentRegion is a property of the ActiveCell object.

Use the End Property to select the cell at the end of the region that contains data.

Learn how to copy information on your worksheet to the clipboard, or to a specified locaion

Learn the different ways to use the Pate method in VBA

Learn to Paste the data that you have copied.

Font Object Members and examples of their use.

Learn how to clear the contents of your cells or ranges.

Represents the name of the object using it.

Represents the data stored in the object, such as a worksheet cell.

In this exercise you will change the worksheet tab’s name to reflect the value in cell A1, when the value in the cell changes.

Learn how to Sort the records in your workbooks.

Learn how to Filter the records in your workbooks.

Learn the simple line of code that allows you to remove duplicate records.

Use the PrintPreview method in Excel VBA to display your worksheets in Preview mode.

Use the PrintOut method in VBA to send your worksheets to your printer.

Key Points for Part Four: Writing VBA Code

Test your skills with this PDF exercise and Excel workbook.

Learn how to use Named Format expression to format your values.

Learn to use the Format Function to create Custom Number Formats

Learn to use the Format Function to create Custom Date/Time Formats

Learn to use the Format Function to create Custom String Formats

Format Currency values with the FormatCurrency function.

The FormatNumber format works similarly to FormatCurrency, the difference being no currency symbol is returned.

Learn how to use the FormatDateTime function

The FormatPercent format works similarly to FormatCurrency, and FormatNumber.
The difference being that the number is formatted as a percentage with a trailing % character.

An Introduction to Control Structures

The If…Then statement is a simple control that tests whether a condition is true or false.

With an If…Then…Else statement, the control can take a secondary course of action. If the condition is true, then an action occurs. If the condition is false, take an alternate action.

When you have a large number of conditions to examine, the If…Then…Else statement will go through each one of them. Visual Basic offers the option of jumping to the statement that applies to the state of a condition.

Key Points for Part Five: Control Structures

What do you think of the course so far?
Is it helpful?
Please leave a Review, or a Star Rating, letting other students know about the course.

A loop is a sequence of instructions that is continually repeated until a certain condition is reached.

If you know with certainty how many times you want to execute a statement, you can use the For...Next expression.

You will create a simple procedure that executes a For…Next loop that populates data into the first six rows of a worksheet.

If you want to control how the incrementing processes, you can set your own, using the Step option.

1. How can you use a For...Next loop to insert 11 additional worksheets in your workbook?
2. How can you use a For...Next loop to print preview the first 3 worksheets in your workbook?

Answers to the questions in the previous lecture.

For Each loops are typically used with collections and arrays

In this example you want to remove the hyphens from each cell in the A column.

A continuation of the previous For Each lecture

You can also exit a For loop. To do this, in the section where you want to stop, type Exit For.

The Do Until loop repeatedly executes a section of code until a specified condition evaluates to True.

An example of using a Do Until Loop

The Do While loop repeatedly executes a section of code while a specified condition continues to evaluate to True.

An example of using a Do While Loop

You can also exit a Do loop. To do this, in the section where you want to stop, type Exit Do.

Key Points for Part Six: Loops

Test your Looping skills with this PDF exercise and Excel workbook.