UDF that returns a date
 

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

UDF that returns a date
 

The UDF should return a number to the cell, which can be formatted as
a date to suit your requirements. The number is an integer measuring
the number of elapsed days, with 1st Jan 1900 = Day 1. Excel assumes
(incorrectly) that 1900 was a leap year, so you need to take this into
account. The serial value for today's date (17th Jan 2008) is 39464.

Hope this helps.

Pete

On Jan 17, 11:31*pm, ep wrote:
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

UDF that returns a date
 

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!).

UDF that returns a date
 

Pretty clever, thanks Rob.

UDF that returns a date
 

Also, any idea if there is native support (rather than catching events) for
this in office 2007, or will there be in future editions? Thanks again.

UDF that returns a date
 

Yn newyddion: ,
Roedd ep wedi ysgrifennu:

Also, any idea if there is native support (rather than catching
events) for this in office 2007, or will there be in future editions?
Thanks again.

Dunno mate. I'm not downgrading to 2007. I'll upgrade when they fix the
broken UI.

Rob