Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster For-Next Loop?
In the function below I have a For-Next loop that loops though a range
of cells. Would it be any faster to read the range into an array and loop through the array? Function InvDistanceWtd(x, y, xdata as Range, ydata as Range, zdata as Range, power) N = Application.Count(xdata) For i = 1 To N D = Sqr((x - xdata(i, 1)) ^ 2 + (y - ydata(i, 1)) ^ 2) SumZoDn = SumZoDn + data(i, 1) / D ^ Power SumIDn = SumIDn + 1 / D ^ Power Next i InvDistanceWtd = SumZoDn / SumIDn End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster For-Next Loop?
Depending on the size of your range, but in general: a LOT faster!
The arrays in my functions are generally 120 elements (actuarial tables); difference in the order of 10 times faster. wrote in message oups.com... In the function below I have a For-Next loop that loops though a range of cells. Would it be any faster to read the range into an array and loop through the array? Function InvDistanceWtd(x, y, xdata as Range, ydata as Range, zdata as Range, power) N = Application.Count(xdata) For i = 1 To N D = Sqr((x - xdata(i, 1)) ^ 2 + (y - ydata(i, 1)) ^ 2) SumZoDn = SumZoDn + data(i, 1) / D ^ Power SumIDn = SumIDn + 1 / D ^ Power Next i InvDistanceWtd = SumZoDn / SumIDn End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster For-Next Loop?
Hello. You pass "zdata" to your function, but I don't see it used. Is
there a typo? -- Dana DeLouis Win XP & Office 2003 wrote in message oups.com... In the function below I have a For-Next loop that loops though a range of cells. Would it be any faster to read the range into an array and loop through the array? Function InvDistanceWtd(x, y, xdata as Range, ydata as Range, zdata as Range, power) N = Application.Count(xdata) For i = 1 To N D = Sqr((x - xdata(i, 1)) ^ 2 + (y - ydata(i, 1)) ^ 2) SumZoDn = SumZoDn + data(i, 1) / D ^ Power SumIDn = SumIDn + 1 / D ^ Power Next i InvDistanceWtd = SumZoDn / SumIDn End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster For-Next Loop?
Yes, I was in the process of renaming the range variable data (used in
line 5) to zdata. Dana DeLouis wrote: Hello. You pass "zdata" to your function, but I don't see it used. Is there a typo? -- Dana DeLouis Win XP & Office 2003 wrote in message oups.com... In the function below I have a For-Next loop that loops though a range of cells. Would it be any faster to read the range into an array and loop through the array? Function InvDistanceWtd(x, y, xdata as Range, ydata as Range, zdata as Range, power) N = Application.Count(xdata) For i = 1 To N D = Sqr((x - xdata(i, 1)) ^ 2 + (y - ydata(i, 1)) ^ 2) SumZoDn = SumZoDn + data(i, 1) / D ^ Power SumIDn = SumIDn + 1 / D ^ Power Next i InvDistanceWtd = SumZoDn / SumIDn End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can this be done faster? | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Is there a faster way | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Which one is faster? | Excel Programming |