Elusive Problem with Arrays

For the life of me I can't figure out what it wrong with a user-defined
function I am developing. The problem seems to be in the calculation of
"Distance" from elements of the arrays.

I'm also having problem transfering the ranges to the arrays when the
number of rows is large (~6000).

Any assistance would be greatly appreciated.

Thank you in advance,
Dave

Function IDW(rngX As Range, rngY As Range, rngXData As Range, rngYData
As Range, rngData As Range, Power as Double)
' Fill arrays by assigning ranges to variant variables
varX = rngX
varY = rngY
varXData = rngXData
varYData = rngYData
varData = rngData

NPoints = Application.Count(rngX)
NData = Application.Count(rngXData)
ReDim arrResult(NPoints)

For i = 1 To NPoints
For j = 1 To NData
Distance = Sqr((varX(i) - varXData(j)) ^ 2 _
+ (varY(i) - varYData(j)) ^ 2)
SumDataWeights = SumDataWeights + varData(j) / Distance ^
Power
SumDistWeights = SumDistWeights + 1 / Distance ^ Power
Next j
arrResult(i) = SumDataWeights / SumDistWeights
Next i
IDW = arrResult
End Function

This shouldn't work with any size range.

varX = Range("A1:A10")

is a two dimensional array of size varX(1 to 10, 1 to 1)

similar for all your other arrays.

--
Regards,
Tom Ogilvy



<dgp@dodgeit.com> wrote in message
news:1114718044.019285.16840@l41g2000cwc.googlegroups.com...
> For the life of me I can't figure out what it wrong with a user-defined
> function I am developing. The problem seems to be in the calculation of
> "Distance" from elements of the arrays.
>
> I'm also having problem transfering the ranges to the arrays when the
> number of rows is large (~6000).
>
> Any assistance would be greatly appreciated.
>
> Thank you in advance,
> Dave
>
> Function IDW(rngX As Range, rngY As Range, rngXData As Range, rngYData
> As Range, rngData As Range, Power as Double)
> ' Fill arrays by assigning ranges to variant variables
> varX = rngX
> varY = rngY
> varXData = rngXData
> varYData = rngYData
> varData = rngData
>
> NPoints = Application.Count(rngX)
> NData = Application.Count(rngXData)
> ReDim arrResult(NPoints)
>
> For i = 1 To NPoints
> For j = 1 To NData
> Distance = Sqr((varX(i) - varXData(j)) ^ 2 _
> + (varY(i) - varYData(j)) ^ 2)
> SumDataWeights = SumDataWeights + varData(j) / Distance ^
> Power
> SumDistWeights = SumDistWeights + 1 / Distance ^ Power
> Next j
> arrResult(i) = SumDataWeights / SumDistWeights
> Next i
> IDW = arrResult
> End Function
>

Thanks for your quick response. Is their a way to read a range into a
variant as a one dimensional array?

Or should I loop through the cells in the range to fill the array? What
impact will this have on the speed of filling the arrays.

Thank you,
Dave

You could loop through the two dimensional arrays something like this:

Distance = Sqr((varX(i, 1) - varXData(j, 1)) ^ 2 _
+ (varY(i, 1) - varYData(j, 1)) ^ 2)
SumDataWeights = SumDataWeights + varData(j, 1) / Distance ^ Power


Hope this helps
Rowan

"dgp@dodgeit.com" wrote:

> Thanks for your quick response. Is their a way to read a range into a
> variant as a one dimensional array?
>
> Or should I loop through the cells in the range to fill the array? What
> impact will this have on the speed of filling the arrays.
>
> Thank you,
> Dave
>
>