Forum Discussion
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...
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.
