Custom format macro?

[LurfysMa]

Can I write a macro to format a number?

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

1 1 day
2.5 2.5 days or 2 days, 12 hours
0.125 3 hours
0.020833 30 minutes

and so on.

These values are calculated. I would like to display them as

nnn.n u

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). The macro will chose the largest units that
have at least 1 digit on the left of the decimal place. For example:

1 1.0D
2.5 2.5D
0.125 3.0H
0.020833 30.0M
730.5 1.5Y

I already have the macro code that I wrote for a VB project. My
problem is how to get Excel to use it to format the value in the cell.

I know I call pass the value to the macro and have it return a string
(text) result, but then that result is not available as a number in
other calculations. I'd like to keep the value in the cell as a number
and just apply the macro for format it.

--

 

[LurfysMa]

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

'************************************************************************
' Format Interval Function

' Formats an 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)

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

Dim result As String 'Intermediate value
Dim units As String 'Units (see above)

If interval >= TSYear Then
result = interval / TSYear
units = "Y"
ElseIf interval >= TSDay Then
result = interval
units = "D"
ElseIf interval >= TSHour Then
result = interval / TSHour
units = "H"
ElseIf interval >= TSMin Then
result = interval / TSMin
units = "M"
Else
result = interval / TSSec
units = "S"
End If

FmtInt = Format(result, "0.0") & units

End Function


--

 

[JLatham]

The problem is in showing the units as part of the result. Excel sees
something like "30.0M" in a cell and doesn't think it's numeric.
You can kind of get around that in a cell formula by using this to get rid
of the units indicator (assumes 30.0M in A1, and formula in B1, or any other
cell)
=Value(Left(A1,Len(A1)-1)
which will show just 30 in the cell with the formula.

Your next problem is to figure out what the resulting value is! Is 30 now
referring to Seconds, Minutes, Hours, Days or Years. You may be best off
writing yet another user defined function to take the displayed entry in such
a cell and convert it to whatever units you may need for any math operations
to be performed. It might look something like the code below, and would be
called (using example with the 30.0M in A1 above) as =ReverseFmtInt(A1)

But I see a loss of accuracy in going back and forth with these functions.
An entry of 370 results in FmtInt returning 1.0Y, but a conversion using
=ReverseFmtInt("1.0Y") would return 365.25 meaning that 4 and 3/4 days got
lost in translation.

Public Function ReverseFmtInt(someTime As String) As Double
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

Dim timeValue As Double

If Len(someTime) > 1 Then
timeValue = Val(Left(someTime, Len(someTime) - 1))

Select Case Right(someTime, 1)
Case "Y"
ReverseFmtInt = timeValue * TSYear
Case "D"
ReverseFmtInt = timeValue * TSDay
Case "H"
ReverseFmtInt = timeValue * TSHour
Case "M"
ReverseFmtInt = timeValue * TSMin
Case "S"
ReverseFmtInt = timeValue * TSSec
Case Else
ReverseFmtInt = 0 ' can't parse input
End Select
Else
ReverseFmtInt = 0 ' can't parse input
End If
End Function

 

[JLatham]

I have been thinking more about this and I suspect that the whole basis of
your request is to get away from that loss of precision that I mentioned
earlier. Correct?

One way to do that would be to use two cells to display the result of your
function: have it place the numeric result into the cell with the formula in
it, formatted to display as 0.0, and then put the type time indicator into a
cell one column over on the same row. I don't know if this is an acceptable
solution or not.

You'd need to modify your function FmtInt() to accept 3 parameters:
Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long)
as Double

You'd call it from a sheet as
= FmtInt(X5,Row(),Column())
while the X5 would be used same as you do now, the Row(),Column() parameters
are always entered in that fashion. That way they always provide the row and
column values of the cell with the formula(s) in it(them).

Later your code would end with

Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
Cells(anyRow, anyColumn).NumberFormat = "0.0"
FmtInt = result
End Function

I haven't tested this yet, but seems to me it should work.

 

[LurfysMa]

I have been thinking more about this and I suspect that the whole basis of
your request is to get away from that loss of precision that I mentioned
earlier. Correct?

It's not the basis of the request, but it is a requirement.

The real point is not to change the underlying data at all -- just
format it for printing. It's like if I put "39258" in a cell. If I
select the "General" format, I will see "39258". If I select the
Number format with 2 decimal places, I will see "39258.00". If I
select the Currency format, I will see "$39,258.00". And.... if I
select the Date format, I'll see "6/25". But no matter what format I
use, the contents of the cell remains "39258".

That's what I want here, except that I want Excel to run my function
and use the value it returns as the display value. I don't want to
change the underlying data at all.

I can't believe that Excel doesn't support this type of custom
formats. t would be trivial to implement.

One way to do that would be to use two cells to display the result of your
function: have it place the numeric result into the cell with the formula in
it, formatted to display as 0.0, and then put the type time indicator into a
cell one column over on the same row. I don't know if this is an acceptable
solution or not.

There are a lot of work-arounds.

You'd need to modify your function FmtInt() to accept 3 parameters:
Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long)
as Double

You'd call it from a sheet as
= FmtInt(X5,Row(),Column())
while the X5 would be used same as you do now, the Row(),Column() parameters
are always entered in that fashion. That way they always provide the row and
column values of the cell with the formula(s) in it(them).

Later your code would end with

Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
Cells(anyRow, anyColumn).NumberFormat = "0.0"
FmtInt = result
End Function

I haven't tested this yet, but seems to me it should work.

--

 

[JLatham]

Sorry, I didn't make the rules. <g>

Notice what you've explained here - referring to a value that starts out as
just a number and then choosing from any of a variety of formats that each
deals with the presentation of the appearance of that numeric value. When
you tack an "M" or "D" or other letter to the end of it, it's no longer a
number - it becomes text.

Perhaps someone who knows more formatting trickery than I do will come to
your rescue.

 

[Roger Govier]

Hi

Surely you would be better off setting the format within your function,
rather than appending a letter and leaving it till the end of the
function.
e.g.
If interval >= TSYear Then
result = format(interval / TSYear,"dd/mm/yyyy")

etc.

 

[LurfysMa]

Sorry, I didn't make the rules. <g>

I'm not sure anyone did.

Notice what you've explained here - referring to a value that starts out as
just a number and then choosing from any of a variety of formats that each
deals with the presentation of the appearance of that numeric value.

Yes, the presentation only, not the data itself.

When
you tack an "M" or "D" or other letter to the end of it, it's no longer a
number - it becomes text.

Well, yes and no. The displayed value is text, but so is "10:12:45" or
"June 6" or "$12,000.00". Those are all built-in formats. They do not
change the data itself. They just format it.

I want to be able to format the data using my own macro and then give
that value to Excel to display, while leaving the underlying data
alone.

I was hoping to be able to put something like "=MyFmt()" in the Custom
format field and have Excel call my function, pass it the cell data,
and use the results to display. Simple and powerful. I tried it and,
of course, it doesn't work.

I was hoping that there might be some hook or add-in to make that
work.

Perhaps someone who knows more formatting trickery than I do will come to
your rescue.

I'm listening...

--

 

[LurfysMa]

Hi

Surely you would be better off setting the format within your function,
rather than appending a letter and leaving it till the end of the
function.

Huh? How so?

e.g.
If interval >= TSYear Then
result = format(interval / TSYear,"dd/mm/yyyy")

The first part of that line is from an earlier version of the
function, which, I think, didn't work. The format pattern
("mm/dd/yyyy") will not do what I want. My datye values are
"intervals" (date2 - date1), not absolute dates. I will never want a
mm/dd/yy value.

Here's the latest version of the function:

Public Function FmtInt(ByVal interval As Double) As String

Const TSWeek As Double = 7 ' 1 week in days
Const TSDay As Double = 1 ' 1 day in days
Const TSHour As Double = TSDay / 24 ' 1 hour in days
Const TSMin As Double = TSHour / 60 ' 1 minute in days
Const TSSec As Double = TSMin / 60 ' 1 second in days
' 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 / TSSec, FmtPat) < 60 Then
FmtInt = Format(interval / TSSec, FmtPat) & "S"
ElseIf Format(interval / TSMin, FmtPat) < 60 Then
FmtInt = Format(interval / TSMin, FmtPat) & "M"
ElseIf Format(interval / TSHour, FmtPat) < 24 Then
FmtInt = Format(interval / TSHour, FmtPat) & "H"
ElseIf Format(interval, FmtPat) < 7 Then
FmtInt = Format(interval, FmtPat) & "D"
Else
FmtInt = Format(interval / TSWeek, FmtPat) & "W"
End If

End Function



--