Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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. -- |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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 -- |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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 "LurfysMa" wrote: 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 -- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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" wrote: 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 -- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote: 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. "LurfysMa" wrote: 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 -- -- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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. "LurfysMa" wrote: On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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. "LurfysMa" wrote: 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 -- -- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
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. -- Regards Roger Govier "LurfysMa" wrote in message ... On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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. "LurfysMa" wrote: 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 -- -- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
On Tue, 26 Jun 2007 16:10:01 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote: 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... -- |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom format macro?
On Wed, 27 Jun 2007 00:58:55 +0100, "Roger Govier"
wrote: 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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
custom format or conditional format? | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
custom format | Charts and Charting in Excel |