Creating Code 128 code set B barcodes in Excel
Azalea_Code_128_B
Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com


The macro in this spreadsheet creates Code 128 code set B barcodes when used with Azalea Software's C128Tools 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 string input that contains the Code 128 code set B character set.
The macro calculates the check digit, adds the start and stop bars, and maps the output into the C128Tools character set. Format the returned value in a C128Tools 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_Code_128_B

C128Tools prints code set A, code set B, code set C, and GS1-128 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-128


Function Azalea_Code_128_B(ByVal yourData As String) As String
' C128Tools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azalea.com

' Creating a Code 128 code set B barcode in Excel 2003
' Your input, yourData, is a string to be encoded as a Code 128 code set B symbol.
' yourData must be the Code 128 code set B character set. Input error checking is your responsibility.

  Dim temp As String                 ' a temporary placeholder
  Dim chunk As String                ' loop chunk
  Dim i As Integer                   ' our loop counter
  Dim checkDigitSubtotal As Integer  ' a check digit throwaway
  Dim e As Integer                   ' a placeholder variable
    
  ' seed the variables
  temp = Chr(182)                   ' code set B start glyph
  checkDigitSubtotal = 104          ' code set B start checkdigit value

  ' map the input string into the C128Tools character set
  For i = 1 To Len(yourData) Step 1
    chunk = Mid(yourData, i, 1)
    Select Case Asc(chunk)
      ' map from above ASCII 200 to the actual character assignments
      Case Is > 200
        temp = temp & Chr(Asc(chunk) - 35)
        ' The space character is at ASCII 194 because TrueType
        ' doesn't allow a glyph in the ASCII 32 slot
      Case Is = 32
        temp = temp & Chr(206)
      Case Else
        temp = temp & chunk
    End Select
  Next i

  ' Calculate the Code 128 mod 103 check digit
  For i = 1 To Len(yourData)
    e = Asc(Mid(yourData, i, 1)) - 32
    If e <> 142 Then
      checkDigitSubtotal = checkDigitSubtotal + (e * i)
    End If
  Next i
  checkDigitSubtotal = checkDigitSubtotal Mod 103

  ' Put together the final output string
  ' code set A start bar, the encoded string, check digit, stop bar
  Select Case checkDigitSubtotal
    Case 0
      ' Mark Presley bug fixed 19jan09
      Azalea_Code_128_B = temp & Chr(206) & Chr(196)
    Case 1 To 93
      Azalea_Code_128_B = temp & Chr(checkDigitSubtotal + 32) & Chr(196)
    Case Is > 93
      Azalea_Code_128_B = temp & Chr(checkDigitSubtotal + 103) & Chr(196)
  End Select

  ' The output, Azalea_Code_128_B, needs to be formatted in one of Azalea Software's Code 128 fonts.

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

End Function