Function

[Hans_]

Hi all,

I am creating a Function but i just can't get it to work.

In cell A1 i have the following: 871040012345)
In cell A2 i have the formula: =EAN(A1)

As a result i get the errormessage #VALUE

This is the code that comes with the EAN function:

Function EAN(EAN12 As String)
Dim Pos1, Pos2, Pos3, Pos4, Pos5, Pos6, Pos7, Pos8, Pos9, Pos10, Pos11, Pos12 As Integer
Dim Total1, Total3 As Integer
Dim InputEAN, EANcheck As String
 
InputEAN = Right(EAN12, 13)
EANcheck = Right(InputEAN, 1)
 
If EANcheck = ")" Then
 
'Define position numbers
    Pos1 = Mid(InputEAN, 1, 1)
    Pos2 = Mid(InputEAN, 2, 1)
    Pos3 = Mid(InputEAN, 3, 1)
    Pos4 = Mid(InputEAN, 4, 1)
    Pos5 = Mid(InputEAN, 5, 1)
    Pos6 = Mid(InputEAN, 6, 1)
    Pos7 = Mid(InputEAN, 7, 1)
    Pos8 = Mid(InputEAN, 8, 1)
    Pos9 = Mid(InputEAN, 9, 1)
    Pos10 = Mid(InputEAN, 10, 1)
    Pos11 = Mid(InputEAN, 11, 1)
    Pos12 = Mid(InputEAN, 12, 1)
 
'Define totals
    Total1 = Pos1 + Pos3 + Pos5 + Pos7 + Pos9 + Pos11
    Total3 = (Pos2 + Pos4 + Pos6 + Pos8 + Pos10 + Pos12) * 3
 
EAN = Total1 & Total3
Else: EAN = "-"
End If
End Function

Does anybody know what is wrong here?

Thanks!!

 

[VoG]

I don't know what is wrong. However, you should be aware that

Dim Pos1, Pos2, Pos3, Pos4, Pos5, Pos6, Pos7, Pos8, Pos9, Pos10, Pos11, Pos12 As Integer

declares Pos1 - Pos 11 as Variant and Pos12 as Integer. You probably intended

Dim Pos1 As Integer, Pos2 As Integer, Pos3 As Integer, Pos4 As Integer, Pos5 As Integer, Pos6 As Integer, Pos7 As Integer, Pos8 As Integer, Pos9 As Integer, Pos10 As Integer, Pos11 As Integer, Pos12 As Integer

 

[Hans_]

Thanks VoG!

Didn't know that, this worked!!

 

[DonkeyOte]

You could use native formula:

Excel Workbook
	A	B
1	871040012345	67
2	123456789012	92
Sheet1

 

[Weaver]

I simplified the code a little and fixed it this way:

Function EAN(EAN12 As String) As String
    Dim pos(12) As Integer
    Dim Total1, Total3 As Integer
    Dim InputEAN, EANcheck As String
     
    InputEAN = Right(EAN12, 13)
    EANcheck = Right(InputEAN, 1)
     
    If EANcheck = ")" Then
     
        'Define position numbers
        For x = 1 To 12
            pos(x) = Mid(InputEAN, x, 1)
            Debug.Print pos(x)
        Next x
    
        'Define totals
        Total1 = pos(1) + pos(3) + pos(5) + pos(7) + pos(9) + pos(11)
        Total3 = (pos(2) + pos(4) + pos(6) + pos(8) + pos(10) + pos(12)) * 3
     
        EAN = Total1 & Total3
    Else
        EAN = "-"
    End If
End Function

It looks as if by accident I fixed the problem highlighted in VoG's post!

 

[Hans_]

Thanks for your replies!!

DonkeyOte, i know i can work with a long complicated formula but i'm not done yet with the enitre function so it would be even longer. And if i want the people at work to use it it should be simple for them...

excelR8R, thanks for this! i am using this now!!

 

[DonkeyOte]

An alternative version

Function EAN(EAN12 As String)
Dim bPos As Byte, dblOdd As Double, dblEven As Double
If Right(EAN12, 1) = ")" Then
    For bPos = 1 To Len(EAN12) - 1 Step 1
        If bPos Mod 2 Then
            dblOdd = dblOdd + Mid(EAN12, bPos, 1)
        Else
            dblEven = dblEven + Mid(EAN12, bPos, 1)
        End If
    Next bPos
    EAN = dblEven + (dblOdd * 3)
Else
    EAN = "-"
End If
End Function

ExcelR8R - beware your Declarations as per VOG's post, in your function Both Total1 and InputEAN are Variants