Solved

Convert Excel Formula to VBA function

Posted on 2014-02-13
10
2,084 Views
Last Modified: 2014-04-16
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
0
Comment
Question by:amandazzler
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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?
0
 

Author Comment

by:amandazzler
Comment Utility
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
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
I wrote a user-defined function with more parameters than you specified so you could adapt to differing worksheet layouts. You would use it with a worksheet formula like:
=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
Public Function Match2(rgReturn As Range, rg1 As Range, Crit1 As Variant, rg2 As Range, Crit2 As Variant) As Variant
Dim v1 As Variant, v2 As Variant
Dim i As Long, n1 As Long
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
        Match2 = rgReturn.Cells(i, 1).Value
        Exit Function
    End If
Next
End Function

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Sample workbook containing user-defined function
Match-Index-ExampleQ28364603.xlsm
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

Open in new window

Match-Index-ExampleQ28364603.xlsm
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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?
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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")

Open 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

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now