Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Elusive Problem with Arrays
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 wrote in message ups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Elusive Problem with Arrays
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Elusive Problem with Arrays
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of arrays | Excel Worksheet Functions | |||
Problem with SUMPRODUCT and Arrays | Excel Worksheet Functions | |||
Problem with plotting a chart when using arrays as Values and Xvalues | Charts and Charting in Excel | |||
Arrays | Setting up and Configuration of Excel | |||
arrays in arrays | Excel Programming |