Shorten excel formula by user defined function VBA

Question

My ultimate goal is to shorten this formula using a user definded function in VBA, with the following as my parameter A1:C10, A1:A10, E1, B1:B10, F1 (this will vary, this is just an pure example)

Excel formula:

=index(A1:C10,match(1,(A1:A10=E1)*(B1:B10=F1),0),G1)

Using user defined function, it gonna be something like this:

=pick(A1:C10, A1:A10, E1, B1:B10, F1, G1)

My VBA code is like this, however, it does not work. Please expert, help me. Many thanks.

Function pick(range As range, con1 As range, con1a As range, con2 As range, con2a As range, col As range) As Variant

pick = WorksheetFunction.Index(range, Match(1, (con1 = con1a) * (con2 = con2a), 0), col)

End Function

Answer 1

use the Evaluate method:

Function pick(range As range, con1 As range, con1a As range, con2 As range, con2a As range, col As range) As Variant

pick = Application.Caller.Parent.Evaluate("Index(" & range.Address(1,1,,1) & ",Match(1, (" & con1.Address(1,1,,1) & " = " & con1a.Address(1,1,,1) & ") * (" & con2.Address(1,1,,1) & " = " & con2a.Address(1,1,,1) & "), 0), " & col.Address(1,1,,1) & ")")

End Function