hai pro,
    i am using XP OS. i want to show the number format like "12,34,56,789.00". my code is text1 = formatnumber("123456789",2,vbTrue,vbTrue,vbTrue). my OS number format digit grouping is - '12,34,56,789.00'. But the code returning like this "123,456,789.00". what i need to do? is there any mistake in code. can any one help me?

Hi,

The last parameter (GroupDig) needs to be set as default. This will give output based on delimiter specified in the computer's regional settings:

-2 = TristateUseDefault - Use the computer's regional settings
-1 = TristateTrue - True
0 = TristateFalse - False

Om Prakash

Hi prakash,
   My problem is TristateUseDefault is not working.
text1 = 1212123

Private Sub Command1_Click()
Text1 = FormatNumber(Text1, 2, vbTrue, vbTrue, vbUseDefault)
End Sub

result is = 1212123.00
    What mistake in my code. please guide me.

harish

It’s no help, but I’m curious: What is the meaning of that grouping for numbers? I have not seen this before.

Given the comments here, I suspect you will have to write a function yourself to do this. What you would do is
Convert the number to a string
Add ".00" to the end
Reverse the string.

Nex you will be reading a character at a time, then building a new string from what you find.

Add the first 6 characters to the new string ("00.321")
Add a comma
If there are two more characters, add them.
If there are more add a comma

Keep repeating that until you run out of characters.
Then reverse the new string and return it as the function’s value.
Then, starting with the first character

Private Function FormatNum(Num As String, vformat As String) As String
    Dim str As String, tmpstr As String, tmpStr2 As String, i As Long
    str = format(Num, vformat)

    If InStr(str, ".") > 0 Then
        tmpstr = Mid(str, 1, InStr(str, ".") - 1)
        str = Right(tmpstr, 3) & Mid(str, InStr(str, "."))
    Else
        tmpstr = str
        str = Right(tmpstr, 3)
    End If

    tmpstr = Replace(Left(tmpstr, Len(tmpstr) - 3), ",", "")
    While Len(tmpstr) > 2
        tmpStr2 = Right(tmpstr, 2) & "," & tmpStr2
        tmpstr = Left(tmpstr, Len(tmpstr) - 2)
    Wend
    tmpStr2 = tmpstr & "," & tmpStr2

    FormatNum = tmpStr2 & str
End Function