calling a function from aexcel vba subroutine

Posted on 2011-04-25
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

Question by:kalbano
    LVL 30

    Assisted Solution

    On a quick view. This doesn't seem right

    Set tmrtable = Range("Art6xTMRtable").value

    Change that to

    Set tmrtable = Range("Art6xTMRtable")

    and so on for the rest as you have defined tmrtable as a range.


    Author Comment

    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.
    LVL 30

    Accepted Solution

    That is because tmrtable is 'nothing'

    In Line 38 did you mean to say

        Case Is = "artiste"

    instead of

        Case Is = artiste

    and in 75

        Case Is = "Primus"

    instead of

        Case Is = Primus


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now