Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Excel Formula to VBA function

Posted on 2014-02-13
10
Medium Priority
?
2,642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857978
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
ID: 39858175
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 81

Accepted Solution

by:
byundt earned 1500 total points
ID: 39858248
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:byundt
ID: 39858254
Sample workbook containing user-defined function
Match-Index-ExampleQ28364603.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39858967
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39859684
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 81

Expert Comment

by:byundt
ID: 39860685
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
ID: 39865482
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 81

Expert Comment

by:byundt
ID: 39865562
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
ID: 39865650
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

688 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