Bar Code Printing Macros & VBA Functions for Excel and Access

This website offers bar code macros and functions for the intermediate to advanced Quattro Pro user. This example implementation is the same for any of the Corel products that allow the client to use VBA, such as the Corel Office Suite and Corel Ventura. Corel versions 10 and above (including the most recent release of 11), allow the client to use VBA within the client's document, spreadsheet, or image. Versions prior to Corel 10.0 do not include the functionality necessary to use VBA and will require the client to manually encode the data. The client would have to use another tool, for instance IDAutomation's Barcode Check Digit Calculator, to encode the data string and then copy the data string into the application and apply the font at that time.

The macros (which consist of VBA code and functions) will generate check digits, add the start and stop characters and format the return string for one of IDAutomation's barcode fonts. The macros can be easily integrated into the client's application for automation purposes and the client doesn't have to be a programmer to use them - they are easy enough for intermediate and advanced users of Corel. The macros and VBA functions are ready to install and are only designed to calculate barcode strings for the barcode fonts from IDAutomation.com, Inc. The macros, functions and Visual Basic source code are free to use as long as the client is using them with one of IDAutomation's barcode fonts and they are compatible with Corel 10.0 or greater, running on Windows or Macintosh systems.

Self Checking Barcode Fonts, MICR and OCR fonts are compatible with Corel Office 10.0 or greater and may be used with ease. To use a self checking font such as Code 39 (AKA code 3 of 9), the user needs to insert a "*" or "!" before and after the data encoded. Therefore, if the user encodes TEST3OF9 in a barcode, the user would need to display the text *TEST3OF9* in a field with the Code 39 font selected. Such as the example below:

Next, the user applies the IDAutomationHC39M font, which is IDAutomation's Human Readable version of IDAutomation's Code 39 fonts.

The Following Macros are Available for Corel:

For all functions below the data input type is a single string. A number may be required for additional parameters. For example, Code128c("123456",0). For all number symbologies such as POSTNET and UPC, the user may input dashes or spaces for easier readability which will be filtered out by the function before the barcode is generated. For UPC-A, UPC-E & EAN-13 the user can enter the +2 and +5 add-on codes by just adding them to the end of the string.

Function	Notes
Code128(Data, Number)	If the user is not sure which Code 128 set is for the user's application, then use this one. This is a "Code 128 Auto" function that will automatically encode any data from ASCII 0 to ASCII 127. It will automatically switch to character set C for numbers also. To encode alpha-numeric UCC/EAN-128, ASCII 202 or character Ê is entered as the FNC1 before each AI. For example, the UCC number of (8100)712345(21)12WH5678 should be entered as: Ê8100712345Ê2112WH5678. More information about AIs and UCC/EAN-128 is located here. (Data, 0) formats barcode output string for the Code 128 barcode fonts. (Data, 1) returns the human readable data. (Data, 2) returns only the check digit.
Code128a()	Use the characters from set B to print characters not on the keyboard; the scanner will scan characters from set A. Formats output to the Code 128 barcode fonts.
Code128b()	Returns codes formatted to the Code 128 character set B. Formats output to the Code 128 barcode fonts.
Code128c(Data, Number)	This code128 function "interleaves" numbers into pairs for high density. (Data, 0) formats output to the Code 128 barcode fonts. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
I2of5()	This function "interleaves" numbers into pairs for high density without check digits and formats the return string to the Interleaved 2 of 5 fonts.
I2of5Mod10(Data, Number)	(Data, 0) performs the mod10 checksum calculation for increased accuracy and formats the return string to the Interleaved 2 of 5 fonts. MOD 10 checksums are required by USPS for special services and for SSC-14 when using Interleaved 2 of 5 for that purpose. (Data, 1) returns the human readable data with the MOD10 check digit included. (Data, 2) returns the MOD10 check digit.
Code39()	Formats the output to print using Code 39 fonts.
Code39Mod43(Data, Number)	(Data, 0) performs the mod43 checksum calculation for increased accuracy and then formats the output to print using Code 39 fonts. The mod43 checksum is usually required for LOGMARS and HIBC applications. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
Codabar()	Formats the output to print using Codabar fonts.
UCC128()	This symbology option encodes an even number of number digits and includes the FNC1 character in set C as required. Use Code 128 Auto to encode additional FNC1 codes or data containing text or odd numbers. Use the UCC128 function only for UCC-128 applications where the input data is an even number such as in SSCC-18 and SCC-14 barcodes. For example, to encode an SSCC-18 barcode, the user would enter 00000123455555555558 as the data input. For more information, please visit here.
SCC14(Data, Number)	SCC14(Data, 0) generates the MOD10 required and then formats the output to print EAN-14 and SCC-14 using Code 128 fonts. The input for Data must be a 13 to 17 digit number; however, only 13 are needed. The required application identifier of (01) is automatically added. SCC14(Data, 1) returns the human readable data for SCC14 / EAN14. SCC14(Data, 2) returns the MOD10 check digit.
SSCC18(Data, Number)	SCC18(Data, 0) generates the MOD10 required for SSCC-18 and then formats the output to print SSCC-18 using Code 128 fonts. The input for Data must be a 17 to 21 digit number; however, only 17 are needed. The required application identifier of (00) is automatically added. SCC18(Data, 1) returns the human readable data for SCC-18. SCC18(Data, 2) returns the MOD10 check digit.
Postnet(Data, Number)	Enter a single string of Zip, Zip + 4 or Zip + 4 + Delivery Point. The input for Data must be a number and can include dashes and spaces. (Data, 0) formats output to the POSTNET barcode fonts. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
USPS_EAN128 (Data, Number)	Used for 22 digit USPS special services labels such as delivery confirmation in EAN128. This is used with IDAutomation's Code 128 fonts. This new EAN128 format is mandatory as of January 10, 2004 according to the USPS Delivery Confirmation Service defined in the September 2002 version of Publication 91. Enter a 19 or 20 digit number; only the first 19 are used. This number is made up of the following: 2 digit service code + 9 digit customer ID + 8 digit sequential package ID + MOD 10 check digit that can be calculated by this function if excluded. In this function, the application identifier of 91 is automatically added for the user. Other USPS EAN128 barcode types must be created by calling Code128() with the appropriate ASCII 202 and AIs included (as documented at IDAutomation's Code 128 FAQ). The user may also refer to the USPS portion of the Code 128 FAQ. (Data, 0) formats output to the Code 128 barcode fonts. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
USPS_USS128 (Data, Number)	NOTICE: After 1/10/2004, the user must use the USPS_EAN128 function to meet USPS requirements. Used for 20 digit USPS special services numbers with Code 128 fonts according to the USPS Special Services defined in the February 2000 version of the Special Services Technical Guide. Enter a 19 or 20 digit number; only the first 19 are used. This number is made up of the following: 2 digit service code + 9 digit customer ID + 8 digit sequential package ID + MOD 10 check digit that can be calculated by this function if excluded. (Data, 0) formats output to the Code 128 barcode fonts. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
RM4SCC()	Formats the output to print using RM4SCC fonts. Checksum calculated according to the mailsort customer barcoding specification.
MSI(Data, Number)	(Data, 0) formats output to the MSI barcode fonts. (Data, 1) returns the human readable data with the check digit included. (Data, 2) returns only the check digit.
Code11()	Only the "C" check digit is calculated which is the standard when encoding 10 digits or less.
UPCa()	Enter UPC-A with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font.
UPCe()	Enter the full 11 or 12 digit UPC-A number. The purpose of this function is to print the UPC-E barcode from a UPC-A barcode that can be compressed. It will print a UPC-A if it cannot be compressed by zero suppression rules. Formats output to the UPC/EAN barcode font.
EAN13()	Enter EAN-13 with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font.
EAN8()	Enter exactly 7 characters (EAN-8 without the check digit). Formats output to the UPC/EAN barcode font.

View the formula in the cells listed under the "Barcode To Scan" column. It is this formula that retrieves the data to encode and passes it to the appropriate barcode function. The function will then format the data to encode in a string formatted to the barcode font.

Then, the font must be selected for the cell and MUST be the same symbology as in this example; the user would select the IDAutomationPOSTNET font to create a POSTNET barcode.

Our barcode macros used in Excel to format a POSTNET barcode.

If the user's function does not require parameters, the user may simply enter it without the ",0" as this example shows,"=Code128b(B15)" If the barcode does not scan or there is a need to troubleshoot, the user may change font to something like "Arial". Fully functional versions of IDAutomation's fonts must be installed to create a proper barcode.

The VBA code and functions reside inside the application. This makes it easy to deploy barcode applications for users that already use Quattro Pro. To use these functions, the user must import it into the application. If the user has not downloaded the VBA Macro, please click here.

This may be necessary if the user has a large column of data that needs to be bar coded and the user does not want to copy and paste the barcode font formula into each cell. In this example Code 128 barcodes were created in the Excel column B from the data in column A. This assumes the user has installed the Code 128 Font Package and imported the VBA module into the user's spreadsheet. First, enter the formula in one cell to format the data to the barcode font for that cell:

Then select that cell and choose Edit - Copy. After that, highlight the entire column by selecting the gray square labeled "B" at the top of the spreadsheet and choose Edit - Paste. The formulas may take a little time to re-calculate.

After the user does this the user will need to change the data in the column to be the barcode font. With the entire column that contains the formula highlighted, choose the IDAutomationC128S font or another appropriate font. The user's barcodes will appear in the entire column.

The database IDAutomation provides contains one table, some sample reports and one module. The module contains the VBA code, functions and macros. To pass data to the function and return it in a report the user would place the formula =function([table.field]) in the control source field. control source in Access 2000 for SSCC-18 barcode

control source in Access 2000 for SSCC-18 barcode

View the design of the reports to gain an understanding of how the bar code macros work. Fully functional versions of IDAutomation fonts must be installed to create a proper barcode.

The VBA code and functions reside inside the access database as a module. This makes it easy to deploy barcode applications for users that already use Access. To use these functions in another Access file the user must copy the module to another database or first export the module and then import it into the other database.

Microsoft Word does not have the capability to call VBA functions from within a document or mail-merge like Excel and Access can. Therefore, to barcode in MS Word, it is necessary to use Excel as the data source for the mail merge. The field used for the data source in Word should point to the column in Excel where the formula has already been applied. To setup the user's Excel spreadsheet with the macros, please see the Excel Tutorial.

After the user has downloaded and extracted the files, open "MS Word Mail Merge.doc". The user may get a message stating that the data source cannot be found. If this happens, choose "Data Source for Word Mail Merge.xls" as the data source. If asked to enable Macros, choose Yes.

In Excel, the user can view the formula in the cells listed under the "Barcode" column. It is this formula that retrieves the data to encode and passes it to the appropriate barcode function. The function will then format the data to encode in a string formatted to the barcode. This column is then passed to Word when the mail merge is started. Then, the barcode font must be selected for the field in MS Word (in this case it is «Barcode») and it MUST be the same symbology. The barcode will not look correct and will not scan until the mail merge is performed because before the mail merge, only the field is displayed. Fully functional versions of IDAutomation's fonts must be installed to create a proper barcode.

The VBA code and functions reside inside the Excel file, not the MS Word file. This makes it easy to deploy barcode applications for users that already use Word and Excel. To setup the user's Excel spreadsheet with the macros, please see the Excel Tutorial.

Quattro Pro & VBA Functions for Corel Office Suite 10.0 and greater on Windows or Macintosh Systems

The Following Macros are Available for Corel:

Quattro Pro & VBA Functions
for Corel Office Suite 10.0 and greater on Windows or Macintosh Systems