XIRR function in VBA with dates in array

I've seen this with other Excel functions. XIRR() is an Excel function, and Excel only knows two data types -- double and string. It doesn't know VBA's Date data type, so it doesn't know what to do with it. Try storing your dates as an array of doubles. Where are the dates coming from? If they are coming from Excel, then it should be enough to simply read them into an appropriate array. If they are coming from VBA, you may need to loop through and use CDbl() to convert from Date to Double.

I was able to get the Max function working though by typing the dates as Long.

Excel usually has no trouble converting numeric data types (Long, Integer, Single) to its native Double. If your dates do not contain time information, then they, too, could be stored as Long.

Still not calculating the XIRR correctly. Returning a zero in the test case, should be returning .243.

You haven't shared anything about your test case, but a case that should return 24.3% but returns 0% (any chance it is 2.98E-9?) sounds like an example where Excel's implementation of XIRR() fails to converge. We've talked about the instabilities in Excel's XIRR() implementation before (and noted that other spreadsheets seem more stable). Example threads:

https://www.excelforum.com/excel-for...-rate-irr.html
https://www.excelforum.com/excel-gen...rr-return.html

Often, Excel's instability can be overcome by providing the optional "guess" parameter to the XIRR() function. I wonder what would happen (knowing that it should return 24.3%) if you tried:
Advance_xirr = Application.Xirr(Payment_amount, Payment_date,0.25)

At this point, I would suggest that debugging this needs to not only consider the data type issue but also needs to be aware of the possible instabilities in Excel's XIRR() function.

PS. Just thought of this -- with some of the other questions about data types that have come up, is there any chance that you have dimmed Advance_xirr as long or integer? I know it seems like a silly mistake, but I have seen enough users make the mistake, that it occurred to me to check.