Apr 10 2021 07:22 AM - edited Apr 10 2021 07:23 AM
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
Apr 10 2021 07:55 AM
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.
Apr 10 2021 08:52 AM