Native Barcode Generator for Excel
- Native Barcode Integration
- Barcode Add-in Integration
- Generating Barcodes in Multiple Cells
- Functions of the Native Barcode Generator
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.
- Extract the files from the Excel Native Barcode Generator package.
- A working example is provided in each package and may be referred to if needed.
- Open the Spreadsheet where the barcode is needed.
- Press ALT-F11 (Mac users choose Option-F11) to open the Visual Basic Editor. If ALT-F11
does not work, choose Developer - Visual Basic.
- 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.
- 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.
- 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.
- 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.
- If multiple cells are to contain barcodes,
perform the following:
- Delete the barcode image from a cell that correctly generates the
- Click on that cell and choose Copy.
- 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 about of data being encoded and the number of cells.
- Delete the barcode image from a cell that correctly generates the barcode symbol.
- Scan the barcode to ensure the image is generated correctly.
- 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
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.
- Extract the files from the Excel Native Barcode Generator package.
- Open the appropriate file ending in xlsm from the files extracted.
- Choose File - Save as and select Excel Add-in (*.xlam).
Note: for Excel 2003 the option will be (*.xla) instead of (*.xlam).
- 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 addins are stored in \Program Files\Microsoft Office\Office14\ADDINS\ .
- After the addin is installed, it must be activated. Activate the addin for use in Excel by using Excel’s addin manager (File > Options > Add-ins > Manage add-ins).
- Open the Spreadsheet where the barcode is needed and follow the steps above from step 6, Generating the Barcode.
The formula used in step 7 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:
Alternatively, to change the options to generate barcode
stripe image, the formula would be:
To generate a QR-Code Font Image (for use with the included barcode font)
with a QuietZone of 4, the formula would be:
To generate a
DataBar Expanded Stacked barcode, with a Segment of 4, the formula would be:
- 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 =
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 form 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.)
- 1 = BMP image (Default)
- Available options:
- 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
- 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:
- CheckCharacterInText - When true shows the check character within the HR text.
- StartChar & StopChar - For Codabar, the start and stop characters to encode in the symbol. These must be entered in parentheses. For example, =IDAutomation_NXLS_Codabar(B3,1,2,"A","B")
|Code-128 Auto||IDAutomation_NXLS_C128 (DataToEncode, ImageType, ApplyTilde, QuietZone) (ApplyTilde is automatically set to True in this function)|
|IDAutomation_NXLS_C128HR (DataToEncode, ApplyTilde, QuietZone) (ApplyTilde is automatically set to True in this function)|
|Code 128 A||IDAutomation_NXLS_C128A(DataToEncode, ImageType, QuietZone)|
|Code 128 B||IDAutomation_NXLS_C128B(DataToEncode, ImageType, QuietZone)|
|Code 128 C||IDAutomation_NXLS_C128C(DataToEncode, ImageType, QuietZone)|
|Code-39||IDAutomation_NXLS_C39(DataToEncode, ImageType, N_Dimension, IncludeCheckDigit, QuietZone)|
|IDAutomation_NXLS_C39HR(DataToEncode, N_Dimension, IncludeCheckDigit, CheckCharacterInText, QuietZone)|
|Code 93||IDAutomation_NXLS_C93(DataToEncode, ImageType, QuietZone)|
|IDAutomation_NXLS_C93HR(DataToEncode, CheckCharacterInText, QuietZone)|
|Codabar||IDAutomation_NXLS_Codabar(DataToEncode, ImageType, QuietZone, StartChar, StopChar)|
|IDAutomation_NXLS_CodabarHR(DataToEncode, StartChar, StopChar, QuietZone)|
|EAN-13||IDAutomation_NXLS_EAN13(DataToEncode, ImageType) (This function only returns image types 1 and 3 by design)|
|Interleaved 2 of 5||IDAutomation_NXLS_I2of5(DataToEncode, ImageType, N_Dimension, IncludeCheckDigit, QuietZone)|
|IDAutomation_NXLS_I2of5HR(DataToEncode, N_Dimension, IncludeCheckDigit, CheckCharacterInText, QuietZone)|
|MSI||IDAutomation_NXLS_MSI(DataToEncode, ImageType, N_Dimension, IncludeCheckDigit, QuietZone)|
|IDAutomation_NXLS_MSIHR(DataToEncode, N_Dimension, IncludeCheckDigit, CheckCharacterInText, QuietZone)|
|Intelligent Mail||IDAutomation_NXLS_IMb(DataToEncode, ImageType, QuietZone)|
|Postnet||IDAutomation_NXLS_Postnet(DataToEncode, ImageType, IncludeCheckDigit, QuietZone)|
|Planet||IDAutomation_NXLS_Planet(DataToEncode, ImageType, IncludeCheckDigit, QuietZone)|
|UPC-A||IDAutomation_NXLS_UPCA(DataToEncode, ImageType) (This function only returns image types 1 and 3 by design)|
|Data Matrix||IDAutomation_NXLS_DataMatrix(DataToEncode, ImageType,
|GS1-DataBar||IDAutomation_NXLS_GS1DataBarOmni(DataToEncode, ImageType, Height, QuietZone)|
|IDAutomation_NXLS_GS1DataBarStackedOmni(DataToEncode, ImageType, Height, QuietZone)|
|IDAutomation_NXLS_GS1DataBarExpanded(DataToEncode, Segments, ImageType, Height, QuietZone)|
- Cross-platform Issues: When transferring a spreadsheet containing a 2D symbol from Windows to Mac, an image may loose its shape. Selecting the image type of 1 (BMP) may resolve the issue.
- 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.
- X dimension: Because the symbols generated form 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 including the IDAutomation 2D scanner with PQA 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.
- 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.
- 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
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.