1000 Seperator

[halla]

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

[KruSuPhy]

do you mean 100 1,000 10,000 only
or like All numbers?(i.e; 1,354 15,645,324)

[halla]

All numbers

[GIAKEN]

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

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

[GIAKEN]

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.

[halla]

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?

[GIAKEN]

Wait, what?

All you do is put your result in the FormatNumber function like: Label1.caption = FormatNumber(result)

[Aaron]

lawl, I was only reading because I troll the forums. :p

[halla]

Works good. Thanks.

[GIAKEN]

I love making complicated looking code

[Jacob]

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

[halla]

Hey whenever you have a decimal place it throws it all off... can you tell me how to fix that.

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

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

[GIAKEN]

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

[GIAKEN]

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

[GIAKEN]

The way Jacob posted is 5x faster than my own hand written one

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

[GIAKEN]

In VB6 the max is a currency type which is something like 900 trillion.

Not sure if a double is larger, never used it.