When coding in VBA, the range object offers a means to fully interact with cells on a spreadsheet. This is done through its object methods and properties — nearly every action undertaken in the Excel interface has a equivalent method or property in VBA.
If you have any prior experience using Excel’s spreadsheet capabilities, you know that there are actions that can be executed on individual cells. In addition to basic text input, borders, and colors, there are many formatting, insertion, and validation options. The list goes on and on, and for every action you can apply to a cell through Excel’s user interface, there is a method or property in VBA to mimic that action.
Object members are the methods and properties that exist within an object. In simple terms, a method forces an object to do something, while a property returns some detail about the object (or appends some detail to it). If you go back to the prior simple example in the range object overview, the Copy and PasteSpecial methods were used to force the range object to copy and paste.
How to Call a Member
Members are called directly after an instance of an object is called (used in code). A period will separate the object and the member. A member which is a method may or may not have additional function parameters. Method function parameters will be discussed later in this post.
Referring to the example in the previous post, the Range object was used to call the Copy member, which is method of the range object.
Range Object Syntax
Objects with Multiple Properties
Member calls can be slightly more elaborate than a simple [Object].[Member] Arrangement.
Examples toward the end of the previous post assigned Workbooks and Worksheets scope to the range object. Like range, Workbooks and Worksheets are both objects with several properties and methods. The interesting thing is in addition to being objects themselves, the Worksheets object is also a property of a Workbooks object, and a Range object is also a property of a Worksheets object. Confused yet?
In revisiting the example, we see the following object arrangement.
In this example, the object is Workbooks(“Book1”). Appended to the Workbooks object is the Worksheets property Worksheets(“Sheet1”), and the Range property Range(“A3”). This is then followed by Copy, which is a method of the Range object (property).
In the above example, the object structure is as follows:
[Object: Workbooks].[Property: Worksheets].[Property: Range].[Method: Copy]
Now if we were to assign the Copy method directly to the Workbook(“Book1”) object without specifying any properties, the operation would not work, as Copy is not a method of the Workbooks object.
However, this would work if only the Range property is removed, but that is coincidental as the Worksheets object also happens to have a Copy method, which copies the worksheet into a new workbook. That is not the intended result of this script (to copy a range), so this should be considered an error in this case.
If workbook scope was previously defined in the script through the active workbook, the following approach would also work:
In this example, Worksheets is the object, Range is the property under Worksheets, and Copy is the method under Range.
To summarize, a number of objects can also be used as properties within other “parent” objects.
Methods with Function Calls
Many methods are actually functions within the object. The method, PasteSpecial—used in a previous post–is an example of this. Within a member function, a parameter is an “envelope” for a certain value to be passed to the function. In many cases, all parameters are optional, which means that we do not need to define any parameters (also called, passing a variable to a function). However, there are some method functions which do require a variable to be passed. Additionally, we may want to utilize the features of a function, which will be shown here using an example of the PasteSpecial method. There are two different approaches:
Approach 1: Parameter Order
This approach is the easiest but may require some additional scripting. After entering the range object and method, press the spacebar. The following tooltip will appear:
The wide yellow box provides an overview of the method’s function declaration, outlining what parameters can be assigned a variable. For the PasteSpecial method, Paste, Operation, SkipBlanks, and Transpose are parameters. The tooltip also provides the enum name of the parameter (after “as”), and the default value (after the equal sign). The listbox below the tooltip provides a specific list of the variables that can be passed. By selecting a variable in the listbox, it will be added in the proper place within the VBA editor.
To move onto the next variable, insert a comma, then space. A similar listbox will appear with options for the Operation parameter.
There are a few syntax requirements here. If you simply want to override the default Operation value (and nothing else), the following code will not work:
This will not work because the Operation parameter is 2nd on the function definition list, behind the Paste parameter. For this to work properly, you will need to select the default emum value in the Paste parameter, this allows you to select a default override value in the Operation enum value, such as follows:
Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationAdd
As you can see, the order preceding the default override is important, but you do not need to pass any variables past the default override — in this case, SkipBlanks and Transpose.
Note on one slight annoyance: at times, the yellow tooltip box will cover up the first value in the variable listbox picker. Clicking on a value within the listbox should resolve this.
Approach 2: Parameter Call
The second, but less intuitive approach is a parameter call. This approach permits you to ignore parameter order by clearly specifying the parameter name after the method call.
Range("A1").PasteSpecial SkipBlanks:=True, Operation:=xlPasteSpecialOperationAdd
This example not only ignores the order of parameters, but it also places the SkipBlanks (3rd in order) parameter before the Operation (2nd in order) parameter. This approach is initiated by spelling out the informal name of the parameter, then following it with “:=” then including the value. For example: “Operation:=xlPasteSpecialOperationAdd”. Commas can separate individual calls. Unfortunately, this approach does not provide a listbox showing optional variables.
The VBA Editor has a comprehensive object browser built-in. The browser can be accessed by pressing the F2 key, or selecting View > Object Browser from the Windows toolbar. The object browser will overlay the coding window
The left column presents classes (objects), while the right column presents the methods that can be assigned to the selected object.
Important Members of the Range Object
The range object contains over 170 members. Any activity you can do to a cell (range) through Excel’s main interface can also be done through VBA. However, with VBA, there are a few members you will frequently encounter and use extensively.
The following list provides a summary overview for commonly used or understood members of the range object. In these examples, workbook and worksheet scope has been omitted for simplification. Read more about scope in the previous post.
The Activate method turns a single cell into the active object. Use of this method is discouraged, as it is commonly unnecessary and can slow down code processing.
The Clear method erases all formatting and values within a cell. The cell is restored to its default state.
The ClearContents method erases any formula or static value within a cell.
The Copy method copies all cell formatting and values to the Windows clipboard.
The Delete method deletes the entire cell. All cells below the deleted cells will shift up. Unlike the right-click > Delete operations done through the Excel interface, this action WILL NOT prompt a request to how to treat cells around it. Use this method with caution.
It is possible to override through a function parameter to force the method to shift left. This can be done with the following script:
The FillDown method will copy (fill) a vertical range with the value at the top of the range. In this example, if a text value exists in cell A1, and cells A2 through A10 are blank, this script will fill every cell within A1 through A10 with the text value in A1.
The Insert method will insert a cell at a specified range. All cells below will shift down.
The PasteSpecial method is a function within the Range object taking up to 4 optional parameters. The parameters are as follow:
- Paste (Optional) takes the XlPaste Type enumeration. Values passed include xlPasteAll (paste everything, the default value), xlPasteAllExceptBorders (paste everything except borders), or xlPasteValues (paste values without formatting). A full list of Paste data type values can be found here.
- Operation (Optional) can add mathematical operators to your pasted value. For example, a spreadsheet has a value of 2 in cell A1, and a value of 8 in cell B1. By passing the value xlPasteSpecialOperationAdd through the Operations parameter, then copying and pasting cell A1 (2) to cell B1 (8), the value of B1 would change to 10. A full list of Operation data type values can be found here.
- SkipBlanks (Optional) requests a TRUE or FALSE value for treatment of blank cells on the clipboard. Passing a value of TRUE will force the clipboard to skip blank values when pasting, while false will paste the empty values. The default of this optional parameter is FALSE.
- Transpose (Optional) will transpose the paste from row to column, or column to row. The default of this optional parameter is false.
See the above summary on Methods with Function Calls for the proper syntax to use with method functions.
The Select method is similar to the Activate method, however, it has the ability to select multiple cells simultaneously. Also similar to the Activate method, it is suggested to avoid utilizing this method, as it is often unnecessary and slows down code processing.
The Address property will return the cell reference address, such as A1 or B2. This property is especially useful for instances where the address of a named range is required.
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
The Borders property applies a border around a cell. The borders property is also the parent object to several other properties, which are included in this example within a With-End With statement. You can view a complete list of properties through the Object Browser (press F2), or, if you wait a second after entering a period for a new class, a popup listbox will appear with a list of properties to choose from, as shown to the left.
The Borders property also permits a enum variable to be passed through the XlBordersIndex parameter to specify a specific side of the cell to have a border. This variable can be specifies within brackets after the Borders property it called.
Also note an alternate way if applying this same treatment to cell A1:
Range("A1").Borders.LineStyle = xlContinuous
Range("A1").Borders.Weight = xlThin
Range("A1").Borders.ColorIndex = 1
While this does provide the same result, it is inefficient in terms of processing and duplicates unnecessary code. It’s better to code repetitive object references (Range and Border) through a With-End With statement.
columnVariable = Range("A1").Column
The Column property returns the column number (also known as column index number) of a Range object. In the above example, 1 will be returned. Range B1 would be 2, Range C1 would be 3, and so-on. This property is especially useful when working with named range. If the object Range(“Named_Range”).Column were located in cell D10, the number 4 would be returned.
The EntireColumn property is slightly unique, as it forces its parent object to adjust its scope from an individual range or group range to the entire column(s) of that range. When used alone, a compile error is returned as it simply redefines the original specified object. For this property to work, a separate method needs to be appended. For example, these two following lines of code will work.
The first line will insert an entire new column on column F, shifting all the columns to the left over (columns A-E remain unchanged). The second line will select the entirety of column F.
The EntireRow property is identical to the EntireColumn property in syntax, but provides the treatment to rows instead of columns. It forces its parent object to adjust its scope from an individual range or group range to the entire row(s) of that range. When used alone, a compile error is returned as it simply redefines the original specified object. For this property to work, a separate method needs to be appended. For example, these two following lines of code will work.
The first line will insert an entire new row on row 4, shifting all the rows below down (rows 1-3 remain unchanged). The second line will select the entirety of row 4.
Range("A1").EntireRow.Hidden = [Boolean]
The Hidden property is another unique property which requires some additional code. First, the Hidden property can only be applied to an entire column or row, so it either needs to be preceded by the EntireRow/EntireColumn property. Second, the object/property must be assigned a Boolean value to specify action to take. A Boolean value of true will hide the column/row, while a Boolean value of false will make it reappear.
Range("A1").EntireRow.Hidden = True ' Hide row 1
Range("A1").EntireRow.Hidden = False ' Unhide row 1
Range("A1").EntireColumn.Hidden = True ' Hide column A
Range("A1").EntireColumn.Hidden = False ' Unhide column A
nameVariable = Range("A1").Name
The Name property returns the given name of a cell. For this property to work, the Range object must be a named range (link) and must be assigned to a variable to hold that range.
The Offset property is another property which forces the range of the specified object—in this case, range A1—to adjust based on the property’s parameters. The Offset requires two parameters to specify a relational point for a range. The parameter format is
Range("A1").Offset([# of rows right], [# of rows down]).[Method]
In the example below, the Range that is 2 columns over and 2 columns down from A1 will be selected with the Select method. A member must be appended to the Offset property for it to work.
rowVariable = Range("A1").Row
The Row property returns the row number (also known as row index number) of a Range object. In the above example, 1 will be returned. Range A2 would be 2, Range A3 would be 3, and so-on. This property is especially useful when working with named range. If the object Range(“Named_Range”).Row were located in cell D10, the number 10 would be returned.
valueVariable = Range("A1").Value
The value property returns the actual text or numeric value of the cell. In the above example, variable valueVariable will hold the text/numeric value of range A1. However, the Value property also permits the assigning of a string or variable to the value of the range, as follows:
Range("A1").Value = “Value added”