PDA

View Full Version : VBA macro need for linear Interpolation



tspeed
09-01-2015, 07:00 AM
I found below code from another thread.


Function LinInterpolate(xVal As Double, _
xRange As Variant, _
yRange As Variant, _
Optional isSorted As Long = 1) As Double

Dim yVal As Double
Dim xBelow As Double, xAbove As Double
Dim yBelow As Double, yAbove As Double
Dim testVal As Double
Dim High As Long, Med As Long, Low As Long

Low = 1
High = xRange.Cells.Count

If isSorted <> 0 Then
'Rem binary search sorted range
Do
Med = Int((Low + High) \ 2)
If (xRange.Cells(Med).Value) < (xVal) Then
Low = Med
Else
High = Med
End If
Loop Until Abs(High - Low) <= 1
Else
'Rem search every entry
xBelow = -1e+205
xAbove = 1e+205

For Med = 1 To xRange.Cells.Count
testVal = xRange.Cells(Med)
If testVal < xVal Then
If Abs(xVal - testVal) < Abs(xVal - xBelow) Then
Low = Med
xBelow = testVal
End If
Else
If Abs(xVal - testVal) < Abs(xVal - xAbove) Then
High = Med
xAbove = testVal
End If
End If
Next Med
End If

xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)

LinInterpolate = yBelow + (xVal - xBelow) * (yAbove - yBelow) / (xAbove - xBelow)
End Function


However, when i can function from excel cell it shows error "

Unable to get the Match property of the WorksheetFunction class(Number=1004)"



I have selected in this format " =LinearInterp($P$1:$P$10,$O$1:$O$10,Q10)"

I am new to VBA macro. Can someone help to resolve this error ?

Thank you for your help in advance

SamT
09-01-2015, 01:10 PM
In VBA you can troubleshoot your code by putting "Option Explicit" at the top of the code page above all other code. By running Debug>>Compile VBA Project from the VBA editor menu, and by use of a test stub to run functions.



Loop Until Abs(High - Low) <= 1
High and Low are either the same or have a difference of 1. I did not analyze the code in the If Else part.


xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)
[x,y]Above and Below may be the same, therefore (yAbove - yBelow) or (xAbove - xBelow) may be zero


LinInterpolate = yBelow + (xVal - xBelow) * (Zero) / (Zero)
Finally: It has been a long, long time, but I remember (A+B)(A-B), Not (A+A)(B+B)

You might try

Dim S As Worksheet
Set S =Sheets("SomeSheet")
MyVariable =LinearInterp(S.Range"($P$1:$P$10"), S.Range("$O$1:$O$10"), S.Range("Q10").Value)



Personally, I would not use the ":" next line style.

xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)
I hope this helps.

Paul_Hossler
09-01-2015, 02:29 PM
Based on the function definition ...




Function LinInterpolate(xVal As Double, _
xRange As Variant, _
yRange As Variant, _
Optional isSorted As Long = 1) As Double[



...I'd guess that this




LinearInterp($P$1:$P$10,$O$1:$O$10,Q10)"



... should be



LinearInterp(Q10, $P$1:$P$10,$O$1:$O$10)"



Assuming that P1:P10 is the range of x values, O1:O10 is the range of y values, and Q10 is the X that you want to interpololate for

mikerickson
09-01-2015, 08:56 PM
Its been a while since I wrote the OP code.
I don't understand the error, that code doesn't use Worksheet.Match

Kenneth Hobs
09-02-2015, 08:15 AM
LinearInterp<>LinInterpolate

You might want to look into =Linest() and =Trend().