Forum Discussion

NJzangle's avatar
NJzangle
Copper Contributor
Apr 10, 2021

VB function returns #VALUE!

I am trying to create a VB function that will  search a table based on the passed parameters.

The table has 4 columns - the first three must match the passed parameters and then the 4th column value is returned. This is my first attempt at using VB / functions. 

I am able to search the table with a formula but not when I try to make that formula a function.

The formula that works is INDEX(tst_parm[Val],MATCH(1,(tst_parm[ser]=H4)*(tst_parm[CL]=I4)*(tst_parm[Parm]=J4),0))

After multiple attempts I put it in the function as:

Function getparm(xser, ycl, zparm)
' the line below is the code I want to execute and should return a value in the Val column of the table tst_parm
getparm = Application.Index(Range("tst_parm[Val]"), Application.Match(1, (Range("tst_parm[ser]") = xser) * (Range("tst_parm[CL]") = ycl) * (Range("tst_parm[Parm]") = zparm), 0))

End Function

I've attached a small file that shows the results. 

Thanks Andy

Macros and VBA

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 10, 2021

    NJzangle 

    The problem is that

     

    (Range("tst_parm[ser]") = xser) * (Range("tst_parm[CL]") = ycl) * (Range("tst_parm[Parm]") = zparm)

     

    is an array expression. Excel can process array expressions, but you cannot use it this way in VBA. You can use Evaluate to emulate the worksheet function:

     

    Function getparm(xser, ycl, zparm)
    getparm = Evaluate("=INDEX(tst_parm[Val],MATCH(1," & _
        "(tst_parm[ser]=""" & xser & """)*" & _
        "(tst_parm[CL]=""" & ycl & """)*" & _
        "(tst_parm[Parm]=""" & zparm & """),0))")
End Function

     

    Note that I had to add quotes around the arguments because all three are text values. You don't have to confirm the formula with Ctrl+Shift+Enter. Evaluate handles arrays automatically.

     

     

Resources