Custon Function to Interpolate Table Error

[kernalsend]

I've created a user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output. I'm stumped, any suggestions? Below is my code.

Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
    Worksheets("ITC").Select
    'Make table into a range for VLookUp
    Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
    Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
    'If the given value does not match a table value exactly
    On Error Resume Next
    J = Application.WorksheetFunction.VLookup(efpd, rnge, 1, False)
    If Err.Number = 1004 Then
        w = Application.WorksheetFunction.VLookup(efpd, rnge, 1, True)
        x = Application.WorksheetFunction.VLookup(efpd, rnge, 2, True)
        y = Application.WorksheetFunction.VLookup(efpd, rnge, 3, True)
        'If the given value is greater than, or equal to the largest table value
        If (w = Range("A3").End(xlDown).Value) Then
                itcinter = x + pwr * (y - x)
        Else
            'If the given value requires a table interpolation
            a = Application.WorksheetFunction.Match(efpd, mtrnge, 1)
            z = Range("A" & a + 3).Value
            xx = Range("B" & a + 3).Value
            yy = Range("C" & a + 3).Value
            b = (z - w)
            itcinter = (x / b) * (z - efpd) * (1 - pwr) + _
            (xx / b) * (efpd - w) * (1 - pwr) + _
            (y / b) * (z - efpd) * (pwr - 0) + _
            (yy / b) * (efpd - w) * (pwr - 0)
        End If
    'If the given value does match a table value exactly
    ElseIf (Application.WorksheetFunction.VLookup(efpd, rnge, 1, False) >= 0) Then
        a = Application.WorksheetFunction.Match(efpd, mtrnge, 0)
        x = Range("B" & a + 2).Value
        y = Range("C" & a + 2).Value
        itcinter = x + pwr * (y - x)
    'Only scenario left is an error
    Else
        itcinter = "error"
    End If
End If
End Function

[protonLeah]

You can put a breakpoint in UDF right after the last dim statement that will allow you to step through it. Just a few observations. You didn't provide a workbook with sample data so we can't step through your code. When you dim'ed your variables, you only put the data type at the end of the statement, VB requires that the type of each variable be specified, if not it defaults to variant. So the only single data type below is "b", the rest are variant. Is that what you intended?

Dim w, x, y, z, xx, yy, b As Single

You declared pwr as single precision but divide it by a double precision constant

[shg]

Cross-post: http://www.ozgrid.com/forum/showthread.php?t=79252

[kernalsend]

protonLeah, that is not what I meant with the data types. I've fixed, thanks a lot. So breakpoints will allow you to step through a function...I was wondering. I've found that the problem is when it goes to assign a range (rnge) which takes in the data from a different worksheet than where the function is being employeed. I've searched and searched but don't know what I'm doing wrong. I'm guessing functions have a rule I'm not aware of that subroutines don't?

I've attached a zipped file of a trimmed down version of my Excel file. Any help would be awesome. Also, below is my "updated" code:

Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge As Range, mtrnge As Range
Dim w As Single, x As Single, y As Single, z As Single, _
xx As Single, yy As Single, b As Single
Dim scenario As Integer, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100
If (scenario = 1) Then
    Worksheets("ITC").Select
    'Make table into a range for VLookUp
    Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
    Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
    'If the given burnup does not match a table value exactly
    On Error Resume Next
    J = Application.WorksheetFunction.VLookup(efpd, rnge, 1, False)
    If Err.Number = 1004 Then
        w = Application.WorksheetFunction.VLookup(efpd, rnge, 1, True)
        x = Application.WorksheetFunction.VLookup(efpd, rnge, 2, True)
        y = Application.WorksheetFunction.VLookup(efpd, rnge, 3, True)
        'If the given burnup is greater than, or equal to the largest table value
        If (w = Range("A3").End(xlDown).Value) Then
                itcinter = x + pwr * (y - x)
        Else
            'If the given burnup requires a table interpolation
            a = Application.WorksheetFunction.Match(efpd, mtrnge, 1)
            z = Range("A" & a + 3).Value
            xx = Range("B" & a + 3).Value
            yy = Range("C" & a + 3).Value
            b = (z - w)
            itcinter = (x / b) * (z - efpd) * (1 - pwr) + _
            (xx / b) * (efpd - w) * (1 - pwr) + _
            (y / b) * (z - efpd) * (pwr - 0) + _
            (yy / b) * (efpd - w) * (pwr - 0)
        End If
    'If the given burnup does match a table value exactly
    ElseIf (Application.WorksheetFunction.VLookup(efpd, rnge, 1, False) >= 0) Then
        a = Application.WorksheetFunction.Match(efpd, mtrnge, 0)
        x = Range("B" & a + 2).Value
        y = Range("C" & a + 2).Value
        itcinter = x + pwr * (y - x)
    'Only scenario left is an error
    Else
        itcinter = "error"
    End If
End If
End Function

[VBA Noob]

kernalsend,

Please read forum rules below. If you agree to them PM (private message) me and I will unlock this thread

Amended. Now unlocked

VBA Noob

[kernalsend]

Cross-post: http://www.ozgrid.com/forum/showthread.php?t=79252

I read the rules as OzGrid but being my first post, had trouble applying them all. I posted here and at OzGrid because when I posted at forums for motorcycle repair, I found that tapping into multiple resources produced many unique, and insightful responses. Also, being my first post, I wanted to see what forums had the fastest and best responses so all I'll need to do is post to one forum.

I never knew VBA forums were so stringent, but VBA Noob provided some insight.

[kernalsend]

I just tried the function on the same worksheet that has the range data (ITC) and it works fine. So, UDFs have a problem with how I brought in data from another worksheet to create a range for the solution. Any ideas how I can change this to make it work? These lines are causing the error:

    Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
    Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))