When working in VBA we often need to refer to a range of cells. What’s the best way to do so?
Well that’s a loaded question! It totally depends on your objective. As with so many things in Excel there are a zillion different ways to refer to a range from VBA. In many circumstances I prefer to use the shortcut brackets, .
Now I will warn you upfront that the shortcut method is never the quickest, but for me the clarity and simplicity it provides usually wins out. And truth be told, the speed difference is modest. If I need to provide the quickest execution speed possible, I’ll chose a different approach. But most of the time the tiny speed difference is irrelevant.
The most common method that I see used to set the value of a cell from VBA is this:
The shortcut method is this:
Just remember that the square brackets are a replacement for the Range/Parentheses/Quotation Marks construct. I guess phrasing it that way highlights the brevity of the shortcut.
The method returns a real reference to the range, so just about anything that can be done with a normal range reference can also be done with the shortcut reference. It can be used on either side of the equal sign. It can be used to feed other functions. And it has all of the methods and properties of a normal range.
To be fair I cheated a little in the above comparison. But to demonstrate that shortcut references have the properties of a normal range, we could have typed it like this:
What is inside the square brackets can be a normal A1 style range reference, but it can also be a defined name. For example, if the workbook contained a Named Range called MyRange, then:
would display a message box with the address that defines the range.
is a reference to all of column A.
is the range defined as all of row 7.
[r14, d20:g30, MyRange, a1].ClearContents
deletes the values from four distinct ranges all at once.
[h:h 5:5] = "hello world"
references the intersection of two ranges to set the value of H5 to a familiar text.
Set vArr = [CurRow CurCol].CurrentRegion
sets the variant array, vArr, equal to all of the cell values in the currently used rectangular range surrounding the intersection of the named ranges, CurRow and CurCol. If thosed named ranges were built from dynamic named formulas then such a construct would be potent indeed. vArr will always be a 2-D variant array, with the rows from the range as the first index, and the columns as the second index.
Now when the shortcut is used on the left side of an equal sign, method, or property assignment, it is important that it reference a range. For example:
[some_named_range] = lngVariable
If the named formula some_named_range references a range on a worksheet, even if a formula is used to produce it, then this will work. On the other hand, if some_named_range references a constant value (from a formula or from a named constant) then the above notation will raise a Run-time error.
I agree with this behavior in the case of a value returned by a named formula, but a named constant should be able to have its value set in this way, in my opinion. But obviously other range properties would not be accessible in the case of a named constant (such as .Address, or .Font, etc.) and this is probably why you cannot change a named constant this way.
The most common usage of the INDEX worksheet function returns a value, not a range like OFFSET does. So if some_named_range was built using INDEX the common way in the name manager, then it is a value and would fail in the above assignment. If it were instead built on OFFSET, then it would work. INDEX can be used to return a range, but that is a story for an upcoming post.
If some_named_range referred to the range of 1,000 cells then:
would place a 5 in all 1,000 cells.
Names.Add "old", [chart_values]
Names.Add "switch_to_record", [record_index].Value
which deserve some explanation.
Names.Add is how to create (or alter an existing named range/formula) from VBA. The first parameter to the method is the name to be applied the named range/formula. The second parameter is the value or values or formula the defined name should refer to. If the name exists when this code runs, it will be replaced by whatever is in the second parameter. There is no way to edit an existing name directly, nor is there a need to delete the existing range first.
Now why does the first line use for the second parameter a range reference, while the second line uses the Value property of a range reference?
Here’s why. [chart_values] is a named formula that returns a set of values and not a range, so it is impossible to use the .Value property on it. As an aside, I usually wouldn’t anyway as the .Value property is the default property of any range reference, and it offers no advantage in speed or clarity. In fact, as you can see in the table below, .Value is usually SLOWER.
Ah, but then why do I use the .Value property for the [record_index] named range in the second line? The answer is a little subtle, but is critical to the successful animation of the chart. The second line is defining a named constant, and it should have a hard number like 3 as its defined value. On the other hand, [record_index] is a named range pointing at a cell on the worksheet. If we dropped the .Value, we would be setting [switch_to_record] to the same cell reference, and NOT to a hard constant value. Using the .Value property ensures that it is set to the hard number and not the range reference.
So how modest is the speed penalty for using this shortcut? I timed a number of different ways of referencing a range, using the GetTickCount library which is accurate to the millisecond. For each test I closed Excel and freshly opened the testing workbook to ensure the memory was clear and that each method was operating under the same conditions. For the first round of tests, I set a range to a value 100,000 times in a For/Next loop, where “i” was the looping variable.
Here are the results:
| Set o = Sheet1.Range(“a1″), o = i
| Cells(1,1) = i
| Set o = Sheet1.Range(“a1″), o.Value = i
| Range(“a1″) = i
| Inside: With Sheet1: .Range(“a1″) = i
| Range(“a1″).Value = i
| Range(“a_named_range”) = i
| Sheets(1).Range(“a1″) = i
| Sheet1.Range(“a1″) = i
| Worksheets(“Sheet1″).Range(“a1″) = i
| Names.Add “a_named_constant”, i
| Worksheets(1).Range(“a1″) = i
| Sheets(“Sheet1″).Range(“a1″) = i
| [a1] = i
| Inside: With Sheet1: .[a1] = i
| [a1].Value = i
| [a_named_range] = i
The clear winner is setting up an object variable to represent the range (it should be noted that “o” was dimensioned as a Range type variable), followed closely by the Cells keyword. If absolute speed is critical to your circumstance then one of these is the way to go.
But remember that these times were the cumulative total required to write a value to the range 100,000 times. So even the slowest method in the test was writing to the range at a speed of 2,652 writes per second!
An interesting note is that reading the ranges is six to seven time faster for each of the range methods, but their relative performance is roughly the same.
I want to also point out that sometimes we need to update the worksheet in a loop. For example to get the animation to show on a chart, we need to give the charting engine a chance to work. If we just run the loop without addressing this issue, we will never see the animation, just the completed state. There are two methods commonly used to do this. The first is to use:
Application.ScreenUpdating = True
inside the loop.
The second is to use:
inside the loop.
So I tested these on the fastest method of range referencing (the object variable method) in the same loop of 100,000 iterations. Here are the results:
Application.ScreenUpdating = True ---> 1,423.56 seconds
DoEvents --->; 65.33 seconds
No that is not a mistake on the first one. The ScreenUpdating method took nearly 24 minutes! So to update a worksheet in a loop, always use DoEvents.
One final point that you should be aware of is that the shortcut range reference only works from within Excel. If you are using some other language to automate Excel, the shortcut range reference will not work.
The shortcut range reference from VBA is a shortcut in the number of characters used to refer to a range, it is not a shortcut in code speed of execution. But for me, in many situations, the brevity and clarity are more important. I work hard to craft the examples I post to Excel Hero, making the VBA clear and concise. I do this to maximize their readability and teaching potential. I feel that my liberal use of the shortcut range reference supports that goal.