Native Barcode Generator for Excel User Manual

• Native Barcode Integration
• Barcode Add-in Integration
• Generating Barcodes in Multiple Cells
• Functions of the Native Barcode Generator
  • Properties
• Support

Buy License Support Download Demo Release Log

This product provides barcoding capability to Microsoft Excel spreadsheets with an embedded VBA macro, making it easy to distribute without needing to distribute additional fonts or other components. Four image types are available including EPS and 1 bpp BMP.

Compatibility

Compatible with locally installed versions of Excel 2007 and greater in Windows and Excel 2016 and greater on Mac with VBA support. Because this product requires VBA support, it is not compatible with the web version of Excel 365; to support this IDAutomation recommends Excel 365 Web Streaming Integration.

Excel Native Barcode Integration

This tutorial demonstrates how to embed dynamic redistributable barcode objects in Microsoft Excel spreadsheets. This product uses VBA macros directly in the Excel file so that even when it is distributed, the spreadsheet will still be able to generate barcodes.

1. Extract the files from the Excel Native Barcode Generator package.
2. A working example is provided in each package and may be referred to if needed. Open the Excel Example.
3. If prompted, choose to Enable Content. This is necessary for the VBA barcode macros to function.
   
4. Verify the example works by changing the data and scanning the barcode. The free Barcode Data Decoder App may be used to test scan popular barcode types such as Code 128, ITF, UPC, EAN, Data Matrix, and QR Code.
5. 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.
6. IDAutomation recommends modifying the Excel example provided because the VBA is already embedded within the example file. If this is not possible, skip to the Custom Spreadsheet Integration section below.
7. Open your existing spreadsheet and click on the uppermost far left cell to select the entire spreadsheet and choose copy.
8. 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 and tall enough to contain the entire symbol.
   
9. Proceed to step 6 below.

Custom Spreadsheet Integration

1. Open the spreadsheet where the barcode is needed.
2. Press ALT-F11 (Mac users choose Option-F11) to open the Visual Basic Editor. If ALT-F11 does not work, enable the Developer Tab and choose Developer - Visual Basic. 
   
3. From the Visual Basic Editor, choose File - Import File and import the appropriate module from the VBA folder of the files extracted. Once the files are imported, they become embedded in the spreadsheet file.
   
4. Choose File - Save and then close the Visual Basic Editor. All functions of the chosen module are now embedded in the spreadsheet.
   Note: It is suggested to save the spreadsheet immediately as an Excel Macro-Enabled Workbook (XLSM) to ready the file for distribution.
   
   
5. Generating the Barcode:
   Size the cell that will contain the barcode symbol so there is enough space to contain it. The size of the barcode will be generated to fit within the cell. If entire columns or rows need to contain barcodes, select the entire rows or columns and size these appropriately.
   
   NOTE: If multiple cells will contain barcode symbols, do not use the ImageType of "2" because it can take Excel a long time to draw shape symbols.
6. Enter the formula, for example, =IDAutomation_NXLS_C128(A3) in the cell that will contain the barcode image and verify the size is correct. Only the cell reference is required; all other properties are optional. Once the image is generated it may have to be deleted to gain access to the formula in the cell, which resides behind the image.
   
   If it is desired to have the text interpretation below the barcode symbol, use the function ending in HR. All functions ending in HR automatically return a BMP image.
   
   
7. If multiple cells are to contain barcodes, perform the following:
   1. Delete the barcode image from a cell that correctly generates the barcode symbol.
      
      
   2. Click on that cell and choose Copy.
      
      
   3. Click on the other cells that are to contain barcodes and choose Paste, then return to the original copied cell and hit Enter. It may take some time for the images to be generated depending on the amount of data being encoded and the number of cells. 
      
8. Scan the barcode to ensure the image is generated correctly. The free Barcode Data Decoder App may be used to test scan most barcode types such as Code 128, ITF, Data Matrix, and QR Code.
9. If the cells are resized and the images need to be regenerated, DataToEncode will need to be changed. Changing the formula to encode nothing, for example, =IDAutomation_NXLS_C128("") will remove the barcode image from the cell.
10. Note: It is suggested to save the spreadsheet as an Excel Macro-Enabled Workbook (XLSM) type of file.
    

Barcode Add-In Integration

Excel Add-In Integration adds barcode generation functionality for a particular user or computer. If the spreadsheet is distributed, Native Barcode Integration is recommended. The add-in must be installed and then activated before it will function.

1. Extract the files from the Excel Native Barcode Generator package.
2. Open the appropriate file ending in xlsm from the files extracted.
3. Choose File - Save as and select Excel Add-in (*.xlam).
   
   Note: for Excel 2003 the option will be (*.xla) instead of (*.xlam).
4. Choose save. The add-in is now installed for the current user. To install it for all users on a computer, shut down Excel and move the add-in file from %appdata%\Microsoft\Addins, (where %appdata% is the system shortcut for the user’s profile (roaming) folder) to the appropriate ADDINS folder within Program Files. For example, Excel 2010 add-ins are stored in \Program Files\Microsoft Office\Office14\ADDINS\ .
5. After the add-in is installed, it must be activated. Activate the add-in for use in Excel by using Excel’s add-in manager (File > Options > Add-ins > Manage add-ins).
6. Open the spreadsheet where the barcode is needed and follow the steps above from step 5, Generating the Barcode.

Functions | Formulas

The formula used in step 8 of this tutorial may be altered to generate different barcode types and change options, which are listed below. Only DataToEncode is required; all other properties are optional. For example, to create a Code 128 barcode from the data in cell A3, the formula would be:
 =IDAutomation_NXLS_C128(A3)

Alternatively, to change the options to generate a barcode stripe image, the formula would be:
 =IDAutomation_NXLS_C128(A3,4)

To generate a QR-Code Font Image (for use with the included barcode font) with a QuietZone of 4, the formula would be:
 =IDAutomation_NXLS_QRCode(A3,3,,,,,,,4)

To generate a DataBar Expanded Stacked barcode, with a Segment of 4, the formula would be:
"=IDAutomation_NXLS_GS1DataBarExpanded($B$7,4)"

Common Properties:

• DataToEncode - This string value represents the data being encoded.
• N_Dimension - Determines the width of the wide bars, which is a multiple of the X dimension. Valid values are 2 and 3. The default is 2 which creates a smaller symbol.
• IncludeCheckDigit- A Boolean value that specifies whether a check digit should be automatically calculated and included for the DataToEncode.
• QuietZone - The white area around the symbol which is a multiple of the smallest bar. The default for 2D barcodes is 1 and for linear barcodes is 2.
• ImageType - The type of image that is returned. The default is 1 for a BMP image.
  • Available options:
    • 1 = BMP image (Default)
      Generates 1bpp BMP images that are very small in size and quicker to generate than EPS images. They also retain their size when transferring spreadsheets from PC to Mac. They can, however, look and print blurry from low-resolution devices.
      
    • 2 = Excel shapes
      This type of drawing is very slow and is not recommended when using multiple symbols within a sheet. To refresh the shape image, the DataToEncode must be changed.
      
    • 3 = Font text
      Returns a text string that when combined with the included font creates a barcode. If this option is used, the fonts within the package must be installed on each computer using the product. This option should only be used when the font produces a better result than other options. The fonts to install are IDAutomation2D XLS for all 2D barcodes, IDAutomation DataBar 13 for GS1 DataBar, and IDAutomationUniXS or IDAutomationUniXXS for all linear barcodes. IDAutomationUPCEANXXS is also provided for the UPCA and EAN13 functions. No other fonts or sizes are provided since these are the only sizes that display correctly within Excel. If other font sizes are needed, the appropriate font package must be purchased. Any cell using IDAutomation2D XLS must be set to "wrap text" to stack the 2D symbol. If it is necessary to embed the font for distribution, it may be possible to embed the Excel worksheet in a Word document in which the desired font is also embedded.
    • 4 = Barcode Stripe
      Valid only for Code 128, ITF, Code 93, and Code 39. Creates a shortened barcode that looks like a strip and consumes a minimal amount of space.
      
      The cell that contains the stripe barcode should have the font set to Courier New (or another mono-spaced font) at 3 or 4 points. This cell must also be wide enough to contain the entire stripe because this type does not auto-size.
      
      This strip may be easily scanned with a linear CCD scanner such as the IDAutomation USB Scanner when the red aiming beam is completely aligned over it.
      
    • 0 = EPS image
      This is an encapsulated Postscript (EPS) vector graphic image recommended for use on the Mac only. EPS images may not retain their exact size when transferred from PC to Mac because they are vector images. If this is an issue, use the BMP image type instead.
      (NOTE: Microsoft has identified a security vulnerability with EPS images in Office applications running on Windows. A Microsoft update released in April 2017 disables EPS images within Excel. Because of this, the 2017 release update of this product was modified so that the BMP option (ReturnType 1) is the default. EPS images may still be used on the Mac without issue.)
• ShowHRText - When true, the HR interpretation is generated as a BMP image. The image type must be set to (1) BMP for the image to be properly generated. For ease of use, this functionality is automatically included in all functions ending in HR. For example, IDAutomation_NXLS_C128HR returns a BMP image with the HR text interpretation.
  
• NewLineCharacter - By default, this is equal to vbCrLf and it may be changed if necessary.
• Height - valid only for GS1 DataBar, should be either 12, 24 (default), or 34.
• Segments - valid only for GS1 DataBar, stacks the symbol when set to a low even number such as 4 or 6. To generate a DataBar Expanded Stacked Image, with a Segment of 4, the formula would be:
  "=IDAutomation_NXLS_GS1DataBarExpanded($B$7,4)"
  
• CheckCharacterInText - When true shows the check character within the HR text.
• ApplyTilde and ProcessTilde - Applicable to Code 128, PDF417, Data Matrix, and QR Code. When enabled, the following options are available:
  - The FNC1 may be encoded with ~202 in Code 128 and ~1 in GS1-DataMatrix or GS1-QRCode.
  - ~??? encodes a 3-digit ASCII character in Code 128 or PDF417.
  - ~d??? encodes a 3-digit ASCII character in Data Matrix or QR Code.
  - ~m?? calculates a MOD10 check character from the preceding ?? digits in Code 128, Data Matrix, or QR Code.
  - ~f?? calculates a MOD43 check character, available only in Data Matrix.
  - ~CO overlays a block image in QR Code according to the Center Override formula.
  - TLV in Base64 - in QR Code when the first character is | Excel automatically encodes TLV values separated by | in Base64, for example:
       |IDAutomation|123456789012345|2022-11-30 06:04:31|92435.77|2692.77
  - TLV without Base64 - in QR Code when the first two characters are ^| then automatically encode TLV values separated by | for example:
       ^|IDAutomation|123456789012345|2022-11-30 06:04:31|92435.77|2692.77
  - Base64 - in QR Code when the first character is = encode the DataToEncode in Base64 excluding the =
- Verify ASCII, GS1 data, TLV and UTF8 with the Barcode Decoder Verification App.

• StartChar & StopChar - For Codabar, the start and stop characters encoded in the symbol. These must be entered in parentheses. For example, =IDAutomation_NXLS_Codabar(B3,1,2,"A","B")
  

Support & Troubleshooting

Common Problems and Solutions:

1. Cross-platform Issues: When transferring a spreadsheet containing a 2D symbol from Windows to Mac, an image may lose its shape. Selecting the image type of 1 (BMP) may resolve the issue.
2. Image Resize or Removal: If the cells are resized and the images need to be regenerated, DataToEncode will need to be changed. Changing the formula to encode nothing, for example, =IDAutomation_NXLS_C128("") will remove the barcode image from the cell.
3. X dimension: Because the symbols generated from this product are designed to fit within their respective cells, the X dimension will have to be determined by adjusting the size of the cell and performing a visual comparison with one of the IDAutomation Barcode Creators using the same data. Additionally, many barcode verifiers can also read the X dimension. When using ReturnType 3 with a barcode font, the point size determines the X dimension. Refer to the font specifications for each particular font to determine the sizes available.
4. Image Refresh: Changing properties may not always cause the barcode image to be updated. By design, the image is only updated when the DataToEncode changes.
5. Transparent Background: By default the background color of all images is white. EPS and shape images can be changed to transparent by locating the following statement in the VBA and changing it to true:
   Const BackgroundTransparent As Boolean = False
6. Image Type Issues: In some cases when changing the ImageType, duplicate images may appear in the cell. This is resolved by removing the images from the cell and restarting Excel. After restarting, refresh the image by updating the DataToEncode.
7. #Value! Error: If you receive this error in the formula cell, refer to the #Value! Error incident.
   

Additional problems and solutions are provided in public support forum threads.

Priority phone, email, and forum support are provided up to 30 days after purchase. Additional priority phone, email, and forum support may be obtained if the Level 2 Support and Upgrade Subscription is active.