Barcode in Microsoft Excel with ActiveX
Some examples how to use the ActiveX-based version of StrokeScribe barcode generator in Excel. The barcode properties may be configured both manually and by using VBA code. The barcode data may be supplied from VBA code or by linking the barcode object to a cell.
For other barcode generation methods, see the related tutorials.
Requirements
- Download and install the barcode generator;
Placing the ActiveX into a Worksheet
1. Switch to the Developer tab, click the Insert->More Controls as shown on the picture:
2. Select the StrokeScribe Control as shown below and then press OK.
3. Left-click anywhere on the worksheet to paste the ActiveX.
The inserted barcode looks like this:
Managing the Barcode ActiveX from VBA
1. Place a button on the worksheet: On the Developer tab, click Insert->Command Button as shown on the picture. Then, left-click anywhere on the worksheet to insert a button.
2. Double-click the command button to open VBA editor:
3. Paste the following code into the Sub body:
StrokeScribe1.Alphabet = DATAMATRIX ' Setting up the ActiveX to produce a DATA MATRIX barcode
Dim data As String
data = Excel.ActiveSheet.Cells(1, 1) ' (1,1) is the cell A1
StrokeScribe1.Text = data ' Encoding the cell's content in the barcode
If StrokeScribe1.Error Then
MsgBox StrokeScribe1.ErrorDescription
End If
4. Switch back to the Excel window.
5. On the Developer tab, uncheck the Design Mode button.
Click the CommandButton1 to modify the barcode.
Code Snippets by Barcode Type
All Barcode Types
How to Encode ASCII Control Characters in a Barcode
Directly, using the Chr() function:
StrokeScribe1.Text = "ABC" & Chr(9) & "DEF" ' Encoding a TAB character in the barcode
Using tilde sequences:
StrokeScribe1.ProcessTilde = True
StrokeScribe1.Text = "123~x0a~x0d" ' Encoding a newline sequence: <CR><LF>
How to Encode a Byte Array in a Barcode
Use this example to encode binary values in CODE 128 and 2D barcodes:
StrokeScribe1.CodePage = -1 ' Disabling codepage translation
Dim b(1 To 3) As Byte ' An array of random bytes
b(1) = &H11
b(2) = &H22
b(3) = &H33
StrokeScribe1.Text = b ' Encoding the array in the barcode
CODE 128
To create a CODE 128 barcode, modify your code in the following way:
StrokeScribe1.Alphabet = CODE128
StrokeScribe1.Text = "ANY TEXT"
Human-readable Text Customization
StrokeScribe1.Alphabet = CODE128
StrokeScribe1.Text = "ENCODED TEXT"
StrokeScribe1.TextBelow = "LABEL TEXT"
EAN-13/UPC-A
To create an EAN-13 barcode, modify your code in the following way:
StrokeScribe1.Alphabet = EAN13
StrokeScribe1.Text = "123456789012"
The barcode generator checks 13-digit strings to have a correct check digit. For 12-digit values, the check digits are automatically calculated by the generator.
UPC-A
When printing UPC-A barcodes, specify 11- or 12-digit string in the Text field. For 11-digit string, a check digit is calculated automatically. A 12-digit string must contain the valid check digit. The barcode generatror will report an error if the check digit is not valid.
StrokeScribe1.Alphabet = UPCA
StrokeScribe1.Text = "12345678901"
GS1 Country Codes
A list of country codes for use in EAN-13/GS1-128/GS1-DATAMATRIX.
QR CODE
To create a QR CODE, modify your code in the following way:
StrokeScribe1.Alphabet = QRCODE
StrokeScribe1.Text = "123ABCD"
Mobile-friendly Barcodes
Use UTF-8 encoding to make barcodes with national characters readable by mobile barcode readers.
StrokeScribe1.Alphabet = QRCODE
StrokeScribe1.CodePage = 65001 ' 65001 = UTF-8
StrokeScribe1.Text = "ΞΟΠΡΣΤΥΦ"
An example of mobile-readable QR Code that contains UNICODE characters:
How to Modify the Error Correction Level
StrokeScribe1.Alphabet = QRCODE
StrokeScribe1.QrECL = H ' L/M/Q/H - the QR CODE error correction levels
How to Force QR CODE's Minimum Matrix Size
This allows to maintain the barcode size/density when encoding data of different sizes:
StrokeScribe1.Alphabet = QRCODE
StrokeScribe1.Text = "123"
StrokeScribe1.QrMinVersion = 11
Micro QR Code
StrokeScribe1.Alphabet = MICROQR
PDF417
To create a PDF417 barcode, modify your code in the following way:
StrokeScribe1.Alphabet = PDF417
StrokeScribe1.Text = "123ABC"
To create a Compact PDF417:
StrokeScribe1.Alphabet = PDF417
StrokeScribe1.CompactPDF417 = True
How to Set the Non-default Error Correction Level
StrokeScribe1.Alphabet = PDF417
StrokeScribe1.PDF417ErrLevel = 8
How to Fix the Number of Rows or Columns
StrokeScribe1.Alphabet = PDF417
StrokeScribe1.PDF417Cols = 3
StrokeScribe1.PDF417Rows = 7
DATA MATRIX
To create a Data Matrix barcode, modify your code in the following way:
StrokeScribe1.Alphabet = DATAMATRIX
StrokeScribe1.Text = "ABC1234"
How to Force the Minimum Matrix Size
StrokeScribe1.DataMatrixMinSize = 132
How to Modify the Quiet Zone Size
StrokeScribe1.QuietZone2D = 0
ECI
Use the Extended Channel Interpretation codes ONLY if the receiving party asked you to do so. NEVER use ECI in smartphone readable barcodes.
StrokeScribe1.ECI = 5
AZTEC Barcode
To create an Aztec barcode, modify your code in the following way:
StrokeScribe1.Alphabet = AZTEC
StrokeScribe1.Text = "ABCD123"
How to Force the Minimum Matrix Size
StrokeScribe1.AztecMinLayers = 132
How to Set the Desired Error Correction Level
StrokeScribe1.AztecECL = 50
How to Modify the Quiet Zone Size
StrokeScribe1.QuietZone2D = 0
GS1-128 and GS1-DATAMATRIX
To produce a 2D barcode, set the Alphabet property as shown below:
StrokeScribe1.Alphabet = GS1DATAMATRIX
To produce a linear GS1 barcode, use the following code:
StrokeScribe1.Alphabet = EAN128
How to Format a GS1 Data String
Let's encode a Batch Number, Product Net Weight and Production Date in a GS1 barcode:
StrokeScribe1.Text = "10" & "1234567" & Chr(29) & "310" & "1" & "000053" & "11" & "120503"
Here is what is encoded in the string:
- "10" = AI 10 - Batch Number;
- "1234567" = The batch number data - up to 20 alphanumeric characters;
- Chr(29) = A variable-length field separator. You need to place this after each variable-length AI like 10 or 21;
- "310" = Product Net Weight in Kilograms;
- "1" = The decimal digit position;
- "000053" = 5.3Kg. For 53Kg, place "0" in the decimal digit position field;
- "11" = Production Date;
- "120503" = May, 03, 2012 in the YYMMDD format.
For more GS1 data string examples, see GS1-128 and GS1-DATAMATRIX. For the full list of the supported AIs, see GS1 AI compatibility list.
Related Tutorials
- Barcode in Microsoft Excel
- Using Formula to Create Barcode in Excel
- Using DLL version of the barcode generator in Excel
- Country codes for use in EAN-13 and GS1 barcodes