Easily generate Code 128 barcodes in Excel
There are many methodologies published online for generating Code 128 barcodes in Excel. Unfortunately, most of them are complicated or don’t work in the later versions of Excel that most people use today.
A Code 128 barcode has six sections:
- Quiet zone
- Start character
- Encoded data
- Check character
- Stop character
- Quiet zone
The check character is calculated from a weighted sum (modulo 103) of all the characters. Because of this, the generation of Code 128 barcodes is not as simple as typing the number sequence into a programme using a barcode font. Attempting to do this with Code 128 barcodes will fail.
Because I recently had reason to generate Code 128 barcodes, I felt it would be valuable to publish my methodology, which relies on the work of several other people. Follow these steps in order to create your own Code 128 barcode generator in Excel:
Download the Code 128 barcode font and install in your fonts folder at
c:\windows\fonts. (You will need administrator permissions to do this).
Ensure that you have the Developer module enabled in Excel. If not, follow these instructions.
Create a new Microsoft Excel sheet. Create a table (making sure that you ‘format as table‘) with the following structure and headings:
|Barcode||Barcode String||Barcode Presentation||Check|
In Excel, go to the Developer ribbon and choose “Visual Basic”.
Right-click on “Modules” in the tree on the left and select “Insert Module”. Then paste the following code, which was written by Philip Treacy:
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
Go back to your Excel sheet and insert the following formulae:
- In cell B2 (“Barcode String”), insert
- In cell C2 (“Barcode Presentation”), insert
- In cell D2 (“Check”), insert:
The formulae should copy down the entire columns. Save your sheet.
Highlight Column C and change the font to “Code 128”. Now when you enter data into cell A2, a barcode should be displayed in cell C2 and so-on down the entire sheet.
If this doesn’t work, you may need to close and re-open Excel at this stage.
Some notes about usage
Unfortunately this script is not perfect and sometimes an a-circumflex (Â) character will be displayed in the middle of the barcode, particularly if copying numbers from other sources.
The formula in Column D is designed to display “Error” if this occurs so as to alert the operator. I added conditional formatting via the following rule:
=$D:$D<>"" so that errors will be displayed:
Often the number can simply be copied and pasted back into the same cell, or re-typed. This doesn’t happen often.
A practical application
Whilst it’s nice to be able to generate Code 128 barcodes in Excel, this isn’t entirely useful on a practical level.
After generating the barcode strings (for example “ÍKLÈ3.323LÎ”), these sequences can be copied and pasted into Word and the Code 128 font applied to them in order to generate a barcode. Unfortunately people need a human-readable number string beside a barcode, which means copying both the barcode string and the number sequence used to generate it. This is laborious and prone to error.
To get around this problem, I use a mail merge in Microsoft Word, combined with a sticker template to generate labels that contain both the barcode string (which is displayed in the Code 128 font) and the original number (which can be displayed in Arial, Times New Roman etc).
Mail merge can be tricky (a good subject for another post), but once mastered can make barcode generation very easy indeed. The other advantage with mail merge is that the barcode can be combined with other useful information on the label stickers in a manner which is efficient and unlikely to generate error.
Once the barcodes are generated in Word, they can easily be printed and affixed to whatever they’re designed to label.