+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
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:
Range("A1").Value = 123The shortcut method is this:
[A1] = 123
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:
[A1].Value = 123
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:
MsgBox [MyRange].Address
would display a message box with the address that defines the range.
[a:a]is a reference to all of column A.
[7:7]is the range defined as all of row 7.
[r14, d20:g30, MyRange, a1].ClearContentsdeletes 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].CurrentRegionsets 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:
[some_named_range] = 5
would place a 5 in all 1,000 cells.
In my Animated Business Chart, the AnimateChart sub contained these two lines:
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:
| Method | Seconds | Writes/Second |
|---|---|---|
| Set o = Sheet1.Range("a1"), o = i | 21.672 | 4,614 |
| Cells(1,1) = i | 22.297 | 4,484 |
| Set o = Sheet1.Range("a1"), o.Value = i | 23.671 | 4,224 |
| Range("a1") = i | 23.812 | 4,199 |
| Inside: With Sheet1: .Range("a1") = i | 23.961 | 4,181 |
| Range("a1").Value = i | 24.594 | 4,066 |
| Range("a_named_range") = i | 24.703 | 4,048 |
| Sheets(1).Range("a1") = i | 25.843 | 3,869 |
| Sheet1.Range("a1") = i | 25.938 | 3,848 |
| Worksheets("Sheet1").Range("a1") = i | 26.141 | 3,825 |
| Names.Add "a_named_constant", i | 26.906 | 3,716 |
| Worksheets(1).Range("a1") = i | 28.468 | 3,512 |
| Sheets("Sheet1").Range("a1") = i | 29.031 | 3,444 |
| [a1] = i | 31.234 | 3,201 |
| Inside: With Sheet1: .[a1] = i | 33.375 | 2,996 |
| [a1].Value = i | 34.375 | 2,909 |
| [a_named_range] = i | 37.703 | 2,652 |
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:
DoEvents
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.
If you liked this article, please share it!
"The INDEX worksheet function returns a value, not a range like OFFSET does."
This is not entirely true.
The Index function can point to a cell and return 2 things
1) When used as a stand alone formula Index points to a cell and returns the value of the cell
2) When Used as portion of a range reference, Index points to a cell and returns the Address
Eg
=Index(A:A,12) will point to the 12th cell in column A and return the value of the cell
=A1:Index(A:A,12) will point to the 12th Cell and return the address A12( This can be observed using the Evaluate formula button, but not through to F9)
=A1:Index(A:A,Counta(A:A)) is thus Dynamic name
Like wise Index():Index() will also return a Range
and can be used to create Dyanamic names for say Dependent validations
I always prefer to use
Set o = Range("SomeRng")
and then use o.value2 = "something" rather than .value
It ensures that I access the "unformated" or "underlying" value of the cell especially when reading from a range
@sam -
You are right of course. And in fact I mentioned this in an earlier draft of the article. But during editing I decided that INDEX in conjunction with a colon in a range is esoteric to the point where including it would be too tangential to this article. I am working on another article where that usage will be explained fully.
However, I now feel I cut too much in the editing. After reading your comment, I changed the paragraph in question.
Thanks for the feedback,
Daniel Ferry
excelhero.com
@sam -
Good tip for the readers.
When I was doing all the timing tests, I actually did the tests both with .Value and .Value2. They were just a few milliseconds different over 100,000 writes. But it was not consistent and so I concluded that there is no real difference in time between the two properties.
If anyone is interested to know the difference between the two, .Value2 does not use the Currency and Date data types, so those values are returned as doubles, potentially saving a step if you need the value as a double instead of a currency or a date:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.value2(office.11).aspx
Regards,
Daniel Ferry
excelhero.com
Daniel - Thanks for posting that, it's always good to see hard data on these things. A few comments:
[] is short for the Evaluate function, so if what is inside the brackets looks like a range address, it will evaluate it as a range address. I wrote a short test routine and stepped through it and noted what was displayed in the "locals" window, with the following results:
TestRange = [a3:a5] ' Variant/Variant(1 to 3, 1 to 1)
TestRange = [a3:a5].Value2 ' Variant/Variant(1 to 3, 1 to 1)
TestRange = [a3:a5].Cells ' Variant/Variant(1 to 3, 1 to 1)
TestRange = Range("a3:a5") ' Variant/Variant(1 to 3, 1 to 1)
TestRange = Range("a3:a5").Value2 ' Variant/Variant(1 to 3, 1 to 1)
TestRange = Range("a3:a5").Cells ' Variant/Variant(1 to 3, 1 to 1)
TestRange = [a3] ' Variant/Double
TestRange = [a3].Value2 ' Variant/Double
TestRange = Range("a3") ' Variant/Double
TestRange = Range("a3").Value2 ' Variant/Double
Set TestRange = [a3:a5] ' Variant/Object/Range
Set TestRange = [a3:a5].Cells ' Variant/Object/Range
Set TestRange = Range("a3:a5") ' Variant/Object/Range
Set TestRange = Range("a3:a5").Cells ' Variant/Object/Range
On the question of speed, if you need to read or write from/to a single cell then I have no argument with what you say, but if you need to read a block of data, do something with it, and write the results back to the spreadsheet, then there is a huge speed advantage in bringing in the data as an array, and writing the results back from an array in one operation, rather than reading and writing from a range object one cell at a time.
I used to use the [] notation a lot (partly becaused it was also recognised in Lotus Script which I used to use more than VBA), but I have allowed myself to be brainwashed by the "be explicit" Nazis :)
One area where I still use it is in development/debugging where I usually write:
[vacant range] = whatever results I want to look at
rather than using debug.print, which I find clumsy and annoying.
Cool site!!
Nice blog layout, As for the post it was ok.
A few days ago I stumbled upon an Excel Version 5 Visual Basic Users Guide from 1994.
Version 5 was the first Excel version with Visual Basic.
Strangely enough even though I had never seen the use of the Range Shortcut [] until I came to this site, it is mentioned in an unmarked paragraph in the cell properties section from .
@Hui -
Very cool!
I cannot remember when I started using it and the Application.Evaluate function, but it was way back there somewhere - probably the '90s.
Regards,
Daniel Ferry
excelhero.com
Excel Hero Academy Homework
Jose Alberto Carranza
Good post, I will use this technique to shorten my codes; specially when referencing only to one cell. Thanks for the Info.
The VBA technique I picked up was mostly MrExcel taught so I have been using the Cells( , ) type reference.
Can take a little time to remember what it's refering to when you return to code - counting columns etc...
Oli (EHA student)
Range("A1").Value = 123
to
[A1] = 123
It's great to find things like this early on when learning a new subject. Thanks for another great post.
Excellent read - this will be helpful when I go back and analyze more of your archived workbooks. Thanks!
Thanks for this one, Daniel, i think for the beginning VBA programmer one of the most difficult hurdles is 'readability' and this addresses that problem very well. Advanced and multi-language programmers are much better in my experience at 'translating' in their heads from code to the understandable and conversational explanations, but the [] really helped me since i am an only VBA programmer.
Im using fewer comments in my code and having no loss of understanding when i look back over it now that i use the [] heavily.
Jesse Warburg (EHA student)
Great overview and comparison between the different ways of referencing ranges! I used to use only Range() and Cells(). Now I know the many different options and the reason of using them. Thanks much, Daniel.
-Luke Liu (student of EHA)
In a word, phew! This stuff repays close reading. I just hope I can remember it all.
Another great post. Shortcut notation really makes the code more concise and readable. Can't believe I didn't know about it.
Excel Hero Academy Homework
Hans Knudsen
I have read the article two times. Have I understood it completely as Daniel demands? Must admit that I am not fully convinced of that. Maybe a third read through is necessary/sufficient.
I am right with Hans on that one - it takes a couple of reads...
It is a well written article, I am just not very skilled when it comes to VBA, so the learning curve on this one is a bit steep.
Daniel, I think the message in the article goes well with your general mantra of
keeping codes and formulas lean and clean. Thanks for the inspiration!
Very interesting. Having learnt VBA the hard way by reviewing recorded macros this will certainly be useful
Thanks Bruce (EHA student)
It's funny, I had never seen nor heard of such a shortcut until I started reverse engineering one of your animations. I thought I understood it, but kept running into problems. Now I know why... I was pointing to constants.
I haven't gone back and re-written existing code to add this (a lot of trouble for semantics), but going forward, it will be used in all my code.
Thanks again!
Very glad to be learning VBA with this goal of readability. (EHA student)
Great read. I'll need to do so a few more times to get it all.
Nice tip and speed comparison too... which means for a not very large set of data these shortcuts are the better way to go , and as the data set increases then it's up to our decision on performance vs readeability i guess. nice ..
Sriram
EHA student
Great post!
-Harold
Very good stuff, as always. Thanks for the insight. The only thing I would add is that, as a relative novice when it comes to VBA, I truly appreciate it when code is annotated with comments. It really helps me to learn and understand the code. Just a thought.
Michael P.
EHA Student.
Thanks to posts like this my VBA is improving a lot over the last few weeks.
However, for me it doesn't really matter if it takes 21 or 37 seconds to finish the 100,000 lines. Since my VBA knowledge is still very basic, it's more about getting the code to do what i want.
Tim
The [] method of referencing is so much quicker to type and easier to read in the code that the speed issues doesn't come into it.
I use range names between the square brackets [myrange] - its basically the same as having sheet based variables and again its easy to read in the code.
Neale Blackwood EHA
Like others I did not understand the use of brackets before this course. Not sure I even saw them before. Thanks!
Ed
EHA student
Excellent article. Thanks, Daniel.
In keeping with the hero metaphor, I'm a bit worried that the VBA material in EHA will be my Kryptonite. However, I am looking forward to giving it my best shot.
Very good information
I think begginers on VBA like me will appreciate the clarity that the [named formulas] method brings
(EHA Student)
Hi Daniel,
My main take away was "Do Events" is 24 times faster than the use of "ScreenUpdating"......
Cheers,
Michael
Excel Hero Academy Student
The disadvantage of being self-taught on anything is that you miss little tricks like this one. I only learned what I needed to know to get the job done ... whether it was efficient or not! I love the [] shortcut as it is clean and concise. Seeing the different ways to reference a range is also helpful. And Do Events will now replace all the screenupdating statements from here on out.
Thanks for all the information Daniel!
Janice (EHA)
Being completely self taught in Excel before reading this post I had never realised the range of alternate syntax available for referring to a range in VBA. Great stuff.
Scott Wiltshire - Academy homework
Mostly intuitive, but good to see it described clearly.
Very interesting - need to read it a few more times to understand it fully.
Have to agree with some of the comments above. The problem with self taught vba, especially from recorded macros, is you don't know what you don't know! I can see this technique will come in useful
Andy
EHA Student
Never new that you could use an intersection reference.
Excel Academy Student
Full Disclosure: Currently I don't do VBA.
Here is what I learned from this article.
1) Numerous "ways" to assign a value to a range or named formula: [ ] is the shortest
2) Just like Excel functions the efficiency/speed of processing the different "ways" is NOT equal
3) The shortcut used on the left side of =,property or method assignment----MUST reference a range (or formula that returns a range) not a constant
4) Defined Names CAN'T be edited they are both ADDED and REPLACED with ADD method
5) VALUE property must be included when the objective is to return a value not a range reference.
6) There are two methods to use inside a loop to assure that the processing of the loop is seen (1)Application.ScreenUpdating = True (2) Do Events
7) Do Events is 20+ Times Faster to process the loop
8) Summary: Shortcut Range references are most desirable but if there is an efficiency issue then consider other alternatives.
Rick
Rick.
Wow. I'd say you got it!
Daniel
I liked Rick's summary, especially since I don't do VBA (yet) either.
~karen, EHA student
Thanks Rick (Konvalinka)for your valuable summary. My acquaintance with VBA is slight!
Paula (EHA)
I read the article + comments a few times, I get the concepts. Now i need to make use of it.
What is VBA?
What is Range("A1").Value = 123?
I could not reproduce any of this on an excel sheet. So I did not get as far as the [] short cut as I did not know what the formula were trying to do in the first place.
If I need to learn this towards herohood can you point us to something about VBA and how is it used.
some of your explanations assume a reasonable level of IT knowledge, but please don't. I can move fast if all the steps are in place but I feel that these articles take a few small jumps and I am falling through the gaps
@mms...Google can point you towards something on VBA and how it is used. You can't really expect a writer to write an advanced tutorial while referencing all the less advanced stuff that all intermediate readers might possibly need to know that stands behind it. Otherwise this would be more like the Excel Bible (Walkenbach) rather than a blog post on an advanced topic.
Interesting, I saw that kind of referencing, but have never used it...maybe I'll give it a try.
Beside, EVALUATE function is quite powerfull and I discovered it recently...especially when dealing with Named Formulas you taught us in Excel Hero Academy.
Thanks Daniel.
Drazen (EHA student)
Your blog is amazing.
1 question - how do you use the above method for referencing variable ranges e.g. Range(cells(i,1),cells(r,5)) where both i and r are variable? Rows("1:" & r).select?
Also who is sam? He seems to be a genius too!
@mdkapashi -
Thanks for visiting and enjoying my blog!
The shortcut reference - the square brackets - is really a shortcut for the EVALUATE method of the Application object of VBA. In the shortcut form, you cannot use variables. But if you use the long form, Evaluate(), you can.
Sam has been one of my readers since the beginning. His knowledge staggers me sometimes.
Daniel,
Thanks a lot for your prompt reply. I never used the EVALUATE method. I looked it up on Google, but didn't find it to be of much use. Are there any clear advantages?
On another unrelated topic - I just can't seem to understand the ManualUpdate method for pivot tables. Can you throw some light on why it is necessary? I found that most programmers seem to use this in all pivot table macros?
Thanks a lot.
Hi Daniel another highly absorbing article, thank you.
One important point I didn't quite grasp was regarding the Do Events vs screenupdating = true and the amazing time difference in favour of an animation loop. But what about a non animation loop impacting the worksheet we all do day to day? I think you are saying for any loop that updates a worksheet always use the Do Events command for some great speed improvements, is that right?
Cheers
John
Hi John.
You are spot on.
The point was that DoEvents is much quicker.