CODEWITHSUNDEEP
excelvba
Jozey
Jozey

Reputation: 1740

Where is the decimal coming from in this calculation?

I have a function that calcuates a BOL number and only take the first 10 digits.

Here's the code.

Public Function GENERATEBOLNUMBER(iYearSuffix As Integer, _
                              sFromZipcode As String, _
                              sToZipCode As String, _
                              iWeight As Integer, _
                              iShowID As Integer) As String

    Application.ScreenUpdating = False

    GENERATEBOLNUMBER = VBA.Left(7 & _
    WorksheetFunction.RoundUp(VBA.Right(sFromZipcode, 5) _
    * VBA.Right(sToZipCode, 5) * iWeight * (iShowID + 1234), 0), 10)

    Application.ScreenUpdating = True
End Function

And here are the values I'm passing it. 7 for the iYearSuffix, 78224 for sFromZipcode and 78224 for sToZipCode, 410 as the iWeight, and 1 as the iShowID. All of this calculates to 3098352701017600, so the final string should be 7309835270, which is the 7 included as the first digit and the following 9 digits.

Where is the decimal coming from? The answer I'm getting is: 73.0983527.

Upvotes: 0

Views: 53

Answers (1)

Comintern
Comintern

Reputation: 22185

You're mixing string handling, numeric manipulation, implicit casts between strings and numbers, VBA, WorksheetFunction, and gigantic numbers. What could possibly go wrong?

If you're going to write a UDF in VBA, write it in VBA. The only data type large enough to store your result is going to be a Decimal, so you'll have to declare it as a Variant and explicitly cast the calculation to force it to coerce:

Public Function GENERATEBOLNUMBER(yearSuffix As Integer, fromZip As String, _
                                  toZip As String, weight As Integer, _
                                  showId As Integer) As String
    Dim result As Variant
    'Calculate intermediary result.
    result = CDec(Right$(fromZip, 5)) * CDec(Right$(toZip, 5)) * weight * (showId + 1234)
    'Shift the decimal place 7 places to the left:
    result = result / 10 ^ 7
    'Skip the RoundUp call - it wasn't doing anything because your result was an integer.
    'Strip the non-integer portion:
    result = Fix(result)
    'Cast to a string an concatenate the "7" onto the start:
    GENERATEBOLNUMBER = "7" & CStr(result)
End Function

Upvotes: 2

Related Questions