Create Barcodes With (Or Without) Excel VBA

Philip Treacy

February 27, 2014

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.

VBA Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Code 39

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

Code39 Barcode

Code 128

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.

Code128 Barcode

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.

Comparing Code39 and Code128 Barcode Widths

Barcode Readers

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.

Some Fun

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'.

Code39 :

Code39 zip file.

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.

Code128 :

Code128.ttf

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.

Code39

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.

Using VBA Function to Create Code39 Barcodes

Code128

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.

Using VBA Function to Create Code128 Barcodes

References

Thanks to Matthew Welch for his Free 3 of 9 font.

JT Barton provided the Code128 font and I've modified the Code128() function based on code he modified from Grand Zebu.

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

6 Ways to Fix Dates Formatted as Text in Excel

Excel Designed Minecraft Cake

177 thoughts on “Create Barcodes With (Or Without) Excel VBA”

  1. Hello Philip, I greatly appreciate your contribution, it worked wonderfully for me, therefore as it is a community I would like to make a contribution because I see that some members of this community need the EAN13 format, here I am sending you an excel form hoping it will be useful to you.

    Note: I could not upload the file but if anyone likes it, they can send me a request to my email and I will gladly share the excel form for EAN 13

    Best regards to the entire community.

    Reply

  2. Hello!

    First of all, your work is excellent! I was able to transform 90% of my product codes in barcodes code 129.
    BUT I get blank spaces. I tried an older version of your code and the newest excel from this website.
    Three examples of problematic code would be:
    6425767000133
    0066-20
    120L 10S

    Images with screenshots can be found here
    https://i.postimg.cc/D0TysXH1/2022-03-25-17-03-00.png
    https://i.postimg.cc/C1DxzNPF/2022-03-25-17-07-14.png

    I would really like some help, and it would be appreciated immensely.

    Cheers!

    Reply

    • Hi Max,
      I cannot replicate your results, please provide more details about your environment: excel version, windows version.
      You can use our forum to create a new topic, you can upload there sample files.

      Reply

  4. good morning, I have a problem with the code length of the characters # and and? how to do it

    Passing a Cell Reference to the Function
    String Encoded String Code39 Barcode
    12345 #NAZWA? #NAZWA?
    ABC123 #NAZWA? #NAZWA?
    A B C #NAZWA? #NAZWA?

    Reply

    • Hi Adam,

      Sorry I’m not following what you mean by the code length.

      If your input is 12345 then the encoded string should be *12345* – why does your table show #NAZWA? ?

      regards

      Phil

      Reply

  5. Hi, i’ve got a problem where this code is working fine on 2 PCs (Both Windows 10) but not working on another windows 10 PC.

    The file is the same file (document on the network) using Excel 365.

    I tried copying and pasting as values from the barcode on both PCs;

    On a working PC 271802 = Í;2″gÎ
    On non-working PC 271802 = ?;2″}? where “?” = the diamond with question mark in it.

    Note I have copied and pasted the value from the working PC to non-working to verify the character sets aren’t somehow missing and it displayed the same on both PCs.

    Any ideas please?

    Reply

      • Hi Evan,

        The font on the non-working machine doesn’t support accented characters. It’s displaying the black diamond instead. The encoded string should look like this

        bar code example

        That said, that shouldn’t affect the barcode as it’s a different font, but there’s clearly something different about the non-working PC. Hard to troubleshoot without being at the PC. What is different about it compared to the working PC’s? Same version of Office? All running 64/32 bit? Same regional settings?

        Regards

        Phil

        Reply

        • Hi, we have checked, same regional settings (English – UK) Same version of office (365 v2104) 64 bit PCs.

          We also re-installed office on the effected machine.

          And to be clear, if I copy and paste the text from one PC to another the accented characters display fine, they only do not display when using the UDF code.

          Reply

          • Hi Evan,

            How do you copy/paste from one PC to another?

            Sorry I don’t know why this one particular PC is having issues and all the rest are not.

            Regards

            Phil

          • Hi Coen,

            Evan never responded to say what the issue was so I’m afraid I don’t know why he was having this problem.

            Regards

            Phil

        • Hi Phil

          I have the same issue as well.
          I’m sharing the same file containing the barcode function on both computers (my laptop and remote desktop). The barcode works on the remote desktop but not on my laptop.

          The problem seems the symbols generated from the vba do not display correctly (chr(199), chr(200), chr(204), chr(205), and chr(206)), which causes the barcode not readable. I have tried to generate these symbols with the function Char() in Excel and it gives the same result. I believe it is something to do with Windows, even though I have not done any changes to the settings before this issue.

          Using UNICHAR() function could rectify this issue in Excel, and I believe a similar function in vba is ChrW(). So I’ve tried to replace Chr() with ChrW() in your vba code, and it does correctly generates the symbols. However, when I replace Chr(200) with ChrW(200), it makes the whole vba collapse.

          could you be able to help on this? I think this may solve this issue for good.

          regards

          Allan

          PS: I don’t know how to add screenshot here, otherwise it maybe easier to understand

          Reply

        • I forget to mention. I can copy those symbols from the remote desktop to my laptop, and they can be correctly displayed. it just has problems when generate from formula or vba

          Reply

  8. Hi Philip,
    I am using my Excel 2016 on Mac. I had success in using the Code39 by the free3of9.tff, unfortunately no good news on code128. I installed the code128.ttf, run your xlsm file, enabled the macro but it shows the same characters on the sheet
    12345 Õ,B»5VŒ Õ,B»5VŒ
    ABC123 ÃABC123cŒ ÃABC123cŒ
    A B C ÃA B C”Œ ÃA B C”Œ
    I tried also to find the code128.tff specifically for mac, but it didn’t make any change.
    In the begging the barcode code128 shows the bars and in the end the letters CE, I installed your code128.tff now shows only the alphabetic characters.

    Reply

    • Hi Lee,

      Some obvious questions, but, the Code128 font is installed on your Mac and you can see it in the list of fonts in Excel? And you have selected the cells where the barcode is generated and those cells are set to the Code128 font?

      It is possible to select part of a string in a cell and set it to the barcode font and the rest to a ‘normal’ text font. So can you delete the affected cells and try again.

      Phil

      Reply

    • Thanks Phil for your fast forward.
      I think the mistake had happened when I installed the code128 for the second time without attempting to the mac warning message about duplicate issues on this font.
      After solving it, the font code128 in Excel shows in barcode picture. However my barcode has some bizarre characters when I use the =code128(A1) or code(“ABC123”). In case I don’t use your macro, the barcode is unreadable. I already format the cell in code128

      Reply

      • Hi Lee,

        I don’t know what is wrong for you. If the font is installed and applied to a cell then it should show as a barcode. It may be that there is some code in the CODE128 function that is not working on the Mac. Can you test on a Windows PC?

        Regards

        Phil

        Reply

  9. Hi,

    I am having trouble with what I think are the macro securities… I have the VBA code correct in the excel workbook that your link provided and the barcodes work and scan well with that file, however, I’ve attempted to use my own excel sheet with your VBA code and code128 font and it works until I close it and try to reopen. At this point, the file has been untouched since last opening but the barcodes will not work. I get a #NAME? error in the cell when I try to update the barcode (when switching to a readable font). The macro settings are the same for your provided workbook and the one I’ve created. My macro settings are “disable all macro settings without notification”. I don’t get a notification pop-up with the excel sheet I created… any ideas on how to fix?

    THANKS!

    Reply

    • Hi,

      If you have your macro security set to ‘disable all without notification’ then the barcode macros won’t work and you’ll get the #NAME errors. So just change your macro security to ‘disable all macros with notification’.

      Phil

      Reply

  10. When I use the following code

    =CONCAT(“*BC”,A19,”$M”,A27,”*”)

    where cell A19 is BC2 and cell A27 is 700, the barcode returns

    BC2$M700

    or $M if I use that instead.

    If I use /M or %M it just puts that in the text and gives me no carriage return. I have tried using both 3 of 9 and 3 of 9 full ascii, but it make no difference.

    Reply

    • Hi John,

      You’ve haven’t explicitly said what you are trying to do, but I assume you are trying to insert a carriage return into the bar code?

      The code39 function doesn’t encode non-printing characters like CHAR(13) which is a carriage return.

      If you enclose something in double quotes, Excel treats it as a string. I’m not sure what the $M %M or /M are intended to do?

      Regards

      Phil

      Reply

  11. Hi There!
    Thank you so much for sharing this code with us. While it works perfectly on my PC, it does not display the same output on my co workers PC. We have the same operating systems but the barcodes in his computer come up with blank rectangles in the middle. Any idea where we could be going wrong?
    Any help will be much appreciated. Thank you

    Reply

  12. Im using this excel function in combination with code 128 font to create code 128 barcodes without using VBA. It might be usefull to you….

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

    Reply

    • How can i try this ?? I have Excel 2019 (Office 365)

      I try to put the string on A2 cell, but an error appear with A1 cell “..CODE(MID(A1,ROW(INDIRECT(“1:”&LE..”

      Thanks

      Reply

      • Hi Andrea,

        Difficult to say what is going wrong as we can’t see your workbook and formula. You can create a topic in the forum if you want and supply your workbook there.

        A thought : if you copied/pasted the formula Marcos created, check that the double quotes are straight " not slanted ”

        Cheers

        Phil

        Reply

  14. is there a max character for either one of the Code? (both 39 and 128)?
    also how can I save the VBA code for all my excel or MS office suites? or do I have to copy and paste the code every time?

    Reply

    • Hi Donna,

      Access supports VBA so this code should work in theory. have you tried it? Not sure what you mean by a report of barcodes.

      Regards

      Phil

      Reply

    • Hi,

      Because the barcodes require a font, I imagine you’ll need that fort in Publisher for the barcodes to work. If you can’t do this, you could export the barcodes as images and get them into Publisher that way.

      Regards

      Phil

      Reply

  20. I downloaded the code in a xlsm workbok and nothing happened? Can you assist me. What do I do next to Open workbook code? Thank you Angel

    Reply

    • Hi Angel,
      Make sure you allow macros to run.
      If you want to see the code, press Alt+F11 to open the visual basic editor, but not sure what you want to see there. As it says in the article:
      “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.”
      You will have to review the article to understand what you need to do to make it work.
      Regards,
      Catalin

      Reply

    • Hi Jai,

      There are several apps that will run on Android for scanning barcodes. Just search for them and pick the one most suitable for you.

      Regards

      Phil

      Reply

  22. Hi Phil, this is a great tool and guide. Do you know if its possible to generate a EAN 128 barcode, so it is read as ]C1 instead of ]C0, which is standard Code 128?

    Thanks

    Steve

    Reply

  23. Thank you for the code and example workbook. It’s impressive and a great help.
    Question: I programmatically create a worksheet as: Column A – The item codes, say G00012.02\S in Cell A1, G00012.02\M in Cell A2, G00012.02\L in cell A3 and G00012.02 in cell A4. In columns B and C the barcodes are generated using your function (128) and in column D, next to each barcode I enter the number of labels I want to print in each size (these are garment barcodes). Is there code that will send the barcode printer the instruction of how many of each to print?

    Reply

    • Thanks Ivor.

      To tell the printer how many to print, you can do this in the printer dialog box before you send the print?

      But are you looking to use a macro to do this? If so can you please open a forum post and supply your workbook so we can take a look.

      Phil

      Reply

  24. Hello Philip,
    Your guide really helped me in utilizing and understanding Code128.
    I have one question regarding group separator (ASCII 29) in code 128.

    I’ve tried changing the VBA code, adding “29” in the code snippet: “Case 29, 32 To 126, 203” to be used, but the font will just show the result as an empty rectangular shape.

    Do you have any suggestion to this?
    Thank you very much.

    this is the sample code: 1720062710K1806000121180612891. Please note that there is a between “10K18060001” and “21180612891”.

    Reply

    • Hi Christian,

      The group separator should be represented by ^] in your string e.g. ABC^]123 separates ABC and 123.

      You don’t need to alter the VBA for this. Once the barcode is produced your scanner should recognise the group separator, if it doesn’t then the reader software isn’t reading it properly.

      Regards

      Phil

      Reply

  25. Hi Philip,

    I too am having problems reading code128 with my scanner in my excel document. The barcodes are being displayed in my excel document but cannot be read. It can read your barcode and when I scan it into the column that has font code 128 it will display the barcode, when scanned into a normal font column it displays ABC123. So I can’t see why I can’t read them once in my excel document?

    Thanks

    Reply

    • HI Jules,
      Not very clear what you’re doing. Why would you scan into a normal range with no barcode font applied? What do you mean by not beeing able to “read them once”? Try to print the barcode generated then see if your scanner can read it.

      Reply

  26. Hi Guys

    Thanks for the tutorial, unfortunately then Code 128 process is not recognised by my scanner (Symbol LS2208) I have used the “enable code 128” function in the scanner.

    the scanner recognises Code39 easily enough, but still not Code 128, so I’m not sure if it is generating the check digit correctly or not. Any ideas trouble shooting idea’s would be welcome, using office 2010

    Reply

    • Hi Murray,

      Can you please start a topic on the forum and attach the workbook you are using to create the barcodes, and a screenshot of the Code128 barcode(s) that are not working. I’ll take a look at it then.

      Cheers

      Phil

      Reply

  27. Thanks for the code!!! I modified the code so that it can easily be used in Excel or Crystal Reports (As a Crystal Custom Function – Basic Syntax). I removed the gosubs, and the % and & after the variable names. Have fun with it!

    To use it:

    Comment out the Option Explicit line at the top for Crystal Reports
    Comment out or un-comment the four variable declarations (Number vs Long)

    Option Explicit ‘ Comment out this line for Crystal Reports

    Function Barcode128(SourceString As String)
    ‘Written by Philip Treacy, Feb 2014
    ‘Modified by John Arnold for Crystal Reports or Excel, Jan 2018
    ‘https://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

    ‘ Uncomment for Crystal Reports
    ‘Dim Counter As Number
    ‘Dim CheckSum As Number
    ‘Dim mini As Number
    ‘Dim dummy As Number

    ‘ Uncomment for Excel
    Dim Counter As Long
    Dim CheckSum As Long
    Dim mini As Long
    Dim dummy As Long

    Dim UseTableB As Boolean
    Dim Code128_Barcode As String
    Barcode128 = “”
    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
    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), 3, 5)
    'if the mini% characters from Counter are numeric, then mini=0
    If Counter + mini = 0
    If Asc(Mid(SourceString, Counter + mini, 1)) 57 Then
    Exit Do
    End If
    mini = mini – 1
    Loop
    End If

    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
    End If

    If Not UseTableB Then
    'We are using Table C, try to process 2 digits
    mini = 1
    'if the mini% characters from Counter are numeric, then mini%=0
    If Counter + mini = 0
    If Asc(Mid(SourceString, Counter + mini, 1)) 57 Then
    Exit Do
    End If
    mini = mini – 1
    Loop
    End If

    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
    End If
    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
    Barcode128 = Code128_Barcode
    End Function

    Reply

  29. Thanks for sharing, but it’s not working for me. I constantly get erroneous characters not supported by Code 128 function (for ex. Ě, Č)
    Therefore I can’t use this barcode. Is there any way I can fix it. Add those characters to my Error list. If so, where can I change code of Code128 function?
    VisualBasic in Developer ain’t doing the trick.

    Reply

  30. Hi, thanks so much for making this available for free!
    On MS Windows it seems to work fine, but using Excel for Mac I’m getting a different result.

    These cells:
    12345
    ABC123
    A B C

    Get “translated” to this:
    Õ,B»5VŒ
    ÃABC123cŒ
    ÃA B C”Œ

    However, as I’m planning to use it on Windows, it’s no problem for me. Just thought I’d let you know. Once more: thanks!

    Reply

    • Hi Adriaan,
      Thank you or pointing out this problem.
      I think it’s related to the font, did you installed a compatible font for Mac? You can find Mac fonts for barcodes here.

      Reply

  31. Hello all,

    have a Q? … Would very much appreciate a solution.
    Have no drama creating Code 128 barcodes in Excel.
    However .. require to move the encoded string to Filemaker Pro for printing.
    What format does the saved output from Excel require to be, to work properly in Filmaker pro
    as an imported file and be printed using the Code128 font?
    Thanking all for their thoughts

    Alec

    Reply

    • Hi ALec,

      I’m not familiar with Filemaker Pro. But as the barcode is just text, with a barcode font, can you take the barcode string into FMP and then change the font for the text?

      Or you could save the barcode as an image and import that to FMP?

      Regards

      Phil

      Reply

      • G’day Phil,

        I thought the same … but it just is not so.
        The actual characters generated by the string do not transfer into FM … other than via a UNITEXT import. But even with this …allocation via Code128.ttf does not generate a printable barcode. Some of the characters remain as portion of the string.
        Very puzzling?

        Reply

        • Hi Alec,

          Is saving the barcode as an image and then bringing that into FM an option? You can copy/paste as image inside Excel, and record/write a quick macro to do this if you have lots of barcodes to create.

          Phil

          Reply

  33. Hi everyone,

    I tried to install the font but when I click on install a message appear telling me that the font seems to be not valid (it is translate from french).

    Do you know how to solve this issue ?

    Thank you all

    Reply

      • Both is not working.
        And for the Code39 is well extracted before but not working.

        Is this font is applicable for any version of Microsoft Office ? Should I put the .ttf file in a specific document ?

        Thomas

        Reply

        • Hi Thomas,

          Can you please create a post on the forum and include a screenshot for the error(s) you are getting when you install the fonts.

          They are True Type Fonts (ttf) so should work with any modern version of Windows.

          What exact steps are you taking t install the fonts?

          Regards

          Phil

          Reply

          • News !

            To install the font you have to be administator of your computer.

            So it is working well for me.

            Thank you Phil

  35. Hi, looks like you’ve done a fantastic job and thanks for sharing it. I have to admit I’m new to barcodes and have no experience with basic. A customer has asked my company to put a 128 barcode on a label for them and we don’t want the shop floor to have to do anything more than enter a couple of details about the order into a crystal reports viewer. Would you know if there is a way for this code to work in a formula in crystal? Please excuse my ignorance as I’m still learning (a lot of stuff).

    Any pointers would be greatly appreciated!

    Reply

    • Hi Patrick,

      Well done on learning new things 🙂

      I don’t know Crystal Reports but as this code is written in VBA, I imagine it would need to be rewritten in something that CR can execute. Not even sure if that is possible.

      Sorry I can’t be of more help.

      Regards

      Phil

      Reply

      • Thanks anyway Phil.

        I’m only new to Crystal Reports myself. It appears to take two syntax options, crystal or basic, so I thought it might work in a similar way but the function editor doesn’t give you a whole load of useful feedback so it’s a bit of an uphill battle without training on it.

        Anyway, I’ll let you know if I get anywhere with it.

        Thanks again,

        Patrick.

        Reply

  36. How do you add Tab and return to a barcode string, I am trying to make barcodes that will enter data into a field tab to a second field enter data then enter a return to accept it.

    Reply

    • Hi Nathan,

      I don’t know a way to do that in Excel. Even if you enter something like 

      =CONCATENATE("abc",CHAR(9),"def")

      the tab char in the middle does not cause 

      def

      to be entered into the next cell.

      Usually this type of input would be managed by a barcode application, a program bought with the barcode scanner and designed for inputting data into multiple cells.

      It might be possible to write something in VBA but we’d need to know exactly what it is you want to do, see example workbooks etc.

      Phil

      Reply

      • Dear Philip and Nathan,

        First of all, great job with this script and thanks putting it online. I run into the same problem as Nathan. On the program side, they sometimes need to scan a text or numbers but sometimes they only need to use the enter possibility. Only in case of deviation, they need to go to a keyboard.. Therefore I wanted to add a Carriage return to each barcode while if I add that automatically with the scanner, he gives 2 return when only scanning 1..

        with the tilde function on some websites he incorporates (I think) an ascii caracter in the barcode (like you suggested, phil).

        On wiki I see that Table B does not have carakters 10 or 13 but starts with 32. Type C and A starts with 00. If I let a 10 carakter go through the script (first part, just passing it through) than it does not set “UseTableB” to false.

        I do not fully understand Barcode 128 yet. Untill now, I worked with 39 which is very simple, but does not give me the carriage return possibility… Maybe any thoughts how to include a carriage return?

        Reply

  37. Hello Phillip,
    thank you very much for this article , is really helpful
    I still need to print the code128 barcodes with specific dimensions – do you have any idea how can i do this? when i change the fonts size in excel i get an outcome that is not what i need
    thank you

    Reply

    • Thanks Kel, you’re welcome.

      What dimensions do you need the barcode to be? By its nature the bars in the code must be a certain width, so there’ll be a point where the font is so small the code can’t be read.

      Phil

      Reply

  38. Hello Philip

    I am using Code39 function to have my field in excel converted in to bar code. It does the required field in to bar code but I see *2333* under the bar code for the string 2333. I am confused whether ** is included in the bar code or it is just the 2333

    Reply

    • Hi,

      The * are the delimiters, they mark the start and end of the barcode. So yes they are included in the barcode. But when you scan the code, it will just be read as 2333.

      Have you downloaded the example workbook and used that to create your barcodes?

      Regards

      Phil

      Reply

  39. Hello Philip,

    Thanks for the great article. But 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. Then tried to add a ABC123 in A2 and in B2 I tried giving =Code128(A2)

    But it returns error as #NAME? in B2. I changed the formated the A2 as Number still no change. It doesnt recogonize the function code128(). Can anyone please help me with this

    Reply

  40. Good day

    I downloaded everything necessary and I got things to work as intended. THANK YOU!!!

    however I am using Code 128 and I need to squeeze in 20 characters within a 40 mm wide label.

    Up to font size 24 it works fine but I cannot get it to scan using a smaller font size. I will need to use a font size of 14 or so. The label printer is using 203 dpi.

    When I try with my standard 600dpi printer it works fine.

    I know there is a smaller size that works. I use that size when I print labels directly from printer’s label software and it scans perfectly.

    Do you know the smallest font size that CODE 128 will work with using your Excel VB ? (using a 203 dpi label printer)

    Reply

    • Hi Ahsan,

      It sounds like you’ve already found the answer? If 24pt works on the 203dpi printer but nothing smaller, then 24pt is the smallest font that printer can print legibly for the scanner to scan. It’s looks like the printer is the issue as it is only 203dpi, but your 600dpi printer works fine.

      Though I am a bit confused. If you can print labels (barcodes?) from the printer’s label software, and these scan, can you not use that to generate barcodes?

      Phil

      Reply

  41. Hi,

    I’ve added your VBA to my PERSONAL.XLSB in the hope that from now on when ever I need it, it’s already there but for some reason I cannot seem to get it to work without adding it to the new workbook each time.

    any ideas? 🙂

    Cheers.

    Reply

    • Hi Raymond,
      I assme you have those fonts installed, they should show up in your word list of fonts. In Word, the barcode should be wrapped between * (there should be an asterisk at the beginning and at the end of the barcode text).

      Reply

  43. When I use the above code I get the following character “” instead of a “space”. All other entered characters work fine. What can be done to fix this?

    Reply

    • Hi Martin,

      Can you please send me a copy of your workbook so I can have a look. You can open a Helpdesk ticket to do this.

      Regards

      Phil

      Reply

    • Hi Suren,

      Have you used my sample workbook? It sounds like you are creating Code128 barcodes, but you haven’t changed the font to Code128 in the cell holding the barcode.

      Regards

      Phil

      Reply

  45. Thanks for your willingness to offer this material on your site. This works as expected and will be put into my toolbox for future use in a warehouse application. Greatly appreciated!

    Reply

  46. Hello,

    Is it possible to set the tilde option to “true” in this program? The barcodes generated scan well for the data that is in a particular cell. However, I’m trying to create barcodes with Excel data and add in the function. If I simply add “~009” to the excel cell, the barcode will encode these as human readable characters and not the key.

    Thank you!

    Reply

  47. Hi Philip,

    Thanks for this concise and professional webpage.

    I work in MS Access and thought I’d load the raw Code128 function (completely unchanged) into an Access module and see how it worked.

    In your sample Excel workbook, the EAN
    5055371301268
    gets compiled as string
    ÍRWE-!:È8GÎ
    (which matches calculator on JBarton website), but when I run it in an Access text book, I get
    ?RWE-!:?8?? (literally)

    I am guessing this is something to do with ASCII character sets, but please could you suggest
    how to resolve issue

    Many thanks

    Reply

    • Hi Ralph,

      Yes it would look like some issue with the character set. The letters with accents/marks above them are the ones that aren’t displaying, though not sure why G is also affected.

      Sorry, I’m not familiar with Access so not sure what an Access text book is, or how you go about changing your character set.

      Regards

      Phil

      Reply

  48. Hello Guys,

    Wondering if you can help me with my issue that i try to solve but i don’t know how.

    Problem:
    I’m trying to print some labels from excel with a macro. It prints excelent with this commands:

    Open cPrinterGroot For Output As #1
    Print #1, “q620”
    Print #1, “N”
    Print #1, “ZT”
    Print #1, “S1”
    Print #1, “D12”
    Print #1, “A12,4,0,4,1,1,N,” + Chr(34) + EPL2_text(partNumber) + Chr(34)
    Print #1, “LO12,45,603,2″
    etc

    But i want to be able to print a barcode instead of a text. I’ve replaced EPL2_text with Code39 but is only printing my part number with ” * ” before and after. I set up the cell as a Code 39 font. Still the same.

    I don’t know what i’m missing….but i don’t know how to make it print the phisically barcode 🙁

    Please let me know if you have an ideea. Kind of stack…and your article is the best i found so far!

    If i print straight from Ctrl+P will print the barcodes…but i don’t want to do that.

    Thank you in advance

    Reply

    • Hi Florin,

      Why are you using a macro to print the barcodes? Why don’t you want to just print (CTRL+P) as normal?

      Regards

      Phil

      Reply

  49. Hi, I have bee trying to get this VBA working for a parts catalogue I want to use by scanning the screen barcode. However when I enter a Part Number which contains more than 3 numbers in sequence, e.g. ABC1234 I get an odd square symbol in the middle of the barcode. I have tried everything to correct but no matter what I do the fourth numeric digit produces this error. Any ideas?

    Reply

    • Hi Wouter,

      The code already supports the use of the SPACE character. If you just want to have some white space in the bar code, why not use spaces?

      Regards

      Phil

      Reply

      • Hi Philip,

        I want to use the tab key for purposes like put in username and paswoord in 2 text fields.
        I enhanced your code with the posibility to use the tab key by putting a ~ in the barcode string. Are you interested in that code?

        Greets,

        Wouter

        Reply

  51. Thanks for the sample. I am always looking for new or different ways to do things. I know this is for Excel but would like to add that this code also works in Access since it is vba. Then you can have a ready list of numbers to use.

    Reply

  53. Hi Philip,

    I’ve just implemented your code in my recently finished warehouse management program for Excel 2007.
    BarCodes are scanned with a phone running on Android (App = Scanpet).
    It works by scannning the data into an xl-file on the phone, then email it “to mother” where it can be red by the warehouse management program.

    Your code works like a charm.

    Thanks for your awesome effort.

    Best Regards, Michael

    Reply

    • Hi Michael,

      Great work. Excellent to hear the code has been useful to you.

      Thanks for letting us know.

      Regards

      Phil

      Reply

  55. Hi Philip,
    I have tried your code but for some reason it doesn’t really outputting correct ascii character, instead its giving me “?” for all accented characters.

    Can you help?
    I am using excel 2007

    Reply

  56. Hi! thank you for the great effort. However I am having trouble getting anything to scan.

    My string is: 2710CLP00500000000000002560520740000001000000

    Algorithm output is: Í;*ÈCLPÇ R 9\T’H ! ÄÎ

    I’m located in chile so im wondering if encoding might have anything to do with it?

    Reply

    • Hi Sergio,

      You haven’t installed the Code128 font.

      Look through the blog post for the section ‘Installing Barcode Fonts’ and download/install Code128.ttf. Then close/restart Excel.

      Cheers

      Phil

      Reply

  57. Good morning,
    there is a same function in Javascript ?

    I have to implement a barcode in a intranet.

    Best Regards
    Claudio Dalfini

    Reply

    • Hi Claudio,

      A quick Google search shows that there are plenty of JS barcode creators. I haven’t used any of them so you should try a few and see what works for you.

      Regards

      Phil

      Reply

    • Hi Santos,

      I’m not quite sure what you are asking. The barcodes are created in Excel using VBA and fonts, but you need a barcode scanner (which can be on something like a phone running Android) to read them.

      Regards

      Phil

      Reply

  60. Hello Philip,

    I just read your post tilted “Create Barcodes With (Or Without) Excel VBA” and it ‘juiced me up’.

    I have recently started teaching students identified as disadvantaged youth, and have been wrestling with an idea that would integrate perfectly in the mandate of the course.

    The students are taking part in a program titled “Supply Chain and Logistics”. My responsibilities are Math, Computer Applications (MS Office) and Excel.

    My idea was for me to setup a computer in the classroom in which MS Excel would be installed and a small database of products would reside on a spreadsheet that student could access with their cellphones.

    Here is what I had in mind:

    – students create 10 or so items in class on which they are going to apply a barcode that they are to generate.

    – the Excel worksheet is then populated with their products. Is this done manually in Excel or can they scan the items with their cells right into the worksheet?

    – they then scan at a random a product in class and the product info appears on their cellphone screen.

    My questions are:

    – can this be done and if so then….
    – what software should I have them install on their cells? (Android and IOS)
    – how does the communication with the ‘central’ computer take place? … Bluetooth?
    – do I have to have some type of driver in the ‘central’ computer?
    – do the scans go directly into the worksheet for new items?
    – when an item is scanned does the ‘driver’ in the ‘central’ computer simply ‘talk’ to Excel to
    forward the required info back to the cell?

    My plan would be to have this as an ongoing project in the Excel portion of the program.

    Our hope is for them to find meaningful employment in the warehouse industry. Most students are on some for of social assistance and anything we can do to give them an edge will be a bonus for them.

    I thank you in advance and look forward to your response.

    Mike

    Reply

    • Hi Mike,

      The only hiccup is the connection back to a central computer which hosts your spreadsheet. There are a number of apps that will scan codes into a document/file, like Scan to Spreadsheet which is available for both iOS and Android.

      But the way that works is to scan the data into a document on the phone/tablet, then email it somewhere where it can be opened in a spreadsheet.

      To achieve what you describe you maybe need something a bit more sophisticated. The systems I have worked with have all used dedicated barcode scanners connected to a PC, which accesses a central database. I don’t have any experience of using phones to do the same thing, sorry.

      You can of course type in the product info into Excel and create the barcodes using my code. Once the phone scans the barcode on the product, the info for that product will appear on the phone’s screen.

      Sorry I can’t help any more.

      Regards

      Phil

      Reply

  61. Hi Phil,

    thank you for sharing! It works, but somehow not for all strings. I need to encode 20 digits string, some of them works just fine but there is issue. When I have certain number in exact position inside the string, only part of barcode will show and the other part is a letter.
    for instance the string below – if I change fourth digit(5) to any other number, barcode will look good and it will work. If I try to create barcode without any changes I will see only part of barcode and letter
    85950726004845030523

    I’ve spend many hours tryink to solve it but I can’t find any way to make it work.

    Do you have any suggestion?

    Thanks a lot

    Reply

    • Hi Jirka,

      Check the text alignment in the cell where your barcode is. Make sure it is left aligned, if it is centered, the left hand side of the barcode will be chopped off.

      I just checked this and created a barcode from your 20 digit number that scans successfully.

      In my original workbook I had the barcode centered, and this may be what is causing the problem for longer codes. It’s not an issue with short ones.

      Thanks for bringing this to my attention, I’ll change the workbooks, hopefully this is the cause of your problem too.

      Regards

      Phil

      Reply

      • Thanks for a quick reply, unfortunately alignment didn’t solve the problem. When I try to create barcode from number I gave you it still shows barcode – letter “A” with some circle above it a then rest of the barcode.

        There must be something else I am overlooking.

        Anyway thanks for your help

        Regards

        Jirka

        Reply

          • It turned out that problem was in font. I had code 128.ttf which appears to be same version like you’re referring to in this page but it had half size(8kb). I try to overwrite the original with the new one(19kb) and everything works just fine.

            Thanks again for a help and providing such a great piece of code!

            Regards

            Jirka

  62. Thanks Phil.

    I want to expand on this and be able to somehow select a record from a database that would print a shelf label with the barcode, description, who makes it etc .for my small business – kind of like a mail merge label in word – but I don’t want to have to switch back and forth between excel and word.

    Reply

    • Hi Kathy,

      I’m a little fuzzy as to exactly how/what you are trying to achieve, but try this.

      The barcode itself is just text so can be stored in a cell as you would with any other text.

      You could create a table in a worksheet that has the information you desire on each label – this is your ‘database’

      On another sheet you retrieve and format the data from the table into the labels you want to print.

      Regards

      Phil

      Reply

  63. Hi
    I tried to use the VB code in excel sheet to generate barcode128 for PN containing # symbol
    For unknown reason some PNs are unreadable
    For example barcode128 string VB conversion for EMC#1860 gives ׁEMC#ּ2\@׃ while the correct conversion is ÑEMC#Ì2\@Ó

    In one computer I get the correct conversion ÑEMC#Ì2\@Ó and in other I get the this EMC#ּ2\@׃

    I have spent many hours to figure out what is going on with this…, yet have no insight

    Any help will be appreciated

    Thanks
    Ami

    Reply

  65. I wish I’d known about this a few years ago.
    I was running a nonprofit and needed a barcode system. I had the USB scanner and had someone trying to develop a custom software to get it working. I never thought to ask if something was possible via Excel.

    Now I know it’s possible. Thanks for this.

    Reply

  69. I’ve been considering adding bar codes to my coin collection database (as if coin collections and databases weren’t individually nerdy enough…). This article will be very handy when I finally pull the trigger!

    Reply

  70. I was so excited when I could print various bar codes by using the 3of9 font in Excel. With the self-made bar code, I can encode every shelf in the stockroom to better monitor stock movement with a handheld scanner. I really had (and still have) no clue why IT dept won’t be able to do so……
    ~_~

    Just one point to share… suggest NOT to have any border surrounding the bar code, as it may affect scanning efficiency.

    Cheers,

    Reply

    • Glad you found this useful. It’s pretty straight forward when you know how isn’t it 🙂

      Good idea, removing borders may help scanning.

      Thanks

      Phil

      Reply

        • Hi Lasha,

          I’m sorry I don’t have any code to produce things like EAN or UPC bar codes. But if you just want to see the encoded string under the barcode, you can just put the string in the cell underneath the generated bar code and print both cells together?

          Regards

          Phil

          Reply

Leave a Comment

Current ye@r *