[SOLVED] Dates in dd/mm/yyyy format return 1004 Error in Excel Vba

Dates in dd/mm/yyyy format return 1004 Error in Excel Vba

I have this code, and it works:

Sub try3()
Dim dates(1 To 2) As Date
Dim values(1 To 2) As Double
Dim TIR As Double

dates(1) = #1/1/2015#
dates(2) = #1/1/2016#

values(1) = -1000
values(2) = 1101

TIR = Application.WorksheetFunction.xirr(values, dates)

End Sub

However, if I change the dates, for example, to

dates(1) = #1/15/2015#
dates(2) = #1/15/2016#

, then I get an error 1004:

"Property Xirr of class WorksheetFuntion could not be obtained".

My computer date format is european (dd/mm/yyyy). If I change it to american (mm/dd/yyyy), then my code works.

I would like to keep the european format in my system, and I would like my code to be runnable from any computer, regardless of their system's date format

I have tried to define the dates as follows, but I get the same error.

dates(1) = DateSerial(2015, 1, 15)
dates(2) = DateSerial(2016, 1, 15)

Any idea how to make VBA understand dates while keeping the european format in my computer?

Thank you

Solution

The problem is not in the XIRR(), it is doing its best:

Sub TestMe()

    Dim TIR As Double
    TIR = Application.WorksheetFunction.Xirr(Array(-1000, 1101), Array(42019, 42384))
    Debug.Print TIR

End Sub

Just try to cast to Long with CLng, whichever date you are working with, it should return 42019 and 42384 in Excel, if you are not using the Date1904 "feature".