In-cell dfBarcode() formula for Excel

The function dfBarcode(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 =dfBarcode(cell,code,flags) into the cell. eg =dfBarcode(A1,7,0) produces an Code 128 barcode in the cell using data from the cell A1.

Similarly the function dfBarcodeH(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.

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

Function dfBarcode(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
    dfBarcode = ""
Else
    j& = Barcodeudf(ByVal St$, cd&, fl&, ByVal Out$, ByVal Human$)
    If (j& > 0) Then
        dfBarcode = Out$
    Else
        dfBarcode = ""
    End If
End If
End Function

Function dfBarcodeH(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

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

‘
‘ -------------------------------------------
‘

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.

Crystal Reports UFL