Font-based Barcode Printing in Excel
Here we will show how use Class based version of StrokeScribe barcode generator to produce linear and 2D barcodes in Excel with specialized TrueType barcode fonts.
Do we recommend the font-based method of making barcodes? No. When generating barcodes in bulk amounts, the xls file with font-based barcodes is twice the size of xls file with WMF pictures (see Using Formula to Create Barcode in Excel). Also, 100+ barcode shapes may slow down Excel (WMF pictures do not). Use the font-based barcode generation method only if you have a clear need to go with fonts.
Requirements
- Download and install the barcode generator
2. Open the VBA window (Alt+F11) and paste the code from the following examples into a worksheet module:
3. Add a reference to the StrokeScribe Class into your VBA project.
How to Use Linear Barcode Fonts in Excel
This example shows how to use a non-ActiveX barcode generator to produce plain text strings that display barcodes when a specially constructed TrueType font is applied to the cells. The example works with all Excel versions starting from Excel XP.
Sub barcode()
Dim wsh As Worksheet
Set wsh = Excel.ActiveSheet ' or Excel.Worksheets("Sheet2")
Dim rng As Range
Set rng = wsh.Range("C5") ' or wsh.Cells(5, 3)
rng.Font.Name = "StrokeScribe 1D" ' or "StrokeScribe 1D Small" or "StrokeScribe 1D Tall"
rng.Font.Size = 12 ' For 203 DPI thermal label printers, use 8/12/18pt fonts.
rng.HorizontalAlignment = xlCenter
rng.VerticalAlignment = xlCenter
Dim ssc As StrokeScribeClass
Set ssc = CreateObject("STROKESCRIBE.StrokeScribeClass.1")
ssc.Alphabet = CODE128 ' or EAN13 or UPCA or I2OF5
ssc.Text = "123" & Chr(9) & "ABC" ' use the Chr() function to encode ASCII control characters
If ssc.Error Then
MsgBox ssc.ErrorDescription
Exit Sub
End If
rng.Value = ssc.FontOut
End Sub
When printing labels on 203DPI thermal printers, use only the following font sizes: 8, 12, 18pt. Other font sizes may cause bar width inconsistency.
How to Use 2D Barcode Fonts (Excel 2007+)
Because all Excel versions we tested automatically add gaps between text lines and because Excel does not allow to adjust font properties (as Word does), it's not possible to draw font-based 2D barcodes in cells in easy way. Starting from Excel 2007, it's possible to use text shapes to properly display multi-line text.
Sub create_barcode()
Dim wsh As Worksheet
Set wsh = Excel.ActiveSheet
On Error Resume Next
Shapes("barcode").Delete
On Error GoTo 0
Dim ss As StrokeScribeClass
Set ss = CreateObject("strokescribe.strokescribeclass.1")
ss.Alphabet = QRCODE
ss.Text = "123ABCD"
shp_left = Application.InchesToPoints(1)
shp_top = Application.InchesToPoints(1)
Set sh = wsh.Shapes.AddLabel(msoTextOrientationHorizontal, shp_left, shp_top, 0, 0)
sh.TextFrame2.TextRange.Text = ss.FontOut
sh.TextFrame2.TextRange.Font.Name = "StrokeScribe 2D" ' or "StrokeScribe PDF417"
sh.TextFrame2.TextRange.Font.Size = 12
sh.TextFrame2.WordWrap = msoFalse
sh.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
sh.TextFrame2.TextRange.Paragraphs.ParagraphFormat.BaselineAlignment = msoBaselineAlignTop
sh.Name = "barcode"
End Sub
A List of Available Fonts
- "StrokeScribe 1D Small" - A small font for linear barcodes. 2mm height at 6pt.
- "StrokeScribe 1D" - A medium-size font, 4mm height at 6pt.
- "StrokeScribe 1D Tall" - A tall font, 8mm height at 6pt.
- "StrokeScribe 2D" - A font for matrix barcodes.
- "StrokeScribe PDF417" - A font to print PDF417 barcodes.
"StrokeScribe 1D xxx" - are universal fonts for linear barcodes. The fonts display Code-128/GS1-128, EAN-13/UPC-A/UPC-E, Code 39. These fonts do not support mailing barcodes and do not display human-readable characters below the barode.
All 1D-fonts have the same bar density at the same point size and optimized for printing on 203 DPI label printers.