Roedd ep wedi ysgrifennu:

Hi all,

I have a c# udf accessed as an automation add-in that needs to return
dates to Excel. I know I can't manipulate the cell's NumberFormat
from within the UDF, but is there a way to return a value that will
be formatted in Excel as a date? It seems any string value I return
will default to general. Let me know if any other info would help.

thanks

Hi,

Here's a reply of mine (including original typos) to a similar question on a
different list. The proposed workaround is in native VBA, but I'm sure you
can translate into C# with a it's fun an games with delegates.

Rob

================================================== =================
This idea comes up from time to time and its origins are in the way that
excel responds to the user entering a worksheet function such as NOW(). Try
it. Excel not only returns the current date and time, but also reformats the
cell. This leads some people to think that a) the formattring is being done
by the function and b) it might be possible to do something similar with a
UDF. In fact, the function is not doing the formatting. Excel is registering
that the function has been entered and is responding afterwards by
'helpfully' changing the number format. An internal list, to which we have
no access, is maintained of the functions which Excel thinks might benefit
from such reformatting.

Maybe this can be done with a global sheet change event hook. We need to
check if any cell changing was unformatted ("General") before we entered our
UDF. If it is, we change the formatting to however we want it. If the UDF is
in an add-in, the whole thing can be encapsulated within the add-in
workbook.

Say we have a function called MyNow in a regular module in an Add-In:

Option Explicit

Function MyNow()
Application.Volatile
MyNow = Now()
End Function

Now (ahem!) we put the following event code in the add-in's Thisworkbook
module:

Option Explicit

Private WithEvents oApp As Excel.Application

Private Sub Workbook_Open()
Set oApp = Application
End Sub

Private Sub oApp_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.NumberFormat = "General" And _
UCase(Target.Formula) Like "=MYNOW(*)" Then _
Target.NumberFormat = "mm:ss"
End Sub

This should do what we want subject to further testing (which I'm not going
to do since I don't have any use for this!).