Javascript converted to Excel VB Function Produces #NUM! error

Hey guys, I have a javascript function that produces a 12 digit UPC code (Based on the first 11 digits:

function ccc12(rawVal) {
  factor = 3;
  sum = 0;
  rawVal = rawVal.toString();
  if (rawVal.length!=11){
      throw "The UCC-12 ID Number requires that you enter 11 digits.";
  }
  for (index = rawVal.length; index > 0; --index) {
    sum = sum + rawVal.substring (index-1, index) * factor;
    factor = 4 - factor;
  }
  return ((1000 - sum) % 10);
}

Assuming the above if I gave 84686400201 as the rawVal, then 2 would be the outcome returned. This was then convert开发者_开发技巧ed to

Function generateUPC(upcCode As Integer) As String
    Dim upcCheckDigit, factor, sum As Integer
    Dim upcString As String
    factor = 3
    sum = 0

    For i = Len(upcCode) To 0 Step -1
        sum = sum + Mid(upcCode, i - 1, 1) * factor
        factor = 4 - factor
    Next i
    upcCheckDigit = ((1000 - sum) Mod 10)
    upcString = upcCode & upcCheckDigit

    generateUPC = upcString
End Function

This function returns the original string plus the last digit, but instead i get #NUM! in the worksheet when I put =generateUPC(84686400201) into the cell.

Any ideas? Never really bothered doing VB Macros etc before so this is new to me

I suggest changing upcCode to a string to avoid overflow and changing the indexes of your loop and within the Mid function to avoid out-of-bounds errors.

Function generateUPC(upcCode as String) As String
    Dim upcCheckDigit, factor, sum As Integer
    Dim upcCode, upcString As String
    factor = 3
    sum = 0

    For i = Len(upcCode) To 1 Step -1
        sum = sum + Mid(upcCode, i, 1) * factor
        factor = 4 - factor
    Next i
    upcCheckDigit = ((1000 - sum) Mod 10)
    upcString = upcCode & upcCheckDigit

    generateUPC = upcString
End Function

VBA integers are -32k to +32k

VBA Longs are -2B to +2B

Your 'upcCode' integer is larger than the long data type so I tried it with Double, which is a float, but works:

Function generateUPC(upcCode As Double) As String
    Dim upcCheckDigit, factor, sum As Double
    Dim upcString As String
    factor = 3
    sum = 0

    For i = Len(upcCode) To 0 Step -1
        sum = sum + Mid(upcCode, i - 1, 1) * factor
        factor = 4 - factor
    Next i
    upcCheckDigit = ((1000 - sum) Mod 10)
    upcString = upcCode & upcCheckDigit

    generateUPC = upcString

End Function