Barcode Types & Uses
There are LOTS of barcode types used for a multitude of things. Various internationally recognised standards exist (and some not standardised) for things like retail stock tracking, mail and parcel tracking, ISBN numbers, warehousing, pharmaceutical packaging and many other uses.
Like creating secret spy messages to leave pinned up around the office 🙂
Their origin can be traced back to the 1960’s to a system used by the Association of American Railroads for tracking railroad rolling stock.
Barcodes became widely adopted when they started to be used to automate supermarket checkouts and quickly spread to many other applications. The first scanning of a UPC (Universal Product Code) barcode was on a packet of chewing gum in June 1974.
In this post I’ll be looking at two implementations, Code 39 and Code 128 barcodes.
This is an alphanumeric barcode that encodes uppercase letters, numbers and the characters " " (SPACE), - (minus), . (period), $ (dollar sign), / (slash), % (percent) and + (plus). This barcode can be of any length.
These are very easy to create as it essentially just requires a special barcode font. The string to be encoded must be wrapped in * to delimit the start and end of the string e.g. ABC123 must be encoded as *ABC123*, and the barcode looks like this
Code 128 was developed to reduce the amount of space required when compared to Code 39 ; it can be about 30% narrower.
It encodes the numbers 0-9, upper and lower case letters and standard ASCII symbols, and creates a variable length barcode.
Creating a Code128 barcode isn’t as simple as just using a special font. The input string must be encoded and a checksum is created for validation. The output from this encoding process is then rendered using a Code128 font.
Comparison of Code 39 and Code 128 Widths
As just mentioned, Code128 is about 30% narrower than Code39, so if you are creating barcodes for longer strings, use Code128.
Each character in Code39 is represented by its own section of the barcode – so it’s a 1 to 1 representation. Code128 can encode more than one character into a single section of barcode, hence making the barcode narrower compared to the same string encoded in Code39.
The following image shows the string ABC123 encoded in Code39 and Code128.
If you don't have a barcode reader to hand, you can download free barcode reader apps for your phone or tablet. Just do a search and you'll find several.
I used my Samsung Galaxy phone and a free app to test the barcodes as I wrote this and had no problems scanning the codes both from my computer screen, and off a printout.
If you do install a free app, let me know what the barcode beside the title of this post says.
Creating a Barcode With VBA
Installing Barcode Fonts
The first thing you need to so is download the fonts you need from these links. Right click the links and choose 'Save as' or 'Save target'.
Extract the files from the Code39 zip file using your Windows zip file manager or a utility like 7Zip. Once the files are extracted, find the file named free3of9.ttf, right click on it, and choose Install.
When this file is saved, right click the file code128.ttf and choose Install.
If you have Excel already open, restart it.
When Excel restarts you will see the two new fonts are available in the list of fonts on the Home tab in the Ribbon.
At its most basic, all you need to do is install the Free 3 of 9 font, then change the font in the cell(s) you want the barcodes to appear in to Free 3 of 9. In the same cell(s) enter your string, don’t forget that letters must be UPPER CASE, and the string must begin and end with an *.
You can enter your string into a cell, say A1, and then format A2 with the Free 3 of 9 font, and in A2 reference A1. This has the benefit of allowing you to see the string generating the barcode.
If you are typing into a cell formatted with the barcode font, you won’t be able to see what you are typing, so look at the formula bar to see what is being typed into the cell.
Code39 VBA Function
I’ve written a small function that takes your string as input, then outputs the string with all letters changed to upper case, and with an * as the start and end delimiters.
The function also checks that only valid characters are in the string. If there are invalid characters an error message is displayed, and an empty string is returned.
This just makes life a little easier.
Use the function as you would any other by typing into a cell like so and passing in a string (enclosed in "") or passing in a cell reference.
You must use VBA to create these barcodes. You also need to install the Code128 font.
In your first cell, enter the string you want to encode, then in another cell call the Code128 function passing in the first cell as an argument. You can also pass in a string (in double quotes "") as the function argument.
If there are any invalid characters in the string, an error is displayed, and an empty string is returned. So if the string does have invalid characters, the cell where you expect the barcode to be will look empty. The function is still there, there’s just no barcode displayed.
As with Code39, you can use a single cell to do all of this, however I find it better to have one cell with your string, and another with your barcode, so you can see what the string is that you are encoding.
Thanks to Matthew Welch for his Free 3 of 9 font. You can read Matthew's blog here http://www.squaregear.net/fonts/free3of9.shtml.
If you found this useful or know someone that would, please use the buttons below to share this on your favorite social network
Option Explicit Public Function Code128(SourceString As String) 'Written by Philip Treacy, Feb 2014 'http://www.myonlinetraininghub.com/create-barcodes-with-excel-vba 'This code is not guaranteed to be error free. No warranty is implied or expressed. Use at your own risk and carry out your own testing 'This function is governed by the GNU Lesser General Public License (GNU LGPL) Ver 3 'Input Parameters : A string 'Return : 1. An encoded string which produces a bar code when dispayed using the CODE128.TTF font ' 2. An empty string if the input parameter contains invalid characters Dim Counter As Integer Dim CheckSum As Long Dim mini As Integer Dim dummy As Integer Dim UseTableB As Boolean Dim Code128_Barcode As String If Len(SourceString) > 0 Then 'Check for valid characters For Counter = 1 To Len(SourceString) Select Case Asc(Mid(SourceString, Counter, 1)) Case 32 To 126, 203 Case Else MsgBox "Invalid character in barcode string." & vbCrLf & vbCrLf & "Please only use standard ASCII characters", vbCritical Code128 = "" Exit Function End Select Next Code128_Barcode = "" UseTableB = True Counter = 1 Do While Counter <= Len(SourceString) If UseTableB Then 'Check if we can switch to Table C mini = IIf(Counter = 1 Or Counter + 3 = Len(SourceString), 4, 6) GoSub testnum If mini% < 0 Then 'Use Table C If Counter = 1 Then Code128_Barcode = Chr(205) Else 'Switch to table C Code128_Barcode = Code128_Barcode & Chr(199) End If UseTableB = False Else If Counter = 1 Then Code128_Barcode = Chr(204) 'Starting with table B End If End If If Not UseTableB Then 'We are using Table C, try to process 2 digits mini% = 2 GoSub testnum If mini% < 0 Then 'OK for 2 digits, process it dummy% = Val(Mid(SourceString, Counter, 2)) dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 100) Code128_Barcode = Code128_Barcode & Chr(dummy%) Counter = Counter + 2 Else 'We haven't got 2 digits, switch to Table B Code128_Barcode = Code128_Barcode & Chr(200) UseTableB = True End If End If If UseTableB Then 'Process 1 digit with table B Code128_Barcode = Code128_Barcode & Mid(SourceString, Counter, 1) Counter = Counter + 1 End If Loop 'Calculation of the checksum For Counter = 1 To Len(Code128_Barcode) dummy% = Asc(Mid(Code128_Barcode, Counter, 1)) dummy% = IIf(dummy% < 127, dummy% - 32, dummy% - 100) If Counter = 1 Then CheckSum& = dummy% CheckSum& = (CheckSum& + (Counter - 1) * dummy%) Mod 103 Next 'Calculation of the checksum ASCII code CheckSum& = IIf(CheckSum& < 95, CheckSum& + 32, CheckSum& + 100) 'Add the checksum and the STOP Code128_Barcode = Code128_Barcode & Chr(CheckSum&) & Chr$(206) End If Code128 = Code128_Barcode Exit Function testnum: 'if the mini% characters from Counter are numeric, then mini%=0 mini% = mini% - 1 If Counter + mini% <= Len(SourceString) Then Do While mini% >= 0 If Asc(Mid(SourceString, Counter + mini%, 1)) < 48 Or Asc(Mid(SourceString, Counter + mini%, 1)) > 57 Then Exit Do mini% = mini% - 1 Loop End If Return End Function Public Function Code39(c39 As String) 'Written by Philip Treacy, Feb 2014 'http://www.myonlinetraininghub.com/create-barcodes-with-excel-vba 'This code is not guaranteed to be error free. No warranty is implied or expressed. Use at your own risk and carry out your own testing 'Input Parameters : A string 'Return : 1. An encoded string which produces a bar code when dispayed using the Free 3 of 9.TTF font ' 2. An empty string if the input parameter contains invalid characters Dim Counter As Integer If Len(c39) > 0 Then c39 = UCase(c39) 'Check for valid characters For Counter = 1 To Len(c39) Select Case Asc(Mid(c39, Counter, 1)) Case 32, 36, 37, 43, 45 To 57, 65 To 90 Case Else MsgBox "Invalid character in barcode string." & vbCrLf & vbCrLf & "Only use 0-9, A-Z, - + . $ % / and the SPACE character", vbCritical Code39 = "" Exit Function End Select Next End If Code39 = "*" & c39 & "*" End Function