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


The macro in this spreadsheet creates ISBN-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 13-digit ISBN-13 numbers and 5-digit supplemental code input as a string.
  the ISBN-13 must not have hyphens or spaces in it.
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_ISBN13

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_ISBN13(ByVal ISBNnumber As String, ByVal Price As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com

' Creating an ISBN-13 barcode in Excel 2003
' ISBNnumber is a 13-digit ISBN number and Price is the retail price used in the 5-digit supplemental code.
' ISBNnumber has no hyphens or spaces. Strip them out if necessary to make an 11-digit string.

' Note: this routine requires the EvenS, OddS, EvenBar, & OddBar functions too.
 
  Dim ISBN As String                  ' a bucket for the ISBN string
  Dim checkDigitSubtotal As Integer   ' a check digit subtotal
  Dim checkDigit As String            ' a check digit throwaway
  Dim temp As String                  ' a temporary placeholder

  ' drop the ISBN check digit
  ISBN = Left(ISBNnumber, 12)

  ' Let's do the EAN-13 check digit calculation
  checkDigitSubtotal = 3 * (Val(Mid(ISBN, 2, 1)) + Val(Mid(ISBN, 4, 1)) + Val(Mid(ISBN, 6, 1)) + Val(Mid(ISBN, 8, 1)) + Val(Mid(ISBN, 10, 1)) + Val(Right(ISBN, 1)))
  checkDigitSubtotal = checkDigitSubtotal + Val(Left(ISBN, 1)) + Val(Mid(ISBN, 3, 1)) + Val(Mid(ISBN, 5, 1)) + Val(Mid(ISBN, 7, 1)) + Val(Mid(ISBN, 9, 1)) + Val(Mid(ISBN, 11, 1))
  checkDigit = Right(Str(300 - checkDigitSubtotal), 1)

  ' Build the output string.
  ' Does the ISBN number begin with 978 or 979?
  If Mid(ISBN, 3, 1) = "8" Then
    temp = Chr(203) + "|xHS" + EvenBar(Mid(ISBN, 4, 1))
  Else
    temp = Chr(203) + "|xHT" + EvenBar(Mid(ISBN, 4, 1))
  End If
  temp = temp + OddBar(Mid(ISBN, 5, 1))
  temp = temp + EvenBar(Mid(ISBN, 6, 1))
  temp = temp + OddBar(Mid(ISBN, 7, 1)) + "y" + Right(ISBN, 5) + checkDigit + "zv"

  ' Calculate the 5-digit supplemental check digit
  checkDigitSubtotal = 3 * (Val(Left(Price, 1)) + Val(Mid(Price, 3, 1)) + Val(Right(Price, 1)))
  checkDigitSubtotal = checkDigitSubtotal + (9 * (Val(Mid(Price, 2, 1)) + Val(Mid(Price, 4, 1))))
  checkDigit = Right(Str(checkDigitSubtotal), 1)

  ' The rest of the output depends upon the checkDigit value.
  Select Case checkDigit
    Case "0" ' EEEOO
      temp = temp + EvenS(Left(Price, 1)) + ":" + EvenS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "1" ' EOEOO
      temp = temp + EvenS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + EvenS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "2" ' EOOEO
      temp = temp + EvenS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + EvenS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "3" ' EOOOE
      temp = temp + EvenS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + EvenS(Right(Price, 1))
    Case "4" ' OEEOO
      temp = temp + OddS(Left(Price, 1)) + ":" + EvenS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + EvenS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "5" ' OOEEO
      temp = temp + OddS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + EvenS(Mid(Price, 3, 1)) + ":" + EvenS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "6" ' OOOEE
      temp = temp + OddS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + EvenS(Mid(Price, 4, 1)) + ":" + EvenS(Right(Price, 1))
    Case "7" ' OEOEO
      temp = temp + OddS(Left(Price, 1)) + ":" + EvenS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + EvenS(Mid(Price, 4, 1)) + ":" + OddS(Right(Price, 1))
    Case "8" ' OEOOE
      temp = temp + OddS(Left(Price, 1)) + ":" + EvenS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + OddS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + EvenS(Right(Price, 1))
    Case "9" ' OOEOE
      temp = temp + OddS(Left(Price, 1)) + ":" + OddS(Mid(Price, 2, 1))
      Azalea_ISBN13 = temp + ":" + EvenS(Mid(Price, 3, 1)) + ":" + OddS(Mid(Price, 4, 1)) + ":" + EvenS(Right(Price, 1))
  End Select

  ' The output, Azalea_ISBN13, is formatted using one of Azalea Software's UPC fonts.
  ' Excel: B1=Azalea_ISBN13(A1)

  ' Or put another way, yourContainer.text=Azalea_ISBN13(yourInputString)

End Function

Private Function OddS(ByVal theString As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com
  Select Case theString
    Case "0"
      OddS = "!"
    Case "1"
      OddS = Chr(34)
    Case "2"
      OddS = "#"
    Case "3"
      OddS = ""
    Case "4"
      OddS = "%"
    Case "5"
      OddS = "&"
    Case "6"
      OddS = "'"
    Case "7"
      OddS = "("
    Case "8"
      OddS = ")"
    Case "9"
      OddS = "*"
  End Select

End Function

Private Function EvenS(ByVal theString As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com
  Select Case theString
    Case "0"
      EvenS = "+"
    Case "1"
      EvenS = ","
    Case "2"
      EvenS = "-"
    Case "3"
      EvenS = "."
    Case "4"
      EvenS = "/"
    Case "5"
      EvenS = ";"
    Case "6"
      EvenS = "<"
    Case "7"
      EvenS = "="
    Case "8"
    EvenS = ">"
    Case "9"
      EvenS = "^"
  End Select
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