0

I have a code to auto-create a form, so at most it references some cells twice or 3 times to set the field name and maybe change font size or colour depending. In situations where it sets a value and changes the font, I can reference the range each time, yielding 2 lines of code, or I can use With to reference it once but it totals 4 lines of code. Intuitively I think 2 lines are less than 4 but I'm a noob and I'm not sure if there is some efficiency in using With that would add up if the entire script used it. Say for example:

Range("A1").value = "Hi"
Range("A1").font.size = 12

Vs 

With Range("A1")
     .value = "Hi"
     .font.size = 12
End With

Which would be faster?

braX
  • 11,506
  • 5
  • 20
  • 33
Andrew
  • 39
  • 7
  • 5
    Why don't you place it in a loop that loops 100k times and test it? – K.Dᴀᴠɪs Nov 30 '18 at 15:13
  • 1
    [You may find this to be of use](https://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code) – cybernetic.nomad Nov 30 '18 at 15:15
  • 1
    I'll expect the With End With is faster, and I think it's better readable, better structured and it (can) save the use of variables – EvR Nov 30 '18 at 15:19
  • 2
    This isn't about performance. `With` is actually pure laziness here. When any expression is duplicated in code, the solution is to extract it into a local variable - the fact that it's an object in this case makes `With` legal, but you'd get the exact same "benefits" with `Set target = ActiveSheet.Range("A1")` and then working off `target`. The `With` block only serves to hide a local object reference - in any case, either way is better than dereferencing the very same object for every statement you need to run against it. – Mathieu Guindon Nov 30 '18 at 15:32
  • @MathieuGuindon How do you feel about using `With target`, where `target` **is** a local var? – K.Dᴀᴠɪs Nov 30 '18 at 15:36
  • 1
    Using With...End With is quicker both when using the object directly and when using an object variable, I just tested. The speed difference I clocked was about 30% in favour of the With...End With construction. – jkpieterse Nov 30 '18 at 15:36
  • 2
    To the close voters that picked primarily opinion-based as a close reason, you're simply incorrect. There is a ***demonstrably*** better option here. – Comintern Nov 30 '18 at 15:37
  • @K.Dᴀᴠɪs rather dirty. A `With` block *with*holds an object reference: if you use `With` here, then a local variable is redundant. Best use of `With` IMO is `With New Something`, where the object is created right there & then, and destroyed at `End With`. If the object survives the `With` block, the block is clunky. – Mathieu Guindon Nov 30 '18 at 15:37
  • @K.Dᴀᴠɪs I think the source of the OP's confusion is not recognizing that `Range("A1")` is a function call, not an object reference. – Comintern Nov 30 '18 at 15:39
  • @MathieuGuindon Hmm. Interesting take. I am certainly guilty of using these statements when I reference the same object many times in succession. For me, it improves readability because I know I am always referring to the same object var - mainly when dealing with ranges (`.Range(.Cells(), .Cells())`) I esp use it in your case `With New RegExp` I use quite often, as once I test a regular expression the obj is usually useless after that point. – K.Dᴀᴠɪs Nov 30 '18 at 15:46
  • 2
    @K.Dᴀᴠɪs C# has contaminated me :) – Mathieu Guindon Nov 30 '18 at 15:47
  • @MathieuGuindon Just so I understand you correctly... I should put `Dim target as variant` and everywhere I would make reference to a range, I should `set target = range` and then reference `target` instead? – Andrew Nov 30 '18 at 16:01
  • 1
    @Andrew `Dim target As Range`... `Set target = Range(...)`. While you can _technically_ use a variant data type here, it's advised that you reserve those for the rare occasion that your variable is _supposed_ to accept different data types, not when the data type is known prior to runtime. – K.Dᴀᴠɪs Nov 30 '18 at 16:02
  • 1
    To expand on the above comment, a `Variant` can't be bound at compile time, while a `Range` can. This means that VBA has to determine what interfaces are available at run-time (via `QueryInterface`, `GetIdsOfNames`, etc). Code that is late bound in this way is *always* less performant due to the work that would otherwise be performed by the compiler. – Comintern Nov 30 '18 at 16:09
  • @Comintern – as it was explained to me on meta, "closing as primarily opinion based" does not mean that there is no real answer possible. It just means that community is not patient enough with keeping the question open, waiting for that answer to arrive. I do not consider it to be fair, too, but this is how SE sites sometimes work. – miroxlav Nov 30 '18 at 16:24
  • @miroxlav I'm well aware of that, and that's why we vote. ;-) The comment was mainly because commenting is the only way to disagree with a close vote - there isn't really any other mechanism to cast a "do-note-close vote". – Comintern Nov 30 '18 at 16:28

3 Answers3

2

In this case, you won't get significant speed difference ⇒ optimize for yourself.

The main optimization today is on time and energy of those who maintain the code.

So you are not optimizing processing if the gain will be 2% faster code or consume 5% less memory. If the gain is insignificant for speed or memory, just optimize the code for the easiest readability.

This also means that the same code construct with different emphasis inside can be optimized once in one way and second time in the other. (Of course, calling of Range("A1") should be always made only once, storing the result into a variable.)

In your case here, use With Range("A1") ... End With. It gives clearer context and saves the developer from reading of the same text. Omitting duplication of the same text, it is also less error-prone.

Community
  • 1
  • 1
miroxlav
  • 11,796
  • 5
  • 58
  • 99
  • Regardless of the speed difference, the statement "the same code construct with different emphasis inside can be optimized once in one way and second time in the other" is incorrect in this specific case. Calling a method repeatedly (which is what `Range("A1")` is doing) when it deterministically can't change is ***always*** an optimization, regardless of how you achieve that goal. – Comintern Nov 30 '18 at 16:03
  • @Comintern – oh yes, I left that gap intentionally to keep the answer short. For the first case, storing function result inside the variable is a must. But well, VBA is still [a quick scripting tool for office people (read the answers here)](https://softwareengineering.stackexchange.com/q/252253/119490) so it does not matter *so much*. You see, the OP is solving here 2% acceleration of the code, but if they really want it faster, they would search for different approaches. As I found around, VB.NET is 200-400% faster in calculations. From this viewpoint, effort of saving few % looks... – miroxlav Nov 30 '18 at 16:11
  • 2
    The attitude that VBA is merely a scripting toy is incredibly demeaning to the programmers that use it. The fact that you're solving a problem in Excel using a programming language that is integrated into the environment doesn't imply that you shouldn't care about a 2% performance gain. It ***certainly*** doesn't mean that you should take license to do something stupid like repeatedly call a function that returns the same thing. If less VBA developers behaved like script-kiddies and more behaved like programmers, there wouldn't be such a proliferation of crap VBA code on the web... – Comintern Nov 30 '18 at 16:22
  • @Comintern – sure, scripting for yourself is one thing and publishing it as "this sample code helps" is other. The latter one carries much more responsibility and should be re-factored prior to publishing. But often, people do not care - it's common follow-up on behavior script-kid vs. programmer you wrote about. – miroxlav Nov 30 '18 at 16:27
  • 1
    Then if your "answer" is "who cares", how is that an answer? – Comintern Nov 30 '18 at 16:29
  • @Comintern – please maybe read better, it is not there. :) The main goal is that if the OP opens their code 2 months later, as less experienced programmer they can lose 20 minutes to figure out what the macro did. This will often take more time than all those insignificant millisecond gains they are trying to achieve. And after another 6 months guess what - they will lose another 20 minutes, not remembering anything :) I am *for* optimizations but I also learned to intentionally overlook some less important details, where possible. – miroxlav Nov 30 '18 at 16:36
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184527/discussion-between-miroxlav-and-comintern). – miroxlav Nov 30 '18 at 16:39
1

I tested the example code 100,000 times in loop as suggested and With was about 2% faster. With had a time of 36.07 seconds and the Range example had a time of 36.81 seconds.

Given all comments, With seems to be better all around, as both faster and easier to read, even when the range is only referenced twice.

Andrew
  • 39
  • 7
-2

I tested with this routine:

Sub UseWith()
Dim oCell As Range
Dim sName As String
Dim lColorIndex As Long
Dim bItalic As Boolean
Dim sSize As Single
Dim bStrikethrough As Boolean

TimerStart "1"
For Each oCell In Sheet2.UsedRange
    sName = oCell.Font.Name
    lColorIndex = oCell.Font.ColorIndex
    bItalic = oCell.Font.Italic
    sSize = oCell.Font.Size
    bStrikethrough = oCell.Font.Strikethrough
Next

TimerReport

TimerStart "2"

For Each oCell In Sheet2.UsedRange
    With oCell.Font
        sName = .Name
        lColorIndex = .ColorIndex
        bItalic = .Italic
        sSize = .Size
        bStrikethrough = .Strikethrough
    End With
Next
TimerReport
End Sub

Result:

1 0.2040441766

2 0.1509655957

So 1 is about 35% slower than 2.

dwirony
  • 5,487
  • 3
  • 21
  • 43
jkpieterse
  • 2,727
  • 1
  • 9
  • 18
  • 1
    While the results bear out the obvious conclusion that option 1 makes fewer calls to the `Range` indexer, you can't get ***any*** reasonable benchmark with only one iteration, especially when you are re-using variables between the 2 sections of code under test. – Comintern Nov 30 '18 at 15:48
  • 2
    The conclusion is anecdotal. Multiple runs arrive to different conclusions, and inverting the two tests produces different results. – Mathieu Guindon Nov 30 '18 at 15:59
  • Anecdotal? I care to disagree. I ran the routine a dozen times, both in the original order and in the reverse order. The result is quite reproducable, Standard deviation of both times are about 0.002 on an average time of 0.2 vs 0.15 repectively. – jkpieterse Nov 30 '18 at 19:15