Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
1 Solution
 
pdvsaProject financeAuthor Commented:
I use the function like this:
AmtOverlay: GetFormat([contractamt])
0
 
Patrick MatthewsCommented:
Please explain what "rounded properly" would be :)
0
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now