Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I have this formula in my Excel sheet:

{=INDEX(B:B,MATCH(1,(C:C="caseName")*(F:F="eventName"),0),1)}

It's works beautifully. However, I would like to make it into a VBA function that takes arguments as it is used over and over again in this worksheet. I simply cannot figure out the syntax.

Here is the shell of the formula...and nothing I have entered in thus far has given any results.

Public Function findDate(caseName As String, eventName As String) As Date

End Function

{=INDEX(B:B,MATCH(1,(C:C="

It's works beautifully. However, I would like to make it into a VBA function that takes arguments as it is used over and over again in this worksheet. I simply cannot figure out the syntax.

Here is the shell of the formula...and nothing I have entered in thus far has given any results.

Public Function findDate(caseName As String, eventName As String) As Date

End Function

Not sure what formula are you using but your formula doesn't seem to make any sense at the first glace.

Can you upload a sample file with a working formula?

I can use the current formula, but I really just want to simply the work that I or someone else has to do. Let me know if this doesn't make sense.

Match-Index-Example.xlsx

Sample workbook containing user-defined function

Match-Index-ExampleQ28364603.xlsm

Match-Index-ExampleQ28364603.xlsm

=MatchMultiple(A:A,B:B,"Br

=MatchMultiple(A:A,B:B,"Br

=MatchMultiple(A:A,B:B,"Br

```
Public Function MatchMultiple(rgReturn As Range, ParamArray RangeAndCriteriaCombos() As Variant) As Variant
'Returns a value from rgReturn on first row that all satisfies all pairs of range and criteria. _
Function handles one or more pairs of range and criteria.
'RangeAndCriteriaCombos are alternating ranges and criteria, separated by commas in calling function. _
Each range is tested against its corresponding criteria.
Dim i As Long, j As Long, nCriteria As Long, n As Long
Dim bCriteria As Boolean
nCriteria = UBound(RangeAndCriteriaCombos)
If nCriteria Mod 2 <> 1 Then
MsgBox "RangeAndCriteriaCombos must be entered as pairs of ranges and criteria values, like SUMIFS function"
Exit Function
End If
n = rgReturn.Worksheet.UsedRange.Rows.Count + rgReturn.Worksheet.UsedRange.Row - 1 'Last row with data in rgreturn.Worksheet
For i = 1 To n
bCriteria = True
For j = 0 To nCriteria Step 2
If IsError(RangeAndCriteriaCombos(j)(i)) Then
bCriteria = False
Exit For
ElseIf RangeAndCriteriaCombos(j)(i) <> RangeAndCriteriaCombos(j + 1) Then
bCriteria = False
Exit For
End If
Next
If bCriteria = True Then
MatchMultiple = rgReturn.Cells(i).Value
Exit For
End If
Next
End Function
```

Match-Index-ExampleQ28364603.xlsm
Just want your opinion. If there are too many arguments in a custom function, isn't it easier to type in the original excel built in function?

I'm not understanding your question.

I don't like to use UDF where inputs are passed implicitly (by range references inside the UDF rather than as formal parameters in the function header line). UDF with implicit inputs won't recalculate if those inputs change, so even though they have fewer arguments, I feel they cause more problems for the user.

I feel a UDF should use as many arguments as are needed to pass all the inputs. I've run into the limit on arguments (60 in FORTRAN IV and 29 in Excel 2003) as a result. As a workaround, I arranged my inputs in a structured array, then passed the array.

I don't like writing VBA that duplicates a built-in function. But in this particular case, the Asker had an array-entered formula that probably took a fair while to calculate because it used every value in a million rows of data. By limiting the search to the used range, the UDF can be faster than the worksheet formula. And using the UDF in a regular formula (instead of something that must be array-entered) is also an advantage. In my own workbooks, I'd use the array formula. But I have certainly seen a number of people who seek to avoid the complexity ask for a UDF in Experts Exchange threads.

Brad

I see your point. Your way does works faster especially if the range keeps changing.

In Amanda's case, I think her data range does not change, and that's why she wants to simplify the input she needs. Basically what she wants is a simple UDF like

```
=FindDate("Brett v Amanda","Meeting")
```

to save time in putting in complicated formula.So, if you modify the code like below, it will work.

```
Public Function FindDate(Crit1 As Variant, Crit2 As Variant) As Variant
Dim rgReturn As Range, rg1 As Range, rg2 As Range, LastRW As Long
Dim v1 As Variant, v2 As Variant
Dim i As Long, n1 As Long
LastRW = Cells(Rows.Count, 1).End(xlUp).Row
Set rgReturn = Range("$A1:$A$" & LastRW)
Set rg1 = Range("$B1:$B" & LastRW)
Set rg2 = Range("$C1:$C" & LastRW)
n1 = rg1.Worksheet.UsedRange.Rows.Count + rg1.Worksheet.UsedRange.Row - 1 'Last row with data in rg1.Worksheet
v1 = Range(rg1.Cells(1, 1), rg1.Worksheet.Cells(n1, rg1.Column)).Value
v2 = Range(rg2.Cells(1, 1), rg2.Worksheet.Cells(n1, rg2.Column)).Value
For i = 1 To n1
If IsError(v1(i, 1)) Or IsError(v2(i, 1)) Then
ElseIf v1(i, 1) = Crit1 And v2(i, 1) = Crit2 Then
FindDate = rgReturn.Cells(i, 1).Value
Exit Function
End If
Next
End Function
```

In amandazzler's original question, the goal was to return a value from column B, while testing columns C and F.

In the sample workbook, the goal was to return a value from column A, while testing columns B and C.

While the worksheet formula would be simpler if you don't pass the three ranges, amandazzler would then be in a position of needing to create a new UDF each time she wanted to change one of those ranges.

As a minor quibble, if the simplified UDF recalculates when its worksheet isn't active, then the wrong results will be returned. I think you would agree that putting the range setting bits inside a With Worksheets("Sheet1") block would be a desirable tweak.

Brad

That's a very true. If the actual data structure and sample data structure is inconsistent, it's very hard for us to create proper vba code for them.

Still, I believe consistency is the problem rather than the method of putting together the VBA.

Both ways are correct and it really depends on prospective.

Maybe it's just me. If I have to put in to much argument in a UDF, I would rather type in the formula in this particular situation, since the original array formula is not that complicated; however, you have opened my eyes in new method in creating UDF for global / general use.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=Match2(A:A,B:B,"Brett v Amanda",C:C,"Visit")

As written, the code stops searching with the last row of data in the worksheet specified by range rg1. This greatly speeds up the UDF if you are passing entire columns as the input parameter.

Make sure that you install the function in a regular module sheet (use Insert...Module to create such a module sheet). Also, use the function wizard (fx icon to left of formula bar) the first time you use the function so it maintains the correct capitalization.

The function parameters are:

rgReturn return a value from this range

rg1 test this range against criteria Crit1

rg2 test this range against criteria Crit2

Open in new window