excel - Are these novelty ways [and possibly the best way?] to refer a dynamic cell in VBA?

Question

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:

1. Cells(i, 1) = Rnd
2. Range("A" & i) = Rnd
3. Cells(i, "A") = Rnd
4. Range("A1")(i) = Rnd
5. [A1].Resize(1000, 1)(i) = Rnd
6. [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.

Answer 1

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).