Storing dates in an array in Libreoffice Basic

Question

For the last few days I have been researching this without finding an answer. I have a list of transactions from which I filter a subset. For the subset I store the date and the amount in an array, to use in a XIRR calculatation.

The input column is tested with 'IsDate' and is found to be True. Furthermore, I use 'ReDim Preserve (0 to n) As Date' when storing the Dates. However, when testing the result, the array does not contain Dates but Doubles.

Please refer to the code below.

Function FundXIRRTotal(ByVal FundISIN As String, ByVal KiesDatum As Date) As Double

Dim wshT As Object
Dim wshS As Object
Dim wshP As Object
Dim wsh As Object
Dim LastRow As Long
Dim i As Long
Dim NumberOfTrans As Long
Dim Guess As Double
Dim arrValues() As Double
Dim arrDates() As Date
Dim oFA As Object

Set wshT = ThisComponent.Sheets.getByName("Transacties")
Set wshP = ThisComponent.Sheets.getByName("Portefeuille")
Set wshS = ThisComponent.Sheets.getByName("Instellingen")

'Find Last Used Row
wsh = wshT.createCursor
wsh.gotoEndOfUsedArea( False )
LastRow = wsh.getRangeAddress().EndRow

NumberOfTrans = 0

'Create Transaction Array
For i = 1 To LastRow
    If wshT.getCellByPosition(3, i).String = FundISIN And CDate(wshT.getCellByPosition(0, i).Value) <= KiesDatum Then

        ReDim Preserve arrValues(0 To NumberOfTrans) As Double
        ReDim Preserve arrDates(0 To NumberOfTrans) As Date

        arrDates(NumberOfTrans) = CDate(wshT.getCellByPosition(0, i).Value)

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundBuy").String Then
            arrValues(NumberOfTrans) = -CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundSell").String Then
            arrValues(NumberOfTrans) = CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundDeposit").String Then
            arrValues(NumberOfTrans) = CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundDivCash").String Then
            arrValues(NumberOfTrans) = CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundDivShares").String Then
            arrValues(NumberOfTrans) = CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundSplit").String Then
            arrValues(NumberOfTrans) = -CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundFee").String Then
            arrValues(NumberOfTrans) = -CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        If wshT.getCellByPosition(1, i).String = wshS.getCellRangeByName("FundWarUit").String Then
            arrValues(NumberOfTrans) = -CDbl(wshT.getCellByPosition(9, i).Value / wshT.getCellByPosition(10, i).Value)
        End If

        NumberOfTrans = NumberOfTrans + 1      

    End If
Next i

'Add current value to Array
'NumberOfTrans = NumberOfTrans + 1
ReDim Preserve arrValues(0 To NumberOfTrans) As Double
ReDim Preserve arrDates(0 To NumberOfTrans) As Date
arrValues(NumberOfTrans) = CDbl(Waarde(FundISIN, KiesDatum))
arrDates(NumberOfTrans) = CDate(KiesDatum)

If Portfolio(FundISIN, KiesDatum) = 0 Then
    FundXIRRTotal = 0
    Exit Function
End If

'Calculate XIRR
oFA = createUNOService("com.sun.star.sheet.FunctionAccess")
FundXIRRTotal = oFA.callFunction("XIRR", Array(arrValues()), Array(arrDates()))

End Function

I am aware of the issue with the Array function where the data type gets lost. I tried to overcome that by creating a new Array As Date, and copying from arrDates to the new one. But no luck, doubles again...

Any help would be appreciated.

Answer 1

The number is the date. The UNO FunctionAccess call will not accept the OpenOffice Basic Date type, and expects a number type instead.

Here is working example code:

Function FundXIRRTotal(ByVal FundISIN As String, ByVal KiesDatum As Date) As Variant
    Dim wshT As Object
    Dim i As Long
    Dim arrValues(0, 2) As Double
    Dim unoDates(0, 2) As Double
    Dim theDate As Double
    Dim oFA As Object
    Dim args(2) As Variant
    Dim result As Variant

    Set wshT = ThisComponent.Sheets.getByName("Transacties")
    For i = 0 To 2
        arrValues(0, i) = wshT.getCellByPosition(3, i).Value
        'The date value will be a number, for example 42020 for 2015-01-16.
        theDate = wshT.getCellByPosition(2, i).Value
        unoDates(0, i) = theDate
    Next i
    oFA = createUNOService("com.sun.star.sheet.FunctionAccess")
    args = Array(arrValues, unoDates)
    result = oFA.callFunction("XIRR", args)
    FundXIRRTotal = result
End Function

See also https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=67996.

By the way, I also tried using com.sun.star.util.Date but that did not seem to work.