Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

user_13812982 0
Viewed 172

Excel "dates" are doubles under the hood, and the XLOPER structure does not seem to have any concept of date, only xltypeNum (unlike Variants in VBA).

I have a simple XLL with a function that takes no arguments returns today's date as an LPXLOPER (type "P"). I have other functions that usually return dates but could also return errors which is why I am using "P" as the return value not "B".

DLLEXPORT LPXLOPER WINAPI epToday()
{
    static XLOPER xResult;// Return value

    xResult.xltype = xltypeNum;
    xResult.val.num = ExcelDateForToday(); //A function that returns a double for today's date

    return &xResult;
}

The return value appears in the calling spreadsheet as a number (eg 40303). I would like to be able to tell Excel to treat my returned double as a date: in the same way that the built-in function TODAY() does. Is there a way of doing this?

I see that =TODAY() changes the cell NumberFormat to "Date", should I be using a callback to change the format for the calling cell? This seems to be what Excel is doing: eg if I re-calc a cell with a =TODAY() function it again sets the NumberFormat to Date.

xll c++
1 Answers

To my knowledge, following the TODAY() approach of setting the date format via a callback is the only way to signal to Excel that the value you're returning from an XLL function is a date. However, it does have a few drawbacks:

  1. Performance (for very large numbers of calls, although I haven't checked this)
  2. Requires special handling if the caller is not the worksheet
  3. Does the wrong thing for =TODAY() - AnotherDate, but so does Excel!
  4. Will override the format on every recalc - may annoy the user if another format is preferred

Excel is able to magically avoid (4) by detecting whether it is being called as part of the calc cycle or not - I'm not sure how to achieve this without seeing the Excel source code. You could wrap you function in something to set the format which would give the user the choice (or to undo your format change as the callback would be queued second), e.g. =SetFormat(MyToday(), "yyyy-mm-dd")

The callback is fairly straightforward in xlOil (disclaimer: I wrote it):

  XLO_FUNC_START(testToday())
  {
    CallerInfo caller;
    if (!caller.fullSheetName().empty()) // Check caller is a worksheet
      excelPost([=] 
      {
        excelApp().Range[caller.writeAddress().c_str()]->NumberFormat = L"dd-mm-yyyy"; 
      });
    std::tm buf; 
    auto now = std::time(0);
    localtime_s(&buf, &now); // Slightly labourious to get current date in C++
    return returnValue(buf);
  }
  XLO_FUNC_END(testToday);
user_132736470
Related