Convert Excel Formula to VBA function

amandazzler,

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?

Here is an tiny little example. I want the VB function to take the arguments "case" and "event" and return the date on which those intersect. There are many dates that will depend on the date returned in the formula, so I want these dates to cascade update when I enter in the missing or changed date.

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

I revised the UDF to handle an arbitrary number of range and criteria combinations (like COUNTIFS or SUMIFS functions). You can use it with worksheet formulas like:
=MatchMultiple(A:A,B:B,"Brett v Amanda")
=MatchMultiple(A:A,B:B,"Brett v Amanda",C:C,"Visit")
=MatchMultiple(A:A,B:B,"Brett v Amanda",C:C,"Visit",D:D,"Successful")

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

Select allOpen in new window

Match-Index-ExampleQ28364603.xlsm

Byundt,

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?

Harry Lee,
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

byundt,

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")

Select allOpen in new window

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

Select allOpen in new window

Harry Lee,
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

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.