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
pdvsaProject financeAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B

if varField & ""="" then GetFormat="": Exit Function

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
0
 
pdvsaProject financeAuthor Commented:
I use the function like this:
AmtOverlay: GetFormat([contractamt])
0
 
Patrick MatthewsCommented:
Please explain what "rounded properly" would be :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
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
0
 
pdvsaProject financeAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
pdvsaProject financeAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.