Excel Vba How To Write Like A Professional

A Quick Environment Check Preview 01:55

A quick check to ensure you are set up correctly to write Macros! We check the following items

1. The Developer Tab is visible
2. Your Security settings are set correctly
3. You know how to save a file with Macros( as a Macro-Enabled Workbook(.xlsm))

Your First Macro Preview 04:47

Create a simple Macro using the Developer Ribbon.

Run the Macro from Excel and then Visual Basic

How to Create Modules Preview 04:36

This lesson shows you how to create Modules and Macros in Visual Basic.

1. Modules are used to hold Macros.
2. You can have as many Modules as you like in a workbook
3. Each module can have as many macros as you like
4. Place related Macros in the same Module

The Immediate Window - How to easily test your output Preview 02:40

The Immediate Window is an incredibly useful tool . You can test your code by writing values here. You can also use it when you want to try out code. It is also a great way of demonstrating how code works so I use it throughout this lesson.

It is simple to use. Write Debug.Print followed by the value/variables you wish to write.

Using Intellisense - Let VBA help you write code Preview 03:39

Intellisense is a predictive tool that helps you write your code.

By using "Complete Word" you can write your code much quicker and with less errors

Getting Started - Support Text Preview 11 pages

Course Notes for the Section: Getting Started

Using Range to manipulate cells Preview 01:46

Use the Range property to write a value to a cell.

If you use Range on it's own it automatically refers to the ActiveSheet - the sheet you last clicked on. As you want to avoid this you should always get the workbook and worksheet you are going to use. The rest of this section shows the techniques for doing this.

How to get any Workbook Preview 03:45

When to use each Workbook method Preview 01:00

In the previous lesson we looked at the different ways of accessing the workbook. In this lesson we look at when you should use them.

Making your code easy to read - Use a Workbook variable Preview 03:20

In this lesson we look at using a workbook variable.

It makes your code easier to read, easier to update and more efficient.

How to get any Worksheet Preview 03:32

In this lecture we look at the different ways of accessing the worksheet

Worksheets("Sheetname")

Worksheets(Index)

ActiveSheet

Putting it all together - A sample Macro Preview 06:16

In this lecture we create a simple Macro that uses what we have learned so far about the workbook and worksheet.

VBA's best kept secret - the worksheet codename Preview 04:03

The code name of the worksheet is the best way of getting a worksheet from the current workbook(the one containing the code).

It has two distinct advantages:

1. If the user changes a worksheet name the code will still work
2. You can use it directly to access a sheet - you don't need to mention the workbook

The Sample Macro - How the code name improves it Preview 03:08

We revisit the min project and update the code so it use the code name of the worksheet. By doing this we can see how the code name is so useful.

Summary of Worksheets and Workbooks Preview 04:09

A summary of what we have learned about Workbooks and Worksheets in this section. This lecture has a downloadable excel workbook with example macros the show how to use workbooks and worksheets.

Workbooks and Worksheets - Downloadable Guide Preview 10 pages

The supporting text for Workbooks and Worksheets. A pdf of what we have covered in this section.

Workbooks and Worksheets Quiz

An introduction to Variables Preview 04:37

An Introduction to variables and how to use them.

How to do almost anything with a string Preview 03:30

A guide to manipulating string variables(pieces of text).

Includes

• Left - gets the left most characters
• Right - gets the right most characters
• Len - gets the length of a string

Using variables to write and read from Cells Preview 03:08

This lecture shows how to write and read between variables and cells.

Using Range to manipulate cells Preview 03:14

This section is all about using cells. This first lecture looks at using the Range property to access cells.

Using Cells - How to choose cells at run time part 1 Preview 04:38

How to choose a cell a run time using the Cells property.

Cells takes row and column as arguments and so these can be easily set when the macro is running.

For Example

Cells(3,1) gives access to the cell A3

Using Offset - How to choose cells at run time part 2 Preview 02:38

Offset is used move a certain number of cells from a range. Like the Cells property it takes row and column as arguments and can be easily changed at runtime.

Sample Macro - Writing to months using Offset Preview 03:17

A sample Macro showing the benefit of using Offset.

This macro reads values from a month and places the result in the correct cell using Offset.

Sample Macro - Writing to months using Cells Preview 02:39

Updated the Macro from the previous lesson so that it uses Cells instead of Offset

Formatting Ranges - Making you work look good Preview 03:53

How to format cells. Included in this lesson:

• Formatting the font to bold
• Formatting the font color
• Formatting the Cell background color
• Formatting the borders around a group of cells

Mini Project Workbooks Preview 00:02

The two workbooks used in this sections videos.

Introduction to Mini Project Preview 00:54

In this section we create a mini project similar to a real world application. We use the techniques we have learned so far on this course.

Formatting the Cells Preview 03:42

We format the area of the report where we are going to write our values.

Write the Totals Preview 02:16

We write the values to our report.

Make it flexible with Offset Preview 04:17

Make the code more flexible using Offset

What is Debugging Preview 01:01

An introduction to this section on Debugging

Stepping through code Preview 02:17

How to step through the code

Using Breakpoints Preview 02:29

How to use breakpoints to stop your code on a certain line.

The Locals Window Preview 02:53

How to use the Locals window to view the values of variables in your current Macro.

The Watch Window Preview 03:30

Similar to the Locals Windows, the Watch Window allows you to create your own items to watch.

More on the Watch Window Preview 02:11

How to look at the properties of objects(e.g. Workbooks and Worksheets) using the Watch Window.

Using Conditions Preview 04:54

Conditions are used mostly in If Statements and Loops. The are statements that evaluate to through or false. Some examples are

• x > 1
• x<1
• x=5
• x<>5
• x>5 and x<=10

The If Statement Preview 02:41

The If statement allows you to make decisions in what your code does

If Statement with ElseIf Preview 03:07

Using ElseIf allows you to add more conditions to your If Statement.

an example

If x>5

Debug.Print "x is greater than 5")

ElseIf x=10

Debug.Print "x equals 10")

Endif

The Select Statement Preview 02:35

The Select Statement is a neater way of writing If and ElseIf.

More with the Select Statement Preview 03:49

Another example of using the Select Statement

Introduction to Loops Preview 02:06

An introduction to using Loops

The For Loop Preview 04:07

A guide to using the For Loop.

This loop uses the format:

For i = 1 To 10

Next i

More on the For Loop Preview 04:51

The For Each Loop Preview 03:42

The For Each Loop is used to read through collections

an example is

For Each wk in Workbooks

Next

The Do Loop Preview 04:21

A guide to the Do Loop which is normally used when you don't know in advance how often it will run.

Using Do Loop with Until Preview 03:12

Using the Do Loop with Until

Using Do Loop with While Preview 02:27

Using the Do Loop with While

While and Wend Preview 00:57

The While Wend loop is considered obsolete. You may come across it in older code so it is important to recognise it.

Summary of Loops Preview 01:55

A Summary of all the Loops we covered in this section

Syntax Errors Preview 04:28

What are syntax errors and how to deal with them.

Syntax errors occur when you enter a line.

Compiler Errors Preview 04:17

A guide to Compiler errors.

These are errors where the individuals lines may be okay but has a whole the code may have errors.

To check for compiler errors select Debug->Compile VBA Project from the menu.

Runtime Errors Preview 02:02

This is an introduction to Runtime errors. The occurs when your Macro is running.

A run time error can be caused by reading data(such as text instead of number) that you Macro cannot handle.

A run time error can be something simple like reading from the wrong cell.

Excel VBA Error Translator Preview 3 pages

This is a list of common VBA errors and how to resolve them. If you have an error you cannot resolve then email me at [email protected] and I will resolve it for you.

How to Open a Workbook Preview 04:18

A lot of the time in VBA you will deal with workbooks that are closed. The code to open workbooks can be tricky. The good news it that I have already written in and you can use it. This section of the course is a guide to using this code.

Opening Workbooks - User Dialog Preview 02:42

Sometimes you may want to allow the user to select the workbook when your Macro is running. I provide the code to do this. This lecture shows you how to easily use it.

Import Export Modules Preview 02:44

How to easily Import and Export modules using VBA.

Includes the downloadable code for Opening workbooks.

Calling Subs Preview 03:20

Arguments and Parameters Preview 04:38

Functions Preview 03:38

Subs v Functions Preview 02:51

Passing Argument Types Preview 01:35

Optional Arguments Preview 04:03

Running - Using a Shortcut Key Preview 01:53

Running - Quick Toolbar Preview 02:19

Running - Using Buttons Preview 02:53

Introduction to Arrays Preview 03:24

Declaring Arrays Preview 04:21

Assigninng Arrays Preview 02:46

Loop through arrays Preview 04:36

Pass Arguments Preview 04:07

Return from Function Preview 04:07

Copying Data Introduction Preview 04:56

Introduction to the copying and filtering data section of the course.

Copying Data using the For Loop Preview 12:12

In this lecture we cover using the For Loop for copying and filtering data.

Using sample data I will show you how to create a complete sub that copies and filters data using the For Loop. You will see how to format the cells correctly, the copy method to avoid and how to use the if statement and for loop to filter data.

Copying Data using the AutoFilter Preview 10:18

In the previous lesson we used the For Loop to copy and filter data. In this lecture we are going to perform the exact same task using the AutoFilter. This method is faster and requires less code than using a For Loop.

We are also going to look at many different examples using the AutoFilter.

Copying Data Using the Advanced Filter Preview 14:54

We are going to use the Advanced Filter to copy and filter data just like in the first 2 lectures of this however. We will first look at the advantages that the Advanced filter has over the other methods. We will then look at how to use the Advanced Filter with lots of examples.

Building a VBA Application Part 1 Preview 13:07

Building a VBA Application Part 2 Preview 14:06

Building a VBA Application Part 3 Preview 15:52