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.
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
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.
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.
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 :
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 :
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.
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.
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.
6 Ways to Fix Dates Formatted as Text in Excel
Max
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!
Catalin Bombea
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.
Max
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!
Philip Treacy
Hi Max,
Yes you need to use the font I provided.
regards
Phil
MD
Having some issue. I got blank spaces when there is more than 2 zeros.
Catalin Bombea
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
adam
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?
Philip Treacy
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
Evan Dovey
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?
Evan Dovey
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
Philip Treacy
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
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
Evan Dovey
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.
Philip Treacy
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
Hello,
Do you have fixed this yet i have te same problem.
Philip Treacy
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
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
Philip Treacy
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
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
Bart
You are a STAR!
Kevin
Is it possible to generate a TAB or CR?
Catalin Bombea
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.”
Lee
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.
Philip Treacy
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
Lee
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
Philip Treacy
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
W. Wells
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!
Philip Treacy
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
John Nollett
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.
Philip Treacy
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
Sim
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
Philip Treacy
Hi Sim,
Have your co-workers got the barcode fonts on their PC’s?
Regards
Phil
Sim
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
Mynda Treacy
Hi Sim
Please open a post on the forum and attach your workbook so I can check it out.
Regards
Phil
Sim
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
Marcos
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)&”ร”
Philip Treacy
Thanks Marcos, excellent!
Andrea
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
Philip Treacy
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
mike
Thank you for this. I did use it in Access with little or no changes.
Philip Treacy
you’re welcome.
Phil
Rita
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?
Catalin Bombea
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.
Donna Rohrer
Hi,
Do you know how I can incorporate this into an Access Database? I want to create a report of Barcodes.
Philip Treacy
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
Mauricio Cuevas
Please, i need EAN-128 font, the real diference betwin code128 and ean128 is the FNC1 symbol, help me
Thanks
Philip Treacy
Sorry I don’t have that font. You’ll be able to buy it if you Google suppliers.
Regards
Phil
mario gomez
todo perfecto!!!! muchas gracias
Catalin Bombea
You’re welcome Mario, glad to hear you found it useful.
Alejandro
After the creation of the barcode is possible to use them into Publisher mail merge???
Philip Treacy
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
Khoa Diep
Thanks for your sharing. It runs perfectly
Philip Treacy
You’re welcome.
Angel
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
Catalin Bombea
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
jai
how can I use my android phone as a Barcode scanner and read in excel,
please give me sample example.
Philip Treacy
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
Steve
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
Philip Treacy
Thanks Steve.
Sorry but I don’t have code to produce EAN barcodes.
Phil
Stephen Hughes
Ok, thanks for the reply Phil
Ivor Shaer
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?
Philip Treacy
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
Christian
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”.
Philip Treacy
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
Jules
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
Catalin Bombea
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.
Murray
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
Philip Treacy
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
John Arnold
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
Philip Treacy
Thanks John
amalia
cuando lo paso a word para generar etiquetas no los lee el lector
Philip Treacy
ยฟPor quรฉ pasarlo a Word? ยฟPor quรฉ no imprimir desde Excel?
Dominik
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.
Philip Treacy
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
Adriaan
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!
Catalin Bombea
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.
Alec Alabakis
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
Philip Treacy
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
Alec Alabakis
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?
Philip Treacy
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
Sb
Brilliant! THanks for sharing
Philip Treacy
You’re welcome.
Phil
Thomas L
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
Philip Treacy
Hi Thomas,
Which font?
Code39 is in a zip file so must be extracted first.
Regards
Phil
Thomas L
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
Philip Treacy
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
Thomas L
News !
To install the font you have to be administator of your computer.
So it is working well for me.
Thank you Phil
Bella
Buen Dia Muy bueno tu aporte, pero me gustaria saber como usar esta funcion en Word
Philip Treacy
Lo siento, no estoy seguro de cรณmo conseguir esto en Word.
Patrick
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!
Philip Treacy
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
Patrick
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.
Philip Treacy
Good luck.
Phil
Nathan
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.
Philip Treacy
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
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
Udo
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?
Catalin Bombea
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
Kel
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
Philip Treacy
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
D
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
Philip Treacy
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
Dhan
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
Philip Treacy
Hi Dhan,
Please start a topic on the forum and attach the file and I’ll have a look for you.
Regards
Phil
Ahsan
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)
Philip Treacy
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
Lee
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.
Catalin Bombea
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
Lee
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!!
Catalin Bombea
You’re welcome, glad to hear you managed to solve the problem ๐
Catalin
Raymond Visser
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
Catalin Bombea
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).
Martin
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?
Philip Treacy
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
Suren
Thank you Philip,
I tried the code and got the barcode. However, the barcode is preceded by ร and ร
what needs to be done?
Philip Treacy
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
Tom Suber
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!
Philip Treacy
You’re welcome Tom.
Regards
Phil
Marc Bernard
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!
Ralph Berry
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
Philip Treacy
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
Florin
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
Philip Treacy
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
Mark
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?
Philip Treacy
Hi Mark,
Please post your workbook to our forum and I’ll take a look.
Regards
Phil
Lee
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
Philip Treacy
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
Wouter Heylen
Hi, is there a way to add a horizontal tab to an encoded barcode?
Philip Treacy
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
Wouter Heylen
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
Mynda Treacy
Hi Wouter,
Yes I’d like to have a look at that code.
Regards
Phil
Marc Bernard
Hello Wouter,
could you please share this modification?
Thank you!
Marc
Tom Van Dam
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.
Philip Treacy
Hi Tom,
Thanks for that. Glad it was useful to you.
regards
Phil
al
Thanks a lot. It works fine.
best regards
al
Philip Treacy
You’re welcome.
regards
Phil
Michae Elekes
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
Philip Treacy
Hi Michael,
Great work. Excellent to hear the code has been useful to you.
Thanks for letting us know.
Regards
Phil
Rupak
Hi I need to generate barcode for employee codes which r 8 digit number can I use ur vba code
Philip Treacy
Hi Rupak,
Yes you can use my code to do this.
Regards
Phil
michael
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
Philip Treacy
Hi Michael,
If you open a Helpdesk ticket and send me the workbook you are using, I will have a look.
Regards
Phil
Sergio Mesa
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?
Philip Treacy
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
Claudio Dalfini
Good morning,
there is a same function in Javascript ?
I have to implement a barcode in a intranet.
Best Regards
Claudio Dalfini
Philip Treacy
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
Santos
Your bar codes say Excel VBA… Barcode Scanner for Android
Philip Treacy
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
Dinesh Takyar
Excellent work!
Philip Treacy
Thanks Dinesh ๐
Mike Plourde
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
Philip Treacy
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
Jirka
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
Philip Treacy
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
Jirka
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
Philip Treacy
Hi Jirka,
Please open a Help Desk ticket and send me your workbook so I can see what is going on.
Regards
Phil
Jirka
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
no problem ๐
glad you figured it out.
Phil
Kathy
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.
Philip Treacy
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
Ami Cohen
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
Philip Treacy
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
Patnaik
Thanks for your files and sharing your ideas for free
please keep posting
Thanking You
Philip Treacy
Hi Patnaik,
Glad to help.
Regards
Phil
Oz
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.
Philip Treacy
Hi Oz,
no worries. glad you found this useful.
Phil
Hari
Nice article, first time I ever saw Excel used for barcoding. Eager to put it in use.
Philip Treacy
Let us know how you implement this Hari
John T Barton
Great article and nice fresh update of something I put together over 15 years ago.
Philip Treacy
Thanks John
Raymond
Great tutorial, as always ๐
Can’t wait to put this into practice.
Thanks a lot.
Regards.
Philip Treacy
no worries Raymond.
Glad you found this useful.
Regards
Phil
Bryan Metz
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!
Philip Treacy
Hi Bryan,
Sounds like a good application for the barcodes. Shh, we won’t tell anyone about the nerdy stuff ๐
Phil
MF
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,
Philip Treacy
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
lasha
hello, i want human readable barcode in digits below barcode. can you halp me?
Philip Treacy
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