2

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

0m3r
  • 12,286
  • 15
  • 35
  • 71
David R
  • 55
  • 7
  • 1
    Strange... the code you have provided works for me (Excel 2016, v.16.0.1.., 64-bit) . The three different versions following works for me: 1. `dates(1) = #1/15/2015#` 2. `dates(1) = DateSerial(2015, 1, 15)` 3. `dates(1) = CDate("2015-01-15")` – Wizhi Oct 25 '18 at 18:12

1 Answers1

0

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".

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    I like the idea of converting dates to integers, but I´m facing the same problem of getting VBA to understand the dates. How do I go from "15/01/2015" to "42019"? I have tried this, but won´t work: dates(1) = CLng("1/15/2015") – David R Oct 26 '18 at 08:06
  • @David R - at least you are narrowing down the question, eliminating the XIRR() from the picture. Can you try CLng("01/15/2015"). The settings seem strange. – Vityata Oct 26 '18 at 16:06
  • I solved it by 1) introducing the dates as Long instead of Dates. 2) Getting the dates from excel (through Range.Value) instead of typing them in the code – David R Oct 28 '18 at 11:59
  • @DavidR - congrats! :) You may even consider getting the dates as `Range.Value2` and parsing them to `Date()` immediately. In the general case it should work. The `Long` was just a proposed workaround to see the locate the real problem. – Vityata Oct 28 '18 at 12:55