asked on

GetFormat module

Experts,

I have this function that rounds large numbers. It works fine except the billions are not rounded properly.

ie: 2,699,458,000.00
rounds to 2.699458B

Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B
Select Case Len(varField)
Case 7 To 9
GetFormat = FormatNumber(varField / 1000000, 0) & "MM"
Case 10
GetFormat = varField / 1000000000 & "B"
End Select
End Function

how can I make
2,699,458,000.00
round to 2.7B?

thank you

pdvsa

ASKER

I use the function like this:
AmtOverlay: GetFormat([contractamt])

Patrick Matthews

Please explain what "rounded properly" would be :)

Rey Obrero (Capricorn1)

use this function

Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B
varField = CDec(Replace(varField, ",", ""))
Select Case Len(varField)
Case 7 To 9
GetFormat = FormatNumber(varField / 1000000, 0) & "MM"
Case 10 To 12
GetFormat = FormatNumber(varField / 1000000000, 1) & "B"
End Select
End Function

pdvsa

ASKER

Capricorn,

thanks. do i need to handle nulls? I get a runtime error 94 "Invalid use of Null". The debugger highlights the varField line.

thank you.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Just replace FormatNumber with Format and brush up a little:

Public Function GetFormat(ByVal varField As Variant) As String

' USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B

    If IsNumeric(varField) Then
        Select Case Len(varField)
            Case 7 To 9
                GetFormat = Format(varField / 10 ^ 6, 0) & "MM"
            Case 10 To 12
                GetFormat = Format(varField / 10 ^ 9, 0) & "B"
        End Select
    End If

End Function

Open in new window

/gustav

pdvsa

ASKER

Capricorn, very nice. it rounded 2,699,458,000.00 to 2.7B, which is exactly what I needed.

Gustav: that worked too (but it did round the 2.7B to 3B).

thank you