469,267 Members | 1,031 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Decimal Point Fixing

Hi,

I suppose this is easy enough if you know how but I don't have any help files
available.

I need to set an output to a text box to two decimal places is there some sort
of format command?

JD
Jul 17 '05 #1
3 18059
On Tue, 24 Jun 2003 16:31:04 +0100, "TheWizard"
<th********@I.HATE.SPAM.blueyonder.co.uk> wrote:
Hi,

I suppose this is easy enough if you know how but I don't have any help files
available.

I need to set an output to a text box to two decimal places is there some sort
of format command?


Format() - if you have the stomach for it

Personally I detest the VBA Format and override it with something I
first wrote in about 1987

This is *very* old code, but it has been working for years
- I try not to tinker with core stuff - unless necessary

- you will need to provide the obvious 'helper' functions

A$ = Format$( "###.##", "1.175" ) - Note: all three are strings
' ################################################## ######
'
'
'
Function Format(Fmt$, HoldP$) As String

' returns P$ formatted to "##,###.##" for example
'
' ~ - Dealers "100.00 " or "100.001 " or "100.00123"
' $F - French Format
' $T - Trimmed Numeric
' $J in both Format and print string suppresses decimals
' $JD as above but appends "." as last char in format
' $N - Truncates off decimals
' $0 - Right packs with 0
' $L - Left Justifies within field
' @ - Returns Blank Field if empty
' $A - Adjusts 0 decimals to 2 decimals if not a round number

' 12/11/87 JF
' 7/3/88 JF - @ , $F & $T added
' 5/4/88 JF - $N added - equivalent of FIX( A# )
' 4/5/88 JF - fixed -.00 to -0.00
' 21/12/90 JF - $0 added
' 7/2/91 JF - $L added
' 10/4/91 JF - $A re-instated (was first introduced 5/10/90)
' 24/11/91 JF - "0000-10" fixed to "-000010"
' 3/2/92 JF - Fix for bad chars ie %
' 22/5/92 - extended to #!& as well - also $JD added
' 27/3/00 JF - Fix for VB5 bad numbers
' 24/5/00 JF - Fix for error in last fix - Must do Abs
' 29//7/02 JF - Fix DPos was P in "-.1" to "-0.1" below

Dim P$, L%, Commas%, Dealers%, BlankNull%, FrenchFormat%, _
JapField%, TrimIt%, JapTrailDP%, FixIt%, PackZero%, _
LeftJust%, AdjustDec%, DP_Pos%, DP%, N#, Q#, i%, DPPos%, E%, _
PercentFlag%
P$ = HoldP$
Call StrReplaceAnyChr(P$, "%#!&", "x")
L = Len(Fmt$)
Commas = InStr(Fmt$, ",")
Dealers = InStr(Fmt$, "~")
BlankNull = InStr(Fmt$, "@")
PercentFlag = InStr(Fmt$, "%")

FrenchFormat = InStr(Fmt$, "$F") ' 9.999,00
TrimIt = InStr(Fmt$, "$T") ' Trimmed Numeric field
JapField = InStr(Fmt$, "$J")
If JapField Then _
JapTrailDP = InStr(Fmt$, "$JD")

FixIt = InStr(Fmt$, "$N")
PackZero = InStr(Fmt$, "$0")
LeftJust = InStr(Fmt$, "$L")
AdjustDec = InStr(Fmt$, "$A")

DP_Pos = InStr(Fmt$, ".")
DP = 0
If DP_Pos Then _
DP = L - DP_Pos

Call StrRemoveStr(P$, ",") ' Strip ','

If JapField Then _
If InStr(P$, "$J") Then _
DP = 0 ' Integer regardless

N# = Val(P$)

If AdjustDec Then _
If N# <> Int(N#) Then _
DP = 2
If FixIt Then
DP = 0 ' for test keys
N# = Fix(N#)
End If

Q# = 10# ^ DP ' round it first
N# = Int(Abs(N#) * Q# + 0.500001) / Q# * Sgn(N#) ' Must do Abs
If N# = 0 Then
If BlankNull Then
P$ = Space$(L)
GoTo QUIT
End If
End If

P$ = Str$(N#)
If DP = 0 Then _
GoTo NO_DECIMALS

' here we sort out leading / trailing part of the decimals

AGAIN:
DPPos = InStr(P$, ".")
If DPPos = 0 Then _
P$ = P$ + ".": _
GoTo AGAIN

If DPPos - 1 > 0 Then _
If Mid$(P$, DPPos - 1, 1) = " " Then _
Mid$(P$, DPPos - 1, 1) = "0"

If Mid$(P$, DPPos - 1, 1) = "-" Then
P$ = "-0" + Mid$(P$, DPPos) ' 29/7/02 JF DPos was P
DPPos = DPPos + 1
End If
P$ = Mid$(P$ + String$(DP, "0"), 1, DPPos + DP)
If Dealers Then
E = Len(P$)
While E > DPPos + 2 And Val(Mid$(P$, E, 1)) = 0
Mid$(P$, E, 1) = " "
E = E - 1
Wend
End If

NO_DECIMALS:
If JapTrailDP Then _
If InStr(P$, ".") = 0 Then _
P$ = P$ + "." ' 999999.
DP = InStr(P$, ".")
If DP = 0 Then ' was INSTR( P$+".", "." )
DP = Len(P$) + 1
End If

If Commas Then
i = DP - 4
While i > 0
If InStr("0123456789", Mid$(P$, i, 1)) Then _
P$ = Mid$(P$, 1, i) + "," + Mid$(P$, i + 1)
i = i - 3
Wend
End If

If FrenchFormat Then
DP = InStr(P$, ".")
Call StrReplaceStr(P$, ",", ".")
If DP Then _
Mid$(P$, DP, 1) = ","
End If
If Len(LTrim$(P$)) > L Then
P$ = String$(L, "!") ' E R R O R
Else
P$ = Right$(String$(L, " ") + P$, L)
End If

If TrimIt Then _
P$ = Trim$(P$)

If PackZero Then
Q = InStr(P$, "-")
Call StrReplaceStr(P$, " ", "0")
If Q > 1 Then
Mid$(P$, Q) = "0" ' 24/11/91 JF
Mid$(P$, 1) = "-"
End If
End If

If LeftJust Then _
LSet P$ = Trim$(P$)

If PercentFlag Then _
P$ = Mid$(P$, 2) + "%"

QUIT:
Format$ = P$
End Function
Jul 17 '05 #2
Use FormatNumber()

Mauro

"TheWizard" <th********@I.HATE.SPAM.blueyonder.co.uk> wrote in message
news:S3****************@news-binary.blueyonder.co.uk...
Hi,

I suppose this is easy enough if you know how but I don't have any help files available.

I need to set an output to a text box to two decimal places is there some sort of format command?

JD

Jul 17 '05 #3
Hemant Pathak
92 Expert
Simpy use Format$ function
const fNumber="###,##.00"
a=inputbox("Enter nay Number")
Text1.text=format(a,fnumber)
Jul 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by Batista, Facundo | last post: by
687 posts views Thread by cody | last post: by
18 posts views Thread by Kuljit | last post: by
13 posts views Thread by =?Utf-8?B?RXRoYW4gU3RyYXVzcw==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.