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.
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. You can read Matthew's blog here http://www.squaregear.net/fonts/free3of9.shtml.
JT Barton provided the Code128 font and I've modified the Code128() function based on code he modified from Grand Zebu.
VBA Code
You can download the code in a .xlsm workbook, a .xls workbook (pre Excel 2007), or the plain text file. You can also copy and paste the code below.
Please Share
If you found this useful or know someone that would, please use the buttons below to share this on your favorite social network
Option Explicit
Public Function Code128(SourceString As String)
'Written by Philip Treacy, Feb 2014
'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
Dim Counter As Integer
Dim CheckSum As Long
Dim mini As Integer
Dim dummy As Integer
Dim UseTableB As Boolean
Dim Code128_Barcode As String
If Len(SourceString) > 0 Then
'Check for valid characters
For Counter = 1 To Len(SourceString)
Select Case Asc(Mid(SourceString, Counter, 1))
Case 32 To 126, 203
Case Else
MsgBox "Invalid character in barcode string." & vbCrLf & vbCrLf & "Please only use standard ASCII characters", vbCritical
Code128 = ""
Exit Function
End Select
Next
Code128_Barcode = ""
UseTableB = True
Counter = 1
Do While Counter <= Len(SourceString)
If UseTableB Then
'Check if we can switch to Table C
mini = IIf(Counter = 1 Or Counter + 3 = Len(SourceString), 4, 6)
GoSub testnum
If mini% < 0 Then 'Use Table C
If Counter = 1 Then
Code128_Barcode = Chr(205)
Else 'Switch to table C
Code128_Barcode = Code128_Barcode & Chr(199)
End If
UseTableB = False
Else
If Counter = 1 Then Code128_Barcode = Chr(204) 'Starting with table B
End If
End If
If Not UseTableB Then
'We are using Table C, try to process 2 digits
mini% = 2
GoSub testnum
If mini% < 0 Then 'OK for 2 digits, process it
dummy% = Val(Mid(SourceString, Counter, 2))
dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 100)
Code128_Barcode = Code128_Barcode & Chr(dummy%)
Counter = Counter + 2
Else 'We haven't got 2 digits, switch to Table B
Code128_Barcode = Code128_Barcode & Chr(200)
UseTableB = True
End If
End If
If UseTableB Then
'Process 1 digit with table B
Code128_Barcode = Code128_Barcode & Mid(SourceString, Counter, 1)
Counter = Counter + 1
End If
Loop
'Calculation of the checksum
For Counter = 1 To Len(Code128_Barcode)
dummy% = Asc(Mid(Code128_Barcode, Counter, 1))
dummy% = IIf(dummy% < 127, dummy% - 32, dummy% - 100)
If Counter = 1 Then CheckSum& = dummy%
CheckSum& = (CheckSum& + (Counter - 1) * dummy%) Mod 103
Next
'Calculation of the checksum ASCII code
CheckSum& = IIf(CheckSum& < 95, CheckSum& + 32, CheckSum& + 100)
'Add the checksum and the STOP
Code128_Barcode = Code128_Barcode & Chr(CheckSum&) & Chr$(206)
End If
Code128 = Code128_Barcode
Exit Function
testnum:
'if the mini% characters from Counter are numeric, then mini%=0
mini% = mini% - 1
If Counter + mini% <= Len(SourceString) Then
Do While mini% >= 0
If Asc(Mid(SourceString, Counter + mini%, 1)) < 48 Or Asc(Mid(SourceString, Counter + mini%, 1)) > 57 Then Exit Do
mini% = mini% - 1
Loop
End If
Return
End Function
Public Function Code39(c39 As String)
'Written by Philip Treacy, Feb 2014
'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
'Input Parameters : A string
'Return : 1. An encoded string which produces a bar code when dispayed using the Free 3 of 9.TTF font
' 2. An empty string if the input parameter contains invalid characters
Dim Counter As Integer
If Len(c39) > 0 Then
c39 = UCase(c39)
'Check for valid characters
For Counter = 1 To Len(c39)
Select Case Asc(Mid(c39, Counter, 1))
Case 32, 36, 37, 43, 45 To 57, 65 To 90
Case Else
MsgBox "Invalid character in barcode string." & vbCrLf & vbCrLf & "Only use 0-9, A-Z, - + . $ % / and the SPACE character", vbCritical
Code39 = ""
Exit Function
End Select
Next
End If
Code39 = "*" & c39 & "*"
End Function
 Excel VBA){kind=link}
Thomas L says
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 says
Hi Thomas,
Which font?
Code39 is in a zip file so must be extracted first.
Regards
Phil
Thomas L says
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 says
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 says
News !
To install the font you have to be administator of your computer.
So it is working well for me.
Thank you Phil
Bella says
Buen Dia Muy bueno tu aporte, pero me gustaria saber como usar esta funcion en Word
Philip Treacy says
Lo siento, no estoy seguro de cómo conseguir esto en Word.
Patrick says
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 says
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 says
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 says
Good luck.
Phil
Nathan says
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 says
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
Kel says
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 says
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 says
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 says
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 says
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 says
Hi Dhan,
Please start a topic on the forum and attach the file and I’ll have a look for you.
Regards
Phil
Ahsan says
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 says
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 says
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 says
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 says
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 says
You’re welcome, glad to hear you managed to solve the problem 🙂
Catalin
Raymond Visser says
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 says
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).
More details here.
Martin says
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 says
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 says
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 says
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 says
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 says
You’re welcome Tom.
Regards
Phil
Marc Bernard says
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 says
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 says
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 says
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 says
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 says
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 says
Hi Mark,
Please post your workbook to our forum and I’ll take a look.
Regards
Phil
Lee says
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 says
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 says
Hi, is there a way to add a horizontal tab to an encoded barcode?
Philip Treacy says
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 says
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 says
Hi Wouter,
Yes I’d like to have a look at that code.
Regards
Phil
Marc Bernard says
Hello Wouter,
could you please share this modification?
Thank you!
Marc
Tom Van Dam says
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 says
Hi Tom,
Thanks for that. Glad it was useful to you.
regards
Phil
al says
Thanks a lot. It works fine.
best regards
al
Philip Treacy says
You’re welcome.
regards
Phil
Michae Elekes says
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 says
Hi Michael,
Great work. Excellent to hear the code has been useful to you.
Thanks for letting us know.
Regards
Phil
Rupak says
Hi I need to generate barcode for employee codes which r 8 digit number can I use ur vba code
Philip Treacy says
Hi Rupak,
Yes you can use my code to do this.
Regards
Phil
michael says
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 says
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 says
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 says
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 says
Good morning,
there is a same function in Javascript ?
I have to implement a barcode in a intranet.
Best Regards
Claudio Dalfini
Philip Treacy says
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 says
Your bar codes say Excel VBA… Barcode Scanner for Android
Philip Treacy says
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 says
Excellent work!
Philip Treacy says
Thanks Dinesh 🙂
Mike Plourde says
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 says
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 says
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 says
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 says
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 says
Hi Jirka,
Please open a Help Desk ticket and send me your workbook so I can see what is going on.
Regards
Phil
Jirka says
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 says
no problem 🙂
glad you figured it out.
Phil
Kathy says
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 says
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 says
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 says
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 says
Thanks for your files and sharing your ideas for free
please keep posting
Thanking You
Philip Treacy says
Hi Patnaik,
Glad to help.
Regards
Phil
Oz says
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 says
Hi Oz,
no worries. glad you found this useful.
Phil
Hari says
Nice article, first time I ever saw Excel used for barcoding. Eager to put it in use.
Philip Treacy says
Let us know how you implement this Hari
John T Barton says
Great article and nice fresh update of something I put together over 15 years ago. If you wish to read my original work its available at http://jtbarton.com/Barcodes/BarcodeOverview.aspx
Philip Treacy says
Thanks John
Raymond says
Great tutorial, as always 🙂
Can’t wait to put this into practice.
Thanks a lot.
Regards.
Philip Treacy says
no worries Raymond.
Glad you found this useful.
Regards
Phil
Bryan Metz says
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 says
Hi Bryan,
Sounds like a good application for the barcodes. Shh, we won’t tell anyone about the nerdy stuff 🙂
Phil
MF says
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 says
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 says
hello, i want human readable barcode in digits below barcode. can you halp me?
Philip Treacy says
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