Faster For-Next Loop?

[dgp]

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

 

[Niek Otten]

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.

 

[Dana DeLouis]

Hello. You pass "zdata" to your function, but I don't see it used. Is
there a typo?

 

[dgp]

Yes, I was in the process of renaming the range variable data (used in
line 5) to zdata.