how to write this function

[yehong]

I want to set the decimal places of a given number based upon a given format. I had posted similar post earlier but this is slightly different.
Exp1:
Given Format=0,000.00
Given Number=1.2345
Expected Result=1.23
Exp2:
G Format=0,000.000%
G Number=1
Exp Result=1.000%
The decimals should not round. Like if G Format is 0,000.000
and Given Number is 1.2346, it should not Result as 1.235, but 1.234.

I want to check the number of decimal places only in a given Format string and then just set the number of decimals of the Given Number. I have tried something but it fails in some conditions. I am using VB6

 

[MattSTech]

Perhaps treating the number as a string using the Mid, Left, Right, or Instr functions...

Matt

 

[MichaelRed]

Most of what you are asking for (and I think the harder opart - Truncation of the Decimal Portion) is easily accomplished with a small Procedure:

Public Function basFrmat(ValIn As Double, Frmat As String) As Single

    'Purpose: Format the Value per the format w/o rounding
    'Michael Red    8/22/04
    'Format == 0,000.000
    'Value = 1.2345
    'REturn 1.234

    Dim Idx As Integer
    Dim Dec As Integer          'Number of Decimal Places Desired
    Dim Sep As Boolean          'Seperator Character Indicator
    Dim ValTmp As Double

   'Search For Seperator
    Idx = 1
    Sep = (InStr(Frmat, ",") <> 0)
    Dec = Len(Frmat) - InStr(Frmat, ".")
    
    ValTmp = CLng(ValIn * (10 ^ Dec))
    basFrmat = Format(ValTmp / (10 ^ Dec), Frmat)

End Function

For some opf the other options you imply (esp using percentage) please make kiberal use of the ubiquitous {F1} (aka HELP.



MichaelRed
mlred@verizon.net

 

[yehong]

Thanks Michael. Your funtion is working ok except one thing. It is also formating the part left to the dot.
Like 0,000.000 and 1.2346, should look result 1.2345, but the function is formatting it as 0,001.234.

 

[Rebis]

Just adjust the format varible. change it from 0,000.00 to 0.00.

hope this helps,

Rebis

 

[MichaelRed]

mahap some confusion or vatiance in versioning. In Ms. A 03 (aka XP) it returns w/o the leading zeros and seperator, although the "Format" string indicates that these should be included. If you review the ref help for format, the Zero returns either a value of the numeric placeholder "0" to fill out the required (character) spaces for the value, whilw the "#" character provides a numeral, but does not 'pad' the string w/ leading and trailing characters (Spaces, zeros, or other placeholders). If you replace the "0"'s in your argument, with "#", it may work correctly in your version.




MichaelRed
mlred@verizon.net

 

[rsinj]

Try this:

Public Function myTrunc(dNum As Double, iPrecision As Integer) As Double

Dim e As Excel.Application
Dim w As Workbook
Dim o As Worksheet

Dim d As Double

  Set e = New Excel.Application
  Set w = Excel.Workbooks.Add
  Set o = w.Sheets(1)
  
  o.Cells(1, 1) = "=trunc(" & CStr(dNum) & "," & CStr(iPrecision) & ")"
  
  myTrunc = o.Cells(1, 1)

  e.DisplayAlerts = False
  e.Quit
  
End Function

?myTrunc(3.14159,2)
3.14

?myTrunc(3.14159,3)
3.141

?myTrunc(3.14159,4)
3.1415

?myTrunc(3.14159,5)
3.14159