Code 39 Full ASCII barcodes in Excel
Azalea_Code_39_Full_ASCII
Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com


The macro in this spreadsheet creates Code 39 Full ASCII barcodes. Pairs of characters are used to expand the standard 44 character version of Code 39 to encode the lower 128 ASCII characters.

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_Code_39_Full_ASCII

C39Tools prints Code 39 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/Code-39


Function Azalea_Code_39_Full_ASCII(ByVal Code39 As String) As String
' C39Tools 24mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com

' Creating a Code 39 Full ASCII barcode in Excel
' Your input, Code39, is a string to be encoded as a Code 39 Full ASCII symbol.
' yourData can be anything in the lower 128 ASCII characters. Input error checking is your responsibility.

  Dim i As Integer                   ' our loop counter
  Dim chunk As String                ' loop chunk
  Dim temp As String                 ' a temporary placeholder
  
  ' map the input into the Full ASCII version of Code 39
  For i = 1 To Len(Code39)
    chunk = Mid(Code39, i, 1)
    Select Case Asc(chunk)
      Case 0
        temp = temp + "%U"
      Case 1 To 26
        temp = temp + "$" + Chr(Asc(chunk) + 64)
      Case 27 To 31
        temp = temp + "%" + Chr(Asc(chunk) + 38)
      Case 32
      ' Because TrueType doesn't support glyphs in the space slot (ASCII 32)
      ' we've moved the space character to the underscore ( _ ).
      ' Here's the search and replace.
        temp = temp + "_"
      Case 33 To 44
        temp = temp + "/" + Chr(Asc(chunk) + 32)
      Case 45 To 46
        temp = temp + chunk
      Case 47
        temp = temp + "/O"
      Case 48 To 57
        temp = temp + chunk
      Case 58
        temp = temp + "/Z"
      Case 59 To 63
        temp = temp + "%" + Chr(Asc(chunk) + 11)
      Case 64
        temp = temp + "%V"
      Case 65 To 90
        temp = temp + chunk
      Case 91 To 95
        temp = temp + "%" + Chr(Asc(chunk) - 16)
      Case 96
        temp = temp + "%W"
      Case 97 To 122
        temp = temp + "+" + Chr(Asc(chunk) - 32)
      Case 123 To 126
        temp = temp + "%" + Chr(Asc(chunk) - 43)
      Case 127
        temp = temp + "%T"
    End Select
  Next i

  ' Add the start and stop bars, the asterisk, before and after the input string.
  Azalea_Code_39_Full_ASCII = "*" + temp + "*"

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