Tags: Excel, barcode, cell, formula, VBA

A Formula to Make Barcode in Microsoft Excel

Requirements
Making the Formula
How To Customize the Barcode
Related Tutorials

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.

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.

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:

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

© 2025 StrokeScribe. All rights reserved. Use of any portion of this site constitutes acceptance of our Terms of Use and Privacy Policy. The website material may not be reproduced, except with the prior written permission.