The Basics of Excel Automation: Interacting With Excel

Welcome back! In this section of the tutorial, I will show you how to manipulate Excel from VBA. If you missed the first part of the series click here.

Let’s get back to the fun stuff.

Naming Our Project:

First, we should name our project. If you look to the left there should be two vertical menus.

The top menu displays objects in the project; the bottom displays the properties of the highlighted object.

If you do not see these two menus, click on the “View” tab of the top navigation menu and select “Project Explorer” and the “Properties Window”.

We should rename the project so that we know which file we’re working on. Click on “VBAProject (Book1)” on the Project menu and in the “Properties” menu we’re going to edit the “(Name)” property. No spaces are allowed in the name so I use underscores.

Now, let’s open the code editor. Code organization in VBA is a bit of a pain compared to other languages. Because it’s a scripting language, little bits and pieces can be found in weird areas. I like to keep all the code for each individual sheet in that sheet. So I’m going to open “Sheet1” and put my code in there.

I’m going to rename it to something more descriptive.

CODE!

Now, this is where programming and math intertwine. Program instructions are written in blocks referred to as function or subroutines. A function is a code-block that returns a value; where as a subroutine is a code-block that just performs the instructions.

In algebraic math functions look like f(x) = ect. They look almost the same with programming. We write “Sub” (short for subroutine) + name of the sub (can be anything you want but the best practices are to use short but descriptive names) + “() End Sub”.

As you can see, some of the words are highlighted in blue. These highlighted words are called key or reserved words that the language has dedicated to mean something and cannot be used for anything else.

Verbose:

Let’s do something interesting. There are things in VBA called objects. The are the code representation of actual objects, like Excel. So, what we’re going to do is use the Excel objects to interact with Excel.

So, in code, we’re calling the Excel object like we would call a dog — by using its name. Something I didn’t mention before is that objects can have properties — functions, variables, and even other objects — associated with them.¹ Here we’re calling the cell “A1” of “Sheet1” in Excel and assigning it the value of “Hello, World!”. The reason “Hello, World!” is in quotes is that alphabetical characters are represented weirdly in computers. So anytime you need to have actual words, they need to be enclosed with quotation marks.

Now, let’s run the code. Press the “F5” key to run the code.²

Wait… did anything happen? Yes! Open up the other Excel program and look at cell A1.

K.I.S.S. — Keep It Simple, Stupid!

But we did all that writing just for “Hello, World!” to appear in cell “A1”? Seems like too much work for me. Well, since we put this code block under “Sheet1” VBA can take that context and give you the same result with “Cells(1,1) = ‘Hello, World!'”.³

And that is the basics of interacting with Excel from VBA.

1. When Code is organized such that objects have these types of properties, it’s called object-oriented design (OOD). Most modern languages are object-oriented programming (OOP) languages. VBA, however, is not a full-fledged OO language because not all aspects of OOD are present in VBA. I will cover this later on. This way of programming can be a very powerful skill to build and will increase productivity near-infinitely once mastered.
2. Be careful when you press this. When the F5 key is pressed, it uses the context of the cursor to decide which section of code to run. It will run whatever block the cursor is inside of.
3. Cells are called by a 2-dimensional array, like in mathematics. If you want to visualize it, think of a grid with the coordinates of 1, 1 being in the upper-left of Excel. Or just replace the alphabetical character of the column with the number corresponding to it’s place in the alphabet.