Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's 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:

BarcodeBarcode StringBarcode PresentationCheck
XXXX

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:

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

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

On 27 October 2015, Wocky wrote: Hyperlink chain icon

Just wanted to say Thank You!

Wocky.

Reply

    On 22 September 2021, Nguyen Duc Hieu wrote: Hyperlink chain icon

    Thank you so much.
    It is wonderful!

    Reply

On 17 November 2015, Molkaan wrote: Hyperlink chain icon

You just saved my life! Thanks man!

Reply

On 21 May 2016, JonM83 wrote: Hyperlink chain icon

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

Reply

    On 7 February 2018, adam wrote: Hyperlink chain icon

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

    Reply

      On 27 November 2018, John wrote: Hyperlink chain icon

      Any luck in finding how to generate Tab inside string?

      Reply

        On 27 January 2023, BD wrote: Hyperlink chain icon

        I know it’s years ago that this comment was posted, but just for future reference:

        In my application I needed a TAB at the end of my barcode, to jump to the next cell. I managed to add that to it with a quick line of code. I am aware that below
        ‘oneliner’ has limitations, since it’s based on the assumption that Table B is used for the barcode, and it will add it only to the end of the barcode. But it worked for me/my barcodes and it gives others something to work with.

        TAB is only available in Table A. Since the macro uses Table B most of the time, you will need to switch to table A inside the barcode (which is control character 101, coded by Chr(201)). The HT character is 73, coded by Chr(105).

        So, I added the following line before the calculation of the checksum: [Code128_Barcode = Code128_Barcode & Chr(201) & Chr(105)], and that’s it. This will add the jump to table A, and the TAB character, after which the checksum is properly calculated. All barcodes scanned for me without any issues.

        Reply

On 23 May 2016, Henry wrote: Hyperlink chain icon

Really, Thank you!!!

Reply

On 26 May 2016, Zoe wrote: Hyperlink chain icon

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

Reply

On 27 May 2016, Tony wrote: Hyperlink chain icon

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

Reply

On 31 July 2016, Alfred Mend wrote: Hyperlink chain icon

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

Reply

On 8 October 2016, RRock wrote: Hyperlink chain icon

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

Reply

    On 8 November 2016, Adam Dimech wrote: Hyperlink chain icon

    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.

    Reply

    On 17 April 2019, Jonny wrote: Hyperlink chain icon

    Agreed. Seemed great but QR reader would not read the barcode generated

    Reply

On 10 December 2016, Greg Witek wrote: Hyperlink chain icon

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.

Reply

On 9 January 2017, Bill wrote: Hyperlink chain icon

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!”,””)

Reply

    On 29 January 2017, Adam Dimech wrote: Hyperlink chain icon

    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.

    Reply

    On 22 November 2018, JtrM wrote: Hyperlink chain icon

    I had the same issue with the formulas looking as you typed here. The fix for me was adding a space after the “=” for each formula in B2, C2 and D2.
    Hope that helps anyone trying to make this work!
    Thanks Adam for the tutorial and Philip for the code.

    Reply

On 9 February 2017, Raj wrote: Hyperlink chain icon

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.

Reply

On 11 March 2017, Ahsanul Haq wrote: Hyperlink chain icon

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.

Reply

    On 23 March 2017, Dhan wrote: Hyperlink chain icon

    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

    Reply

    On 7 August 2017, John Paul wrote: Hyperlink chain icon

    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.

    Reply

      On 7 August 2017, John Paul wrote: Hyperlink chain icon

      i am using excel 2017, would it be okay?

      Reply

On 31 March 2017, Jonathan wrote: Hyperlink chain icon

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.

Reply

    On 1 August 2017, Adam Dimech wrote: Hyperlink chain icon

    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.

    Reply

On 6 April 2017, Roberto wrote: Hyperlink chain icon

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!

Reply

On 12 August 2017, Sandor wrote: Hyperlink chain icon

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

Reply

On 8 February 2018, ttan wrote: Hyperlink chain icon

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.

Reply

On 17 February 2018, Mike wrote: Hyperlink chain icon

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

Reply

On 10 May 2018, Luciano wrote: Hyperlink chain icon

Incredible solution… simple, perfect.

Reply

On 12 June 2018, Dan wrote: Hyperlink chain icon

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!

Reply

    On 4 December 2018, chronos88sk wrote: Hyperlink chain icon

    having the same issue…. barcode is generated, but none of my 2 scanners is able to read it….

    Reply

      On 29 January 2019, Adam Dimech wrote: Hyperlink chain icon

      My guess is that you may not have enough whitespace (border) around each code. Try spacing things out a bit and see how you go.

      Reply

On 2 July 2018, Aku wrote: Hyperlink chain icon

How can i encode

Reply

On 22 August 2018, Gerard wrote: Hyperlink chain icon

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

Reply

On 8 November 2018, Tenichi wrote: Hyperlink chain icon

Can I input carriage return in Code for Microsoft Access? And How?

Reply

On 14 November 2018, Chris wrote: Hyperlink chain icon

Using Office 2016 and was getting “The syntax of this name isn’t correct” error. Used AHSANUL HAQ’s trick and was able to get it to work.

By simply putting =Code128(Cell Reference). Worked like a charm. Thank you all for the help and this amazingly simple approach.

Reply

On 8 January 2019, Joao Nicolau wrote: Hyperlink chain icon

Just to say thank you very much!
Very simple and understandable explanation.
It works perfectly!

Reply

On 6 February 2019, Reinaldo Caeiro wrote: Hyperlink chain icon

THANK YOU!
Very helpful.

Reply

On 8 March 2019, Craig wrote: Hyperlink chain icon

After I copy and past Module 2 and go back to the sheet to input the formulas, I get an error, “Ambiguous name detected: Code128” and the sheet does not work. Any suggestions?

Reply

    On 8 March 2019, Craig wrote: Hyperlink chain icon

    Disregard, figured it out. I had another module installed that was conflicting with this one. When I removed it and only had the above code in place it works.

    Reply

On 5 April 2019, Eduard wrote: Hyperlink chain icon

It is a fantastic work, helped me to. Thank you.

Reply

On 20 April 2019, Bill wrote: Hyperlink chain icon

THANK YOU! This worked exactly as it was written and worked the way I needed to.

Reply

On 25 April 2019, Nicholas wrote: Hyperlink chain icon

I have Excel 2016 and just got a new computer for work (the permissions scheme is different as our company changed ownership). I was running this code great on my old computer but for some reason it’s not working correctly on current machine.
First, per of I had to change the formula in colum B from: “=Code128([@Barcode])” to “=Code128(A2)” & the code in column C from: “=[@[Barcode String]]” to: “=B2” to avoid getting a formula syntax error that Excel would not let me over-ride.

But now the barcode in column C is not generating the proper string. I think it’s the leading character “Ì”. Sure enough, when I put this character in it is not generating the correct barcode for that individual character in the font (other characters seem fine though). I’ve reinstalled the font 3 times so I’m not sure why it would be mapping the incorrect barcode-“font” character. Of course, this manifests as being un-scannable by my scanners.

Any idea why it would be doing this? Is my change in syntax above to blame? Is there something wrong with my permissions/macros that would keep me from being able to run this VBA code?

Reply

    On 26 April 2019, Nicholas wrote: Hyperlink chain icon

    Actually, it does appear to have been my font: must have been corrupted causing the incorrect mapping.

    It appears my changes to the code do not affect the operation of this tool.

    Reply

On 13 May 2019, Dominic wrote: Hyperlink chain icon

Excellent job! Thank you!

Reply

On 26 May 2019, Robin Hoot wrote: Hyperlink chain icon

If you’re a VB noob like me, make sure you don’t have a name conflict. Don’t name the module Code128 since the sub procedure is already named Code128. You will end up with a #NAME? error as it is looking at the module first before the procedures within it.

Reply

On 6 June 2019, Shawn A wrote: Hyperlink chain icon

This is really great. Having a strange problem, though – the values I’m encoding are a mix of uppercase letters and numbers, sometimes with a dash. My sample set worked great. In my “real data”, though, the barcode is ending up encoding the text as lower-case, which results in my item not being found after scanned.

Ex: sample data: “DF002-D1” encodes correctly, scan finds the associated item. In the “real data”, this value ends up encoding as ‘df002-d1’ resulting a not found error after scan.

I have a development background, and work with Excel a lot. This really saved me a lot of time and was very educational, but I cannot figure out why this value is ending up with lowercase is happening.

Any hints or tips or path to go down would be greatly appreciated.

Thanks again.

Reply

On 18 June 2019, Alexis G wrote: Hyperlink chain icon

Worked like a charm

Thanks!

Reply

On 26 June 2019, Marcos wrote: Hyperlink chain icon

Hi there, after struggling for a while I came up with this formula to generate the string. Perhaps is helpfull to someone.

=”Ì”&A1&CHAR(MOD(SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))-32,ROW(INDIRECT(“1:”&LEN(A1))))+104,103)+32)&”Δ

Just paste the forumula in cell B1 (and asing a bar code true type font to it). Then write in cell A1 what you need to code.

Reply

    On 15 August 2019, Viktor wrote: Hyperlink chain icon

    Hi,

    Markos it works almost perfectly. Thanks a lot!

    I had only one problem.
    I am working with character combinations and in 90% of the cases it works correctly.

    For example these are ok:

    T00B0B4
    T00B04F
    T00AF4E
    T00AF5D

    But these are not:

    T00B05C
    T00B0C7
    T00AF5B
    T00B054

    Here the function puts some special characters and those are not compatible with the font type.

    Do you have any suggestions? Anybody?

    Thanks in advance,

    Reply

      On 23 July 2020, Mark wrote: Hyperlink chain icon

      Hi Viktor,

      If you are still looking for the answer to this, I have just come across this site looking to do the same mself and have managed to tweak Marcos’ code slightly to hopefully fix the issue:

      ="Ì"&A2&CHAR(MOD(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))-32,ROW(INDIRECT("1:"&LEN(A2))))+104,103) + (IF((MOD(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))-32,ROW(INDIRECT("1:"&LEN(A2))))+104,103)<95),32,100)))&"Î"

      This will hopefully resolve your issues with the check digit on certain codes. Also as per the comment by PERRY HOOGE, I think you may need a slightly different font download as the one in the link seems to be slightly corrupt. I used libre_barcode_128 and it worked a treat.

      Many thanks all!

      Reply

On 29 June 2019, Esteban wrote: Hyperlink chain icon

It’s works! Thanks you!

Reply

On 31 July 2019, B K wrote: Hyperlink chain icon

Thanks for helping me out with this. This was an efficient solution and I appreciate your coding expertise.

Reply

On 7 September 2019, Joe Fayed wrote: Hyperlink chain icon

I really appreciate you sharing this work; it really saved me a lot of time!

Reply

On 12 January 2020, Perry Hooge wrote: Hyperlink chain icon

This was incredibly helpful! Works like a charm! Careful, the web has a Code128 font that is corrupted. Found another copy that worked.

I do have a question though. Has anyone ever tried to get this working in MS Access? I’m not a great VBA coder so if anyone has this working in MS Access, please let the world know.

Reply

On 21 January 2020, Rasmus wrote: Hyperlink chain icon

It works great – but it is important to use a correct code128 font – first I tried it with another one I had found – that didn’t scan. Combined with the LoMag Barcode scanner on my Android phone – it’s really great. The scannings are automatically saved in a file and the file can be uploaded to your cloud-service. Like another user I couldn’t make the original formulas work – but I just refererred to the cell names A2, B2, C2 etc. instead – just using the Code128 function to generate the string of (weird) characters. Thanks!

Reply

On 4 February 2020, bayamon wrote: Hyperlink chain icon

It work really well, but I have one issue: How can I encode a CR character?

Reply

On 26 March 2020, Jorge MF wrote: Hyperlink chain icon

Thank you very much. It’s very useful.

Reply

On 30 May 2020, Nick wrote: Hyperlink chain icon

Does this work on MS Access? Would love the same guide but for MS access :)

Reply

On 5 June 2020, Carlos wrote: Hyperlink chain icon

Thank you for posting this code. It worked really good. Has anyone found a solution for the  error everything you had two zeros together? For example MC40100X144. Tried typing them instead of pasting, cell changed to =Code128(A2) etc but anything with a 00 somewhere in the middle generates that error.

Thank you in advance.

Reply

On 21 August 2020, DWL wrote: Hyperlink chain icon

Everything worked great except the barcodes won’t scan. Tried different sizes, made sure there was a enough white space. Still won’t scan. Any one have any ideas?

Reply

On 10 September 2020, Balogh András wrote: Hyperlink chain icon

Thank you for your work!
Unfortunately, the barcode is unreadable to me. First, I had to understand the difference between a generated barcode and a readable barcode. I found that the start and stop characters are different, my barcode uses code B instead of code A. Something about the check character is also different for something. On Wikipedia, I found the right characters as well as the calculation method.
After that I fixed the VBA code which now only uses Code B.
The next is to see if it helps anyone else.

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

‘Create CODE_128B barcode
For Counter = 1 To Len(SourceString)
dummy% = Asc(Mid(SourceString, Counter, 1))
dummy% = IIf(dummy% < 127, dummy% – 32, dummy% – 100)
If Counter = 1 Then
CheckSum& = dummy%
Else
CheckSum& = CheckSum& + (Counter * dummy%)
End If
Next
CheckSum& = (104 + CheckSum&) Mod 103
If CheckSum& < 95 Then
CheckSum& = CheckSum& + 32
Else
CheckSum& = CheckSum& + 100
End If

Code128_Barcode = Chr(209) & SourceString & Chr(CheckSum&) & Chr$(211)

Code128 = Code128_Barcode

End If
'Modify by András Balogh, Sep 2020
End Function

I would also like to share this Excel function which has the same result.
="Ñ"&A1&CHAR(MOD(SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-32,ROW(INDIRECT("1:"&LEN(A1))))+104,103) + (IF((MOD(SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-32,ROW(INDIRECT("1:"&LEN(A1))))+104,103)<95),32,100)))&"Ó"

Thanks
András

Reply

On 16 November 2020, Iqbal Hossain wrote: Hyperlink chain icon

is it any option like this type easy for QR CODE

Reply

On 4 December 2020, Mark Lobsinger wrote: Hyperlink chain icon

Hello Adam,
Thanks for your code it works great. There were a few people on in this thread who asked about adding Control Codes (ie Tab or Enter). Is this possible in your code if so how would it work? Great Job and thanks for sharing.

Reply

On 7 January 2021, Jerry Whidby wrote: Hyperlink chain icon

I found this solution to be easier. Search for “Generating Barcodes For a Mail Merge in Word 2017” on YouTube. It’s a video by Shane Rosenkrantz . No VBA required.

Reply

On 8 January 2021, Serge Marcotte wrote: Hyperlink chain icon

It is working fine but I just cannot have this “@” in a scannable string… no matter what I try, code128 or code39 full ASCII. I need it to write down email address in fast registration of product for many email adresses.
Do you know how to do it?
Thank you in advance.

Reply

On 3 February 2021, Shoji Nishikawa wrote: Hyperlink chain icon

Thank you so Much !!! Your a life saver for my project
I did have to change around the 128 fonts to match our system, however it work like a charm !!!!
Thanks again

Reply

On 15 March 2021, siddharth chopra wrote: Hyperlink chain icon

Worked perfect for me !!! The way I used it was

A2 has the string you want a barcode for Cell format should be Number.
B2=Code128(A2) Cell format should be Number
C2=B2 Cell format should be Number and Font should be Code128 font

Reply

On 6 July 2021, Jan wrote: Hyperlink chain icon

Another solution: go to (for example) http://generator.onbarcode.com/online-code-128-barcode-generator.aspx , create a barcode with the options you need, copy the link address of the barcode picture like:

http://generator.onbarcode.com/linear.aspx?TYPE=8&DATA=1234567&UOM=0&X=1&Y=60&LEFT-MARGIN=0&RIGHT-MARGIN=0&TOP-MARGIN=0&BOTTOM-MARGIN=0&RESOLUTION=72&ROTATE=0&BARCODE-WIDTH=0&BARCODE-HEIGHT=0&SHOW-TEXT=true&TEXT-FONT=Arial%7c9%7cRegular&TextMargin=6&FORMAT=gif&PROCESS-TILDE=false

paste it in cell A2 and change it to an image formula with the reference to A1 (after the text: DATA)

=IMAGE("http://generator.onbarcode.com/linear.aspx?TYPE=8&DATA="&A1&"&UOM=0&X=1&Y=60&LEFT-MARGIN=0&RIGHT-MARGIN=0&TOP-MARGIN=0&BOTTOM-MARGIN=0&RESOLUTION=72&ROTATE=0&BARCODE-WIDTH=0&BARCODE-HEIGHT=0&SHOW-TEXT=true&TEXT-FONT=Arial%7c9%7cRegular&TextMargin=6&FORMAT=gif&PROCESS-TILDE=false")

Write your text or number or combination in A1.

Reply

On 10 December 2021, Aaron wrote: Hyperlink chain icon

Huge thanks to you and the others for this. Life saving!

Reply

On 15 January 2022, Jay wrote: Hyperlink chain icon

This really came in handy for huge training project at work. Thanks you so much for this!

Reply

On 20 January 2022, PRAMOTE wrote: Hyperlink chain icon

Thank you, just ensure the Current language for non-Unicode programs is English. Great work!!!!

Reply

On 23 January 2022, Michael Anzalone wrote: Hyperlink chain icon

Hello Philip,

First – thank you for this code, it is very helpful. While using it recently, I found two consistent encoding errors, and have solved one of them so far.

The first problem is that in my experience, the checksum is sometimes incorrectly encoded as a space. Here is my modification to your code:

‘Calculation of the checksum ASCII code
CheckSum& = IIf(CheckSum& < 95, CheckSum& + 32, CheckSum& + 100)
If CheckSum& = 32 Then CheckSum& = 194 'This line added 22-Jan-2022 by MJA
Add the checksum and the STOP
Code128_Barcode = Code128_Barcode & Chr(CheckSum&) & Chr$(206)

An example of the data I am encoding is: AT129D1 – without the added line, the data is improperly encoded with a space (gap) in the barcode.

The second problem I have noticed is that numeric data beyond 3 digits does not encode properly. I haven't delved into the reason(s) why, but I thought I'd let you know about my experience. So, "123" encodes properly, but "1234" does not.

Again, thank you very much for the code – it is much appreciated!

Best,
Michael Anzalone

Reply

    On 25 March 2022, Chris wrote: Hyperlink chain icon

    Thank you for fixing this! The code works 100% for me now. Saves me a bit of time with manually generating replacement barcodes for space errors.

    Reply

    On 7 June 2022, Mel wrote: Hyperlink chain icon

    As an addition on Michael Anzalone’s solution: every double zero in sourcestring, starting on odd places (i.e. 550055, position 3 and 4 is zero), means a value of zero for dummy%, which is converted (by adding 32 in the calculation) to the equivalent of a space. Same as the checksum in the example of Michael.
    To prevent the space in the barcode i’ve added 2 extra lines of code.

    In the creation of the barcode string i change the space chr(32) to chr(194):

    dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 100)
    If dummy% = 32 Then dummy% = 194 'prevent spaces in barcode MEL
    Code128_Barcode = Code128_Barcode & Chr(dummy%)

    For the checksum calculation the value of zero is actually needed if you want the right checksum so here i change the value back from 94 (calculated value) to zero:

    dummy% = IIf(dummy% < 127, dummy% – 32, dummy% – 100)
    If dummy% = 94 Then dummy% = 0 'use zero in checksum calculation MEL
    If Counter = 1 Then CheckSum& = dummy%

    I know it's a little quick and dirty and there is probably a cleaner way to fix it, but so far it does the job and even though the Check in the table shows an error there isn't any. The  (which is causing the space problem) isn't a problem anymore…

    regards,

    Mel

    Reply

On 4 March 2022, Max wrote: Hyperlink chain icon

genius. 2022 and still relevant. a big thank you

Reply

    On 12 December 2022, Mathew wrote: Hyperlink chain icon

    is it possible for someone to write a vb script for the check sum of 128c for bartender ?

    Reply

On 25 March 2023, Alex (Australia) wrote: Hyperlink chain icon

This is a fantastic tool and a great little tutorial, there was only one (1) section that I made an error in, I created the module in the wrong open workbook (oops). Some issues might be seen when saving as a macros enabled file, but otherwise very well explained for my level of excel knowledge (not very advanced).
Thank you for the cut and past script.

Reply

On 4 April 2023, Chris wrote: Hyperlink chain icon

This is fantastic, thank you so much.

I’ve been using this with a wireless barcode scanner to walk around our station scanning barcodes – it adds them to column A and reproduces the barcode so I can either copy/past the barcode from column 8, or simply scan it into other applications from my desk. The amount of effort this will save me is phenomenal.

Reply

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.