Need aid creating a User-defined FUNCTION to format a number

[WuAhUuU]

I need help creating a user-defined function for the simple purpose of formating numbers.

Example
I have the following number:
119.50
I want the function to format the number to the following form:
119-16 (16/32 is 0.5 in the decimal, so the base for all calculations will be 32.

Another example:
I have the following number:
119.171875
I want the function to format the number to the following form:
119-05 + (the plus just says that 5.5 should be divided by 32)

The reason I want this is that if the number reaches a certain decimal it just becomes to long and ugly on sight.

My basic train of thought was to separate the integer from the decimal. And then work my way through the decimal.
1st got the decimal and multiplied by 32 to get the whole number from the decimal in the form of 0.25-31.75 (if the decimal from the decimal leftover is 0.25 =1/4, 0.5 = +, 0.75 = 3/4). So a number such as 5.75 would look like this -05 3/4. After that I simply put together many CONCATENATE functions to join the whole number and the formatted decimal. Need help in creating such function.

I have a huge code, but I actually want to create a function to automatically format the number and use less memory on the sheet.

Here is the code but worthless I might add:

=CONCATENATE(INT(O23),"-",IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))," ",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.25,"1/4",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.5,"+",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.75,"3/4",""))))

 

[jasonb75]

Sorry for asking what may seem obvious to you, but please bear in mind that I'm not a financial professional so I do need to try and get the way things work clear.

My understanding of the required format is Integer, followed by decimal expressed as x/32, followed by any remainder expressed as 1/4's of 1/32, with 1/2 shown as + and no remainder left blank.

The error margin on my last effort could come from rounding, for example, what would the correct expression be for 119- 16 3/8? 119- 16+ or 119-16 1/4?

Should such values be rounded up, down, or to nearest 1/4?

If I can get this formula to give the correct result then turning it to a UDF will take only a couple of minutes.

 

[WuAhUuU]

Now I am having a problem with the red part of the funtion:

Public Function format32(number1 As Variant) As String
Dim format As Integer
Dim decimal2 As String
Dim decilam3 As String
 
format = 32
numberi = Int(number1) 'Integer of number1
decimal1 = number1 - numberi 'Decimal of number1
decimal2 = decimal1 * format 'Decimal converted to format
[COLOR=red]    Select Case decimal2
        Case Is < 10
            decimal2 = "0" & decimal2
        Case Else
            decimal2 = decimal2
    End Select[/COLOR]
[COLOR=darkgreen]'------[/COLOR]
[COLOR=darkgreen]'Problem I am having in the red part is simple. If decimal2 is below 10 [/COLOR]
[COLOR=darkgreen]'I want [/COLOR][COLOR=darkgreen]the macro to automatically add a 0 in front of the [/COLOR]
[COLOR=darkgreen]'number. Example:[/COLOR]
[COLOR=darkgreen]'Decimal2 is 3.5[/COLOR]
[COLOR=darkgreen]'It should read -03+[/COLOR]
[COLOR=darkgreen]'The + side is calculated in decimal3[/COLOR]
[COLOR=darkgreen]'The - part is a separator between the integer and the [/COLOR]
[COLOR=darkgreen]'FORMATTED decimal[/COLOR]
[COLOR=darkgreen]'------
[/COLOR]decimal3 = decimal2 - Int(decimal2) 'Decimal of decimal2
    Select Case decimal3
        Case 0.25
            decimal3 = " 1/4"
        Case 0.5
            decimal3 = " +"
        Case 0.75
            decimal3 = " 3/4"
        Case Else
           decimal3 = ""
    End Select
format32 = decimal2
End Function

This code is almost DONE... Need extra help on this baby... Thanx

 

[WuAhUuU]

Your code works fine. However, decimals in the 32s format below 10 should have a 0 preceeding them. Therefore,

119.109375 should actually be 119-03+ (added a 0 infront of the number).

If you look at my funtion, it is almost done. The last things I have to do is to figure out how to put the 0 before decimals formatted below 10, and concatenate

(numberi) (decimal2 working properly) and (decimal3)

Thank you

 

[jasonb75]

try replacing the entire red section with

decimal2 = Format(decimal2, "00")

Not sure that will work, it should, but I've been having trouble with the Format function on the code I was trying.

In the formula version the correction is far more simple,

=SUBSTITUTE(INT(B8)&"- "&TEXT(ROUND(MOD(B8,1)*128,0)/4,"00 ?/?"),"1/2","+")

 

[WuAhUuU]

This funtions works flawlessly except for the zero part:

Public Function format32(number1 As Variant) As String
Dim format As Integer
Dim Separator As String
Dim numberi As String
Dim decimal2 As String
Dim decimal3 As String
Dim decimal4 As String
format = 32
Separator = "-"

numberi = Int(number1) [COLOR=darkgreen]'Integer of number1
[/COLOR]decimal1 = number1 - numberi [COLOR=darkgreen]'Decimal of number1
[/COLOR]decimal2 = decimal1 * format [COLOR=darkgreen]'Decimal converted to format
[/COLOR][COLOR=red]    Select Case decimal2
        Case Is < 10
            decimal2 = "0" & decimal2
        Case Else
            decimal2 = decimal2
    End Select
[/COLOR][COLOR=darkgreen][B]'-----[/B][/COLOR]
[B][COLOR=#006400]'Again, the last problem I am having now is to put the 0 before[/COLOR][/B]
[B][COLOR=#006400]'a number that is less than 10, but the rest of the funtion works[/COLOR][/B]
[B][COLOR=#006400]'flawlessly![/COLOR][/B]
[COLOR=#006400][B]'-----[/B][/COLOR]
decimal3 = Int(decimal2) [COLOR=darkgreen]'Integer of decimal2
[/COLOR]decimal4 = decimal2 - decimal3 [COLOR=darkgreen]'Decimal of decimal2
[/COLOR]    Select Case decimal4
        Case 0.25
            decimal4 = " 1/4"
        Case 0.5
            decimal4 = " +"
        Case 0.75
            decimal4 = " 3/4"
        Case Else
           decimal4 = ""
    End Select
together = numberi + Separator + decimal3 + decimal4
format32 = together
End Function

 

[WuAhUuU]

try replacing the entire red section with

decimal2 = Format(decimal2, "00")

Not sure that will work, it should, but I've been having trouble with the Format function on the code I was trying.

In the formula version the correction is far more simple,

=SUBSTITUTE(INT(B8)&"- "&TEXT(ROUND(MOD(B8,1)*128,0)/4,"00 ?/?"),"1/2","+")

the decimal2 format funtion does work... just one more step and we are done!

thanx for the parallel discussion using a formula

 

[jasonb75]

It appears that something is causing the formatting to trip up, evaluating the formula version in the UDF (which should give identical results) is returning 119- 00 for all decimal values, however, this code appears to work.

Function format32(decVal As Variant) As String
format32 = Int(decVal) & "- " & _
Replace(WorksheetFunction.Text(Round((decVal - Int(decVal)) * 128, 0) / 4, "00 ?/?"), "1/2", "+")
End Function

If you prefer your version I'll see if I can get it working.

Always worth keeping a working formula in the discussion, even though you wanted a UDF, a simple, working formula can provide ideas that you might not have otherwise seen.

 

[WuAhUuU]

the decimal2 format funtion does work... just one more step and we are done!

thanx for the parallel discussion using a formula

Actually the formula worked... Just had to change the formula a bit

here is the end FUNTION:

Public Function format32(number1 As Variant) As String
Dim base As Integer
Dim Separator As String
Dim numberi As String
Dim decimal2 As String
Dim decimal3 As String
Dim decimal4 As String
 
base = 32
Separator = "-"
numberi = Int(number1) 'Integer of number1
decimal1 = number1 - numberi 'Decimal of number1
decimal2 = decimal1 * base 'Decimal converted to format
decimal3 = format(decimal2, "00") 'Format decimal2 to "00"
decimal4 = decimal2 - Int(decimal2) 'Decimal of decimal2
    Select Case decimal4
        Case 0.25
            decimal4 = " 1/4"
        Case 0.5
            decimal4 = " +"
        Case 0.75
            decimal4 = " 3/4"
        Case Else
           decimal4 = ""
    End Select
 
together = numberi + Separator + decimal3 + decimal4
format32 = together
End Function

HAD TO MAKE STRINGS OUT OF EVERY VARIABLE

AND WE ARE DONE.

THANK FOR ALL THE HELP IN BUILDING THIS FUNTION. SIMPLE BUT EFFICIENT FOR PRESENTATION PURPOSES!!

 

[jasonb75]

Glad you got there in the end, I had the solution that I offered in #17 over an hour ago, but it needed a correction that took until now to find. Something that works perfectly in a formula appears to fail in a UDF. I blame Microsoft for a defective product