Today we’re going to

do something different. We’re going to create barcodes in Excel, specifically the UPC-A barcode version which is a very common barcode. It has 12 numeric digits

which are uniquely assigned to each trade item. We’ve all seen barcodes on

the products that we buy. Did you ever imagine

creating them in Excel? Well, Pete did and he actually did it. Now, some time ago, Pete sent me a message that went something like this. (upbeat music) Now, I know some are going

to say, there are programs that are meant for this,

Excel isn’t one of them. We agree, here’s what

Pete said about this. (upbeat music) So let’s have some fun. Let’s create barcodes

and practice some VBA. (upbeat music) Here it is, here is Pete’s automatic

barcode generator in Excel. All you have to do is type in the barcode which is split to one digit for type, five digit for manufacturer

and five digits for product and this entire barcode here will be automatically generated. We can test this quickly. Let’s take a look at this area right here and this number right here,

I’m going to change the type to a one (talks), this changes. Now let’s test this for

manufacturer as well, just keep an eye on this area

where the four is underneath, I’m going to change the

first digit to a three and notice that this

changed automatically. Now I had no clue about

how barcodes are generated so I took a look at the reference image that Pete had included in the file and I also did some research online. This may look a bit complex

but it’s actually quite simple. So that type digit that

we input is basically the type of barcode. Then we have the five

digit manufacturer code and the five digit product code. There is something at

the end of the barcode which is called the modulo check and that was generated

automatically, we didn’t input that. The formula for it is actually right here. It takes a combination of these numbers, it does a calculation and

then it subtracts the result of this formula from the

next highest multiple of 10. So in this example, the result

of the calculation is 23 so 30 minus 23 is seven and

that’s the modulo check. So basically, this is driven by a formula. Now how are these lines,

these black lines generated? They’re generated from these numbers. So each number here has

a set of binary codes, so zero and ones that

are associated with it. So for example, if you

take a look at the five, the five consists of

this set of binary codes, you can picture it this

way, each digit here reflects a column in Excel. A zero is an empty column

and a one is a black column. So now if you take a look at the five here and we see this set of binary

code written right here, underneath it, the zero is an

empty column, a white column and a one is a black column. Then we have three zeros, so

empty columns and then a one. Each number has its own set, so a one here when we look up here, it has

this set of binary codes. So the black columns

are in different places. We have a similar pattern

for the right side codes. They have their own sets of binary codes. In addition to this, we

have a standard layout for the right guard, center

guard and the left guard. Now how did Pete manage to

create this barcode in Excel from these numbers? Well before I show this to

you, we need to make sure that it works. So let’s take an actual product,

let’s put in the barcode of it in here, let’s scan

it to see if it works. So here I have a bottle of

honey Chipotle barbecue sauce. On the back here, I have the barcode. Now the first digit, that’s the type so I’m just going to put

it in Excel, it’s a zero. Then I have the five digit

manufacturer code which is 13409 and then I have the five digit

product code which is 51519. Then notice that the modulo is an eight, let’s take a look at our file here, the modulo is also an eight. Now I’m going to bring up my

phone to scan the barcode. It works, it says honey

Chipotle barbecue sauce, the ingredients are listed here. High fructose corn syrup,

that’s the first ingredient, doesn’t sound so healthy

but I already finished it. Well, the good news is that

our barcode reader works. Now let’s take a look

at how this is set up. First off, let’s take

a look at the numbers on the bottom of the barcode. Well, this one is simple,

it’s a direct reference to a name cell which is called type number which is this cell right here. This changes, this is

going to change as well. Next, we have a five

digit manufacturer code. Now notice that each digit here is sitting in its own set of merge cells. To get this done properly,

Pete has used a combination of the left function together

with the mid function to get the other digits out separately. And the same applies to the

five digit product code. Now for the modulo check, it’s referencing a named range called modulo

check which is actually sitting in a hidden cell that’s right here. So I’m just going to unhide

this area and we can see that modulo check cell

is sitting right here and this is the formula behind it. So we have the mod function to get the next highest

multiple of 10 and then Pete has plugged in the formula

that we saw right here, that’s going to give us

the modulo check number. Now what about the bars here? Well, notice that the

columns are really narrow and each column is either

black or it’s white. So this is following the binary digits. So if it’s zero, it’s white,

if it’s one, it’s black. Now for the left guard, these are fixed so is the center guard

and the right guard. The other ones have digits behind them. Now there is no formula

here because this part is generated with VBA but

before I show you the VBA code, let’s just double check this. If I take this image, I’m just

going to copy and paste it here and then crop it for the left

code just so we can follow how this works. So type is the first part of

the barcode, our type is zero so it has this set of binary codes. So notice the first three

columns have to be empty and then we have two

black columns, then empty and then black, it’s exactly

what we see right here, three empty, two filled,

one empty, one black. And then if you take a

look at the first digit of the manufacturer, it’s a

one and the one has this set of binary codes where the

first two columns are empty and then we have two fields columns. The next one is a three

and if you look down here, that’s how a three looks, that’s what it translates to, right? So that’s how you can read this. Now, let’s quickly take a look at the VBA. So I’m going to press

Alt + F11, we notice that there is one module in

here, it looks like this, I’m just going to scroll to

the bottom so you get an idea of how long this is. Now this is written in a

very nice and simple way. What I like about it is that

it’s super easy to follow. So what Pete has done here is

he’s created different sets of array variables to keep those

left codes and right codes, basically those binary

codes that we saw before. So he’s keeping them in

variables and then what he does is he loops through each

of the digits here to check which of these binary codes

apply to it and then he keeps it in memory in an array. So if you’re curious to find

out more about VBA arrays, I have a separate video on

this and I’m going to add it to the description of the

video and to the cards. After he keeps this in array,

he does the loop a few times to run through all the

digits of the code and then in the end, he just copies

and pastes the array that he creates to the Excel file. So one’s for the left array

which is the manufacturer codes and the second time for the right array which is the product code. Now these are names in Excel. So if I actually go here

and select rightArray, notice that we see everything

after the center guard. So it’s basically the sets of

zeros and ones that is kept in memory in VBA is just

pasted right back in. But after we paste in zeros and ones, how come we get these colors? Well these colors are actually created with conditional formatting. So if I just go to conditional

formatting to manage rules, notice that we have a simple

formula, if it’s a one, the cell should be black, if it’s a zero, it shouldn’t have a color. And the cells, the height of

the cell is made long enough to look like a bar. Now one last tweak that Pete did to this is to make this dynamic, right? So every time we change

this, we don’t have to click a button to execute or

to refresh the code, it does it automatically

and that’s because there is a selection change

procedure behind this code. So if I go back to VBA, with Alt + F11, the sheet here is called

barcode, that’s the sheet, its code name is source. There is a worksheet

selection change procedure so that every time we change a number, only in these three cells, the

CreateBarcode sub-procedure which was in module one

here is going to run. If we don’t change any of these cells, the macro is not triggered. If you would like to take

a closer look at the code, feel free to download the

workbook and go through it at your own pace. That’s it, that’s how you

can create barcodes in Excel. If you’d like to take a look at the file, you can download it from

the link in the description, Pete was very kind to share this with you. Let’s give a thumbs up to thank Pete. Thank you for dropping by

today and if you’re new, consider subscribing before you leave. (upbeat music)