Elusive Problem with Arrays

D

dgp

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
 
T

Tom Ogilvy

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.
 
D

dgp

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
 
R

Rowan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top