Excel 1D Barcode VBA Font Encoder
IDAutomation provides VBA font encoders for use in Excel with all barcode font products. The VBA encoder is compatible with Excel on Windows 2010 and greater and Excel on Mac 2016 and greater. It is also compatible with local installations of Office 365.
This tutorial is designed for 1D linear barcode fonts such as Code 128, ITF, UPC, EAN, and Code 39. When using 2D barcodes, refer to the Excel 2D Font Encoder. This type of implementation displays the barcode symbol directly within the cells of a spreadsheet. Barcode symbols can also populate an entire column or row. This example implements Code 128 from the Universal Font Package.
- Download and open the zip file download and Install the barcode font. Windows users may run the setup application for an automated installation. Users on Mac and other operating systems should extract all files in the zip and open the Excel Example, which is located in the Integration folder. Open the Excel Example, and choose Enable Content. This is necessary for the VBA barcode macros to function.
- Verify the example works by changing the data and scanning the barcode. The free Barcode Data Decoder App may be used to test scan the barcode.
- If it is possible, IDAutomation recommends modifying the Excel example because the VBA is already embedded within the example file. If this is not possible, refer to the Custom Spreadsheet Integration section below. IDAutomation also provides a video demonstrating the easy method of modifying the example to use barcode fonts in Excel.
- Open your existing spreadsheet and click on the uppermost far left cell to select the entire spreadsheet and choose copy.
Switch to the Excel Example spreadsheet, select a new sheet at the bottom such as "Sheet 2", click on the uppermost far left cell and choose Paste. After this, you should be able to copy the cell that contains the barcode from "Sheet1" and paste it into "Sheet 2". Be sure to modify the formula so the proper data is encoded in the barcode and size the cell containing the barcode so it is wide enough to contain the entire symbol.
- To populate an entire column or row with barcodes, copy the cell containing the barcode and paste it into a selection so the formula adjusts appropriately. More information about this is provided at the OfficeBarcode page about how to populate a column with barcodes in Excel.
- Note: It is suggested to save the spreadsheet as an Excel Macro-Enabled Workbook (XLSM) type of file.
Custom Spreadsheet Integration
- If possible, IDAutomation recommends modifying the Excel example provided because the VBA is already embedded within the example file. If it is desired to use an existing spreadsheet, the VBA must first be imported. To do this, add the Developer tab, if it is not already there. Go to File - Options - Customize Ribbon, select Developer under Main Tabs on the right-hand side, and click OK.
Next, choose Developer - Visual Basic - File - Import File and import the VBA file. The VBA file will be located in the integration folder of the downloaded zip file. After the VBA is imported it should show up in the Modules folder.
- Enter the formula in the cell to generate the text that is formatted to the barcode font. This is the text that created the barcode when combined with the appropriate barcode font.
- Change the font in the cell to the appropriate barcode font and the barcode should appear. Many barcode fonts are difficult to select from the font menu. If this is the case, right-click on the cell and choose Format Cells, choose the Font tab, and select the font this way. Be sure to select the correct point size for your application and size the cell large enough so there is an appropriate white space around the symbol.
- To populate an entire column or row with barcodes, refer to the OfficeBarcode page about how to populate a column with barcodes in Excel.
- Save the spreadsheet as an Excel Macro-Enabled Workbook (XLSM) type of file.
- An Excel Font Encode App is also provided to easily copy and paste barcodes into other programs or for custom VBA modification.
- Using Code 39 Fonts in Excel.
- ActiveX barcodes in Excel.