Efficiency of Dynamic ranges

[Guy Normandeau]

Is is more efficient to use? I would need to know when the range is
evaluated. Is it at the beginning or everytime it is referenced. When you
use 'set vRange = range('tRagen")' is the vRange re-evaluated everytime it
is used as in 'set rValue = rRange.find(...)'. Is there a place that would
give optimization tips.

Sample 1:

dim rRange as range
dim rValue as range

set rRange = range("tRange")
....
set rValue = rRange.find(<variable>, lookin:=xlValues)


Versus

Sample 2:

dim rValue as range
....
set rValue = range("tRange").find(<variable>, lookin:=xlValues)


The 'find' will be executed at variaous times between 150000 - 200000 times.
(I have many sources of data).

 

[Tom Ogilvy]

the reference will not be reevaluated set rRange =

rRange will not be reevaluated, but refers to a fixed range.

 

[Guy Normandeau]

Based on your statement, it would be more efficient to setup all my ranges in
the beggining of the macro. ie:

set rCust = Range("tCustomers")
set rReps = Range("tReps")
set rTerr = Range("Territories")
....

What kind of perfomance gain would I generally see by using the predefined
ranges rCust versus Range("tCustomers")?

Guy Normandeau

 

[Jim Thomlinson]

I just ran a couple of tests and I saw no perforamnce gains refering to a
range object over refering directly to the range itself. Here is the code I
tested. I personally do not tend to set up range objects and set them to my
named ranges. I do however use range objects all of the time as they make
coding a whole pile easier. As a matter of programming habit I declare all of
my variables (Incuding objects) first and then I initialize all of my
variables (where possible) next and then I get into the body of the code. I
find it easier for debugging if I keep my code in discrete sections instead
of all over the place...

Sub test1()
Dim sngStartTime As Single
Dim sngEndTime As Single
Dim rng1 As Range
Dim rng2 As Range
Dim lngCounter As Long

Set rng1 = Range("A1")

sngStartTime = Timer
For lngCounter = 1 To 10000
rng1 = rng1 + 1
Next lngCounter
sngEndTime = Timer
MsgBox "Duration1 " & sngEndTime - sngStartTime

sngStartTime = Timer
For lngCounter = 1 To 10000
Range("A2") = Range("A2") + 1
Next lngCounter
sngEndTime = Timer
MsgBox "Duration1 " & sngEndTime - sngStartTime
End Sub

 

[Guy Normandeau]

Thanks for the reply Jim. I can see that in your test, you are essentially
refering to single cells.

I thought I'd do a few tests with some of my data and what I found is that,
as Tom stated, the range is only evaluated once when using set the name range
to the range object. All access to the range is to a fixed range therefore
eliminating the need to evaluate the formula in the named range.

Here are the results of running this code.

for 100 iterations(For lngCounter = 1 To 100)
Loop1 Loop2
2.12 2.44
2.15 2.43
2.13 2.45
for 1000 iterations(For lngCounter = 1 To 100)
Loop1 Loop2
19.96 23.57
19.98 23.58
20.82 23.71
for 1 iteration(For lngCounter = 1 To 1) but adding find
Loop1 Loop2
223.73 281.27

My named range of 18163 rows has the following assignment:
=OFFSET(Cust!$A$3,0,0,COUNTA(Cust!$A:$A),1)

Sub test1()
Dim sngStartTime As Single
Dim sngEndTime As Single
Dim rPartners As Range
Dim rCust As Range
Dim rCust2 As Range
Dim lngCounter As Long
Dim c As Range

Set rCust = Range("tCust")
Set rCust2 = Range("tCust")

sngStartTime = Timer
For lngCounter = 1 To 10
For Each c In rCust
'Set rPartners = rCust2.Find(c.Value, LookIn:=xlValues)
Next
Next lngCounter
sngEndTime = Timer
MsgBox "Duration1 " & sngEndTime - sngStartTime

sngStartTime = Timer
For lngCounter = 1 To 10
For Each c In Range("tCust")
'Set rPartners = Range("tCust").Find(c.Value, LookIn:=xlValues)
Next
Next lngCounter
sngEndTime = Timer
MsgBox "Duration1 " & sngEndTime - sngStartTime
End Sub


I would concur with you Jim that defining the ranges make perfect sense. It
cleans the code and makes it much easier to debug. If one is it use the
range to refer to small single cells, the difference is very minimal and
assigning the range object might just be a waste of time. However, if
performance is an issue, defining and setting the range object seems to be
the way way to go.


Thanks Jim and Tom for your help!