calling a function from aexcel vba subroutine

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

Question by:kalbano
  • 2
LVL 30

Assisted Solution

SiddharthRout earned 200 total points
ID: 35461812
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

ID: 35462389
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

SiddharthRout earned 200 total points
ID: 35462426
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


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

807 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