Skip to content

Easily generate Code 128 barcodes in Excel

This simple methodology can be employed to reliably 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:

  1. Quiet zone
  2. Start character
  3. Encoded data
  4. Check character
  5. Stop character
  6. 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:

Step 1

Download the Code 128 barcode font and install in your fonts folder at c:\windows\fonts. (You will need administrator permissions to do this).

Step 2

Ensure that you have the Developer module enabled in Excel. If not, follow these instructions.

Step 3

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
X X X X

Step 4

In Excel, go to the Developer ribbon and choose “Visual Basic”.

Visual-Basic-Developer-Ribbon-Excel

Step 5

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

Step 6

Go back to your Excel sheet and insert the following formulae:

  • In cell B2 (“Barcode String”), insert =Code128([@Barcode])
  • In cell C2 (“Barcode Presentation”), insert =[@[Barcode String]]
  • In cell D2 (“Check”), insert: =IF(ISNUMBER(SEARCH("Â",[@[Barcode Presentation]],1)),"Error!","")

The formulae should copy down the entire columns. Save your sheet.

Step 7

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:

Barcode-Cells-Excel

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.

   

Comments:

28 responses to “Easily generate Code 128 barcodes in Excel”

  • Written by Wocky on 27 October 2015:

    Just wanted to say Thank You!

    Wocky.

  • Written by Molkaan on 17 November 2015:

    You just saved my life! Thanks man!

  • Written by JonM83 on 21 May 2016:

    Does this work when trying to pass special characters like a horizontal tab. I’ve researched that a horizontal tab can be produced in code 128 using ^9 or possibly ^09. I would like a barcode for “String” + Horizontal Tab + “String”. Thanks in advance for your help.

    -Jon

    • Written by adam on 7 February 2018:

      Did you ever figure this out? I’m struggling too.

  • Written by Henry on 23 May 2016:

    Really, Thank you!!!

  • Written by Zoe on 26 May 2016:

    Hi, I tried to follow this instruction but one error popped up when I entered the formula for C2. Excel said “the name that you entered is not valid. Reason for this can include: – the name does not begin with a letter or an underscore; – The name containes a space or other invalid characters; – the name conflicts with an Excel built – in name or the name of another object in the workbook.

    Also, the Barcode String content looks weird with some characters being replaced by “?”.

    Please help!!

  • Written by Tony on 27 May 2016:

    Works great, wonderful work. I made sure to cite this source. Thank you.

  • Written by Alfred Mend on 31 July 2016:

    Great website!
    This function works very well with almost everything that I tested.
    But it´s not generating a readable bar code 128 for some sequences like this one:
    35160756642960000452590000879700000291361960

    Can you try it?
    Tks

  • Written by RRock on 8 October 2016:

    Did not work. I followed the directions and generated a barcode but it would not scan. Thanks for the help anyway.

    • Written by Adam Dimech on 8 November 2016:

      Your printed bar code may have been too small or alternatively the code was too close to the edge of the label. There are minimum clearance zones and font sizes that are required for the bar codes to work. I have also encountered this when it’s been printed too small.

  • Written by Greg Witek on 10 December 2016:

    Great work here Philip! Thank you for the help. I am having trouble. There is a large space in the middle of my barcode when using the sequential values FP10001, FP10002, FP10003, etc. These are employee numbers so I have some control over them. So, I tried 10001FP, 10002FP, 10003FP, etc. and the problem is not as bad but many of the barcodes have a large blank in them.

    TIA.

  • Written by Bill on 9 January 2017:

    I am feeling simple after reading everyones positive results.
    I have Excel 2016.
    I tried to do the steps as indicated.
    In fields b2, c2, d2 I copied the formulas as directed and tje formula’s is now what is now displayed in b2, c2, d2. I did change the font for column C and it is displaying the formula as a barcode.

    No matter what number I put in b2 the barcode in c2 seems to represent the formula (=[@[Barcode String]]) as a bar code.
    Can someone help me with what I have missed. Thanks.

    Following is an example of what I see in excel
    Barcode Barcode String Barcode Presentation Check
    12345 =Code128([@Barcode]) =[@[Barcode String]] =IF(ISNUMBER(SEARCH(“”,[@[Barcode Presentation]],1)),”Error!”,””)

    • Written by Adam Dimech on 29 January 2017:

      It sounds like your cells may be formatted as text. Ensure that your cells are formatted as a number then try re-entering the formulae again. It should then work.

  • Written by Raj on 9 February 2017:

    I try this code in vb6 & barcode printing properly

    but not scanning by barcode scanner.

    not getting any error .

    i am stuck up.

    what should i do ?? plz help.

  • Written by Ahsanul Haq on 11 March 2017:

    Wow…works like a charm!!

    no need to use it only on tables. just use like a normal formula in Excel.

    =code128(Cell reference)

    Change font to Code 128.

    My one did not work intially as I used a code 128 font from someone elses website. Just use the font as ginve by the developer in links above.

    • Written by Dhan on 23 March 2017:

      I am kind of stuck. I tried to install the font Code 128. And tried to insert the entire code in to the Visual basic by inserting the module. Without formatting as table, tried to add a ABC123 in A2 and in c2 I tried giving =Code128([@Barcode]) and also =Code128(A2)

      But both returns error. I changed the formated the A2 as Number still no change. Can anyone plese elp me with this

    • Written by John Paul on 7 August 2017:

      Hi, I just want to know how can i input the formula to accept by Excel as i am seeing errors when typing this formula =[@[Barcode String]] . I’ve also tried to just input =[@[B2]] but it doesnt work. Please help.

      • Written by John Paul on 7 August 2017:

        i am using excel 2017, would it be okay?

  • Written by Jonathan on 31 March 2017:

    Hi,

    Thanks for the tip.
    I managed to build a sheet using this and combining it with mailmerge in word, for a laboratory application.
    How about publishing the your example Word mailmerge doc as well?

    One thing to check is that the paragraph character at the end of a line is not included. It’s best to change the font to any characters adjacent to the barcode mailmerge field to a normal text font. Otherwise, confounding characters will appear after the barcode in Word.

    • Written by Adam Dimech on 1 August 2017:

      Yes, you are correct. I have noticed the same think. I have been contemplating writing about the Word part of this process but I’ve not yet found the time.

  • Written by Roberto on 6 April 2017:

    Hi all!

    Well Done! Very useful!
    Only one thing: I would like to understand better the algorythm behind code128 VB function.
    Is very difficult to make it simple. I read about 128EAN specification, but I would like a simple translation. Why counter + 3 ? Why 4,6 ( due to 11 max lenght of a char in 0/1 codification for a 128 character??
    Why 205, 199, 204 Chr conversion?
    A flowchart would be nice :)
    Thanks to all!

  • Written by Sandor on 12 August 2017:

    Hi there!

    Thanks for this article, it was very useful for me. But I have problem: for example the “FA0175 12” Barcode value appears as “ĚFA-0175 12wΔ in the Barcode String cell. Or “3000-ARIA-60” get “Í> Č-ARIA-60[Δ. The Ě or Č or similar special characters appear in all my Barcode always.

    Could you help to find the solution for this error?

    TIA
    Sandor

  • Written by ttan on 8 February 2018:

    Hi Adam,
    thanks for publishing and sharing this approach.
    Followed and works great.
    Hope someone can throw some light to my problem.
    if the the input is a special character like “-“, the
    VB throws out as “-” (no coding )
    but the Code128 interprets as “/”.

    Any suggestion? Many thanks.

  • Written by Mike on 17 February 2018:

    Receiving an error
    The Syntax of this name isn’t correct.
    Verify that the name:
    -Starts with a letter or underscore
    -Doesn’t include a space or character that isn’t allowed
    -Doesn’t conflict with an existing name in the workbook

    I would like to barcode a series of sku’s similar to this one: 1-73Ghz_E6510_QC

    Barcode Barcode String Barcode Presentation Check
    1-73Ghz_E6510_QC =Code128([@Barcode]) =[@[Barcode String]] =IF(ISNUMBER(SEARCH(“”,[@[Barcode Presentation]],1)),”Error!”,””)

    The worksheet name is unrelated to anything in the formulas

  • Written by Luciano on 10 May 2018:

    Incredible solution… simple, perfect.

  • Written by Dan on 12 June 2018:

    Having trouble getting bar codes to scan. Everything looks good in the spreadsheet. When I print various sized code 128 fonts it won’t scan. I’ve used various apps to capture the barcode. Nothing. Any other suggestions for a code that seems valid but just won’t scan? Thanks!

  • Written by Aku on 2 July 2018:

    How can i encode

  • Written by Gerard on 22 August 2018:

    Hey just want to say thank you for the steps, now I wish I was proficient in excel to actually get this right, I have played but seems my knowledge of excel and the functions are not what I thought they were

    thanks again anyway will try get someone to do this for me

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.