dLSoft  barcode & labelling software     

Barcodes in Excel

Generating barcode in Excel spreadsheets may be achieved using any of the several methods provided in Barcodes & Labels for Office. One technique is to use macros to create barcodes from the text in each cell selected when the macro is called. Another is to use a formula to create a barcode from data in another cell. Barcodes & Labels for Office also includes and Active-X control that may be placed on a spreadsheet and its data source taken from a specified cell. Suitable for Excel 2007, 2010 or later.

Macro generate barcodes in Excel

 

In-cell formula

Barcodes & Labels for Office includes an in-cell formula for creating barcodes in Excel. The formula creates a font-based barcode in one cell using the data provided in another cell and may be used as an alternative to the macros for spreadsheets that will only be distributed to users who have the Barcodes & Labels for Office product installed.

The function barcode(cell,code,flags) enables the contents of a cell to show a font-based barcode based on the data in another cell simply by placing the formula =Barcode(cell,code,flags) into the cell. eg =barcode(A1,9,0) produces an EAN 128 barcode in the cell using data from the cell A1.

Similarly the function barcodeH(cell,code,flags) returns the human readable version of the barcode (including any checkdigit character calculated automatically is flags=1).

The code for these functions is included in the macros module. However, if these are the only functions required the code provided in the Help file may be pasted in a module by following these steps:
1. Open the workbook
2. Open the Visual Basic editor
3. Right-click on the VBAProject and choose Insert Module from the pop-up menu displayed
4. Copy the code provided and paste it into the empty module
5. Select "Close and return to Microsoft Excel" from the Visual Basic File menu.

Once this has been done simply select an empty cell and enter a formula such as =barcode(A1,9,0) into the Formula Bar, press enter and characters appear in the cell. As with any formula, the formula may be copied down to include as many rows as required. Select the cells containing the new characters and change the font to the required size of the barcode font (eg UNCW, 24 point) and the barcodes are complete.

Note that, by default, formulae in Excel are normally recalculated when Excel recognises that the data for the formula changes. However, we recommend forcing a recalculation of all formulae in the spreadsheet before assuming that all barcodes have been updated. The way of forcing recalculation depends on the version of Excel, so see you Excel Help for details. In most case SHIFT+F9 recalculates the current worksheet.

Barcodes created using the In-cell formula cannot be distributed to other Excel users who do not have the Barcodes & Labels for Office product installed. Developers wishing to use in-line barcode formulae in spreadsheets for distribution should use the dBarcode Developers Kit.

Using Macros to create Barcodes in Excel

Barcodes & Labels for Office includes Macros to create a barcode that may either be positioned as a barcode image (Pbarcode macro) over the adjacent cell while deleting the cell’s previous content, or replacing the adjacent cells contents with a font based barcode (Fbarcode macro). In each case the cell height is resized to accommodate the barcode.

An entire column may be selected on a worksheet by pushing the column letter button at the top of the column. When a macro is called, the barcode will be copied onto the worksheet at the position of the adjacent column. For alternative arrangements the macros can be customised (see below) by editing the VBA source code provided.

Setting the barcode properties

The properties of the barcodes created using the macros may be set entirely by editing the VBA source code of the macros or by running one of the macros PBarcodeSetup or FBarcodeSetup at least once before the PBarcode and FBarcode macros are used.

Whenever you wish to create a single barcode simply click the cursor in the cell containing the text you need to turn into a barcode, then run one of the Barcode macros from the list of macros in the Macros dialog.

Options

If you wish to make barcode images from the data in several cells or an entire column, select the cells required or push the column button at the top of the column, then choose the required Barcode macro (PBarcode for pictures, FBarcode for font-based barcodes).

After the PBarcode macro has run the spreadsheet contains pictures super-imposed over cells. The pictures cannot be selected by clicking on a column header. However, the macro SelectPictures allows all pictures on the spreadsheet to be selected simultaneously, and the macro DeletePictures allows them all to be deleted. Note that these two macros affect all pictures on the spreadsheet – so if there are images other than barcodes created with PBarcode those image will be selected or deleted as well.

On the other hand the FBarcode macro creates font-based barcodes in the adjacent cell or column. In both cases the destination of the barcode may be modified by editing the macro as illustrated below.

If you use the Barcode macros frequently you may wish top create a key shortcut for the macros concerned. To do this select the required macro in the Macros dialog, then push the Options button. Enter the required shortcut key in the dialog box presented and push the OK button.

Shortcut Options

Now whenever you select a cell and push the appropriate shortcut key a barcode will be placed in the adjacent cell.

Customisation of the Excel macros

By default the PBarcode macro centres each barcode image over the cell adjacent to its data source cell using the cellcolumn1 value as shown below (where cellcolumn is the column that contains the source data). The height of the row is adjusted to be 120% of the barcode image height. By editing the code the barcode position can be customised.

cellcolumn1 = cellcolumn + 1
St$ = (Cells(cellrow, cellcolumn).Text)
If Len(St$) = 0 Then ' skip empty cells
    Barcode = -1
    GoTo done
    End If
'
i = 0
er1 = BarCodew(ByVal St$, i)
If er1 = 0 Then
    Cells(cellrow, cellcolumn1).ClearContents
    Cells(cellrow, cellcolumn1).Select
    Cells(cellrow, cellcolumn1).Interior.ColorIndex = xlNone
    Set px = ActiveSheet.Pictures.Paste
    Cells(cellrow, cellcolumn1).RowHeight = px.Height * 1.2 ' cell heights are in points; this is 20% larger than barcode height
    px.Border.LineStyle = xlNone
    ' centre barcode image in the cell
    ih = (Cells(cellrow, cellcolumn1).Height - px.Height) / 2
    iw = (Cells(cellrow, cellcolumn1).Width - px.Width) / 2
    px.Left = Cells(cellrow, cellcolumn1).Left + iw
    px.Top = Cells(cellrow, cellcolumn1).Top + ih
    Barcode = -1
Else
    If er1 = 1 Then
        x$ = "Wrong number of characters in barcode"
    ElseIf er1 = 4 Then
        x$ = "Illegal character in barcode data"
    Else
        x$ = "Error in barcode data"
    End If
    MsgBox x$
    Barcode = 0
End If
done:

The FBarcode macro places the font-based barcode text into the cell adjacent to the data, using the column identifier cellcolumn1% in the code below. The font used for the cell is set to the fontname and height returned from the BarCodewF() call (where it had been stored by the FBarcodeSetup macro)

Out$ = String(120, " ")
Barcode2 = 1
cellcolumn1% = cellcolumn% + 1 ' edit this line if you want the barcode elsewhere
St$ = (Cells(cellrow%, cellcolumn%).Text)
Cells(cellrow%, cellcolumn1%).ClearContents
If Len(St$) = 0 Then ' skip empty cells
    Barcode2 = -1
    GoTo done
    End If

fonname$ = String(48, " ")
fonht& = 24
j& = BarCodewF(ByVal St$, nn&, ByVal Out$, ByVal fonname$, fonht&)
If (j& > 0) Then
    Cells(cellrow%, cellcolumn1%) = Out$
    Cells(cellrow%, cellcolumn1%).Font.Name = fonname$
    Cells(cellrow%, cellcolumn1%).Font.Size = fonht&
    Rows(cellrow%).RowHeight = fonht& * 1.2
Else
    If j& = 1 Then
        x$ = "Wrong number of characters in barcode"
    ElseIf j& = 4 Then
        x$ = "Illegal character in barcode data"
    Else
        x$ = "Error in barcode data"
    End If
    MsgBox x$
    Barcode2 = 0
End If
done:

The destination cell for the barcode may be changed by editing the cellcolumn1% variable in

2D barcodes in Excel

Barcodes & Labels for Office can generate picture barcodes for most 2D types and font barcodes for some 2D types in Excel.

2D font-based barcodes in Excel are useful; for example, they may be used to provide 2D barcodes in Word's Mailmerge. However, there are some limitations when it comes to using these barcodes in Excel alone. Multi-line cells in Excel do not permit the adjustment of the line-spacing, so correctly formed 2D barcodes based on fonts cannot be printed directly from a worksheet.