DochyJP: Page creation

2021-11-24T11:53:12Z

Page creation

New page

{{Template:Class-Infobox-Generic
|image = 
|Section = [[Section::{{PAGENAME}}]]
|Source = [[Source::DataSource]] 
|Language = [[Language::English]] 
|Topic = [[Topic ::Microsoft]] 
|SubTopic = [[SubTopic::Excel]] 
|DocumentType = [[DocumentType::Documentation]] 
|LastEditBy = [[LastEditBy::{{REVISIONUSER}}]]
|LastEdit = [[LastEdit::{{REVISIONYEAR}}-{{REVISIONMONTH}}-{{REVISIONDAY2}}]]
|Status = [[Status::Active]] 
|Access = [[Access::free]] 

}}

== Easily generate Code 128 barcodes in Excel ==
A Code 128 barcode has six sections:
#Quiet zone
#Start character
#Encoded data
#Check character
#Stop character
#Quiet zone

The check character is calculated from a weighted sum (modulo 103) of all the characters. Because of this, the generation of Code 128 barcodes is not as simple as typing the number sequence into a programme using a barcode font. Attempting to do this with Code 128 barcodes will fail.

=== Step 1 ===
Download the [http://www.dafont.com/code-128.font Code 128 barcode font] and install in your fonts folder at c:\windows\fonts. (You will need administrator permissions to do this). Without being administrator, you can just drag & drop the ttf file from Font management under Windows.

=== Step 2 ===
Ensure that you have the Developer module enabled in Excel.

=== Step 3 ===
Create a new Microsoft Excel sheet. Create a table (making sure that you ‘format as table‘) with the following structure and headings:
{| class="wikitable"
|+ Caption text
|-
! Barcode !! Barcode String !! Barcode Presentation !! Check
|-
| X || X || X || X
|}

=== Step 4 ===
In Excel, go to the Developer ribbon and choose “Visual Basic”.

=== Step 5 ===
Right-click on “Modules” in the tree on the left and select “Insert Module”. Then paste the following code :
<pre>
Option Explicit
Public Function Code128(SourceString As String)
'Written by Philip Treacy, Feb 2014
'http://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
</pre>

=== Step 6 ===
Go back to your Excel sheet and insert the following formulae:

*In cell B2 (“Barcode String”), insert =Code128([@Barcode])
*In cell C2 (“Barcode Presentation”), insert =[@[Barcode String]]
*In cell D2 (“Check”), insert: =IF(ISNUMBER(SEARCH("Â",[@[Barcode Presentation]],1)),"Error!","")
The formulae should copy down the entire columns. Save your sheet. Attention, this require an xlsm file format.

=== Step 7 ===
Highlight Column C and change the font to “Code 128”. Now when you enter data into cell A2, a barcode should be displayed in cell C2 and so-on down the entire sheet.

If this doesn’t work, you may need to close and re-open Excel at this stage.

[[Category:Documentation]]

EAN128 barcodes in Excel - Revision history

DochyJP: Page creation