?
Solved

calling a function from aexcel vba subroutine

Posted on 2011-04-25
3
Medium Priority
?
381 Views
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
    Else
        For i = 2 To nx
            If inputs(i, 1) >= x Then
                upperx = i
                lowerx = i - 1
             
                Exit For
            End If
        Next
    End If
    
     
    If y < inputs(1, 2) Then
        lowery = 2
        uppery = 2
    ElseIf y > inputs(1, ny) Then
        lowery = ny
        uppery = ny
    Else
        For i = 1 To ny
            If inputs(1, i) >= y Then
                uppery = i
                lowery = i - 1
                Exit For
            End If
        Next
    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

0
Comment
Question by:kalbano
  • 2
3 Comments
 
LVL 30

Assisted Solution

by:SiddharthRout
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.

Sid
0
 

Author Comment

by:kalbano
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.
0
 
LVL 30

Accepted Solution

by:
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

Sid
0

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