We usually use the expressions: Cells(i, 1)
, Cells(i, "A")
, or Range("A" & i)
to refer a dynamic cell in VBA, but I find the following bizarre code also works:
Sub Test1()
For i = 1 To 10000
Range("A1")(i) = i
Next
End Sub
I also found the shortcut notation using the square brackets [ ] for referring to a cell (Yes, I know that a shorthand method of expressing the EVALUATE command) can also be used here like the following codes
Sub Test2()
For i = 1 To 10000
[A1].Resize(1000, 1)(i) = i
Next
End Sub
or
Sub Test3()
For i = 1 To 10000
[A1].Offset(i - 1) = i
Next
End Sub
It's contrary to popular belief that the square brackets can only refer to fixed ranges with shortcut notation. I tested all of them and they returned the same outputs.
Honestly, I've never thought those three expressions ever exist, so I guess they're probably new. Is it true?
Not only did I find them, I also tested them to see which one is the best. By the best I mean in their performance using timing test. I tested the statements:
Cells(i, 1) = Rnd
Range("A" & i) = Rnd
Cells(i, "A") = Rnd
Range("A1")(i) = Rnd
[A1].Resize(1000, 1)(i) = Rnd
[A1].Offset(i - 1) = Rnd
to the following code
Sub Test()
Dim i As Long
Randomize
For i = 1 To 1000 'I also tested them with 10,000 loops
'Put the expression here
Next
End Sub
I obtained the time duration to complete on my machine as follow
1,000 loops
1 2 3 4 5 6
0.290110725 0.298291317 0.305540433 0.289084126 0.325044276 0.318445433
0.270974218 0.287950980 0.276009685 0.277133638 0.318741694 0.312968414
0.277361318 0.274790389 0.273291810 0.275994401 0.311879789 0.312000675
0.279113453 0.275501647 0.275247422 0.281113426 0.311558662 0.315628943
0.270359637 0.276440868 0.279950951 0.276444561 0.320118775 0.311556754
0.270066136 0.281525061 0.273649022 0.276767648 0.311083246 0.311015128
0.274146235 0.277156933 0.274465750 0.287375210 0.311426416 0.319849274
0.269184843 0.277200430 0.276525859 0.276931561 0.322461782 0.310902381
0.271190611 0.283046575 0.280286123 0.275876294 0.312358236 0.313066500
0.271210909 0.277953463 0.274105173 0.276916590 0.312845710 0.321566549
Average time
0.274371809 0.280985766 0.278907223 0.279363746 0.315751859 0.314700005
and
10,000 loops
1 2 3 4 5 6
1.897854697 1.975970014 2.026380540 1.963044684 2.667340257 2.404596752
1.893136200 1.958722430 1.997488630 1.957524600 2.412742475 2.364692000
1.915567238 1.991447404 2.026974359 1.972207855 2.396174991 2.408500400
1.885336683 1.964379644 2.001175971 1.950138292 2.362537378 2.369196417
1.889658641 1.959677449 1.998453783 1.984470995 2.372677528 2.366525087
1.885327819 1.963668734 1.997487505 2.038683070 2.367691027 2.380044796
1.878379741 1.958654295 2.002764956 2.008183347 2.368766984 2.362091273
1.894069516 1.960857991 1.994435035 2.031241378 2.377953481 2.367554909
1.894528017 1.972240515 2.003587552 1.961539277 2.364523191 2.373092790
1.883387443 1.965169572 1.999893716 1.948455660 2.363346303 2.368680396
Average time
1.891724600 1.967078805 2.004864205 1.981548916 2.405375362 2.376497482
Based on these two results, though the results were inconclusive for comparing the expressions: Range("A" & i)
, Cells(i, "A")
, and Range("A1")(i)
and also for comparing [A1].Resize(1000, 1)(i)
and [A1].Offset(i - 1)
, it turned out the fastest performance was Cells(i, 1)
. Is this true in general? Why so? My guess is during run-time VBA always uses Cells(i, 1)
, so when the code is being compiled all references in other expressions must be converted to expression 1 since I strongly believe VBA must memorize both its compiled version of the code and whatever expressions we used to write our code. But it's only speculation on my part.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
Using this property we can use any column in the specified worksheet and work with it. When we want to refer to the cell, we use either the Range object or Cells property. Similarly, how do you refer to columns in VBA? We can refer to columns by using the “Columns” property.
the following bizarre code
Not really. The code is not bizarre.
The thing is, a Range
variable is a "viewport" for the sheet rather than a fixed box with concrete walls. You absolutely can go beyond the initially defined bounds of your Range
.
The line
Range("A1")(i) = i
is equivalent to
Range("A1").Cells(i).Value = i
which is perfectly doable even though the Range("A1")
only contains one cell - you are just stepping beyond that cell.
I've never thought those three expressions ever exist, so I guess they're probably new. Is it true?
No, it's not. They are very old. The oldest Office I have at hand is 2003 and it works there, and I'm pretty confident it used to work in Office 95.
The reason you never heard about them is probably because no one is using them, which is arguably a good thing. While they are valid from the object model's point of view, they are more confusing than more straightforward ways.
it turned out the fastest performance was
Cells(i, 1)
. Is this true in general? Why so?
I never tested it (and I won't), but logically Cells(i, 1)
should be the fastest because it does not involve string parsing and/or evaluating. Arguably, all the other options ultimately call Cells(i, 1)
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With