| Home: Products: Font Tools: Bar Code Printing Macros & Pure VBA Functions for Microsoft Excel and Access: |
Barcode Macros & VBA Functions
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Overview: |
| "The IDAutomation.com Code 128 Font and VBA macros enabled us to integrate barcoding into a Microsoft Excel spreadsheet that works cross-platform between Classic Macintosh, OS-X and Microsoft Windows operating systems." - Charles Daneri, Objective Systems, LLC (computer consulting firm), Baltimore, MD. |
The macros provided consist of a pure VBA module with many functions. The macros are free to use with IDAutomation's licensed barcode fonts. The macros are compatible with Microsoft Office 97, Office 2000, Office XP, Office 2003 and Office 2007 running on Windows® or Office 2004 for Macintosh systems. Office 2008 for Mac removed the ability to use VBA macros, IDAutomation offers other MAC compatible options.
The unique encoding in IDAutomation's fonts and macros allow cross-platform support between Macintosh and Windows systems in most countries. For some double byte language settings such as Chinese, Korean and Japanese languages, IDAutomation suggests using the IDAutomation Universal Barcode Font Advantage™ with IDAutomation's Universal Font VBA functions.
(Universal font support only provided with
Purchase.)
| VBA Barcode Macro Tutorial for Excel: |
After the file has been downloaded and extracted, open the sample Excel spreadsheet provided. The Excel spreadsheet contains a few examples for various barcode types. A VBA module is included in the sample spreadsheet that contains custom functions and macros that format data to IDAutomation's barcode fonts. Use the sample spreadsheet as a reference if help is needed creating barcodes.
| "I found good documentation on your product, and clearly defined license terms. Your web site is well designed and has a wealth of information. We have been able to implement bar coded data collection and reporting. The barcode font has enabled us to streamline our data collection process significantly. It was extremely simple to set up. We use the Microsoft Excel macro to create internal forms that can easily be scanned throughout our manufacturing process." Thomas Bell, Cynosure, Westford, MA |
How to Create a Barcode in Excel:

| Adding Barcodes to an Entire Column in Excel |
IDAutomation's barcode fonts and macros provide an easy method of barcoding a column in Excel. In this example, Code 128 barcodes are created in column B from data in column A. This example assumes the Code 128 Font Package has been installed and the VBA module has been imported into the spreadsheet.
| Importing and Exporting VBA in Excel |
The barcode macros and functions reside inside the Excel file as a custom module. To use these barcode functions in an Excel spreadsheet, the IDAutomationVBA module must be imported. If the IDAutomationVBA.bas file is not available for import, it may be exported from the sample spreadsheet provided.
To Import the VBA functions and macros into a new spreadsheet on a Windows PC, choose Tools - Macro - Visual Basic Editor. Choose File - Import File. Because Office 2004 for Mac does not have an import feature, users will need to copy the RTF file manually by going to Tools - Macros - Visual Basic Editor. Once the editor window is open, go to Insert - Module and paste the contents of the RTF file for the corresponding type barcode being used. (For example, if Code 128 barcodes are needed using the Universal font, copy the IDAutomationC128UniFunctionMAC.rtf into the editor window.)
To Export choose Tools - Macro - Visual Basic Editor. Then choose View - Project Explorer. Highlight IDAutomationVBA within the Modules folder and choose File - Export File. Name for the exported file IDAutomationVBA.bas.
| Tutorial for Using VBA Barcode Macro Functions in Access: |
After the files have been downloaded and extracted, open the sample Access database provided. The sample database contains one table, some sample reports and one module. The module contains the custom VBA code, functions and macros. The sample database should be used as a reference if help is needed creating barcodes.
How to Create Barcode in an Access Report:
Before
starting this tutorial, ensure VBA macros are the desired implementation.
IDAutomation explains several methods of creating barcodes in Access
in the Barcode Integration
Guide for Microsoft Access.
To
format the data to the barcode in a text box, a function must be placed
in the Control Source specifying the data field that needs to be encoded.
The formula =function([field])
should be entered in the Control Source property of the text box, for
example: =Code128([Data]). In some cases,
it may be necessary to specify the table and field in the control source,
for example: =Code128([Table1.Field1]).
If there is a doubt as to which function to use,
IDAutomation recommends using the
Code 128 fonts
with the =Code128([field]) function. If
a large amount of data needs to be encoded, IDAutomation recommends
using the PDF417
Font and Encoder or the
Data Matrix
Font and Encoder.NOTE: When distributing Access Databases, the associated barcode font must be installed on each computer used to print barcodes. If this is an inconvenience, IDAutomation also offers the Native Barcode Generator for Access, which is a complete barcode generator object that stays embedded in the database. For information about other methods of bar-coding in Access, please refer to IDAutomation's Barcode Tutorial for Microsoft Access.
| Importing and Exporting Macros & VBA in Access |
The VBA code, functions and macros reside inside the access database as a VBA module. To use the functions in another Access database, either the module must be copied to the other database, or the module must be exported from the sample database and imported into the other database.
| Microsoft Word Mail-Merge Barcode Tutorial: |
Before starting this tutorial, ensure VBA macros are the desired implementation for a Word mail-merge. An easier method exists when using Codabar fonts (for numbers only) or Code 39 barcode fonts (for numbers and uppercase letters) which are explained in the Word Barcode Integration Guide.
To create barcodes in a Microsoft Word mail merge using macros, it is necessary to use Excel as the data source. The field used for the data source in Word should be the column in Excel where the formula has been applied, which is used to format the data to the barcode font. IDAutomation suggests using the last column of the spreadsheet in order to avoid any merging problems. To setup the Excel spreadsheet with the macros, please refer to the Excel Tutorial.
| VBA Macro Functions and Properties: |
The functions listed below are available in IDAutomation's IDAutomationVBA.bas file and are only valid when used with the font listed in the "font to use" column.
Functions beginning with IDAutomation_Uni_ are only to be used with the IDAutomation Universal Barcode Font Advantage™ and are supplied in the IDAutomationNativeWindowsDLLVBA.bas file provided with that package.
In all functions listed below, DataToEncode is the string data type. Other data types, such as numbers or dates, may need to be converted to the string data type to be properly encoded. Additional parameters are available for some functions and are optional. For example, Code128("123456",0,True). For UPC-A, UPC-E & EAN-13, the +2 and +5 add-on codes may be created by adding the digits to the end of the data being encoded.
| Function | Font to use | Notes |
| Code128(DataToEncode,
Optional ReturnType, Optional ApplyTilde) |
IDAutomationC128 | The recommended and primary Code
128 function that automatically encodes all text from
ASCII
1 to ASCII
127. This method
contains many options. It may be necessary
to use the optional ReturnType and ApplyTilde for special purposes: |
| Code128a(DataToEncode) | IDAutomationC128 | Formats output to the Code 128 barcode fonts, character set A. |
| Code128b(DataToEncode) | IDAutomationC128 | Returns codes formatted to the Code 128 character set B. Formats output to the Code 128 bar code fonts. |
| Code128c(DataToEncode, Optional ReturnType) |
IDAutomationC128 | This code128 function "interleaves"
numbers into pairs for high density. (DataToEncode, 0) formats output to the Code 128 barcode fonts. (DataToEncode, 1) returns the human readable text with the check digit included. (DataToEncode, 2) returns only the check digit. |
| Code39(DataToEncode) | IDAutomationC39 or IDAutomationHC39 |
Formats the output to print using Code 39 fonts. |
| Code39Mod43(DataToEncode, Optional ReturnType) |
IDAutomationC39 or IDAutomationHC39 |
(DataToEncode, 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. (DataToEncode, 1) returns the human readable data with the check digit included. (DataToEncode, 2) returns only the check digit. |
| Code93(DataToEncode) | IDAutomationC93 | Formats the output to print with the 2 required check digits using Code 93 fonts. |
| Codabar(DataToEncode) | IDAutomationCB | Formats the output to print using Codabar fonts. |
| Code11(DataToEncode) | IDAutomationC11 | Formats output to the Code11 barcode fonts. Only the "C" check digit is calculated which is the standard when encoding 10 digits or less. |
| EAN13(DataToEncode) | IDAutomationUPCEAN | DataToEncode is a number string of 12, 13, 14, 15, 17 or 18 digits with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros. |
| EAN8(DataToEncode) | IDAutomationUPCEAN | DataToEncode is a number string of 7 or 8 characters (EAN-8 without the check digit). Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros. |
| I2of5(DataToEncode) | IDAutomationI25 or IDAutomationHI25 |
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(DataToEncode, Optional ReturnType) |
IDAutomationI25 or IDAutomationHI25 |
(DataToEncode, 0) performs the mod10
checksum calculation for increased accuracy and formats the return
string to the
Interleaved
2 of 5 fonts.
USPS
requires MOD 10 checksums for special services
and for SSC-14 when using Interleaved 2 of 5 for that purpose. (DataToEncode, 1) returns the human readable data with the MOD10 check digit included. (DataToEncode, 2) returns the MOD10 check digit. |
| IntelligentMail(DataToEncode) | IDAutomationPOSTNET | Formats output to produce USPS Intelligent Mail & OneCode barcodes with the IDAutomationPOSTNET font.** |
| MOD10(DataToEncode) | not applicable | Returns the MOD 10 check digit for a given string of numbers according to the UCC/EAN method. |
| MOD37(DataToEncode) | not applicable | The ISO7064 MOD 37-2 Checksum Algorithm
that is commonly used in
ISBT-128
barcodes to calculate the keyboard check character K. This function
is only available after the IDAutomation_MOD37.vb
module is imported. (DataToEncode, 0) returns the check digit. (DataToEncode, 1) returns bracket characters around the check digit. |
| MSI(DataToEncode, Optional ReturnType) |
IDAutomationMSI or IDAutomationHMSI |
(DataToEncode, 0) formats output
to the MSI barcode
fonts. (DataToEncode, 1) returns the human readable data with the check digit included. (DataToEncode, 2) returns only the check digit. |
| Postnet(DataToEncode, Optional ReturnType) |
IDAutomationPOSTNET or IDAutomationPLANET |
Enter a single string of Zip, Zip
+ 4 or Zip + 4 + Delivery Point or any number of digits for the
planet code. The DataToEncode must be a number and can include dashes
and spaces. (DataToEncode, 0) formats output to the POSTNET barcode fonts. (DataToEncode, 1) returns the human readable data with the check digit included. (DataToEncode, 2) returns only the check digit. |
| RM4SCC(DataToEncode) | IDAutomationRM | Formats the output to print using RM4SCC fonts. Checksum calculated according to the mail sort customer bar-coding specification. |
| SpliceText(DataToEncode, Optional SpacingNumber, Optional ApplyTilde) | Text font | Returns text with a space inserted every SpacingNumber of digits to improve readability. If ApplyTilde is set to True, the tilde will be processed as described here. ApplyTilde is False by default. |
| UCC128(DataToEncode) | IDAutomationC128 | This symbology option calls the Code128() method with the Tilde option enabled and the FNC1 character in set C as required. It is often an easier method to use when encoding an even number of number digits. For example, to encode an SSCC-18 barcode, the user would enter 00000123455555555558 as the data input. For more information, please visit here. Formats output to the Code 128 barcode fonts. |
| UPCa(DataToEncode) | IDAutomationUPCEAN | DataToEncode is a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros. |
| UPCe(DataToEncode) | IDAutomationUPCEAN | DataToEncode is a UPC-E number of 6, 7 or 8 digits or a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported only with 13, 14, 16 or 17 digits. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing "00005000000". UPC-E1 is supported when the first character is "1" instead of "0". |
| The following functions are no longer included because they have been replaced with the Code 128() function. | ||
| USPS_EAN128 () | IDAutomationC128 | The USPS_EAN128
function is no longer provided because it has been replaced by IDAutomation's
Code128() function. Used for USPS special services labels such as delivery confirmation in EAN128 according to the USPS Delivery Confirmation Service defined in the September 2002 version of Publication 91. This is used with IDAutomation's Code 128 font IDAutomationC128L at 16 points. The following formula will create USPS_EAN128 in Code 128 with the Code128() function: IDAutomation_Code128 ("Ê" & "912312214123442441134" & "~m19", 0, True ) Where the number of 912312214123442441134 may be replaced with the user's data field, for example: IDAutomation_Code128 ("Ê" & {Table1.DataField1} & "~m19", 0, True ) This number encoded is made up of the following: 2 digit service code + 9 digit customer ID + 8 digit sequential package ID + MOD 10 check digit. For more information, refer to the USPS portion of the Code 128 FAQ and the UCC/EAN portion of the Code128 Barcode FAQ. |
| SCC14() | IDAutomationC128 | The SCC14
function is no longer provided because it has been replaced by IDAutomation's
Code128() function. The following formula will create SCC14 in Code 128 with the Code128() function: IDAutomation_Code128 ("Ê" & "013001234567890" & "~m13", 0, True ) For more information, refer to the UCC/EAN portion of IDAutomation's Code128 Barcode FAQ. |
| SSCC18() | IDAutomationC128 | The SCC18
function is no longer provided because it has been replaced by IDAutomation's
Code128() function. The following formula will create SCC18 in Code 128 with the Code128() function: IDAutomation_Code128 ("Ê" & "0000801234999999999" & "~m17", 0, True ) For more information, refer to the UCC/EAN portion of IDAutomation's Code128 Barcode FAQ. |
| All the functions listed below are preceded with IDAutomation_Uni_ and are only to be used with the IDAutomation Universal Barcode Font Advantage™ and are supplied in the IDAutomationNativeWindowsDLLVBA.bas file provided with that package. | ||
| Function | Font to use | Notes |
| C128(DataToEncode, ApplyTilde) | IDAutomation_Uni | The recommended
and primary Code 128 function that automatically encodes all text
from ASCII
1 to ASCII
127. This method
contains many options. This is a "Code
128 Auto" function that will automatically encode any data from
ASCII 1 to ASCII 127. It will automatically switch to character
set C to encode numbers as necessary. To encode alpha-numeric
UCC/EAN-128, ASCII 202 or character Ê is entered as the
FNC1 before each AI. When any barcode begins with the FNC1, it automatically
starts in Set C as required. |
| C128HR(DataToEncode, ApplyTilde) | Text font | If Code 128 barcodes
are being created which need the text formatted, this function may
be used. It is generally only used to format the text for UCC/EAN
barcodes according to IDAutomation's
Code128 Barcode FAQ. For example: IDAutomation_C128HR ("Ê" & "8100712345" & "Ê" & "2112345678", TRUE ) If ApplyTilde is set to True, the tilde will be processed. ApplyTilde is True by default in this function. |
| C128A(DataToEncode) | IDAutomation_Uni | Formats output to set A of Code-128. Use caution with this option because any lowercase character creates a function. Use the letter "i" for a tab and "m" for a return. For most purposes, it is better to use the C128() function instead of this one. |
| C128B(DataToEncode) | IDAutomation_Uni | Formats output to Code-128, character set B. For most purposes, it is better to use the C128() function instead of this one. |
| C128C(DataToEncode) | IDAutomation_Uni | This code128 function "interleaves" even numbers into pairs for high density. An even number of digits is required. For most purposes, it is better to use the C128() function instead of this one. |
| C39(DataToEncode, N_Dimension, IncludeCheckDigit) | IDAutomation_Uni | Formats the output for bar-coding in Code 3 of 9 with the universal font. A MOD 43 checksum will be calculated if IncludeCheckDigit is true. For example: IDAutomation_Uni_C39 ("123456789", 3, TRUE ) |
| Codabar(DataToEncode, N_Dimension, StartChar, StopChar) | IDAutomation_Uni | Creates Codabar
(aka NW7) with the universal font. StartChar and StopChar are also
required as the start and stop characters. Valid start and stop
characters are A, B, C and D. IDAutomation_Uni_Codabar ("123456789", 3,"A","B" ) |
| I2of5(DataToEncode, N_Dimension, IncludeCheckDigit) | IDAutomation_Uni | This function "interleaves" numbers into pairs for high density without check digits and formats the return string to the universal font. An even number of digits is required. A MOD 10 checksum will be calculated if IncludeCheckDigit is true. |
| IntelligentMail(DataToEncode) | IDAutomation_Uni | Formats output to produce USPS Intelligent Mail and OneCode barcodes with the IDAutomation_Uni font.** |
| MSI(DataToEncode, N_Dimension, IncludeCheckDigit) | IDAutomation_Uni | Formats output for bar-coding in the MSI/Plessey symbology. A MOD 10 checksum will be calculated if IncludeCheckDigit is true. |
| Planet(DataToEncode, IncludeCheckDigit) | IDAutomation_Uni | NOTE: Because this bar-code type has a specific height requirement, this function only works with the XS, S or M size of the Universal Font. XS is the normal version, S has the bars narrow by 10% and the M font has the bars narrow by 20%. DataToEncode is a single string of Zip, Zip + 4 or Zip + 4 + Delivery Point. A MOD 10 checksum will be calculated if IncludeCheckDigit is true. |
| Postnet(DataToEncode, IncludeCheckDigit) | IDAutomation_Uni | NOTE: Because this bar-code type has a specific height requirement, this function only works with the XS, S or M size of the Universal Font. XS is the normal version, S has the bars narrow by 10% and the M font has the bars narrow by 20%. DataToEncode is a single string of Zip, Zip + 4 or Zip + 4 + Delivery Point. A MOD 10 checksum will be calculated if IncludeCheckDigit is true. |
| SpliceText(DataToEncode, Optional SpacingNumber, Optional ApplyTilde) | Text font | Returns text with a space inserted every SpacingNumber of digits to improve readability. If ApplyTilde is set to True, the tilde will be processed as described here. ApplyTilde is False by default. |
| UCC128(DataToEncode) | IDAutomation_Uni | This symbology
option automatically encodes the FNC1 character in set C as required
for UCC-128 barcodes. All functions of ApplyTilde
are enabled in this function for calculating the required MOD 10
check digit and cannot be disabled, for example: IDAutomation_Uni_C128 ("00000123455555555558" & "~m19" ) For more information, please visit the UCC/EAN FAQ. |
| MODU10(DataToEncode) | not applicable | A MOD 10 check digit is returned. This is useful in calculating check digits for UCC/EAN 128 barcodes where a MOD check digit is needed. More information about UCC/EAN-128 is located here. |
** USPS Intelligent Mail and OneCode support requires the installation of the IDAutomationNativeFontEncoder.dll file, which may be downloaded from http://www.idautomation.com/fonts/tools/windows_dll/. Pure VBA code for this function has not been produced because of the complexity of the calculations involved. The DLL is free to use, provided You or Your organization has purchased a valid License for associated barcode font.
Description
of the Parts of the Functions:
|
|
|
Product Quick Links: [Barcode Fonts | Font Tools | Components | Label Software | Scanners | Printers | RFID | Product Index] |
© Copyright 2000-2008 IDAutomation.com, Inc., All Rights Reserved. Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Legal Notices.
|
Over 70% of Fortune 100 companies use IDAutomation's products to automate their businesses. |