The Range Object
A Range Object is the way VBA references single cells, multiple cells, rows, columns even 3-D ranges of cells. I can’t overemphasize how important a concept the range object is for extending Excel with VBA.
Prior to learning VBA, you probably experienced some moment of desperation where you wanted to automate a procedure in your spreadsheet by recording a Macro. If you did a simple copy/paste operation, the resulting code generated by the macro recorder probably looked something like this:
Listing 2: Example of Recorded Macro with Range Object
Sub Macro2() ' ' Macro2 Macro ' Macro recorded 1/4/2006 by Randy ' Range("A2:B6").Select Selection.Copy Range("D2").Select ActiveSheet.Paste End Sub
It probably works fine if you want to repeat the exact same procedure…and maybe you figured out how to hack it up and make it do what you wanted. But let’s delve a little deeper so you can understand how these objects really work so that you can find out how to put them to work doing what you really want to do.
Range Object – Better Practices
The first thing to learn is how to get a value from a cell into VBA and how to put a value back into a cell from VBA. Before you can work with a value in VBA, you need to get it from the worksheet.
Listing 3: Getting and Assigning Values To/From Cells
'Getting/Assigning One Value sngValue = Range("'Sheet1'!B5").Value
The previous example works fine, but its better programming practice to use a variable containing a range object as shown in the examples below.
Listing 4: Better Methods for Getting and Assigning Values To/From Cells
'Use a Range Object variable to refer to the Cell Dim objMyRange As Excel.Range Set objMyRange = Range("'Sheet1!'B5") sngValue = objMyRange.Value objMyRange.Value = sngValue + 50 'Loop through One Column or Row of Cells Dim oMyRange As Excel.Range Dim X As Integer Set oMyRange = Range("'Sheet1!'B5:B20") For X = 1 To oMyRange.Cells.Count MyValues(X) = oMyRange.Cells(X) Next 'Loop through Multiple Rows and Columns Dim oMyRange As Excel.Range Dim R, C As Integer Set oMyRange = Range("'Sheet1!'B5:D20") 'First loop through columns For C = 1 To oMyRange.Columns.Count 'Now through each row For R = 1 To oMyRange.Rows.Count 'Note how we need to specify the row 'and column of the cell in the range SomeVariable = oMyRange.Cells(R, C) Next 'go to next row Next 'go to next column
Range Object – BEST Practices
Say you write a formula in cell B1 that refers to cell A1. Now if you drag the contents of A1 into A2. Excel is pretty smart, and it will update your formula to refer to A2 instead of A1. Unfortunately, this isn’t the case with cell addresses in VBA. If you move cells around or insert or delete columns , you could end up with broken VBA code. Not very fun! So what to do? Use named ranges!
Before you even start your VBA coding, define names for any cell or range of cells that you will be reading from or writing to in VBA. To define a name…
In Excel 2003:
- Insert->Name->Define
- Highlight cell or range of cells, and type new name in the Name Box
In Excel 2007:
- On Formulas Tab, Defined Names Section…
- Click Defined Name and enter the range and the name…or Click on Name Manager
Now, your VBA code becomes:
Listing 5: Best Practices for Using Ranges and Named Cells
MyVBAVar = Range("some_name").Value Range("output_name").Value = MyVBAResult
Now you can feel free to move cells in Excel and your code won’t know the difference. Just remember to click and drag, NOT CUT AND PASTE! You can also expand a table of data used in your code just by manipulating the named range.
Warning: Be careful putting a number back into a cell with the methods described. For Single and Double variables, Excel sometimes returns something that might be off in higher order decimal places. Ie. 15.50000167 instead of 15.5. For starters, I recommend sticking with Doubles for your floating point number variables as opposed to Singles. Also, you might consider using the round function prior to assigning the value back to the Excel Range. It’s likely not an issue from an accuracy perspective, but it could cause a logic error is you are using a logical comparison with an = as opposed to > or < operators. For example, 15.50000167 is not equal to 15.5.
