Function Azalea_EAN13(ByVal EAN As String) As String ' UPCTools ' Copyright 2016 Azalea Software, Inc. All rights reserved. www.azaleabarcodes.com ' Creating an EAN-13 in Excel 2003 ' Your input, EAN, is a string consisting of a 12-digit number. Dim checkDigitSubtotal As Integer ' a check digit subtotal Dim checkDigit As String ' the check digit itself Dim temp As String ' a temporary placeholder ' Calculate the EAN-13 check digit. checkDigitSubtotal = 3 * (Val(Mid(EAN, 2, 1)) + Val(Mid(EAN, 4, 1)) + Val(Mid(EAN, 6, 1)) + Val(Mid(EAN, 8, 1)) + Val(Mid(EAN, 10, 1)) + Val(Right(EAN, 1))) checkDigitSubtotal = checkDigitSubtotal + Val(Left(EAN, 1)) + Val(Mid(EAN, 3, 1)) + Val(Mid(EAN, 5, 1)) + Val(Mid(EAN, 7, 1)) + Val(Mid(EAN, 9, 1)) + Val(Mid(EAN, 11, 1)) checkDigit = Right(Str(300 - checkDigitSubtotal), 1) ' Begin building the output string with the 1st character's human-readable, L guard bars & odd parity of 1st digit temp = Chr(194 + CInt(Left(EAN, 1))) & "x" & OddBar(Mid(EAN, 2, 1)) ' Build the remainder of left half of symbol's parity is based on 1st digit Select Case Left(EAN, 1) Case "0" ' OOOOO temp = temp + OddBar(Mid(EAN, 3, 1)) temp = temp + OddBar(Mid(EAN, 4, 1)) temp = temp + OddBar(Mid(EAN, 5, 1)) temp = temp + OddBar(Mid(EAN, 6, 1)) temp = temp + OddBar(Mid(EAN, 7, 1)) Case "1" ' OEOEE temp = temp + OddBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + OddBar(Mid(EAN, 5, 1)) temp = temp + EvenBar(Mid(EAN, 6, 1)) temp = temp + EvenBar(Mid(EAN, 7, 1)) Case "2" ' OEEOE temp = temp + OddBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + EvenBar(Mid(EAN, 5, 1)) temp = temp + OddBar(Mid(EAN, 6, 1)) temp = temp + EvenBar(Mid(EAN, 7, 1)) Case "3" ' OEEEO temp = temp + OddBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + EvenBar(Mid(EAN, 5, 1)) temp = temp + EvenBar(Mid(EAN, 6, 1)) temp = temp + OddBar(Mid(EAN, 7, 1)) Case "4" ' EOOEE temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + OddBar(Mid(EAN, 4, 1)) temp = temp + OddBar(Mid(EAN, 5, 1)) temp = temp + EvenBar(Mid(EAN, 6, 1)) temp = temp + EvenBar(Mid(EAN, 7, 1)) Case "5" ' EEOOE temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + OddBar(Mid(EAN, 5, 1)) temp = temp + OddBar(Mid(EAN, 6, 1)) temp = temp + EvenBar(Mid(EAN, 7, 1)) Case "6" ' EEEOO temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + EvenBar(Mid(EAN, 5, 1)) temp = temp + OddBar(Mid(EAN, 6, 1)) temp = temp + OddBar(Mid(EAN, 7, 1)) Case "7" ' EOEOE temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + OddBar(Mid(EAN, 4, 1)) temp = temp + EvenBar(Mid(EAN, 5, 1)) temp = temp + OddBar(Mid(EAN, 6, 1)) temp = temp + EvenBar(Mid(EAN, 7, 1)) Case "8" ' EOEEO temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + OddBar(Mid(EAN, 4, 1)) temp = temp + EvenBar(Mid(EAN, 5, 1)) temp = temp + EvenBar(Mid(EAN, 6, 1)) temp = temp + OddBar(Mid(EAN, 7, 1)) Case "9" ' EEOEO temp = temp + EvenBar(Mid(EAN, 3, 1)) temp = temp + EvenBar(Mid(EAN, 4, 1)) temp = temp + OddBar(Mid(EAN, 5, 1)) temp = temp + EvenBar(Mid(EAN, 6, 1)) temp = temp + OddBar(Mid(EAN, 7, 1)) End Select ' Add the center guard bars & the build right half of symbol using set C (0-9) Azalea_EAN13 = temp + "y" + Mid(EAN, 8, 5) + checkDigit + "z" ' The output, Azalea_EAN13, needs to be formatted in one of Azalea Software's UPC fonts. ' We recommend UPCTallThin at 73 points. ' Excel: B1=Azalea_EAN13(A1) ' Or put another way, yourContainer.text=Azalea_EAN13(yourInputString) End Function Private Static Function OddBar(ByVal theString As String) As String ' UPCTools 16may12 jwhiting ' Copyright 2012 Azalea Software, Inc. All rights reserved. www.azaleabarcodes.com OddBar = Chr(65 + (Val(theString))) End Function Private Static Function EvenBar(ByVal theString As String) As String ' UPCTools 16may12 jwhiting ' Copyright 2012 Azalea Software, Inc. All rights reserved. www.azaleabarcodes.com EvenBar = Chr(75 + Val(theString)) End Function