Intro to VBA: Macros Explained in the Kitchen (Part 1 of 3)

What is VBA?

Visual Basic for Applications (VBA) is the programming language we use when writing macros in Excel (and other Office programs). It allows us to program Excel so we can save time with boring repetitive tasks, and focus on the fun stuff. We can use VBA for everything from automating a simple task to developing robust applications and add-ins.

Seeing VBA code for the first time might make your head spin. It's not too difficult to read, but understanding what the code means and how it works will make VBA a lot easier to use.

Programming Objects in Your Kitchen

VBA is all about programming objects. We basically write code that manipulates the objects in Excel. Objects are just about every thing you can think of in Excel like worksheets, ranges, charts, pivot tables, etc.

I was thinking about this while chopping potatoes for a soup my wife was making. How would I program all the steps I was taking to wash, peel, and cut the potatoes? I was working with quite a few different objects like the sink, cutting board, peeler, and knife.

To prepare the potatoes I had to perform actions with the objects like peeling with the peeler, chopping with the knife, etc. I also had to check the properties of the objects: the sharpness of the knife, firmness of the potatoes, and size of the cutting board.

All of this information could easily be translated into code to reference the properties and actions of the objects I was working with. And this is exactly what we do in VBA.

We program objects.

The VBA code to cut the potatoes might look like the following (note, this is NOT actual VBA code):

Food("Potatoes").Cut

Now, my wife needed them cut a certain way for the soup. She wanted 1/2 inch cubes. So we might need to specify parameters for the cut. Something like the following.

Food("Potatoes").Cut (Style:="Cube", Size:=0.5)

The “Cut” is an action that is performed on the potato, which is part of the Food collection. These actions are referred to as Methods in VBA.

What if I wanted to determine if the knife was sharp and clean. Those would be properties of the knife and might look something like the following.

Tools("Chef Knife").Sharp = False
Tools("Chef Knife").Clean = True

Looks like I have a clean knife, but I might need to sharpen it first. I should probably check how firm my potatoes are, which would also be a property of the potato…

Now think about the rest of the meal, and all the objects in the kitchen. There is an endless combination of tools, ingredients, and processes you could use to prepare a meal. That's part of what makes cooking fun and creative, for those that enjoy it. 🙂

Well the same is true with VBA and the Excel Object Model.

Programming Objects in VBA – Properties and Methods

Just like our cooking example, we can write VBA code to automate tasks in Excel. This code can read/write properties of the objects, or run actions (methods) on the objects.

The Excel Object Model is the library of all the objects in Excel. As you can imagine, it's a huge library!

Each object has its own properties and methods that we can use to make decisions and take actions with our code. There are three basic things we can do with properties and methods.

#1 – Read Properties

We can get information from Excel by reading the properties of the objects. This is similar to asking questions about the current workbook, worksheet, etc. Here are a few examples:

• Worksheets.Count – returns the number of worksheets in the active workbook.
• Range("A1").Value – returns the value in cell A1 of the active workbook and active sheet.

#2 – Write Properties

We can also set or change the properties of the objects in Excel. This is usually done by using the equals sign “=” in VBA. Here are a few examples:

• Worksheets(1).Name = "Summary" – changes the name of the first worksheet in the active workbook to Summary.
• Range("A1").Value = 100 – changes the value in cell A1 to 100.

#3 – Perform Actions with Methods

Methods are actions that can be performed with the object. These are usually actions you would take in Excel by pressing a menu button or keyboard shortcut. Here are a few examples.

• Range("A1").Copy Range("D1") – copies cell A1 and pastes it to cell D1
• ActiveWorkbook.Close – Closes the active workbook.
• Worksheets.Add – adds a worksheet before the active sheet (same as Shift+F11 keyboard shortcut)

Most properties and methods contain additional parameters that you can specify to customize your request. Looking back at the example of cutting the potato, my wife wanted 1/2 inch cubes so I specified that when using the Cut method.

When we use the Worksheets.Add method in Excel to add a worksheet, the Add method has optional arguments or parameters that can be specified to tell VBA: where to place the new sheet, how many sheets to insert, and what type of sheet.

You can also press Ctrl+I to bring up this info box with the parameters.

Learning Objects, Properties, & Methods

The Excel Object Model is a massive library.  I have been coding VBA for 10 years and still don't know everything.  I probably never will.  But it's easy to get help and learn about the objects you want to use.

Put the text cursor inside any property or method and press F1 on the keyboard to view the help page for that item. This is a great way to see what all the parameters are and learn more about VBA.

Here's a link to the help page for the Sheets.Add Method

By the way, the keyboard shortcut to open the VB Editor is Alt+F11.  Press Alt+F11 (Function+Option+F11 on Mac 2011) from anywhere in Excel to open the VB Editor and view the code modules.

What's Next?

In the next article we will look at the Excel Object Hierarchy and learn what the dots between the words mean.  We will also look at some best practices for referencing these objects so you don't get in trouble…

Click here to go to Part 2 – The Excel Object Hierarchy in VBA >