POSTNET barcodes in Excel
Azalea_POSTNET
Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com


The macro in this spreadsheet creates POSTNET barcodes used on U.S. mail for 5-, 9-, and 11-digit ZIP codes.

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).

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_POSTNET

POSTools prints POSTNET 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/POSTNET


Function Azalea_POSTNET(ByVal ZIPcode As String) As String
' POSTools 25mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com

' Creating a POSTNET barcode in Excel
' ZIPcode, is a 5-digit ZIP code, a 9-digit ZIP+4, or an 11-digit Delivery Point Barcode to be encoded as a POSTNET symbol.

  ' one way to groom ZIPcode is to reject anything that isn't the correct length
  ' embedded hyphens and spaces will trigger this (98126-2398 for instance)
  '  If Len(ZIPcode) <> 5 And Len(ZIPcode) <> 10 And Len(ZIPcode) <> 13 Then
  '    Exit Function
  '  End If

  ' stored ZIP codes usually contain embedded hyphens and spaces that need to be elminiated
  ' "12345-6789" becomes "123456789" and "12345-6789 00" becomes "12345678900"
  Select Case Len(ZIPcode)
    Case 13
      ZIPcode = Left$(ZIPcode, 5) & Mid$(ZIPcode, 7, 4) & Right$(ZIPcode, 2)
    Case 10
      ZIPcode = Left$(ZIPcode, 5) & Right$(ZIPcode, 4)
  End Select

  Dim i As Integer                   ' our loop counter
  Dim checkDigit As Integer          ' the check digit variable

  checkDigit = 0

  For i = 1 To Len(ZIPcode)
    checkDigit = checkDigit + Val(Mid(ZIPcode, i, 1))
  Next i

  ' Concantenate the start bar, the ZIP code, the check digit, & the stop bar.
  Azalea_POSTNET = "s" + ZIPcode + Right(Str(100 - checkDigit), 1) + "s"

  ' Excel: B1=Azalea_POSTNET(A1)
  ' Or put another way, yourContainer.text=Azalea_POSTNET(yourInputString)
  
End Function