Excel VBA: How to Write Macros Like a Professional

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))

Create a simple Macro using the Developer Ribbon.

Run the Macro from Excel and then Visual Basic

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 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.

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

Course Notes for the Section: Getting Started

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.

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.

In this lesson we look at using a workbook variable.

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

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

Worksheets("Sheetname")

Worksheets(Index)

ActiveSheet

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

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

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.

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.

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

An Introduction to variables and how to use them.

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

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

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

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

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.

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.

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

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

The two workbooks used in this sections videos.

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.

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

We write the values to our report.

Make the code more flexible using Offset

An introduction to this section on Debugging

How to step through the code

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

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

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

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

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 allows you to make decisions in what your code does

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 is a neater way of writing If and ElseIf.

Another example of using the Select Statement

An introduction to using Loops

A guide to using the For Loop.

This loop uses the format:

For i = 1 To 10

Next i

The For Each Loop is used to read through collections

an example is

For Each wk in Workbooks

Next

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

Using the Do Loop with Until

Using the Do Loop with While

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

A Summary of all the Loops we covered in this section

What are syntax errors and how to deal with them.

Syntax errors occur when you enter a line.

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.

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.

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 paulkellykk@gmail.com and I will resolve it for you.