How to Fix Excel Code 128 Barcodes Not Scanning
Question details
The user needs a way to properly generate Code 128 barcodes in Excel, as applying a standard barcode font creates visual barcodes that cannot be read by scanners.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Generating automated Code 128 barcodes within an Excel spreadsheet for inventory or tracking purposes.
- Observed behavior
- The generated Code 128 barcodes visually look correct but fail to scan, whereas barcodes generated from external online tools scan perfectly.
Verify that your hardware barcode scanner is properly configured to read Code 128 symbology by testing it on a known working barcode generated from an online tool.
Use a VBA Macro to Calculate the Code 128 Checksum
Implement a VBA macro to automatically generate the mandatory start, stop, and checksum characters required for Code 128 before applying the barcode font.
Code 128 requires specific mathematical encoding to be readable. A standard font cannot perform these calculations. By using a custom VBA function, you can translate your raw data into a properly encoded string that the barcode font can then render into a scannable image.
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Click 'Insert' from the top menu and select 'Module' to create a blank script window.
Paste a reliable open-source Code 128 VBA encoding script (widely available online) into the module, then save and close the VBA editor.
In your Excel sheet, type your new custom function (e.g., =Code128(A2)) in the destination cell to encode the data.
Select the cell containing the newly encoded string and change its font to your installed Code 128 font.
Utilize an Excel Barcode Generator Add-in
Install a dedicated Excel add-in that automatically handles the complex encoding and generates a scannable barcode image.
Manage Complex Spreadsheets and Macros with WPS Office
If you are dealing with barcode generation limitations or expensive licensing in Microsoft Excel, consider switching to WPS Office. It provides a highly compatible, feature-rich spreadsheet environment that fully supports VBA macros for complex Code 128 checksum calculations at no cost.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel inventory or barcode tracking file.
- 3. Enable Macros: Accept the security prompt to enable macros so your custom Code 128 encoding functions run seamlessly.
Frequently Asked Questions
Why does changing the font to Code 128 work visually but fail to scan?
Code 128 symbology requires a specific start character, a complex mathematical checksum character, and a stop character. Simply changing the font only alters the text's appearance but omits these mandatory machine-readable elements, causing scanners to reject it.
Can I generate a Code 39 barcode just by changing the font?
Yes. Unlike Code 128, Code 39 does not strictly require a complex checksum character. You can typically generate a scannable Code 39 barcode by simply adding asterisks to the beginning and end of your text (e.g., ="*"&A2&"*") and applying a Code 39 font.
Can I calculate the Code 128 checksum using built-in Excel formulas instead of VBA?
While it is technically possible using a very long and complex array of nested formulas (using MID, CODE, and MOD functions), it is difficult to maintain and highly prone to errors. Using a VBA function or an add-in is significantly more reliable.
