How to pass list of values to user defined function (UDF) in Excel

Question

---------------------------------------
A                   B       C   D
---------------------------------------
Timestamp           Stock   Qty Price
---------------------------------------
01 February 2011    ST1     100 10
02 March 2013       ST1     900 11
01 February 2011    ST2     200 121
04 May 2014         ST1     250 15
02 March 2016       ST2     150 130
04 May 2018         ST2     250 140
08 September 2016   ST1     300 19
01 February 2012    ST3     400 190
11 November 2017    ST1    -400 20

I am looking to create a function in excel by the name MyXIRR(Dates as Range, Values as Range, Balance as Double, BalanceAsOn Date)

To this function I need to pass data from Timestamp column as Dates, Qty X Price as Values for each Stock. Balance and BalanceAsOn is something I will determine separately.

For example for ST1

the Dates will be below

01 February 2011    
02 March 2013       
04 May 2014     
08 September 2016
11 November 2017

the values will be below

The problem I am facing is how to pass on list of values based on filter on Stock code.

I am not expert on INDEX or MATCH etc but when I tried something like

=MyXIRR(INDEX(A1:A9,MATCH("ST1",B1:B9,0)), INDEX(C1:C9*D1:D9,MATCH("ST1",B1:B9,0)), 100.00, TODAY())

OR use this as Array by pressing Ctrl + Shift + Enter i.e

{=MyXIRR(INDEX(A1:A9,MATCH("ST1",B1:B9,0)), INDEX(C1:C9*D1:D9,MATCH("ST1",B1:B9,0)), 100.00, TODAY())}

Then on debugging the MyXIRR I get just one value in Dates and one value in Values i.e. 01 February 2011 and 100. I expect the MyXIRR to get list not just first value.

Suspecting I am misusing INDEX I tried IF and SUMPRODUCT but I get #VALUE! Error for both of below two cases

=MyXIRR(IF(B1:B9="ST1",A1:A9,0), IF(B1:B9="ST1",C1:C9*D1:D9,0), 100.00, TODAY())

OR

= MyXIRR(SUMPRODUCT((B1:B9="ST1"),A1:A9), SUMPRODUCT((B1:B9="ST1"),C1:C9*D1:D9), 100.00, TODAY() )

Can someone suggest what wrong I am doing and/or suggest way forward?

I am using Excel for Mac version 16.16.2

Below is how my function looks like

Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double,  BalanceAsOn As Date) As Double

Dim Cell As Range
 For Each Cell In Dates
    MsgBox Cell.Value
 Next Cell

MyXIRR = Dates.Count
End Function

This just loops in only once and display 01/02/2011 when I call it for ST1

What value does the function actually return? In other words, what is the value of Dates.Count? — Excel Developers, Oct 02 '18 at 15:18
the function will have to return an array in order to be able to make it a list that is returned. you could use dictionary too, but that seems overkill — Scott Holtzman, Oct 02 '18 at 15:25
If you want an array as an input you will need to change the conditions: `Public Function MyXIRR(Dates As Variant, Trans As Variant,...` and INDEX/MATCH will always only return the first match, so go with the IF as an array formula. — Scott Craner, Oct 02 '18 at 15:27

Domenic · Answer 1 · 2018-10-03T17:38:34.987

1

[Note that the following solution has not been tested on a Mac version of Excel.]

To pass only the values that correspond to "ST1", first you'll need to declare Dates and Trans as a Variant, as Scott has already mentioned, and then change the body of your function accordingly...

Public Function MyXIRR(Dates As Variant, Trans As Variant, Balance As Double, BalanceAsOn As Date) As Double

     Dim currentDate As Variant

     For Each currentDate In Dates
         MsgBox currentDate
     Next currentDate

     MyXIRR = UBound(Dates)

End Function

Then you can use the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=MyXIRR(INDEX(A2:A10,N(IF(1,SMALL(IF(B2:B10="ST1",ROW(B2:B10)-ROW(B2)+1),ROW(INDIRECT("1:"&COUNTIF(B2:B10,"ST1"))))))),INDEX(C2:C10*D2:D10,N(IF(1,SMALL(IF(B2:B10="ST1",ROW(B2:B10)-ROW(B2)+1),ROW(INDIRECT("1:"&COUNTIF(B2:B10,"ST1"))))))),100,TODAY())

If done correctly, Excel will automatically place curly braces {...} around the formula.

edited Oct 03 '18 at 17:38

answered Oct 02 '18 at 17:10

Domenic

7,844
2
9
17

Your index(n()) can be simplified to simple IFs `IF(B2:B10="ST1",A2:A10)` and `IF(B2:B10="ST1",C2:C10*D2:D10)` – Scott Craner Oct 02 '18 at 17:14
@ScottCraner Yes, very true. In that case, though, it might be better to simply pass the entire range, along with the criteria, to MyXIRR and then deal with everything in the code. This way, you're passing a range instead of an array, and the formula won't need to be confirmed with CSE. And, hopefully, it should be more efficient. – Domenic Oct 02 '18 at 18:09
Personally, that is how I would do it, I would make the criteria and criteria range a new column. All good suggestions. Basically the error was trying to pass a range when an array is wanted. – Scott Craner Oct 02 '18 at 18:12
@ScottCraner Yeah, same here. I only offered my solution since it was my understanding that the OP only wanted to pass the relevant values to the function. – Domenic Oct 02 '18 at 18:28
I tried this solution but not working. I am getting #VALUE!. For now I will pass the entire range and search criteria in function and will calculate XIRR within VBA code. Will return to solving this Array issue sometime later. – Ishwar Jindal Oct 03 '18 at 16:12
Did you confirm the formula with CONTROL+SHIFT+ENTER, instead of just ENTER? – Domenic Oct 03 '18 at 17:28
I confirm the formula with CONTROL + SHIFT + ENTER. Also I wrote a simpler function just to test if I am able to pass array like below `code` Public Function MyTest(Dates As Variant) As Double Dim Cell As Range For Each Cell In Dates MsgBox Cell.Value Next Cell MyTest = Dates.Count End Function `code` – Ishwar Jindal Oct 04 '18 at 14:57
When I call it by passing simple range like =MyTest(A2:A10) (press ENTER), this function loops through all 9 values. When I enter a simple Array function like {=MyTest(C2:C9*D2:D9)} then I get #VALUE! error. It appear array is not working – Ishwar Jindal Oct 04 '18 at 15:21
In the first instance, you're passing a range object, whereas in the second instance you're passing an array, hence the error. The method in which the values are accessed in MyText will differ depending on the type of object you're passing to it. Did you try my edited version of MyXIRR? – Domenic Oct 04 '18 at 17:00

score 0 · Answer 2 · answered Oct 02 '18 at 15:21

Try this:

Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double, BalanceAsOn As Date) As Double

    Dim Area as Range    
    Dim Cell As Range

    For Each Area in Dates.Areas
        For Each Cell In Area
            MsgBox Cell.Value
        Next Cell
    Next Area

    MyXIRR = Dates.Count
End Function

How to pass list of values to user defined function (UDF) in Excel

2 Answers2