double vlookup in excel vba

[ENGinTraining]

I attempting to search a table in excel for 2 variables. I already made a column containing both variables, formatted as "A|B".

The excel code would be =vlookup(state8s_p & "|" & state8s_s,'Table A-6E(P|s)'A6:H616!,5,False)

The above code works in a cell in excel, but I am trying to write it in Visual Basic, and in the cell it gives me a #Name? error. Any Idea why?

Code:

Function testdouble(state8s_p As Double, state8s_s As Double) As Double


Dim rA6E As Range
Dim orig_p As Double
Dim orig_p2 As Double
Dim dSideHi As Double
Dim dSideLo As Double
Dim dPresHi As Double
Dim dPresLo As Double


Set rA6E = Worksheets("Table A-6E(P|s)").Range("A6:H616")



orig_p = Application.VLookup(state8s_p + "|" + state8s_s, rA6E, 5, False)
testdouble = orig_p

End Function

There are a few extra variables, because this is only part of a larger program, but this is the part that is giving me an error.

 

[shg]

How about changing the name to something simple, like "Bob"?

 

[ENGinTraining]

that actually worked... why is that? I just had some one help me in person and they said they believed it was because after I inserted a new module i renamed it to the same name of my function in the module?

 

[shg]

You don't want to do that.

As I said before, you should pass all data the function needs to return a result. Otherwide, Excel sees no dependency. So the first few lines should look like this:

Function sup_state8_hs(state8s_p As Double, state8s_s As Double, [COLOR=red]rA6E As Range[/COLOR]) As Double
    Dim orig_p      As Double
    Dim orig_p2     As Double
    Dim dSideHi     As Double
    Dim dSideLo     As Double
    Dim dPresHi     As Double
    Dim dPresLo     As Double
    Dim Count       As Long
 
    dPresHi = state8s_s
    dPresLo = state8s_s
 
    On Error Resume Next
 
    orig_p = Application.VLookup(state8s_p & "|" & state8s_s, rA6E, 5, False)

 

[ENGinTraining]

ok its defiantly working now. Thank you for all the help. I guess I thought I was just changing a label of the module, not realizing it was the name and It mattered.