Macro to validate EAN code

[alialeola]

Hi guys,

Can a genius help me transpose the below validation into a macro, pretty please?

The check digit for an EAN-13 code is calculated as follows:

1. Count digit positions from the left to the right, starting at 1.
2. Sum all the digits in odd positions. (As example, for EAN code 9780521425575, this is 9 + 8 + 5 + 1 + 2 + 5 = 30 – note that the final 5 is not included since this is the check digit, which is what we are currently trying to calculate.)
3. Sum all the digits in even positions and multiply the result by 3. (In the example, this is (7 + 0 + 2 + 4 + 5 + 7) × 3 = 75.)
4. Add the results of step 2 and step 3, and take just the final digit (the ‘units’ digit) of the answer. This is equivalent to taking the answer modulo-10. (In the example, the sum is 30 + 75 = 105, so the units digit is 5.)
5. If the answer to step 4 was 0, this is the check digit. Otherwise the check digit is given by ten minus the answer from step 4. (In the example, this is 10 – 5 = 5.)
6. The check digit is appended to the right of the 12 identification digits. The check digit can have any value from 0 to 9.
   
   Alternatively, there is a shortcut to checking for errors because of the way the check digit is derived. You take the full 13-digit received code and do steps 1 to 4 from the calculation used above. If the code is correct, the value at step 4 will be 0. If the code is wrong, it will have some other value.
   
   
   

   Exploring communications technology

   This free course, Exploring communications technology, looks at some of the principles underpinning communications technology. It looks at the properties of signals and media (such as optical fibre...

   www.open.edu

 

[jimrward]

I will post a possible solution in the morning when I get back to my pc,here are my ramblings so far on an iPad not made it a function and it doesn’t return a value yet

Sub Jim()

Dim number As String

Number =“97805142557”

Call ean (number)

End Sub

Sub ean (number As String)

Dim SumEven As Integer

Dim SumOdd As Integer

Dim Flag As Integer

SumEven = 0

SumOdd = 0

Flag = 1

For j = 1 To 12

    digit = Mid (number, j, 1)

    If Flag = 1 Then

        SumOdd = SumOdd + digit

    Else

        SumEven = SumEven + digit

    End If

‘

‘* toggle flag

‘* 1 is odd -1 is even so each time around the loop we flip it

‘*

    Flag = Flag * -1

Next J

SumEven = SumEven * 3

Total = SumEven + SumOdd

check = Right (Total, 1)

If check <> 0 Then

    check = 10 - check

End If

End Sub

 

[alialeola]

I will post a possible solution in the morning when I get back to my pc,here are my ramblings so far on an iPad not made it a function and it doesn’t return a value yet

Sub Jim()

Dim number As String

Number =“97805142557”

Call ean (number)

End Sub

Sub ean (number As String)

Dim SumEven As Integer

Dim SumOdd As Integer

Dim Flag As Integer

SumEven = 0

SumOdd = 0

Flag = 1

For j = 1 To 12

    digit = Mid (number, j, 1)

    If Flag = 1 Then

        SumOdd = SumOdd + digit

    Else

        SumEven = SumEven + digit

    End If

‘

‘* toggle flag

‘* 1 is odd -1 is even so each time around the loop we flip it

‘*

    Flag = Flag * -1

Next J

SumEven = SumEven * 3

Total = SumEven + SumOdd

check = Right (Total, 1)

If check <> 0 Then

    check = 10 - check

End If

End Sub

You are my only hope!

 

[jimrward]

What do you want the function to do
Do you want to pass it 12 characters and get the check digit
Or
Pass it 13 characters and verify the check digit is ok

 

[alialeola]

What do you want the function to do
Do you want to pass it 12 characters and get the check digit
Or
Pass it 13 characters and verify the check digit is ok

I would need it to check the 13 available digits and add the validation "OK" / "FALSE" in the first empty column.

 

[jimrward]

@alialeola ok rather than VBA I found something similar for validation of UPC codes and someone came up with a nifty formula, in their case they multiplied the odd ones by 3 so i have modified the formula
do you have any examples of EAN code with a zero for the check digit so i can test against the formula below

Book1
	A	B	C
1	EANCode	ValidationDigit	PassFail
2	9780521425575	5	Pass
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MOD(-SUM(MID(A2,{1,3,5,7,9,11;2,4,6,8,10,12},1)*{1;3}),10)
C2	C2	=IF(TEXT(B2,"0")=RIGHT(A2,1),"Pass","Fail")