GS1 Barcodes (GS1-128 and GS1-DataMatrix) in Excel

Requirements
Placing GS1 barcode as an Active Document
Placing GS1 barcodes in Excel with formula
Encoding numeric values in GS1 barcodes

An example how to insert linear and 2D GS1 barcodes in Excel with StrokeScribe barcode generator.

Requirements

Download and install the barcode generator

Placing GS1 barcode as an Active Document

Use this method when you need a single simple GS1 barcode with manual data entry. This method does not require programming.

1. Switch to the Insert tab on the Ribbon and click Object.

Inserting the barcode generator object in Excel as an Active Document

2. Look for the StrokeScribe Document in the list:

3. Right-click the barcode object, select StrokeScribe Control->Properties from the context menu.

Switch to the General tab;
Set Alphabet = EAN128 or GS1DATAMATRIX;
Check the Process tilde option;
Enter the barcode data into the Text field. For example: 10123456~d029112501303101000055

In this text string, we encode a batch number (AI 10), a FNC1 code (~d029), a production date (AI 11) and a product net weight (AI 310Y):

10123456~d029112501303101000055

For the GS1 data format explanation, look in the tutorials below.

Because the batch number is a variable-length value, we put a FNC1 code after it.

The production date format is YYMMDD. So the 250130 is decoded as 30-Jan-2025.

The decimal point position indicator Y in the product net weight (AI 3101Y) is encoded as shown:

3100000055 = 55Kg
3101000055 = 5.5Kg
3102000555 = 5.55Kg
3101000555 = 55.5Kg
3103555555 = 555.555Kg

Placing GS1 barcodes in Excel with formula

Step 1. Open the VBA window (Alt+F11) and create a standard module (VBAProject->Insert->Module).

Step 2. Paste the following code into the module:

' This code is called by the cell formula and produces a GS1-128 or a GS1-DataMatrix barcode.
Function CreateBarcodeGS1(data As String) As String
  Dim AC As Range
  Set AC = Excel.Application.Caller ' The name of the cell where the formula is executed

  Dim wsh As Worksheet
  Set wsh = AC.Worksheet ' The worksheet where the caller cell is located

  shname = "barcode_" & AC.Address()

  On Error Resume Next ' If we already have a barcode in the cell, deleting it
  wsh.Shapes(shname).Delete
  On Error GoTo 0

  bar_w = AC.MergeArea.Width  ' The caller cell's dimensions
  bar_h = AC.MergeArea.Height

  Dim gs1_generator As StrokeScribeClass ' Running the barcode generator
  Set gs1_generator = CreateObject("STROKESCRIBE.StrokeScribeClass.1")
  gs1_generator.Alphabet = EAN128 ' or =GS1DATAMATRIX
  gs1_generator.Text = data  ' The data to encode in the barcode

  image_path = Environ("TEMP") & "\GS1.wmf" ' Creating a temporary vercor picture
  rc = gs1_generator.SavePicture(image_path, WMF, 1440, 1440 / (bar_w / bar_h)) ' WMF dimensions are in TWIPs
  If rc > 0 Then
    CreateBarcodeGS1 = gs1_generator.ErrorDescription
    Exit Function
  End If

  Dim shp As Shape ' Firring the barcode picture into the cell
  Set shp = wsh.Shapes.AddPicture(image_path, msoFalse, msoTrue, AC.Left, AC.Top, bar_w, bar_h)
  shp.Name = shname

  Kill image_path
  CreateBarcodeGS1 = ""
End Function

Step 2.1. Choose between GS1-128 and GS1-DataMatrix barcode generation by modifying the Alphabet property in the code.

Step 3. Add a reference to the StrokeScribe Class into your VBA project.

Step 4. Switch back to the Excel window. Paste the following formula into the cell A1:

=  "10123456"  & CHAR(29) & "11250130" & "3101000055"

As in previous example, we encode the same GS1 values in the barcode: a batch number (AI 10), a FNC1 code (~d029), a production date (AI 11) and a product net weight (AI 310).

For the GS1 data format explanation, look in the tutorials below.

Step 5. Paste this formula into a cell that is large enough to fit the barcode:

=CreateBarcodeGS1(A1)

Encoding numeric values in GS1 barcodes

As you see, values like weight, length, volume do not have decimal point. Regular numbers from a spreadsheet need to be converted to GS1 format.

Paste the following function into the VBA module and then use it to convert numbers:

Public Function Y6N(txt)
    p = InStr(txt, ".")
    If p > 0 Then p = Len(txt) - p
    txt = Replace(txt, ".", "")
    v = Format$(txt, "000000")
    y = Format$(p, "0")
    Y6N = y + v
End Function

An example of product net weight (AI 3101Y) conversion:

=CreateBarcodeGS1("310" & Y6N("123.456"))

GS1 Barcodes (GS1-128 and GS1-DataMatrix) in Excel

Table of Contents

Requirements

Placing GS1 barcode as an Active Document

Placing GS1 barcodes in Excel with formula

Encoding numeric values in GS1 barcodes

Related Tutorials