Excel worksheetfunction DateDif

Add-in Express™ Support Service
That's what is more important than anything else

Excel worksheetfunction DateDif
How to use in a XLL addin 
Henri Pellemans


Guest


Hi at ADX,

I cannot figure out how to use Excel worksheetfunctions in a XLL addin.

How can I use e.g. DateDif (not DateDiff) in a XLL addin? 


Public Shared Function adxDateDif(ByVal startDate As DateTime,
                                  ByVal endDate As DateTime,
                                  ByVal period As String) As Integer
            '    DateDif = 351 (?)
            '    Excel Worksheetfunction DateDif
            Return ???.DateDif(startDate, endDate, period)
End Function


BTW I always have 'Option Strict' ON

Thanks for any help, and best regards,

Henri
Posted 03 Sep, 2016 12:42:45 Top
Henri Pellemans


Guest


I tried this one: 



Public Shared Function adxDateDif(ByVal startDate As DateTime,
                                  ByVal endDate As DateTime,
                                  ByVal period As String) As Integer
    Dim result As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Datedif, startDate, endDate, period)
    Return Convert.ToInt32(result)
End Function


There are no compiling errors. However the result in Excel is always '36'.
Posted 05 Sep, 2016 04:40:01 Top
Henri Pellemans


Guest


It looks that this one does the job: 


Public Shared Function adxDateDif2(ByVal startDate As DateTime,
                                   ByVal endDate As DateTime,
                                   ByVal period As String) As Integer
            Dim values(2) As Object
            values(0) = startDate
            values(1) = endDate
            values(2) = period
            Dim result As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Datedif, values)
            Return Convert.ToInt32(result)
End Function


My questions:

- why does adxDateDif in my previous message not work?
- is my function adxDateDif2 correct?
- can you add one or two examples to the ADX manual (in PDF). If you search for "ADXExcelWorksheetFunction" in the manual, then there are two results, from which I could not derive the above.

Best regards,

Henri

PS

The DateDif Excel function is not available in the ExcelApp object model. It is also not available in Excel sheets as intellisense nor in the Excel function wizard, but it can be used even though. From the website of Chip Pearson I copied the following:

"The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Do not confuse the DATEDIF worksheet function with the DateDiff VBA function."
Posted 05 Sep, 2016 05:07:31 Top
Marcus Datascout


Guest


Hi Henri,

Does it make a difference if you convert the DateTime parameters to Office Automation dates?

What you're passing are .Net DateTime references which are not the same as Excel date values (which are in essence floating point numbers under the bonnet).
So '05-Sep-2016' would be converted to '42618'.

Here's a reference:
http://stackoverflow.com/questions/16262007/datetime-tooadate-time-only

Cheers - Marcus
Posted 05 Sep, 2016 05:08:57 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Marcus,

Thank you very much! Using non-Automation dates may be the cause of the issue.

Hello Henri,

Henri Pellemans writes:
- can you add one or two examples to the ADX manual (in PDF). If you search for "ADXExcelWorksheetFunction" in the manual, then there are two results, from which I could not derive the above.


Thank you for the great suggestion! Did you try Marus' idea?


Andrei Smolin
Add-in Express Team Leader
Posted 05 Sep, 2016 09:20:58 Top
Henri Pellemans


Guest


Hi Andrei,

I did not try Marcus' idea yet. I had no time for that today. BTW Marcus, thank you for your suggestion.

But we had this discussion before. I have hundreds of XLL UDFs in which I retrieve dates from Excel cells using DateTime in the parameter definition of my UDF and they all work fine. Never ever had a problem with that!

The same is true when I write the (DateTime) result of an XLL UDF to a Excel cell, using DateTime as output of an UDF

Let me give you some easy to understand examples: 



Public Shared Function pensionDate(ByVal birthDate As DateTime,
                                   ByVal pensionAge As Integer,
                                   Optional ByVal firstDay As Boolean = False) As DateTime
            Dim myDate As DateTime = birthDate.AddYears(pensionAge)
            If firstDay Then myDate = firstDayThisMonth(myDate)
            Return myDate
End Function


The value of birthDate is stored in e.g. Excel cell A1, pensionAge in cell A2 and firstDay in cell A3. The result of the UDF goes in cell A4. As said I never had a problem with that. 


Public Shared Function firstDayThisMonth(ByVal thisDate As DateTime) As DateTime
            Dim myDate As New DateTime(thisDate.Year, thisDate.Month, 1)
            Return myDate
End Function


firstDayThisMonth is an Excel UDF as well as a helper function for the Excel UDF pensionDate.


These are pretty straightforward examples. Conversion to Double or DateTime.ToOADate and back to DateTime here would not be too timeconsuming. But I have some complicated UDFs in which I could easily go into an error if I had constantly to convert the dates.

Once the solution is found, as mentioned in my third posting, it is more easy to find out why the "solution" in the second posting is not working. If I am right the ADXExcelWorksheetFunction only accepts the (Excel) parameters as a parameter array, not the individual parameters.

Best regards,

Henri
Posted 05 Sep, 2016 11:07:10 Top
Henri Pellemans


Guest


I tried the suggestion of Marcus.

I have never used Excel WorksheetFunctions in my XLL UDFs. So this is my first trial. And "the rule of Andrei" is NEVER use the Excel object model in an XLL UDF. So we have to use ADXExcelWorksheetFunction instead.

It seems in my example there are two possibilities:

Either pass the parameters as an array
OR
Pass the parameters individually, but convert DateTime parameters to AODate

I prefer the array method. 


Public Shared Function adxDateDif1(ByVal startDate As DateTime,
                                   ByVal endDate As DateTime,
                                   ByVal period As String) As Integer
            Dim result1 As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Datedif, startDate, endDate, period)
            Dim result2 As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Datedif, startDate.ToOADate, endDate.ToOADate, period)
            'result1 = always 36 thus wrong
            'Return Convert.ToInt32(result1)
            'result2 seems to be correct
            Return Convert.ToInt32(result2)
End Function

Public Shared Function adxDateDif2(ByVal startDate As DateTime,
                                   ByVal endDate As DateTime,
                                   ByVal period As String) As Integer
            Dim values(2) As Object
            values(0) = startDate
            values(1) = endDate
            values(2) = period
            'result seems to be correct
            Dim result As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Datedif, values)
            Return Convert.ToInt32(result)
End Function


Have a nice evening, and best regards,

Henri
Posted 05 Sep, 2016 11:59:21 Top
Marcus Datascout


Guest


Hi Henri,

Glad to hear it worked out for you.

I personally prefer casting to AODate for two reasons:

1- I find it makes the code easier to read (for me at least).
I find EndDate more intuitive and informational than values(1).

2- By leaving the values as Object, it's up to the run-time to determine what the appropriate data type should be (remember that .Net is strongly typed) and then cast it on your behalf.
This adds computational overhead. Sure, this won't make a difference with a small number of function calls, but will be very noticeable in spreadsheets with 10s or 100s of thousands of formulae - trust me on this one ;)

All the best - Marcus
Posted 05 Sep, 2016 15:19:47 Top
Henri Pellemans


Guest


Hi Marcus, thank you for your remarks.

You wrote: "I find EndDate more intuitive and informational than values(1)."
I agree.

I ran some tests using an Excel sheet with 1,000,000 formulas. I measured the speed of calculation in milliseconds with "CalculateFullRebuild" as follows:

1 Open the Excel file
2 CalculateFullRebuild first time
3 CalculateFullRebuild second time
4 Save the Excel file
5 CalculateFullRebuild third time

The results were as follows:

Native Excel DateDif function = 1092 / 1076 / 1087 milliseconds
adxDateDif1 function = 9582 / 9447 / 9446 milliseconds (and yes, I deleted the result1 code)
adxDateDif2 function = 9585 / 9472 / 9470 milliseconds

I do believe that passing the variables untyped causes some overhead. Either this is nearly not measurable or it is compensated because the variables are passed as an array. I do not know.

Best regards,

Henri
Posted 06 Sep, 2016 02:08:23 Top