VBA If statement returning incorrect value-CodePudding

I am working on automating a script to apply a check digit to a range of fields. In doing this, I have most of it setup and had it work without issue and now its acting up again. I was hoping that I could get a second review of everything.

I have been using the following number to test: 00276933863801021

The correct check digit is 6

Function ChkDgt(ByVal Scanline As String)

Dim n, w, p, c, cp As Long
Dim x, y, r As Integer

'Checks for Spaces in Scanline and removes them
Scanline = Replace(Scanline, " ", "", 1, , vbBinaryCompare)

'Determine number of characters in series
For i = 1 To Len(Scanline)
    
    'Determines character being processed
    n = Mid$(Scanline, i, 1)
    
    'Defines the weighted value based on odd/even number position
    If i Mod 2 = 1 Then
        w = 2
    Else: w = 1
    End If
    
    'multiplies character(number) by weighted value
    p = n * w
    
    'All numbers must be between 0 and 9. This checks for 2 digits and adds the two together ex. (16 => 1   6 = 7)

    If Len(p) = 2 Then
        x = Left(p, 1)
        y = Right(p, 1)
        c = x   y
    Else: c = p
    End If
    
    'Sums all character products 
    cp = cp   c
    

Next

'Returns the remainder value of the final sum
r = cp Mod 10

'10 - remainder value equals the check digit
chk = 10 - r

'Returns the Check Digit
ChkDgt = CStr(chk)


End Function

When looking at the Len(p) to determine 2 values/characters this is where i am losing my numbers and getting the wrong check digit.

    If Len(p) = 2 Then
        x = Left(p, 1)
        y = Right(p, 1)
        c = x   y
    Else: c = p
    End If

When i = 5 on the counter the product should be 12 which converts to 3, but it is staying as 12.

Any help would be much appreciated.

Best, John

CodePudding user response：

Dim n, w, p, c, cp As Long
Dim x, y, r As Integer

The above declaration is not what you intend. cp will be a Long, r will be an Integer but all other variables will be Variants.

This results in c = x y being executed as a string concatenation, as x and y are strings wrapped in a Variant.

CodePudding user response：

Keep out with the Len() function: for strings it returns the amount of characters, but for other types it returns something internal. Therefore I advise you to turn your argument into a string first:

If Len(CStr(p)) = 2 Then

CodePudding user response：

Here are several recommendations for your code.

First, use Option Explicit. This will allow the compiler to flag undefined variables which will save you trouble at some point.

Second, define your variables correctly. Without explicitly defining the type, the variable would be declared as Variant. So in this case:

Dim n As Long, w As Long, p As Long, c As Long, cp As Long
Dim chk As Integer, i As Integer, x As Integer, y As Integer, r As Integer

Third, define your function correctly. Functions should return a value. If not specified a Variant is returned. So in this case:

Function ChkDgt(ByVal Scanline As String) As String

Fourth, change the block of code in question to this:

'All numbers must be between 0 and 9. This checks for 2 digits and adds the two together ex. (16 => 1   6 = 7)
If p > 9 Then
   x = Left(p, 1)
   y = Right(p, 1)
   c = x   y
Else
   c = p
End If

With these changes in place, the correct result is returned.