Barcodes in Excel

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
378
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. 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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
Thanks I'm using font 128B will this still work the same
 
Upvote 0
You sir are a diamond this is brilliant I can't thank you enough for you help.

Very Happy Chappy
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top