• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Create Barcodes With (Or Without) Excel VBA

You are here: Home / Excel VBA / Create Barcodes With (Or Without) Excel VBA
Create Barcodes With (Or Without) Excel VBA
February 27, 2014 by Philip Treacy

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.
Please enter a valid email address.

You can download the code in a .xlsm workbook, or a .xls workbook (pre Excel 2007).

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.

Create Barcodes With (Or Without) Excel VBA
Philip Treacy

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.

More Excel VBA Posts

Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Calculate end of period dates in Excel

Excel End of Period Dates

Calculating Excel End of Period Dates can be tricky if you don't use standard month ends. Here is a formula and UDF that solves the problem.

More Excel VBA Posts

macro to center across selection

Center Across Selection Macro

Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category: Excel VBATag: Excel VBA
Previous Post:6 Ways to Fix Dates Formatted as Text in Excel6 Ways to Fix Dates Formatted as Text in Excel
Next Post:Excel Designed Minecraft CakeExcel Designed Minecraft Cake

Reader Interactions

Comments

  1. Max

    March 26, 2022 at 1:27 am

    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
    • Catalin Bombea

      March 29, 2022 at 3:39 pm

      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
      • Max

        March 30, 2022 at 9:40 pm

        Hi Cฤƒtฤƒlin!

        I was using Libre Barcode 128 Text from Google Fonts
        When i used the font provided by you in this page i got excelent results!
        Thank you a lot!

        Reply
        • Philip Treacy

          March 31, 2022 at 1:07 pm

          Hi Max,

          Yes you need to use the font I provided.

          regards

          Phil

          Reply
  2. MD

    December 15, 2021 at 12:00 pm

    Having some issue. I got blank spaces when there is more than 2 zeros.

    Reply
    • Catalin Bombea

      December 15, 2021 at 4:42 pm

      Hi MD,
      Can you post a sample file with your code on our forum? (create a new topic after sign-in)
      Will be easier to help you.
      Cheers,
      Catalin

      Reply
  3. adam

    May 20, 2021 at 6:01 pm

    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
    • Philip Treacy

      May 22, 2021 at 12:27 pm

      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
  4. Evan Dovey

    May 6, 2021 at 6:45 pm

    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
    • Evan Dovey

      May 6, 2021 at 6:54 pm

      Additional – note I re-downloaded the sample file to the on-working PC and have the same issue.
      I uploaded an image to google docs to show it.
      https://drive.google.com/file/d/1ZdRA3nZrdGPLhpr7lgI92G_pe8Xi2FHX/view?usp=sharing

      Reply
      • Philip Treacy

        May 10, 2021 at 1:49 pm

        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
        • Evan Dovey

          May 20, 2021 at 1:55 am

          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
          • Philip Treacy

            May 22, 2021 at 12:24 pm

            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

          • Coen

            January 13, 2022 at 8:39 pm

            Hello,

            Do you have fixed this yet i have te same problem.

          • Philip Treacy

            January 14, 2022 at 10:05 am

            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

        • Allan Guo

          June 22, 2023 at 9:34 pm

          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
          • Philip Treacy

            June 28, 2023 at 1:03 pm

            Hi Allan,

            Is your laptop a Mac? VB for Mac’s don’t support UNICODE strings beyond value 128

            https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/chr-function

            Other than that, I can’t reproduce the issue. If I change Chr to ChrW everything still works ok.

            How exactly does the code break when you change Chr to ChrW? On every barcode generation? Only with a particular string?

            Regards

            Phil

        • Allan Guo

          June 22, 2023 at 9:37 pm

          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
  5. Bart

    December 18, 2020 at 3:46 am

    You are a STAR!

    Reply
  6. Kevin

    December 15, 2020 at 10:06 pm

    Is it possible to generate a TAB or CR?

    Reply
    • Catalin Bombea

      December 17, 2020 at 7:12 pm

      Hi Kevin,
      “$I” is the code for HT-“Horizontal Tab”, try this switch to build your string.
      For other chars, according to https://support.idautomation.com/Barcode-Fonts/Encoded-Tab-in-Barcode-is-not-recognized-in-Excel/_1839,
      “the scanner must be set to USB Keyboard Emulation or Function Key Mapping. Depending on the scanner manufacturer, another term may be used. Contact your scanner manufacturer to find out how to enable the setting. Once it is enabled, the barcode that includes a tab will work as designed in Excel.”

      Reply
  7. Lee

    October 16, 2019 at 12:15 pm

    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
    • Philip Treacy

      October 16, 2019 at 12:22 pm

      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
    • Lee

      October 16, 2019 at 1:41 pm

      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
      • Philip Treacy

        October 17, 2019 at 10:28 am

        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
  8. W. Wells

    August 23, 2019 at 9:44 am

    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
    • Philip Treacy

      August 23, 2019 at 11:12 am

      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
  9. John Nollett

    August 21, 2019 at 5:16 am

    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
    • Philip Treacy

      August 21, 2019 at 4:07 pm

      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
  10. Sim

    August 16, 2019 at 2:04 pm

    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
    • Philip Treacy

      August 16, 2019 at 3:39 pm

      Hi Sim,

      Have your co-workers got the barcode fonts on their PC’s?

      Regards

      Phil

      Reply
      • Sim

        August 19, 2019 at 5:52 am

        Hi Phil,
        I did download the same font from the site (https://grandzebu.net/informatique/codbar-en/code128.htm), as per you suggestion. It still does not work.
        Thank you

        Reply
        • Mynda Treacy

          August 20, 2019 at 12:35 pm

          Hi Sim

          Please open a post on the forum and attach your workbook so I can check it out.

          Regards

          Phil

          Reply
          • Sim

            August 22, 2019 at 5:59 am

            Hi Phil,
            Turns out the font from GrandZebu was the issue, when i removed and installed the font link on this page all worked out fine.
            Thank you

  11. Marcos

    June 12, 2019 at 8:29 am

    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
    • Philip Treacy

      June 12, 2019 at 4:51 pm

      Thanks Marcos, excellent!

      Reply
    • Andrea

      July 23, 2019 at 8:43 pm

      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
      • Philip Treacy

        July 24, 2019 at 8:29 am

        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
  12. mike

    May 17, 2019 at 2:16 am

    Thank you for this. I did use it in Access with little or no changes.

    Reply
    • Philip Treacy

      May 20, 2019 at 7:14 pm

      you’re welcome.

      Phil

      Reply
  13. Rita

    May 11, 2019 at 3:16 pm

    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
    • Catalin Bombea

      May 11, 2019 at 9:00 pm

      Hi Rita, you can create an add-in. That add-in will be available on any excel file you open on your computer. If that is neded on other computers, you can send them the add-in.

      Reply
  14. Donna Rohrer

    April 27, 2019 at 11:11 am

    Hi,
    Do you know how I can incorporate this into an Access Database? I want to create a report of Barcodes.

    Reply
    • Philip Treacy

      April 28, 2019 at 9:08 pm

      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
  15. Mauricio Cuevas

    March 29, 2019 at 3:21 am

    Please, i need EAN-128 font, the real diference betwin code128 and ean128 is the FNC1 symbol, help me

    Thanks

    Reply
    • Philip Treacy

      March 29, 2019 at 3:30 pm

      Sorry I don’t have that font. You’ll be able to buy it if you Google suppliers.

      Regards

      Phil

      Reply
  16. mario gomez

    March 22, 2019 at 8:09 am

    todo perfecto!!!! muchas gracias

    Reply
    • Catalin Bombea

      March 22, 2019 at 9:19 pm

      You’re welcome Mario, glad to hear you found it useful.

      Reply
  17. Alejandro

    December 31, 2018 at 2:19 am

    After the creation of the barcode is possible to use them into Publisher mail merge???

    Reply
    • Philip Treacy

      December 31, 2018 at 1:04 pm

      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
  18. Khoa Diep

    December 25, 2018 at 8:09 pm

    Thanks for your sharing. It runs perfectly

    Reply
    • Philip Treacy

      December 27, 2018 at 10:05 am

      You’re welcome.

      Reply
  19. Angel

    December 6, 2018 at 1:47 am

    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
    • Catalin Bombea

      December 6, 2018 at 2:09 pm

      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
  20. jai

    December 4, 2018 at 6:18 pm

    how can I use my android phone as a Barcode scanner and read in excel,
    please give me sample example.

    Reply
    • Philip Treacy

      December 5, 2018 at 9:43 am

      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
  21. Steve

    November 29, 2018 at 7:38 pm

    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
    • Philip Treacy

      December 3, 2018 at 3:42 pm

      Thanks Steve.

      Sorry but I don’t have code to produce EAN barcodes.

      Phil

      Reply
      • Stephen Hughes

        December 3, 2018 at 7:10 pm

        Ok, thanks for the reply Phil

        Reply
  22. Ivor Shaer

    November 27, 2018 at 6:32 pm

    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
    • Philip Treacy

      November 28, 2018 at 2:14 pm

      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
  23. Christian

    July 29, 2018 at 1:58 pm

    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
    • Philip Treacy

      July 30, 2018 at 9:22 am

      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
  24. Jules

    July 6, 2018 at 6:34 pm

    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
    • Catalin Bombea

      July 10, 2018 at 1:14 pm

      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
  25. Murray

    May 11, 2018 at 1:08 pm

    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
    • Philip Treacy

      May 11, 2018 at 1:24 pm

      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
  26. John Arnold

    January 27, 2018 at 5:51 am

    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
    • Philip Treacy

      February 1, 2018 at 9:59 am

      Thanks John

      Reply
  27. amalia

    December 11, 2017 at 1:14 am

    cuando lo paso a word para generar etiquetas no los lee el lector

    Reply
    • Philip Treacy

      December 11, 2017 at 12:27 pm

      ยฟPor quรฉ pasarlo a Word? ยฟPor quรฉ no imprimir desde Excel?

      Reply
  28. Dominik

    November 17, 2017 at 12:49 am

    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
    • Philip Treacy

      November 17, 2017 at 10:28 am

      Hi Dominik,

      Can you please supply a sample workbook so we can take a look. You can start a new topic on the forum and attach the file to that.

      You’ll need to register for a free forum account before you can post on the forum.

      Regards

      Phil

      Reply
  29. Adriaan

    October 13, 2017 at 4:13 am

    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
    • Catalin Bombea

      October 13, 2017 at 7:03 pm

      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
  30. Alec Alabakis

    October 1, 2017 at 10:40 am

    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
    • Philip Treacy

      October 1, 2017 at 11:15 am

      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
      • Alec Alabakis

        October 1, 2017 at 11:51 am

        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
        • Philip Treacy

          October 1, 2017 at 12:38 pm

          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
  31. Sb

    September 21, 2017 at 3:22 am

    Brilliant! THanks for sharing

    Reply
    • Philip Treacy

      September 21, 2017 at 9:26 am

      You’re welcome.

      Phil

      Reply
  32. Thomas L

    June 1, 2017 at 1:04 am

    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
    • Philip Treacy

      June 1, 2017 at 9:49 am

      Hi Thomas,

      Which font?

      Code39 is in a zip file so must be extracted first.

      Regards

      Phil

      Reply
      • Thomas L

        June 1, 2017 at 4:39 pm

        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
        • Philip Treacy

          June 1, 2017 at 7:23 pm

          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
          • Thomas L

            June 2, 2017 at 11:56 pm

            News !

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

            So it is working well for me.

            Thank you Phil

  33. Bella

    May 27, 2017 at 3:23 am

    Buen Dia Muy bueno tu aporte, pero me gustaria saber como usar esta funcion en Word

    Reply
    • Philip Treacy

      May 29, 2017 at 10:21 am

      Lo siento, no estoy seguro de cรณmo conseguir esto en Word.

      Reply
  34. Patrick

    May 20, 2017 at 4:39 am

    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
    • Philip Treacy

      May 20, 2017 at 9:57 am

      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
      • Patrick

        May 22, 2017 at 6:43 pm

        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
        • Philip Treacy

          May 23, 2017 at 9:12 am

          Good luck.

          Phil

          Reply
  35. Nathan

    May 18, 2017 at 3:50 am

    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
    • Philip Treacy

      May 23, 2017 at 9:11 am

      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
      • Udo

        September 9, 2017 at 4:48 pm

        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
        • Catalin Bombea

          September 15, 2017 at 5:30 pm

          Hi Udo,
          Phil’s code is using only CodeB and CodeC.
          The complete char sets are:

          128A (Code Set A) – ASCII characters 00 to 95 (0-9, A-Z and control codes), special characters, and FNC 1-4
          128B (Code Set B) – ASCII characters 32 to 127 (0-9, A-Z, a-z), special characters, and FNC 1-4
          128C (Code Set C) – 00-99 (encodes two digits with a single code point) and FNC1

          I think you should try the code from this page, their code can handle control
          chars:
          https://stackoverflow.com/questions/13909248/generating-code-128-barcodes-using-excel-vba

          Reply
  36. Kel

    April 26, 2017 at 6:49 pm

    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
    • Philip Treacy

      April 26, 2017 at 6:58 pm

      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
  37. D

    March 29, 2017 at 5:40 am

    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
    • Philip Treacy

      March 29, 2017 at 12:58 pm

      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
  38. Dhan

    March 23, 2017 at 2:52 am

    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
    • Philip Treacy

      March 23, 2017 at 10:04 am

      Hi Dhan,

      Please start a topic on the forum and attach the file and I’ll have a look for you.

      Regards

      Phil

      Reply
  39. Ahsan

    March 13, 2017 at 9:39 pm

    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
    • Philip Treacy

      March 14, 2017 at 1:42 pm

      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
  40. Lee

    February 17, 2017 at 12:39 pm

    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
    • Catalin Bombea

      February 19, 2017 at 7:36 am

      Hi Lee,
      I think this version should be what you need: create-an-excel-add-in-for-user-defined-functions-udfs
      What exactly does not work? The function does not show up when you start writing its name in a cell, right? The above article should clarify things.
      Catalin

      Reply
      • Lee

        February 22, 2017 at 5:00 pm

        Perfect,

        Yes the function was not available so I was having to add the VBA to the new workbook each time.

        I’ve created an add-in for it and all is good ๐Ÿ™‚

        Thank you Catalin!!

        Reply
        • Catalin Bombea

          February 23, 2017 at 4:06 am

          You’re welcome, glad to hear you managed to solve the problem ๐Ÿ™‚
          Catalin

          Reply
  41. Raymond Visser

    February 3, 2017 at 6:11 am

    Hi,

    is there a way to make this work in Ms Word?
    If i copy the code to Word the barcode comes out funny

    Kind regards

    Reply
    • Catalin Bombea

      February 4, 2017 at 4:18 pm

      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
  42. Martin

    January 9, 2017 at 5:59 pm

    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
    • Philip Treacy

      January 11, 2017 at 1:25 pm

      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
  43. Suren

    November 10, 2016 at 6:58 pm

    Thank you Philip,

    I tried the code and got the barcode. However, the barcode is preceded by ร and รŽ
    what needs to be done?

    Reply
    • Philip Treacy

      November 10, 2016 at 8:18 pm

      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
  44. Tom Suber

    October 4, 2016 at 8:43 am

    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
    • Philip Treacy

      October 5, 2016 at 2:23 pm

      You’re welcome Tom.

      Regards

      Phil

      Reply
  45. Marc Bernard

    June 23, 2016 at 5:15 am

    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
  46. Ralph Berry

    June 7, 2016 at 7:46 am

    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
    • Philip Treacy

      June 9, 2016 at 8:36 am

      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
  47. Florin

    June 2, 2016 at 12:59 am

    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
    • Philip Treacy

      June 2, 2016 at 9:42 am

      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
  48. Mark

    May 5, 2016 at 12:17 am

    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
    • Philip Treacy

      May 5, 2016 at 10:03 am

      Hi Mark,

      Please post your workbook to our forum and I’ll take a look.

      Regards

      Phil

      Reply
      • Lee

        December 21, 2016 at 5:50 pm

        I also get the same error as Mark, every time the number string is greater than 3 after text then a box appears in the middle of the code.
        Did you find a solution?
        Excellent bit of code by the way ๐Ÿ™‚

        cheers

        Reply
        • Philip Treacy

          December 21, 2016 at 10:15 pm

          Thank you Lee.

          I don’t think Mark sent me his workbook so I couldn’t look at it for him.

          Can you please provide your workbook? If you can post it on the forum that will be great

          https://www.myonlinetraininghub.com/excel-forum

          Thanks

          Phil

          Reply
  49. Wouter Heylen

    March 22, 2016 at 12:58 am

    Hi, is there a way to add a horizontal tab to an encoded barcode?

    Reply
    • Philip Treacy

      March 22, 2016 at 12:58 pm

      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
      • Wouter Heylen

        March 22, 2016 at 9:51 pm

        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
        • Mynda Treacy

          March 31, 2016 at 10:24 am

          Hi Wouter,

          Yes I’d like to have a look at that code.

          Regards

          Phil

          Reply
        • Marc Bernard

          June 23, 2016 at 5:32 am

          Hello Wouter,

          could you please share this modification?

          Thank you!

          Marc

          Reply
  50. Tom Van Dam

    March 15, 2016 at 9:59 pm

    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
    • Philip Treacy

      March 16, 2016 at 8:25 am

      Hi Tom,

      Thanks for that. Glad it was useful to you.

      regards

      Phil

      Reply
  51. al

    March 4, 2016 at 5:27 am

    Thanks a lot. It works fine.

    best regards
    al

    Reply
    • Philip Treacy

      March 4, 2016 at 8:39 am

      You’re welcome.

      regards

      Phil

      Reply
  52. Michae Elekes

    March 3, 2016 at 2:33 am

    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
    • Philip Treacy

      March 3, 2016 at 8:10 am

      Hi Michael,

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

      Thanks for letting us know.

      Regards

      Phil

      Reply
  53. Rupak

    February 5, 2016 at 10:40 pm

    Hi I need to generate barcode for employee codes which r 8 digit number can I use ur vba code

    Reply
    • Philip Treacy

      February 6, 2016 at 9:16 am

      Hi Rupak,

      Yes you can use my code to do this.

      Regards

      Phil

      Reply
  54. michael

    January 7, 2016 at 5:19 pm

    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
    • Philip Treacy

      January 7, 2016 at 5:41 pm

      Hi Michael,

      If you open a Helpdesk ticket and send me the workbook you are using, I will have a look.

      Regards

      Phil

      Reply
  55. Sergio Mesa

    November 18, 2015 at 12:08 am

    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
    • Philip Treacy

      November 19, 2015 at 12:51 pm

      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
  56. Claudio Dalfini

    November 12, 2015 at 2:21 am

    Good morning,
    there is a same function in Javascript ?

    I have to implement a barcode in a intranet.

    Best Regards
    Claudio Dalfini

    Reply
    • Philip Treacy

      November 12, 2015 at 11:07 am

      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
  57. Santos

    July 10, 2015 at 7:11 am

    Your bar codes say Excel VBA… Barcode Scanner for Android

    Reply
    • Philip Treacy

      July 10, 2015 at 12:17 pm

      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
  58. Dinesh Takyar

    March 24, 2015 at 4:11 pm

    Excellent work!

    Reply
    • Philip Treacy

      March 24, 2015 at 5:27 pm

      Thanks Dinesh ๐Ÿ™‚

      Reply
  59. Mike Plourde

    March 18, 2015 at 5:32 am

    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
    • Philip Treacy

      March 19, 2015 at 12:23 pm

      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
  60. Jirka

    March 12, 2015 at 7:54 pm

    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
    • Philip Treacy

      March 12, 2015 at 9:19 pm

      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
      • Jirka

        March 13, 2015 at 1:37 am

        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
        • Philip Treacy

          March 13, 2015 at 10:01 am

          Hi Jirka,

          Please open a Help Desk ticket and send me your workbook so I can see what is going on.

          Regards

          Phil

          Reply
          • Jirka

            March 13, 2015 at 5:52 pm

            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

          • Philip Treacy

            March 13, 2015 at 8:03 pm

            no problem ๐Ÿ™‚

            glad you figured it out.

            Phil

  61. Kathy

    January 29, 2015 at 1:49 am

    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
    • Philip Treacy

      January 31, 2015 at 9:21 am

      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
  62. Ami Cohen

    November 3, 2014 at 6:37 am

    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
    • Philip Treacy

      November 3, 2014 at 7:51 pm

      Hi Ami,

      Can you please send me your workbook so I can have a look at it. You can create a ticket in the Helpdesk and send your workbook that way.

      Regards

      Phil

      Reply
  63. Patnaik

    March 13, 2014 at 3:33 pm

    Thanks for your files and sharing your ideas for free

    please keep posting

    Thanking You

    Reply
    • Philip Treacy

      March 17, 2014 at 8:57 am

      Hi Patnaik,

      Glad to help.

      Regards

      Phil

      Reply
  64. Oz

    March 1, 2014 at 2:20 am

    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
    • Philip Treacy

      March 1, 2014 at 8:59 am

      Hi Oz,

      no worries. glad you found this useful.

      Phil

      Reply
  65. Hari

    February 28, 2014 at 10:49 pm

    Nice article, first time I ever saw Excel used for barcoding. Eager to put it in use.

    Reply
    • Philip Treacy

      March 1, 2014 at 8:58 am

      Let us know how you implement this Hari

      Reply
  66. John T Barton

    February 28, 2014 at 12:54 am

    Great article and nice fresh update of something I put together over 15 years ago.

    Reply
    • Philip Treacy

      February 28, 2014 at 8:56 am

      Thanks John

      Reply
  67. Raymond

    February 27, 2014 at 11:33 pm

    Great tutorial, as always ๐Ÿ™‚
    Can’t wait to put this into practice.
    Thanks a lot.

    Regards.

    Reply
    • Philip Treacy

      February 28, 2014 at 7:57 am

      no worries Raymond.

      Glad you found this useful.

      Regards

      Phil

      Reply
  68. Bryan Metz

    February 27, 2014 at 10:51 pm

    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
    • Philip Treacy

      February 28, 2014 at 9:02 am

      Hi Bryan,

      Sounds like a good application for the barcodes. Shh, we won’t tell anyone about the nerdy stuff ๐Ÿ™‚

      Phil

      Reply
  69. MF

    February 27, 2014 at 4:59 pm

    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
    • Philip Treacy

      February 27, 2014 at 5:18 pm

      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
      • lasha

        November 13, 2016 at 1:34 am

        hello, i want human readable barcode in digits below barcode. can you halp me?

        Reply
        • Philip Treacy

          November 13, 2016 at 8:33 pm

          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 Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

office scripts course

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.