Mark Moore - Mastering Excel Macros: Object Variables (Book 6)

Mastering Excel Macros

Book 6 Object Variables

Mark Moore

Copyright 2015 by Mark Moore. All rights reserved worldwide. No part of this publication may be replicated, redistributed, or given away in any form without the prior written consent of the author/publisher or the terms relayed to you herein.

Object variables are tricky to learn. In all the previous lessons, you have learned programming topics using the activecell. This is a great way to learn because when you step through code you can see exactly what is going on. If there is anything wrong, you can fix it and then step through again to ensure everything is working as expected.

However, using the activecell can cause many headaches (which I will explain in a few paragraphs) and in many cases object variables will eliminate these issues.

Keep in mind, like I have said before, programming is like writing a paragraph. There are millions of ways to say the same thing. If you have a macro and you got it working by only using the activecell method, you dont have to change it. You can use object variables, you just dont have to.

As with all lessons, this one also has several follow along workbooks you can download to work through the exercises, you can sign up and get the workbook on my website at: http://markmoorebooks.com/macros-object-variables/

Declaring Variables

In all the lessons so far, I have mentioned but not enforced the programming concept of declaring variables. In case you forgot, declaring variables means that you are explicitly telling Excel what to expect when using a variable. For example, the code shown below tells Excel exactly what Variable1 is.

The Dim statement stands for Dimension. It is how you tell Excel that the next word is going to be a variable. Then you use the As keyword and finally the type of variable (an integer in this case). Most programming languages insist on variable declaration because it makes the language much more efficient. The language knows exactly what actions are, and are not possible with a variable. The language knows exactly how much memory space to allocate to a variable; its super efficient.

I know what you are thinking, If it is so great, why did we skip over it? Because it is not practical for non-programmers, and it opens a whole complicated can of worms. For example, an integer data type can only hold numbers between -32,768 to 32,767. Any number outside that range must use another data type. You can use Long Integer or Single or Double. The issue is that each data type has its ranges. Any numbers falling outside the data type range will cause an error. That means that now on top of learning how to program, you also have to keep in mind the possible ranges of the data the macro will use.

Im not short changing you by skipping topics; its just that learning all of this is not time well spent for learning basic macro programming. Instead, we skip the variable declaration part and let Excel assign the Variant data type to all our variables.

However, when working with object variables, you must declare them. Excel needs to know that it is an object so that you can use all their features.

What is an Object Variable Anyway?

Variables, as we have used them so far in the lessons, have consisted of numbers or text. An object variable, on the other hand, is an entire object that we can use as needed. Remember that everything in Excel is an object. Objects have properties (height, width, etc.) and methods (delete, insert, etc.). Similar objects are grouped into collections (for example, the sheets collection is the grouping of all the worksheets in a workbook). You assign a variable to an object, thus creating an object variable. Once the object variable has been set, you can use all of the objects methods and properties in the macro.

Code Without Object Variables

Consider the situation shown below. You receive data from distributors, but the data is not clean. You dont know what system the distributor uses or how they get their data. All you know is you get it in the format shown in column A:D, and you need to make it look like columns F:K so you can build reports off the cleaned up data.

The major issue you have is that the item#, salesperson, and region are all together in column A. Because you will receive this data regularly, you are going to write a macro to automate this as much as possible. You also need to skip any sales made by Tom.

How would you do this? Lets start by writing some pseudo code. Pseudo code is just an outline of what we want the code to do. This is a good practice to get into for complex macros. It helps you think through the steps needed without having to worry about getting everything perfect. It is an exercise to order the steps and get the logic right.

1 - Select cell A1

2 - Loop until the cell in column A is blank

3 - In the activecell, extract the Item #, Region, and Salesperson

4 - If salesperson is Tom, skip the row

5 - Get Qty, Price, and Extended Price from the cells next to the activecell

6 - Store the address of the activecell in column A

7 - Go to the first blank line in column F

8 - Input the Item # in column F

9 - Input the Region in column G

10 - Input the Qty in column H

11 - Input the Price in column I

12 - Input the Extended Price in column J

13 - Input the Salesperson in column K

14 - Go back to the last cell processed in column A (this was the cell address from step 6)

15 - Move the activecell one row down

16 - Repeat the loop that started in step 2

Thats the big picture. The code gets data from columns A:D, then processes it and puts it in in columns F:K. The problem is that this involves a lot of bouncing back and forth. The code needs to remember where the last processed row in column A was, then find the first blank row in column F, put in the processed data, then go back to the last row in column A and repeat the process.

Instead of bouncing back and forth between columns and finding the last cell the code left off at, you are going to set up a few object variables to keep track of this for you.

Code With Object Variables

Lets start working with object variables.

The object you will use in this exercise is the range object. The range object can represent either a single cell or multiple cells. You will use two object variables to keep track of two cells: the cell where the data is coming from (i.e., the source in columns A:D) and the cell where the data is going to (i.e., the destination in columns F:K). By doing this, you will not have to worry about remembering where the program left off in both columns; the object variables will store this for you.

1 - Open the follow along workbook named Range Object Variable.xlsm

(Remember, you can sign up to get the workbook at my website: http://markmoorebooks.com/macros-object-variables/)

You will see this spreadsheet:

This is the same spreadsheet you saw before. Your goal remains the same: take the records from columns A:D, process them, and then put them in columns F:K. You need to skip records that have Tom as a salesperson.

2 - Open the VBA editor and add a module

3 - Create a new ProcessRows macro

Your macro should look like this now: