Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency of Dynamic ranges
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency of Dynamic ranges
the reference will not be reevaluated set rRange =
rRange will not be reevaluated, but refers to a fixed range. -- Regards, Tom Ogilvy "Guy Normandeau" wrote: 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency of Dynamic ranges
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 "Tom Ogilvy" wrote: the reference will not be reevaluated set rRange = rRange will not be reevaluated, but refers to a fixed range. -- Regards, Tom Ogilvy "Guy Normandeau" wrote: 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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency of Dynamic ranges
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 -- HTH... Jim Thomlinson "Guy Normandeau" wrote: 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 "Tom Ogilvy" wrote: the reference will not be reevaluated set rRange = rRange will not be reevaluated, but refers to a fixed range. -- Regards, Tom Ogilvy "Guy Normandeau" wrote: 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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency of Dynamic ranges
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! "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Guy Normandeau" wrote: 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 "Tom Ogilvy" wrote: the reference will not be reevaluated set rRange = rRange will not be reevaluated, but refers to a fixed range. -- Regards, Tom Ogilvy "Guy Normandeau" wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
Dynamic Ranges? | Excel Programming | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Dynamic Ranges Q | Excel Programming |