Creating EAN-13 barcodes in Excel
Azalea_EAN13
Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com


The macro in this spreadsheet creates EAN-13 barcodes when used with Azalea Software's UPCTools font package.

Because this spreadsheet is built around a macro, you *must* enable macros for this spreadsheet to work! The .xls file is for Excel 2003 and the .xlsm is for Excel 2007. An alternative is to use a User Defined Function as an .xla (Excel 2003) or .xlam (Excel 2007).

The macro accepts 12-digit input as a string. The macro calculates the check digit and maps the output into the UPCTools character set. Format the returned value in a UPCTools font and a barcode will be displayed and printed.

Press ALT-F11 to view the macro in the Visual Basic Editor.

To add the macro to your own spreadsheet:
  Tools/Macro/Visual Basic Editor
  Insert/Module
  Paste in the macro code
  Close the Visual Basic Editor

When you return to your spreadsheet, a new User Defined function is available: Azalea_EAN13

UPCTools prints UPC, ISBN-13, EAN, JAN, and ISSN barcodes. Available for Windows, OS X, Linux/UNIX, et al. Free sample code and free tech support. Buy online and download immediately.

www.azalea.com/UPC


Function Azalea_EAN13(ByVal EAN As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com

' Creating an EAN-13 in Excel 2003
' Your input, EAN, is a string consisting of a 12-digit number.
' Note: this routine requires the EvenBar and OddBar functions too.

  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 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com
  OddBar = Chr(65 + (Val(theString)))
End Function
 
Private Static Function EvenBar(ByVal theString As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com
  EvenBar = Chr(75 + Val(theString))
End Function