• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
1000 Seperator
#1
I have a program calculating large numbers that are hard to read. I want it to add the 1000 seperator or , to change...

1000 to 1,000

how do I go about doing this? I found Dataformat and changed it to Number and also checked Use 1000 seperator but it still does not work.

THanks
  Reply
#2
do you mean 100 1,000 10,000 only
or like All numbers?(i.e; 1,354 15,645,324)
  Reply
#3
All numbers
  Reply
#4
Here's my very own function I just wrote up. Supports numbers in the trillions Wink

Code:
Public Function FormatNumber(ByVal Number As Currency) As String
Dim loopi As Long
Dim TempStr As String

    FormatNumber = CStr(Number)
    
    If Len(FormatNumber) > 3 Then
        TempStr = Left$(FormatNumber, Len(FormatNumber) - ((Len(FormatNumber) \ 3) * 3))
        For loopi = Len(FormatNumber) \ 3 To 1 Step -1
            TempStr = TempStr & "," & Mid$(CStr(Number), Len(CStr(Number)) - ((loopi * 3) - 1), 3)
        Next
        FormatNumber = TempStr
        If Left$(FormatNumber, 1) = "," Then FormatNumber = Right$(FormatNumber, Len(FormatNumber) - 1)
    End If
  
End Function
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#5
Well I see Aaron is viewing this, so I'll post that I made a fix it just in case you used it and didn't know it has been fixed.
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#6
How would I go about using this? Right now I just have label captions showing the result of the formula (which equal big numbers)

Im guessing I would have to change it so the formulas are stored in variables and then have the caption = the variable or whatever.

But how do I go about using your function along with that?
  Reply
#7
Wait, what?

All you do is put your result in the FormatNumber function like: Label1.caption = FormatNumber(result)
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#8
lawl, I was only reading because I troll the forums. :p
  Reply
#9
Works good. Thanks.
  Reply
#10
I love making complicated looking code Big Grin
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#11
Code:
Private Sub Command1_Click()
Dim l As Long
    l = 123939201
    Debug.Print Format$(l, "###,###,###,###.00")
End Sub

That will produce: 123,939,201.00

Code:
Private Sub Command1_Click()
Dim l As Long
    l = 12
    Debug.Print Format$(l, "$###,###,###,###.00")
End Sub

Will produce: $12.00
  Reply
#12
Hey whenever you have a decimal place it throws it all off... can you tell me how to fix that.


GIAKEN Wrote:Here's my very own function I just wrote up. Supports numbers in the trillions Wink

Code:
Public Function FormatNumber(ByVal Number As Currency) As String
Dim loopi As Long
Dim TempStr As String

    FormatNumber = CStr(Number)
    
    If Len(FormatNumber) > 3 Then
        TempStr = Left$(FormatNumber, Len(FormatNumber) - ((Len(FormatNumber) \ 3) * 3))
        For loopi = Len(FormatNumber) \ 3 To 1 Step -1
            TempStr = TempStr & "," & Mid$(CStr(Number), Len(CStr(Number)) - ((loopi * 3) - 1), 3)
        Next
        FormatNumber = TempStr
        If Left$(FormatNumber, 1) = "," Then FormatNumber = Right$(FormatNumber, Len(FormatNumber) - 1)
    End If
  
End Function
  Reply
#13
Jacob Wrote:
Code:
Private Sub Command1_Click()
Dim l As Long
    l = 123939201
    Debug.Print Format$(l, "###,###,###,###.00")
End Sub

That will produce: 123,939,201.00

Code:
Private Sub Command1_Click()
Dim l As Long
    l = 12
    Debug.Print Format$(l, "$###,###,###,###.00")
End Sub

Will produce: $12.00
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#14
have it loop the string once looking for decimal points, then have "For loopi = Len(FormatNumber) \ 3" start at that new place instead.
  Reply
#15
Jacob's method is better, but eh...

Code:
Public Function FormatNumber(ByVal Number As Currency) As String
Dim LoopI As Long
Dim TempStr() As String

    TempStr = Split(CStr(Number), ".", , vbTextCompare)
    FormatNumber = TempStr(0)
    
    If Len(FormatNumber) > 3 Then
        TempStr(0) = Left$(FormatNumber, Len(FormatNumber) - ((Len(FormatNumber) \ 3) * 3))
        For LoopI = Len(FormatNumber) \ 3 To 1 Step -1
            TempStr(0) = TempStr(0) & "," & Mid$(FormatNumber, Len(FormatNumber) - ((LoopI * 3) - 1), 3)
        Next
        FormatNumber = TempStr(0)
        If Left$(FormatNumber, 1) = "," Then FormatNumber = Right$(FormatNumber, Len(FormatNumber) - 1)
    End If
    
    If UBound(TempStr) > 0 Then FormatNumber = FormatNumber & "." & TempStr(1)
    
End Function
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#16
The way Jacob posted is 5x faster than my own hand written one Tongue

So I adapted to it!

Code:
Public Function FormatNumber(ByVal Number As Currency) As String

    If InStr(1, Number, ".", vbTextCompare) Then
        FormatNumber = Format$(Number, "###,###,###,###,###.####")
    Else
        FormatNumber = Format$(Number, "###,###,###,###,###")
    End If
    
End Function
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply
#17
does that keep working for numbers larger than 15 digits? The max value of a uint64_t is 20 digits.
  Reply
#18
In VB6 the max is a currency type which is something like 900 trillion.

Not sure if a double is larger, never used it.
Nean Wrote:Yes harold. Give it to me.
[Image: aguserbar3.png]
[Image: banner.png]
  Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)