CODEWITHSUNDEEP
vbaexcel
j. m
j. m

Reputation: 23

VBA (Excel) issue with Check Digit formula

i recently wrote this short function but with little experience in VBA im failing to see why this isn't working.

Sub barcodedigit()
Dim barcode(12) As Variant
Dim i As Integer
Dim oddscount As Integer
Dim evenscount As Integer
Dim evensnumbers As Integer
Dim oddsnumbers As Integer
Dim finalnumber As Double
Dim remainder As Integer
oddsnumbers = 0
evensnumbers = 0

For i = 1 To 12

barcode(i) = InputBox("Please enter number" & i)

Next i

For i = 1 To 12
If barcode(i) Mod 2 = 0 Then
evenscount = evenscount + 1 And evensnumbers = evensnumbers + barcode(i)

Else

oddscount = oddscount + 1 And oddsnumbers = oddsnumbers + barcode(i)


End If

Next i

oddsnumbers = oddsnumbers * 3

finalnumber = oddsnumbers + evensnumbers

remainder = finalnumber Mod 10

MsgBox ("Oddscount is equal to " & oddscount & vbNewLine & "Remainder is   equal to" & " " & remainder)
End Sub

My issue here is that this is setting every result to 0 (oddscount & remainder) even though this should logically work.

Any help is appreciated.

edit:: removing and worked perfectly!

Upvotes: 0

Views: 2377

Answers (3)

Peter Nouwen
Peter Nouwen

Reputation: 1

I think it can be much more elegant. See this funtion for calculating checkdigits for GTIN-8/12/13/14/GSIN/SSCC: Input is a string with the GTIN excluding the checkdigit. And you get back the checkdigit.

Function GetCheckDigit(Gtin As String)
Dim i As Integer
Dim CheckSum  As Integer

    CheckSum = 0

    For i = 1 To Len(Gtin)
        If i Mod 2 = 1 Then
            CheckSum = CheckSum + (CInt(Mid(Gtin, Len(Gtin) - i + 1, 1)) * 3)
        Else
            CheckSum = CheckSum + CInt(Mid(Gtin, Len(Gtin) - i + 1, 1))
        End If
    Next

    GetCheckDigit = (10 - (CheckSum Mod 10)) Mod 10

End Function

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

I wrote something like that here. Here is the code: 

Function IsCodeValid(sNumber As String) As Boolean
    On Error Resume Next
    If Len(sNumber) < 8 Then Exit Function
    IsCodeValid = (Right(sNumber, 1) = CheckDigit(Left(sNumber, Len(sNumber) - 1)))
    If Err.Number <> 0 Then Debug.Print Now, sNumber, Err.Number, Err.Description
End Function

Function CheckDigit(ByVal gtin As String) As String
'general purpose check digit calculator
'given all figures except last one, calculate check digit as used for GTIN-8, GTIN-12, EAN13, EDI GLN, etc...
'parameter: number as string, WITHOUT the last digit
'           works with string length up to 254 char
'returns:   the last digit
'author:    Patrick Honorez - www.idevlop.com
'notes:     provided without any warranties
'           Copyleft as long as you keep this header intact
'help for algorithm can be found here:
'           http://www.gs1.org/barcodes/support/check_digit_calculator#how

    Dim m() As String, lSum As Long, i As Integer
    Dim chk As Integer, large As Long, mult As Byte
    'store string into an array
    m = Split(StrConv(gtin, vbUnicode), Chr(0))
    mult = 3    'multiplier initial value is 3
    'calc right to left to start with 3 as multiplier
    For i = UBound(m) - 1 To 0 Step -1  'ignore last value of array: it's always = to chr(0)
        lSum = lSum + Val(m(i)) * mult
        If mult = 3 Then mult = 1 Else mult = 3 'swap multiplier value between 3 and 1
    Next i
    'find difference between lSum and the 10 that's equal or greater
    large = (lSum \ 10) * 10
    If large < lSum Then large = large + 10
    chk = large - lSum
    CheckDigit = CStr(chk)
End Function

Upvotes: 0

Reg Edit
Reg Edit

Reputation: 6916

Your bitwise And is very strange—I think you mean

If barcode(i) Mod 2 = 0 Then
    evenscount = evenscount + 1
    evensnumbers = evensnumbers + barcode(i)
Else
    oddscount = oddscount + 1
    oddsnumbers = oddsnumbers + barcode(i)
End If

Upvotes: 2

Related Questions