We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


calling a function from aexcel vba subroutine

kalbano asked
Medium Priority
Last Modified: 2012-05-11
I have function for a double linear interpolation of a table.  This function is in a module.  I am trying to write a subroutine that calls the function and tells the function what table to use as well as the size and the depth.  

I have had a variety of error messages depending on what variation I have tried in the code.  Definitely doesnt like my function call
Function Linearinter22dc(inputs As Range, x As Double, y As Double)
    Dim nx As Long, ny As Long
    Dim lowerx As Long, lowery As Long, upperx As Long, uppery As Long, i As Long
    nx = inputs.Rows.Count
    ny = inputs.Columns.Count
    If x < inputs(2, 1) Then
        lowerx = 2
        upperx = 2
    ElseIf x > inputs(nx, 1) Then
        lowerx = nx
        upperx = nx
        For i = 2 To nx
            If inputs(i, 1) >= x Then
                upperx = i
                lowerx = i - 1
                Exit For
            End If
    End If
    If y < inputs(1, 2) Then
        lowery = 2
        uppery = 2
    ElseIf y > inputs(1, ny) Then
        lowery = ny
        uppery = ny
        For i = 1 To ny
            If inputs(1, i) >= y Then
                uppery = i
                lowery = i - 1
                Exit For
            End If
    End If
    Dim XL As Double, XU As Double, YL As Double, YU As Double
    Dim temp1 As Double, temp2 As Double
    XL = inputs(lowerx, 1)
    XU = inputs(upperx, 1)
    YL = inputs(1, lowery)
    YU = inputs(1, uppery)
    temp1 = (inputs(lowerx, lowery) * (XU - x) _
    + inputs(upperx, lowery) * (x - XL)) / (XU - XL)
    temp2 = (inputs(lowerx, uppery) * (XU - x) _
    + inputs(upperx, uppery) * (x - XL)) / (XU - XL)
    Linearinter22d = (temp1 * (YU - y) + temp2 * (y - YL)) / (YU - YL)
End Function

Sub TMR()
Dim tmrtable As Range
Dim depth As Double
Dim eqblock As Double

depth = Sheets("MU").Range("B22").Value
eqblock = Sheets("MU").Range("B15").Value
Select Case Sheets("MU").Range("B7").Value

    Case Is = artiste
        Select Case Sheets("MU").Range("b12").Value
            Case Is = 6
                Set tmrtable = Range("Art6xTMRtable").value
            Case Is = 18
               Set tmrtable = Range("Art18xTMRtable").value
        End Select
    Case Is = Primus
        Select Case Sheets("MU").Range("B12").Value
            Case Is = 6
              Set tmrtable = Range("Pri6xTMRtable").value
            Case Is = 18
              Set tmrtable = Range("Pri18xTMRtable").value
        End Select
End Select

 Call Linearinter22dc(tmrtable, depth, eqblock)

Open in new window

Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)


I had already tried that and got run time error 91:  object variable or with block variable not set.  The error highlights on the nx = inputs.rows.count ine of the interpolation routine.
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.