Barcodes in Excel

HappyChappy

Board Regular
Joined
Jan 26, 2013
Messages
182
Office Version
2019, 2010, 2007
Platform
Windows
Hi having great problems with bar codes currently using a web based site https://www.barcodesinc.com/generator/index.php to generate barcodes then cutting and pasting them into excel as a picture. The site uses Code 128b for the fonts.
I downloaded said font and thought it must be better to do it myself but the latter part of the barcode seems to be different from my down loaded picture. i'm using the formula ="*"&A28&"*"
for the life of me i cant see why it's different any help on this please.
a typical barcode would be 973415748
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,266
Office Version
365
Platform
Windows
Hi HappyChappy,
it took a bit of fiddling, but I think I figured it out: I took the font from https://www.dcode.fr/barcode-128 and some code from here: https://www.dafont.com/font-comment.php?file=code_128 . As you can see, there are 3 characters that need to be added to the string: it starts with a starting character, ends with a closing character and there is a check character which needs to be calculated. For the check character, see e.g. https://www.barcoderesource.com/code128_barcodefont.html (scroll down).
Anyhow, if you do that for your code 973415748, the formula is =CHAR(204) & 973415748&">" & CHAR(206) , the > is the calculated check character. To make life a bit easier, I took this code ( https://www.dafont.com/font-comment.php?file=code_128 ) and transformed into an Excel function (place in a module and use it as a normal function).
Cheers,
Koen
Code:
Function CreateBarcode(StrIn As String) As String

StrOut = Chr(204)

CheckVal = 0
For c = 1 To Len(StrIn)
    a = Asc(Mid(StrIn, c, 1))
    If a >= 32 And a <= 126 Then
        StrOut = StrOut & Mid(StrIn, c, 1)
        CheckVal = CheckVal + c * a
    End If
Next c

CheckChar = CheckVal Mod 103 + 32
StrOut = StrOut & Chr(CheckChar)
StrOut = StrOut & Chr(206)
CreateBarcode = StrOut

End Function
 

HappyChappy

Board Regular
Joined
Jan 26, 2013
Messages
182
Office Version
2019, 2010, 2007
Platform
Windows
Thanks I'm using font 128B will this still work the same
 

HappyChappy

Board Regular
Joined
Jan 26, 2013
Messages
182
Office Version
2019, 2010, 2007
Platform
Windows
You sir are a diamond this is brilliant I can't thank you enough for you help.

Very Happy Chappy
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,266
Office Version
365
Platform
Windows
Hi Happy,
there seemed to be a small error in the check character calculation. I updated the function/macro and it seems to work again (see the same file as above).
Cheers,
Koen
 

HappyChappy

Board Regular
Joined
Jan 26, 2013
Messages
182
Office Version
2019, 2010, 2007
Platform
Windows
Once again many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,927
Members
406,510
Latest member
wizekor

This Week's Hot Topics

Top