output array

[rgandy]

how can i create a custom function that does something like the following:

function(interpolate) will take a range, interpolate values in the range, and output them as an array

ie.
1
blank
blank
4

would produce

1
2
3
4

 

[EBGreen]

Untested Pseudo code, but:

For Each nItem In FillRange(1, 4)
Debug.Print nItem
Next

Function FillRange(nStart As Int, nStop As Int) As Int()

For i = nStart To nStop
Redim arrResult(UBound(arrResult) + 1)
arrResult(UBound(arrResult)) = i
Next
FillRange = arrResult
End Function

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[vladk]

Look at Add-in programs included with Excel in the help file.

 

[GlennUK]

Surely the LINEST function does that?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Bong]

Assuming you want linear interpolation (as opposed to some polynomial or other exotic scheme), you need to define the number of points to interpolate. Then, the number of segments, given the specified end points, is equal to the number of interpolation points plus one. The formula is then (end point - start point)/number of segments for the increment in each segment (in your example: (4-1)/3 = 1).

Say you call your output array, IntArray. Then something like this:

numPoints = 5
startPoint = 4.2
endPoint = 42
segmentInc=(endPoint-startPoint)/(numPoints+1)
IntArray(0)=startPoint
for i=1 to numPoints
  IntArray(i)=IntArray(i-1)+segmentInc
next
IntArray(numPoints+1)=endPoint

_________________
Bob Rashkin