The Range Object – Let VBA Talk to the Spreadsheet

If you’re interested in learning VBA, that means you want to write scripts that interact with a spreadsheet. The foundation of this interaction is the Range Object. The Range Object contains all the necessary features and tools to make your VBA scripts speak to a spreadsheet.

Difficulty Beginner
Time Est. 20-25 Minutes
VBA/Macros Yes
Template Files None
Solution Files 2016-11-10-the-range-object-solution

Getting Started with VBA Exercise 1

This exercise within the Getting Started with VBA series begins the focus on The Range Object.

One significant advantage that VBA has over traditional programming languages is the ease of which VBA can store data for long-term usage directly on a worksheet. Traditional programming languages require complex commands and queries to read, modify, and write data in external files or databases. With VBA directly overlaying Excel documents, the file that stores data is always easy to access. Additionally, with Excel’s basic functionality, it’s easy to interact with any VBA produced data through the Excel sheet.

While there are many ways for VBA to interact with spreadsheets, the primary method—and most powerful method—is through the range object. In programming terms, an object is an instance of a class which can be a combination of values (variables), methods (functions), or properties (data structures). In Excel, a range specifies a specific cell address, or—if provided—a name of a range within a worksheet. Given that, the “range object” within VBA permits VBA to interact with specific cells, or a group of cells within a worksheet.

One of the many great features in Excel is the ability to create VBA without even opening the VBA editor. This is done by recording a macro — a process in which Excel records specific mouse clicks and keyboard strokes while the macro recorder is active. This exercise will start with recording a macro. Once the macro is recorded and tested, you will go into the VBA editor and make modifications to the recorded macro. By modifying the code, you will learn how your specific code interacts with the spreadsheet.

Step 1: Record a macro.

Start by activating the macro recorder in the developer tab. If you do not see the developer tab, read this post on how to activate the developer tab. Once the developer tab is active, click on (1) Developer > (2) Record Macro. Note that there is a video recording at the end of this step that walks through the recording process. Keep in mind that macros do not care about the spreadsheet data while recording. In other words, we can record a macro which interacts with blank cells, add the some data after the recording, and the macro will perform the same actions regardless of the data in those cells when the macro is run.

Activate the macro recorder.
Activate the macro recorder.

This will open the Record Macro dialog box.

The "Record Macro" dialog box.

In the Macro name field, replace the word “Macro1” with “Copy_Paste”. The Shortcut key field will be blank — type the character “r” in the field. Then press OK. The macro will now record all mouse mouse/keyboard interactions with the worksheet.

While the macro recorder is on, follow these steps

  1. Click on cell A3
  2. Click on Home > Copy
  3. Select cell D3
  4. Click on Home > Paste
  5. Hit the Enter key to clear the clipboard
  6. Go back to the Developer tab and click Stop Recording, located exactly where the Record Macro button was previously located.

The following video walks through the 6 steps listed above.

As you may have guessed, this macro’s purpose is to copy whatever the contents of cell A3 are into cell D3. Each time you click on a cell, the range object is activated and the macro recorder inserts lines of code within the VBA editor to mimic your inputs.

Step 2: Test the recorded macro.

There are many ways to run a recorded macro or VBA script. We’ll test this recorded macro using two methods. Before testing, enter text into cell A3. In the example below, “Test Value” has been entered.

1. Macro Dialog Box

Navigate to the Macro dialog box by clicking on (1) Developer > (2) Macros. The Macro dialog box will appear. (3) Click on “Copy_Paste”, then Run.

Navigate to the "Run Macro" dialog box.
Navigate to the “Run Macro” dialog box.

A video of the macro in action.

2. Keyboard Shortcut

Another method of testing the macro is by using the shortcut key. If you recall in the Record Macro dialog box, you entered “r” in the Shortcut key field. By pressing keys “Ctrl. + r” simultaneously, the macro will run.

Step 3: Assign macro to a button.

Now that the macro is confirmed as working properly, the macro’s run action can be assigned to a button, providing clarity to users on how to activate the macro.

A button can be added through the Developer menu. Click on (1) Developer > (2) Insert, then click the rectangle icon on the upper right under Form Controls. This is the button form control.

Navigate to the Botton Form Control.
Navigate to the Button Form Control.

The cursor will turn into a cross-hair. Click on the worksheet where you would like the button to appear. In this example, the button is placed around cell G4. Upon clicking on the location, the Assign Macro dialog box will appear.

Assign the Copy_Paste macro to the button/shape.

Click on “Copy_Paste”, then press OK. The macro is now assigned to the button.

Do one more quick test to make sure that the button and macro are working properly.

If the value in cell A3 is copied to cell D3, the macro is properly interacting with the range object.
If the value in cell A3 is copied to cell D3, the macro is properly interacting with the range object.

Step 4: Review and clean up range object code in the VBA editor.

As previously mentioned, the macro recorder records all inputs from the keyboard or mouse that interact with the active excel sheet. These inputs are then translated into code within a VBA module. Open the VBA editor by selecting Developer > Visual Basic. Within the project pane of the editor, open the Modules file, then (2) click on Module1.

VBA code produced by the macro recorder.

The code in the coding pane was produced by the macro recorder. It’s also being include here for easy copying:

While this code is only 13 lines, there is still an opportunity for cleanup. Complicated VBA projects can take up thousands of lines of code, so it’s important to keep code clean no matter how simple the operation is.

What does it mean to have clean code? Clean code is that can be read very clearly to the extent that another knowledgeable developer will understand the intention of the code. Given that VBA code can contain a lot of noise and unnecessary steps, it’s important to write code that can be executed efficiently and optimally within its constraints. This is specifically relevant to the macro recorder, as spreadsheet interaction can lead to many unnecessary steps. In fact, the recorded macro made in step 1 could be coded in as few as 3 lines:

This shrinks the recorded 13 lines of code down to 3 lines. This post will not dwell on this specific script — this approach is discussed in detail in the next post on range methods in properties. However, there is still room for cleanup with regards to the copy/paste functionality. The following shrinks the recorded code down to 6 lines, while accomplishing the same task.

The cleaned-up code accomplishes a few things:

  • The copy and paste actions are “methods” that can be assigned to the range object. Methods will be discussed in detail in the next post, but in summary, they can be combined with the range line to make the code easier to understand. It’s easier to understand in this case, as it adds some obviousness to which range (cell) is being copied and pasted to.
  • The revised code eliminated the Select method (e.g. Range(“A3”).Select) within the range object, as it’s no longer necessary to define the scripts scope on individual lines.
  • There is a new line at the top, which selects the sheet (Sheet1.Select), which is the only line needed to assign the scope for the rest of the code.
  • Comments have been added to each line. While this example goes would be considered overkill (the operations are obvious), it’s a good practice to get into as you learn VBA. As you become more versed with VBA objects and methods, you can reserve comments for less obvious and complicated operations.

Step 5: Be creative with code.

By cleaning and reviewing the code, it should be clear how the code works. Think about how you can tweak the code to copy and paste in different cells. By changing cell address values within the range object (between the quote characters), you can make the script copy and paste anywhere in the spreadsheet. Go ahead, experiment!

Now let’s say you wanted to copy and paste values from two additional cells. This can easily be added by duplicating the two copy/paste range object methods, as shown below.

You should continue to tweak and test this code and experiment. By trying new approaches and encountering errors, you will begin to learn the possibilities (and limitations) of the range object. You will learn the most through trial and error.

While this specific code does not seem to accomplish much, it’s a basic starting point to automating processes and adding advanced functionality to spreadsheets. In fact, the approach presented in this post is the foundation to two advanced level projects in the Spreadsheet Design series: Adding New Data to Datasets in Excel, and Modifying Data in Excel with VBA.

Full VBA Code

Video/Screen Captures

Download Solution: 2016-11-10-the-range-object-solution

Other Points

  • There are no set-in-stone rules for clean code. Much of it depends on personal preferences, although there are some agreed upon standards. Even if you deviate from agreed upon standards, your code is likely to still work. Some of these standards will be discussed around the end of this series.
  • Commenting in code is even more important if you are working on a team project or anticipate passing your project off to others. Effective comments will give team members a starting point to build on your progress.

Drawbacks/Bugs

  • The specific methods used in this example (copy/paste) are not the most efficient for moving data around in an Excel sheet. More efficient and streamlined approaches will be discussed in the next post on range objects and properties.
  • Generally, it’s a good idea to avoid the “select” method as much as possible. This is considered inefficient, and complex code may require up to 10x processing time if the select method is used heavily.

Leave a Reply