VB function returns #VALUE!

%3CLINGO-SUB%20id%3D%22lingo-sub-2265405%22%20slang%3D%22en-US%22%3EVB%20function%20returns%20%23VALUE!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2265405%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20VB%20function%20that%20will%26nbsp%3B%20search%20a%20table%20based%20on%20the%20passed%20parameters.%3C%2FP%3E%3CP%3EThe%20table%20has%204%20columns%20-%20the%20first%20three%20must%20match%20the%20passed%20parameters%20and%20then%20the%204th%20column%20value%20is%20returned.%3C%2FP%3E%3CP%3EI%20am%20able%20to%20search%20the%20table%20with%20a%20formula%20but%20not%20when%20I%20try%20to%20make%20that%20formula%20a%20function.%3C%2FP%3E%3CP%3EThe%20formula%20that%20works%20is%26nbsp%3BINDEX(tst_parm%5BVal%5D%2CMATCH(1%2C(tst_parm%5Bser%5D%3DH4)*(tst_parm%5BCL%5D%3DI4)*(tst_parm%5BParm%5D%3DJ4)%2C0))%3C%2FP%3E%3CP%3EAfter%20multiple%20attempts%20I%20put%20it%20in%20the%20function%20as%3A%3C%2FP%3E%3CP%3EFunction%20getparm(xser%2C%20ycl%2C%20zparm)%3CBR%20%2F%3E'%20the%20line%20below%20is%20the%20code%20I%20want%20to%20execute%20and%20should%20return%20a%20value%20in%20the%20Val%20column%20of%20the%20table%20tst_parm%3CBR%20%2F%3Egetparm%20%3D%20Application.Index(Range(%22tst_parm%5BVal%5D%22)%2C%20Application.Match(1%2C%20(Range(%22tst_parm%5Bser%5D%22)%20%3D%20xser)%20*%20(Range(%22tst_parm%5BCL%5D%22)%20%3D%20ycl)%20*%20(Range(%22tst_parm%5BParm%5D%22)%20%3D%20zparm)%2C%200))%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3CP%3EI've%20attached%20a%20small%20file%20that%20shows%20the%20results.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Andy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2265405%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2265437%22%20slang%3D%22en-US%22%3ERe%3A%20VB%20function%20returns%20%23VALUE!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2265437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1022418%22%20target%3D%22_blank%22%3E%40NJzangle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20is%20that%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(Range(%22tst_parm%5Bser%5D%22)%20%3D%20xser)%20*%20(Range(%22tst_parm%5BCL%5D%22)%20%3D%20ycl)%20*%20(Range(%22tst_parm%5BParm%5D%22)%20%3D%20zparm)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eis%20an%20array%20expression.%20Excel%20can%20process%20array%20expressions%2C%20but%20you%20cannot%20use%20it%20this%20way%20in%20VBA.%20You%20can%20use%20Evaluate%20to%20emulate%20the%20worksheet%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20getparm(xser%2C%20ycl%2C%20zparm)%0A%20%20%20%20getparm%20%3D%20Evaluate(%22%3DINDEX(tst_parm%5BVal%5D%2CMATCH(1%2C%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22(tst_parm%5Bser%5D%3D%22%22%22%20%26amp%3B%20xser%20%26amp%3B%20%22%22%22)*%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22(tst_parm%5BCL%5D%3D%22%22%22%20%26amp%3B%20ycl%20%26amp%3B%20%22%22%22)*%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22(tst_parm%5BParm%5D%3D%22%22%22%20%26amp%3B%20zparm%20%26amp%3B%20%22%22%22)%2C0))%22)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20I%20had%20to%20add%20quotes%20around%20the%20arguments%20because%20all%20three%20are%20text%20values.%20You%20don't%20have%20to%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%20Evaluate%20handles%20arrays%20automatically.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0280.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271688i10CF2967DBF839CB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0280.png%22%20alt%3D%22S0280.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

2 Replies

@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.

 

S0280.png

@Hans Vogelaar 

Thank you!! This will make my spreadsheet much easier to read and maintain. 

Andy