Excel 2D Barcode VBA Font Encoder

IDAutomation provides VBA font encoders for use in Excel with all barcode font products. 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. 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. Implementation of 2D fonts within Excel does have some minor limitations. If these limitations are not acceptable, IDAutomation recommends the Excel Native Barcode Generator because it creates images in cells without fonts.

Implementation and Tutorial:

1. This tutorial uses the 2D QR Code Font as an example which is also the same implementation as Data Matrix, Aztec, and PDF417. A separate Excel Linear 1D Code 128 Font example is also available that covers linear fonts in Excel because the implementation is slightly different.
2. Download the latest version of one of the barcode font packages. 
3. Install the IDAutomation 2D XLS barcode font. Windows users should run the installer in the package which is the file that ends in EXE. Users on Mac and other operating systems should extract all files in the zip and follow the font installation procedures to install the desired barcode font.
4. After the font is installed, open the Excel Example which is in the Integration folder. This is the example that will show how the font can be viewed directly in Excel.
   
   
   
5. If prompted, choose to Enable Content. This is necessary for the VBA Barcode Macros to function.
   
   
   
6. Make a note of the formula that is used to format data to the font by choosing the cell that contains the barcode. Copy and paste this formula into Notepad or another text editor so you can use the function later. When using the 2D font in Excel, there will be small lines when viewing on the screen in the barcode image but those lines should not appear when the barcode is printed or a high-resolution PDF is generated. Refer to the Excel 2D Font Limitations for more information about this.
   
   
   
7. 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.
8. If it is possible, IDAutomation recommends modifying the Excel Example provided because the VBA is already embedded within the example file. IDAutomation also provides a video demonstrating the easy method of modifying the example to use barcode fonts in Excel. To do this, open your existing spreadsheet and click on the uppermost far left cell to select the entire spreadsheet and choose copy. If this is not possible, refer to the Custom Spreadsheet Integration section below.
   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.
   
   
9. 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.
10. Note: It is suggested to save the spreadsheet as an Excel Macro-Enabled Workbook (XLSM) type of file.
    
11. Other Excel files are also included such as the Font Encoder App and a functional Word Mail Merge. The Font Encoder App demonstrates how to display the symbol in a simple Excel VBA program. Any of the examples provided may be customized and used directly. These files may not be available in versions prior to 2020, and in that case, Level 2 Support should be purchased for the associated product to obtain the latest version.

Custom Spreadsheet Barcode Integration

1. Open Excel and begin setting up the spreadsheet where the first column will be the Data to Encode and the second column will be the encoded data formatted for the barcode.
   
   
2. Return back to Excel and select the Visual Basic option from the Developer Tab. To enable the developer tab, go to File - Options - Customize Ribbon, select Developer under Main Tabs on the right-hand side, and click OK.
   
   
   
3. Click the developer tab and choose Visual Basic. 
   
4. This will open the Microsoft Visual Basic for Application screen. Select File - Import File.
   
   
5. Locate and import the VBA file that ends in .bas from the integration folder.
   
   After importing, the VBA will appear in the modules folder in the Project window:
   
   
6. Choose File - Close and Return to Microsoft Excel:
   
   
7. 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. In this example, the formula used is  =IDAutomation_QRFontEncoder(data to encode) and it is entered in cell B2 referencing cell A2 to encode the data.
   
   When the cell to encode is referenced, a string of random characters should appear:
   
   
8. At this point, the file could be saved with the data encoded for Mail Merge purposes, as the barcode font would be applied after the mail merge had been completed.
9. Change the font in the cell to the appropriate barcode font. Right-click on the cell, and select Format Cells:
   
   Select the IDAutomation2D XLS.ttf font:
   If the font was installed prior to beginning the tutorial and the font name appears but the preview of the font does not reboot the computer to register the font.
   
   
10. When using the IDAutomation2D XLS font, select "wrap text" under the text control section of the Alignment tab.
    
11. Click ok, and there should be a barcode in place of the encoded data:
    
    There may be small lines when viewing on the screen in the barcode image but those lines should not appear when the barcode is printed or a high-resolution PDF is generated. Refer to the Excel 2D Font Limitations for more information about this.
12. When saving the file, a pop-up may appear:
    
    Click No, and select .xlsm extension to save the file as a macro-enabled workbook.
    
    
13. For additional assistance, refer to the examples included in the integration folder. The examples may also be referred to for troubleshooting purposes to use a process of elimination to narrow down issues.