Iceberg Code

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

It could happen one day that you need to print a Barcode.
Well in the Barcode.xls files you can get all you need to do it within Excel.

First you need to install the Barcode Fonts (in the file you find them - "Free 3 of 9" & "Free 3 of 9 Extended" The origin of the fonts could be lost)

How To add a new font to your computer?

1. Click Start, point to Settings, click Control Panel, and then double-click Fonts.
2. On the File menu, click Install New Font.
3. Click the drive, and then click the folder that contains the fonts you want to add.
4. Click the font you want to add.

Notes
* To select more than one font to add, hold down the CTRL key, and then click each of the fonts you want.

* For TrueType, Raster, or Adobe Type 1 fonts, you can also add the font by dragging the appropriate files to the Fonts folder. If you want to see the file extensions in Windows Explorer, click View, click Options, and then click the View tab. Click to clear the Hide file extensions for known file types check box. This works only for fonts that are not in the Fonts folder.


Now you are ready to translate all your code in a Barcode, but before you have to add a checksum to your original code. To do this you'll find 3 function that calculate the correct code: Append_EAN_Checksum, Format_Code128 and Format_UPC_String

Download it now
As usual if you prefer to read the code you can do it below:

'EAN -13 'EAN-13 is used world-wide for marking retail goods. 'The symbol encodes 13 characters: the first two or three are a country code which 'identify the country in which the manufacturer is registered (not necessarily where 'the product is actually made). The country code is followed by 9 or 10 data digits '(depending on the length of the country code) and a single digit checksum. 2-digit 'and 5-digit supplemental barcodes may be added for a total of 14 or 17 data digits. ' 'The Uniform Code Council (the organization which issues retail codes in the USA) 'has announced that January 1, 2005 will be the date by which all retail scanning 'systems in the USA must be able to accept the EAN-13 symbol as well as the standard 'UPC-A. This change will eliminate the need for manufacturers who export goods to 'the US and Canada to double-label their products. ' 'The checksum is a Modulo 10 calculation: ' Add the values of the digits in the even-numbered positions: 2, 4, 6, etc. Multiply this result by 3. Add the values of the digits in the odd-numbered positions: 1, 3, 5, etc. Sum the results of steps 2 and 3. The check character is the smallest number which, when added to the result in step4, produces a multiple of 10. Example: Assume the barcode data = 001234567890 0 + 2 + 4 + 6 + 8 + 0 = 20 20 * 3 = 60 0 + 1 + 3 + 5 + 7 + 9 = 25 60 + 25 = 85 85 + X = 90 (nearest equal or higher multiple of 10), therefore X = 5 (checksum) Here is a sample Visual Basic function to calculate the checksum: Function Append_EAN_Checksum(RawString As String)      Dim Position As Integer      Dim Checksum As Integer      Checksum = 0      For Position = 2 To 12 Step 2            Checksum = Checksum + Val(Mid$(RawString, Position, 1))      Next Position      Checksum = Checksum * 3      For Position = 1 To 11 Step 2            Checksum = Checksum + Val(Mid$(RawString, Position, 1))      Next Position      Checksum = Checksum Mod 10      Checksum = 10 - Checksum      If Checksum = 10 Then            Checksum = 0      End If      Append_EAN_Checksum = RawString & Format$(Checksum, "0") End Function 'Calculating a Code 128 Barcode Checksum in Access Basic 'This bit of Access Basic (same as Visual Basic) will calculate a Code 128 Modulo 103 checksum. 'Barcode fonts from different publishers may map the some characters to 'different locations, so be sure to check. Function Format_Code128(InString As String) As String      Dim Sum As Integer, i As Integer      Dim Checksum As Integer, Checkchar As Integer      Dim MyString As String, CVal As Integer      '      ' Initialize running total with value of      ' Subset B start character      '      Sum = 104      '      ' Scan the string and add character value times position      '      For i = 1 To Len(InString)          '          ' Copy one character from InString position i to MyString          '          MyString = Mid$(InString, i, 1)          '          ' Get the numeric value of the character and subtract          ' 32 to shift (the space character, ASCII value 32, has          ' a numeric value of 0 as far as Code 128 is concerned)          '          CVal = Asc(MyString) - 32          '          ' Add the weighted value into the running sum          '          Sum = Sum + (CVal * i)      Next i      '      ' Calculate the Modulo 103 checksum      '      Checksum = Sum Mod 103      '      ' Now convert this number to a character. This conversion      ' takes into account the particular mapping of the font      ' being used (this example is for the font published by      ' Azalea Software.      '      If CheckDigit = 0 Then          Checkchar = 174      ElseIf CheckDigit < 94 Then          Checkchar = CheckDigit + 32      Else          Checkchar = CheckDigit + 71      End If      '      ' Now format the final output string: start character,      ' data, check character, and stop character      '      MyString = Chr(162) + InString + Chr(Checkchar) + Chr(164)      Format_Code128 = MyString End Function 'UPC retail codes require a Modulo 10 check digit. 'This sample code accepts the UPC number, calculates the check digit, 'and returns the finished string. ' 'The general method involves adding up the digits in the odd-numbered 'positions and multiplying that sum by 3; then adding to that result 'the values of the digits in the even-numbered positions. 'Divide this result by 10 and subtract the remainder from 10. 'If the result is equal to 10, set it to zero; otherwise this is the check digit. Function Format_UPC_String(InString As String) As String      Dim OutString As String      Dim Multiplier As Integer, Sum As Integer, i As Integer      Dim CheckDigit As Integer      '      ' Initialize the sum to zero      '      Sum = 0      '      ' Add up the values of digits in the odd-numbered positions      '      For i = 1 To Len(InString) Step 2          Sum = Sum + Val(Mid$(InString, i, 1))      Next i      '      ' Multiply this result by 3, then add in the values of      ' the digits in the even-numbered positions      '      Sum = Sum * 3      For i = 2 To Len(InString) Step 2          Sum = Sum + Val(Mid$(InString, i, 1))      Next i      '      ' Now calculate the Modulo 10 check digit      '      CheckDigit = Sum Mod 10      CheckDigit = 10 - CheckDigit      If CheckDigit = 10 Then          CheckDigit = 0      End If      OutString = InString + Format$(CheckDigit)      Format_UPC_String = OutString End Function