Using a formula to print barcode in Excel

Tags: Excel, barcode, cell, formula, VBA

A Formula to Make Barcode in Microsoft Excel

An example how to build a formula to create barcodes in Microsoft Excel with StrokeScribe barcode generator. The formula fits barcodes into cells and works both with single and merged cells.

The barcodes are produced as small-sized vector-based WMF pictures. This method does not use barcode fonts, ActiveX objects, or any online services.

Creating barcodes in Excel using a special formula and the COM-class version of the StrokeScribe barcode generator offers several advantages.

First, it enables seamless integration with Excel's data management capabilities, allowing users to automatically generate barcodes from existing data sets.

The special formula simplifies the process, making barcode creation accessible even to those with limited technical expertise.

Additionally, use of StrokeScribe barcode generator enhances barcode customization, providing users with a range of symbologies and formatting options.

This method streamlines the workflow by automating the barcode generation in high-volume environments.

Requirements

Download and install the barcode generator.

Compatibility

Excel XP/2003: yes;
Excel 2007/2010/2013: yes;
Excel 2016/2019/2021/Office 365: yes;
Office Online: no;

The example is compatible with any Microsoft Excel versions starting from Excel 2007.

The example is not compatible with online version of Microsoft Office.

How It Works

- First, we need a cell formula that just calls this VBA function.

- In the VBA function, we use the barcode generator to produce a picture in vector format.

- The formula cannot return a picture directly, so we store the barcode picture in a temporary file and then use the Shapes.AddPicture() method to load it back into Excel.

- The Application.Caller property allows to determine which cell (or a cell range) is running the formula and fit the barcode into the cell.

Making the Formula

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

Do not use a worksheet or workbook module - the formula will not call a function from there.

If you do not see the VBA project tree, press Ctrl+R.

Creating a VBA module for the barcode formula

Step 2. Copy the following code into the module:

Expand/Collapse

'An example VBA code to work with a barcode cell formula.
'Put this code into a standard VBA module.
'Use this formula in a cell: =CreateBarcode("1234ABCD")

Function CreateBarcode(data As String) As String
  Dim AC As Range
  Set AC = Excel.Application.Caller ' A reference to the cell which called the CreateBarcode()

  Dim wsh As Worksheet
  Set wsh = AC.Worksheet ' An Excel worksheet that contains the "caller cell"

  shname = "barcode_" & AC.Address() ' Constructing an unique name for the shape.

  ' When the cell's content changes, we need to delete the previously created shape.
  On Error Resume Next
  wsh.Shapes(shname).Delete
  On Error GoTo 0

  ' To fit barcode picture into a cell, we need to calculate the width:height ratio of the cell.
  bar_w = AC.MergeArea.Width
  bar_h = AC.MergeArea.Height

  Dim ss As StrokeScribeClass
  Set ss = CreateObject("STROKESCRIBE.StrokeScribeClass.1")
  ss.Alphabet = CODE128 ' or =QRCODE  or =DATAMATRIX 
  ss.Text = data

  ' We need to create a temporary file for the barcode picture to load it into a picture shape.
  image_path = Environ("TEMP") & "\barcode.wmf"

  ' The barcode picture has the same aspect ratio with the caller cell.
  ' The picture dimensions are specified in TWIPs (1440 per inch).
  rc = ss.SavePicture(image_path, WMF, 1440, 1440 / (bar_w / bar_h))

  If rc > 0 Then
    CreateBarcode = ss.ErrorDescription
    Exit Function
  End If

  ' This loads the picture from the temporary file back to Excel.
  Dim shp As Shape 
  Set shp = wsh.Shapes.AddPicture(image_path, msoFalse, msoTrue, AC.Left, AC.Top, bar_w, bar_h)
  shp.Name = shname

  Kill image_path ' We don't need the temporary picture file anymore.
  CreateBarcode = "" ' This will clear any text contained in the cell.
  ' Useful if a previous function call returned an error description text.
End Function

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

Step 4. Switch back to the Excel window.

Choose a cell and type in the formula field:

=CreateBarcode("1234ABCD")

The formula will create a barcode as shown on the picture:

A barcode generated with the formula in Excel

Encoding Multiple Field Values in One Barcode

Enter any text into cells G1 and F1.

Paste the below formula into the cell A1:

=CreateBarcode(F1 & CHAR(9) & G1)

The formula creates a barcode with values of the two fields separated by the ASCII TAB character.

How To Customize the Barcode

Here are some short examples how to modify your formula to produce the most common barcode types.

Barcode Rotation

This rotates both 2D and linear barcodes. Add the code into the above example below the ss.Alphabet=.

ss.Rotation = 90 ' The barcode is rotated 90 degrees CCW.

UNICODE

To make non-ASCII characters readable by smartphones, store them in UTF-8:

ss.Alphabet = QRCODE
ss.CodePage = 65001 ' UTF-8

CODE 128

ss.Alphabet = CODE128
ss.Text = data

For CODE 128, you can control the human-readable label text below the barcode:

ss.Text = data
ss.TextBelow = "A custom barcode label"

Or just disable the label:

ss.ShowText = False

QR CODE

ss.Alphabet = QRCODE  ' or =MICROQR for Micro QR Code
ss.Text = data

' Error correction level - optional
ss.QrECL = H ' or L or M or Q or H

' Minimum QR CODE matrix size - optional
ss.QrMinVersion = 11

DATA MATRIX

ss.Alphabet = DATAMATRIX
ss.Text = data

' The minimum DATA MATRIX size - optional
ss.DataMatrixMinSize = 132  

' The quiet zone size - optional
ss.QuietZone2D = 0