[RESOLVED] Problem formatting an array

[MrDudemeister]

I need to format every number in MyArray with a thousand separator. I want it to add ",".

Since the numbers in the array will be <= maximum value of a Long, which is 2,147,483,647.

What should the "???????" be replaced with? .

For i = LBound(MyArray) To UBound(MyArray)
Myarray(i) = Format(Myarray(i), ???????)
Next

[Siddharth Rout]

For thousand's separetor

Try This

For i = LBound(Myarray) To UBound(Myarray)
    Myarray(i) = Format(Myarray(i), "#,##0.00")
Next

[MrDudemeister]

Hmm, that code just adds ",00" at the end of the number. I need it to add "," between every thousand.

Like this: (1,000) (10,000) (100,000) (1,000,000).

[Siddharth Rout]

hmmm, let me check that for you....

I just tried it, it works fine Yes it does add the decimal. If you don't want that then simply remove it. for example

Msgbox Format(100000,"#,##")

will give you 100,000

[MrDudemeister]

Well then the problem seems to be with the listbox. You see I format the number just before I add them to a listbox. Maybe the listbox replace "," with a blank space somehow?

[Siddharth Rout]

Okay Try this

Sub Testit()
'
'Your Code
'
For i = LBound(Myarray) To UBound(Myarray)
    Myarray(i) = Format(Myarray(i), "#,##")
    MsgBox Myarray(i) '<===== Try this
Next
End Sub

Does it display the relevant numbers?

[MrDudemeister]

Nope, it still displays them with a blank space. Ex: (700 000 000)

[Siddharth Rout]

ok just try this single line of code...

Msgbox Format(100000,"#,##")

What does it give you?

[MrDudemeister]

Lol, still a blank space; this is getting weird .

[Code Doc]

Builld a form with a list box and try this:

Private Sub Form_Load()
or I = 1 To 1000
    List1.AddItem Format$(Int(Rnd * 2000000000), "#,###,###,###")
Next
End Sub

You should get 1000 random numbers all formatted with commas in the right position.

[MrDudemeister]

I don't. Instead of commas I get blank spaces. Anyone know why?

[Siddharth Rout]

Hi Code

I could be wrong but this won't work in his case (see post 6-9)

[Merri]

MsgBox FormatNumber$(&H7FFFFFFF, , , , vbTrue)

Note that results depend on locale settings: most VB functions are locale aware, thus you get results according to which country the computer is located at.


If you wish to force to have commas every X character, you have to do it by yourself.

Public Function Comma(ByVal Expression As String, Optional ByVal Spacing As Byte = 3) As String
    Dim lngA As Long, lngLen As Long
    If (Len(Expression) > Spacing) And (Spacing > 0) Then
        lngLen = (Len(Expression) \ Spacing) + Len(Expression) + ((Len(Expression) Mod Spacing) = 0)
        Comma = String$(lngLen, ",")
        lngLen = lngLen - Spacing + 1
        For lngA = 1 To Len(Expression) \ Spacing
            Mid$(Comma, lngLen, Spacing) = Mid$(Expression, Len(Expression) - (lngA * Spacing) + 1, Spacing)
            lngLen = lngLen - Spacing - 1
        Next lngA
        lngLen = lngLen + Spacing - 1
        If lngLen > 0 Then
            Mid$(Comma, 1, lngLen) = Left$(Expression, lngLen)
        End If
    Else
        Comma = Expression
    End If
End Function

MsgBox Comma(&H7FFFFFFF)

[MrDudemeister]

Thanks a lot for the help. It works with the code provided by: Merri.

[Siddharth Rout]

Merri you beat me to it

or Go to "control panel", and set the country "local configuration" to English (US).

[Code Doc]

Hi Code, I could be wrong but this won't work in his case (see post 6-9)

I saw those in somewhat disbelief. Merri has the solution--machine settings. I suspected that. So, after we build the spaces, we can force the commas back into the string. This is short and sweet:

Private Sub Form_Load()
Dim MyNum As String
For I = 1 To 1000
    MyNum = Format$(Int(Rnd * 2000000000), "# ### ### ###")
    MyNum = Replace(MyNum, " ", ",")
    If Left$(MyNum, 1) = "," Then MyNum = Mid$(MyNum, 2)
    List1.AddItem MyNum
Next
End Sub

[Merri]

Code Doc: sorry, that still isn't guaranteed to work. There are maybe around 200 different locale settings out there that can each be dramatically different from each other. How can you guarantee that it'll work unless you have investigated and ensured it'll work with each locale?

Of course most of the locale settings for numbers are quite similar, but there can still be some surprises.

Thus the only way you can guarantee something to work is to avoid using a locale function.


As for other sample, there are many ways to understand 11/11/2001 - it could be MM/DD/YYYY like in the US – or it could be DD/MM/YYYY like in some European countries. This is why one shouldn't use CDate() to convert strings to dates in some cases. Worst thing to do is to use hard coded string dates.