Adding commas to numbers

[Frankie902]

How do I add commas to numbers so I can turn 1000 to 1,000 or 1000000 to 1,000,000.

[jbart]

Use the format function.

Private Sub Command1_Click()
    MsgBox Format(1000000, "###,###,###")
End Sub

[Frankie902]

What if its an unknown random number. Would that still work?

[jbart]

Do you mean the value is contained in a variable?

Option Explicit
Dim lngNumber As Long
 
Private Sub Command1_Click()
    lngNumber = 1000000
    MsgBox Format(lngNumber, "###,###,###")
End Sub

[Frankie902]

Lets say would ###,###,### work with 1,000 100,000 or 1,000,000,000?

[jbart]

Yes. Actually it looks like you could do the following and it will place commas in the number.

Private Sub Command1_Click()
    MsgBox Format(1000000000000#, "#,###")
End Sub

[Frankie902]

thanks

[Nucleus]

Matthew Gates' solution is preferable as it uses the group delimiter specified in the computer's regional settings, meaning it the code is portable to countries where the group delimiter is not a comma.