Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 851
Anastasiya-Romanova 秀 Avatar asked Aug 04 '16 14:08

Anastasiya-Romanova 秀


People also ask

How do I reference a cell in VBA?

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

How do you refer to a column in VBA?

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.


1 Answers

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

like image 59
GSerg Avatar answered Oct 17 '22 02:10

GSerg