Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming, microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation, microsoft.public.office.developer.com.add_ins
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
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!). |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
UDF that returns a date
Pretty clever, thanks Rob.
|
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
UDF that returns a date
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date formula returns the 15th or the end of month | Excel Worksheet Functions | |||
Vlookup returns bad date | Excel Discussion (Misc queries) | |||
Need Help. Lookup or sum technic that returns a date. | Excel Discussion (Misc queries) | |||
Date function returns #NUM! | Excel Worksheet Functions | |||
Date+ returns #Num | Excel Worksheet Functions |