Is there any way to get Excel (2007) to call a custom macro to format
the (numeric) contents of a cell but leave the cell contents as a
number that can be used in calculations in other cells?



I have a bunch of cells containing time intervals stored as floating
point numbers in units of days. These are not dates, but they are
stored in the same format. Some examples:

1 1 day
2.5 2.5 days or 2 days, 12 hours
0.9 .9 days or 21.6 hours
3/24 3 hours
0.125 3 hours
1/24 1 hour
0.0416667 1 hour
30/60/24 30 minutes
0.020833 30 minutes

and so on.

These values are calculated using a function I wrote:

=MyFun(C15,B11,c)

They can range from 1 second (1/60/60/24 - .000011574) to several
years (5*365.25 = 1,826.25). I need the raw numners for calculations,
but I would like to see the nearest units displayed, something like:

nnn.nu

where "nnn.n" is a floating point number formatted to 1 decimal place
and "u" is a character indicating the units (Y=years, D=days, H=hours,
M=minutes, S=seconds).

I have a macro (attached below) from a VB6 application that does this
formatting. The macro will chose the largest units that have at least
1 digit on the left of the decimal place. For example:

Raw Number Displayed As
1 1.0D
2.5 2.5D
0.9 21.6H
3/24 3.0H
0.125 3.0H
1/24 1.0H
0.0416667 1.0H
30/60/24 30.0M
0.020833 30.0M
12.3/60/60/24 12.3S
730.5 1.5Y

I have copied this macro over to Excel. It works if I call it from a
cell, such as:

=FmtInt(MyFun(C5,B11,c))

This works perfectly, but the result in the cell is a string (text)
and I cannot do arithmetic with it.

Is there some way that I can get Excel to call my macro to do the
formatting and display the formatted result (like using one of the
custom patterns), but leave the cell contents as is?





In case anyone is interested, here's the formatting macro:



'************************************************* ***********************
' Format Interval Function
'
' Formats a time interval as nnn.nu
'
' nnn.n = interval to 1 decimal place
' u = units: Y=years, D=days, H=hours, M=minutes, S=seconds
'
' Syntax: y = FmtInt(interval)
'
' Note: The format function

Public Function FmtInt(ByVal interval As Double) As String

Const TSYear As Double = 365.25 ' 1 year
Const TSDay As Double = 1 ' 1 day
Const TSHour As Double = TSDay / 24 ' 1 hour
Const TSMin As Double = TSHour / 60 ' 1 minute
Const TSSec As Double = TSMin / 60 ' 1 second
' Note: if a variable number of decimal places are needed,
' this constant must be generated dynamically
Const FmtPat As String = "0.0" ' The format function pattern

If Format(interval / TSYear, FmtPat) = TSYear Then 'If = 1 year,
FmtInt = Format(interval / TSYear, FmtPat) & "Y" 'Format in
years
ElseIf Format(interval, FmtPat) = TSDay Then 'If = 1 day,
FmtInt = Format(interval, FmtPat) & "D" 'Format in
days
ElseIf Format(interval / TSHour, FmtPat) = TSHour Then 'If = 1 hour,
FmtInt = Format(interval / TSHour, FmtPat) & "H" 'Format in
hours
ElseIf Format(interval / TSMin, FmtPat) = TSMin Then 'If - 1
minute
FmtInt = Format(interval / TSMin, FmtPat) & "M" 'Format in
minutes
Else 'Otherwise,
FmtInt = Format(interval / TSSec, FmtPat) & "S" 'Format in
seconds
End If

End Function


--