本人刚写的模块,给大家共享。
Public Function List_Separator(Mystr As String) As String
If IsNumeric(Mystr) Then
Dim Snumber As String '//存放小数的值
If Len(Trim(Mystr)) = Len(Trim(Str(Int(Val(Mystr))))) Then
Snumber = "00"
Else
Snumber = Str(Right(Mystr, Len(Mystr) - Len(Str(Int(Val(Mystr))))))
End If
'///
Mystr = Trim(Str(Int(Val(Mystr))))
Dim AMystring As String
Dim BMystring As String
For i = 3 To Len(Mystr) Step 3
AMystring = Mid(Mystr, Len(Mystr) - i + 1, 3)
If i >= Len(Mystr) Then
BMystring = AMystring & BMystring
Else
BMystring = "," & AMystring & BMystring
End If
Next i
If Len(Mystr) Mod 3 = 1 Then
BMystring = Left(Mystr, 1) & BMystring
ElseIf Len(Mystr) Mod 3 = 2 Then
BMystring = Left(Mystr, 2) & BMystring
End If
List_Separator = BMystring & "." & Trim(Snumber)
Else
List_Separator = "Error"
End If
End Function
Public Function List_Separator(Mystr As String) As String
If IsNumeric(Mystr) Then
Dim Snumber As String '//存放小数的值
If Len(Trim(Mystr)) = Len(Trim(Str(Int(Val(Mystr))))) Then
Snumber = "00"
Else
Snumber = Str(Right(Mystr, Len(Mystr) - Len(Str(Int(Val(Mystr))))))
End If
'///
Mystr = Trim(Str(Int(Val(Mystr))))
Dim AMystring As String
Dim BMystring As String
For i = 3 To Len(Mystr) Step 3
AMystring = Mid(Mystr, Len(Mystr) - i + 1, 3)
If i >= Len(Mystr) Then
BMystring = AMystring & BMystring
Else
BMystring = "," & AMystring & BMystring
End If
Next i
If Len(Mystr) Mod 3 = 1 Then
BMystring = Left(Mystr, 1) & BMystring
ElseIf Len(Mystr) Mod 3 = 2 Then
BMystring = Left(Mystr, 2) & BMystring
End If
List_Separator = BMystring & "." & Trim(Snumber)
Else
List_Separator = "Error"
End If
End Function
这样就得出不正确的数值
现在只要将这句话加入就行了。
Mystr = CDbl(Mystr) '//避免你说的情况
Dim Snumber As String '//存放小数的值
Dim Bar As String
If IsNumeric(Mystr) Then
Mystr = CDbl(Mystr)
If CDbl(Mystr) < 0 Then
Mystr = Right(Mystr, Len(Mystr) - 1)
Bar = "-"
End If
Dim Snumber As String '//存放小数的值
If Len(Trim(Mystr)) = Len(Trim(Str(Int(Val(Mystr))))) Then
Snumber = "00"
Else
Snumber = Str(Right(Mystr, Len(Mystr) - Len(Str(Int(Val(Mystr))))))
End If
'///
Mystr = Trim(Str(Int(Val(Mystr))))
Dim AMystring As String
Dim BMystring As String
For i = 3 To Len(Mystr) Step 3
AMystring = Mid(Mystr, Len(Mystr) - i + 1, 3)
If i >= Len(Mystr) Then
BMystring = AMystring & BMystring
Else
BMystring = "," & AMystring & BMystring
End If
Next i
If Len(Mystr) Mod 3 = 1 Then
BMystring = Left(Mystr, 1) & BMystring
ElseIf Len(Mystr) Mod 3 = 2 Then
BMystring = Left(Mystr, 2) & BMystring
End If
BMystring = Bar & BMystring
List_Separator = BMystring & "." & Trim(Snumber)
Else
List_Separator = "Error"
End If
End Function
现在这个应该不会有错的。
_l_(蠢瓜)
小弟菜鸟``没发现Format函数``