Are these novelty methods [and perhaps the best way?] Referencing a dynamic cell in VBA?

Question

Usually we use expressions: Cells(i, 1) , Cells(i, "A") or Range("A" & i) to refer to a dynamic cell in VBA, but I believe the following fancy code also works:

 Sub Test1() For i = 1 To 10000 Range("A1")(i) = i Next End Sub 

I also found the label designation using the square brackets [] to refer to the cell (yes, I know that the abbreviated method of expressing the EVALUATE command) can also be used here, for example, 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 

Contrary to popular belief, square brackets can only refer to fixed ranges with a label designation. I checked them all and they returned the same results.

Honestly, I never thought that these three expressions ever exist, so I think they are probably new. It's true?

---

I not only found them, but also tested them to find out which one is better. For the better, I mean their effectiveness using a time 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 next 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 got the length of time to complete on my machine, following

 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, although the results were inconclusive for comparing the expressions: Range("A" & i) , Cells(i, "A") and Range("A1")(i) , as well as for comparing [A1].Resize(1000, 1)(i) and [A1].Offset(i - 1) , it turned out that the fastest performance was Cells(i, 1) . Is this true in general? Why is that? I assume that at runtime, VBA always uses Cells(i, 1) , so when the code compiles, all links in other expressions should be converted to expression 1, since I strongly believe that VBA should remember as its compiled version of the code, so are any expressions that we use to write our code. But this is only an assumption on my part.

Answer 1

following fancy code

Not really. The code is not fancy.
The fact is that the Range variable is a “viewport” for the sheet, not a fixed box with concrete walls. You absolutely can go beyond the originally defined boundaries of your Range .

Line

 Range("A1")(i) = i 

equivalently

 Range("A1").Cells(i).Value = i 

which is fine, even if Range("A1") contains only one cell - you just go beyond that cell.

I never thought that these three expressions ever exist, so I think they are probably new. It's true?

No no. They are very old. The oldest Office that I have on hand is 2003, and it works there, and I'm sure it worked in Office 95.

The reason you never heard of them is probably because no one uses them, which is probably good. Although valid from the point of view of the object model, they are more complicated than simpler methods.

it turned out that the fastest performance was Cells(i, 1) . Is this true in general? Why is that?

I have never tested it (and will not), but Logically Cells(i, 1) should be the fastest because it does not include parsing strings and / or evaluation. Perhaps all other options end up calling Cells(i, 1) .