In-cell Barcode() formula for Excel

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 a Code 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 below may be pasted in a module by:

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 shown below and paste it into the empty module
5. Select "Close and return to Microsoft Excel" from the Visual Basic File menu.

Rem -------------------------------------------
Rem code for simple Barcode() inline formula for Excel
Rem
Declare Function Barcodeudf Lib "dlsbar34" (ByVal szIn As String, ByRef cd As Long, ByRef fl As Long, ByRef szOut As String, ByRef szHuman As String) As Long

Function barcode(dat As Range, code As Integer, flags As Integer) As String
cd& = code
fl& = flags
Out$ = String(120, " ")
Human$ = String(84, " ")
St$ = dat.Text
If Len(St$) = 0 Then
    barcode = ""
Else
    j& = Barcodeudf(ByVal St$, cd&, fl&, ByVal Out$, ByVal Human$)
    If (j& > 0) Then
        barcode = Out$
    Else
        barcode = ""
    End If
End If
End Function

Function barcodeH(dat As Range, code As Integer, flags As Integer) As String
cd& = code
fl& = flags
Out$ = String(120, " ")
Human$ = String(84, " ")
St$ = dat.Text
If Len(St$) = 0 Then

    barcodeH = ""
Else
    j& = Barcodeudf(ByVal St$, cd&, fl&, ByVal Out$, ByVal Human$)
    If (j& > 0) Then
        barcodeH = Human$
    Else
       barcodeH = ""
    End If
End If
End Function

Rem
Rem -------------------------------------------
Rem

The code parameter is a number that determines the barcode type required (see the Barcode Types Table for complete list), and the flags parameter specifies all other options available. Generally this will be 0, but if automatic check digit calculation is required it should be 1. For additional options available through the flags parameter see the Barcodes Help file for a specified barcode type.

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 Barcode Macros product installed. Developers wishing to use in-line barcode formulae in spreadsheets for distribution should use the dBarcode 1D Developers Kit.

Barcode Macros for Access