Barcode Macros & VBA Functions
for Microsoft® Office Excel®, Access® and Word® on Windows® or Macintosh Systems

Easily generate barcodes in Microsoft Office applications with IDAutomation's Barcode Macros & Native VBA Functions. When data needs to be encoded in a barcode, the macros format the data and return a character string that will create a correct barcode when combined with the appropriate Barcode Font. This product may be used royalty free with a valid license to any of IDAutomation's barcode fonts.

Index:

• Excel Tutorial
  • Adding Barcodes to an Entire Column in Excel
  • Importing and Exporting VBA in Excel
• Access Tutorial
  • Importing and Exporting VBA in Access
• Word Mail Merge Tutorial
• VBA Macro Functions and Properties
• Download VBA Macros (Universal font support only provided with Purchase.)
• Support
• Order or Download
Barcode Fonts - Prices from $99 for the Single User License

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.)

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.

How to Create a Barcode in Excel:

1. Before starting this tutorial, ensure VBA macros are the desired implementation. IDAutomation explains several methods of creating barcodes in Excel in the Barcode Integration Guide for Microsoft Excel.
2. Excel 2000 and 2003 requires a medium security setting to run macros. Open Excel and set the security level to medium by choosing Tools - Macro - Security. Office 2007 applications may require the macros to run from a trusted location. When the document is re-opened, choose “enable macros” if prompted. In Office 2004 for Mac, secure setting can be enabled by going to Excel - Preferences - Security and selecting Macro Security.
3. If a different spreadsheet is used and not the sample provided, the macros must be imported into the spreadsheet.
4. Create an area in the spreadsheet for the barcode.
5. Size the column to make sure it is wide enough to contain the entire barcode.
6. Format the column so that any text appearing in it will be centered. This is necessary to create the white space (often called the quiet zone) before and after the barcode.
7. Enter the formula in this cell that will format the data to the barcode font. If there is a doubt as to which function or barcode to use, IDAutomation recommends using IDAutomation's Code 128 fonts with the =Code128(B10,0) function where "B10" refers to the cell location of the data that is to be formatted and encoded in the barcode, the ",0" formats barcode output string to the Code 128 barcode fonts.
8. Examine the spreadsheet to make sure the data is being properly formatted to the barcode font. Strange characters may append to the beginning and ending of the data from the fields - this is normal. In some cases, the data may need to be reformatted and it may appear to be scrambled. This is normal for Code 128 and Interleaved 2 of 5 when numbers need to be compressed within the barcode.
   
9. After verification that the text in the cell has been formatted, select the appropriate font and point size for the cell. In this example, the IDAutomationC128XS font is selected to create a Code 128 barcode. If unsure of the point size to use, IDAutomation recommends 12 points. The XS and S sizes of IDAutomation's fonts in Code 128 and Code 39 are specifically designed to format correctly in Excel. Larger fonts will not usually format correctly in the cell.
   
10. Ensure the column is wide enough to contain the entire barcode with some white space before and after the barcode.
11. Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation offers many hand-held USB barcode scanners.

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.

1. Enter the formula in one cell to format the data to the barcode font as explained in the Excel tutorial:
   
2. Select that cell and choose Edit - Copy.
3. Highlight an entire column by selecting the gray square labeled "B" at the top of the spreadsheet (it may be necessary at this point to deselect any cells that are not needed for barcoding) and choose Edit - Paste. The formulas may take some time to paste and re-calculate if the spreadsheet is large.
    
4. Change the font of the column to the appropriate barcode font. In this example, with the entire column that contains the formula selected, choose the IDAutomationC128S font. The barcodes will appear in the entire column.
   
5. Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation offers many hand-held USB barcode scanners.

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.

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:

1. 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.
2. If a different database is being used and not the sample provided, the macros must first be imported into the database before proceeding.
3. Most recent versions of Access require a medium security setting to run macros. Open Access and set the security level to medium by choosing Tools - Macro - Security. When the document is re-opened, choose “enable macros” if prompted. Office 2007 applications may require the macros to run from a trusted location.
4. Open a report in design mode.
5. Create a text box where the barcode will appear.
6. 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.
   Optional: The Control Source may be changed to a formula that appends text to the data or combines multiple fields,
   for example: =Code128("PREFIX" & [Field1])  or  =Code128([Field1] & [Field2])  or  =Code128([Field1] & "," & [Field2]). In Code 128, the ApplyTilde feature may also be used to encode tab and return functions. For example, the following formula creates a tab function between two fields: =Code128([Field1] & "~009" & [Field2])
7. Run the report to make sure the data is being pulled from the fields and formatted to the barcode font. Strange characters may append to the beginning and ending of the data from the fields - this is normal. In some cases, the data may need to be reformatted and it may appear to be scrambled. This is normal for Code 128 and Interleaved 2 of 5 when numbers need to be compressed within the barcode.
8. Change the font of the formula field to the appropriate barcode font and set the point size. This is a very important step; if this is not done, barcodes will not be displayed. If unsure of the point size, IDAutomation recommends 12 points.
9. Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation offers many USB Barcode Scanners.

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.

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.

• To copy the module to another database: (This option is not available in all versions of Access) Choose the Modules folder. Highlight the IDAutomationVBA module and choose File - Save as. Choose to save the module to an external file. Choose the database to copy the module to and choose OK.

• To export: Choose the Modules folder. Highlight the IDAutomationVBA module and choose File - Save as. Choose to save the module to an external file named IDAutomationVBA.

• To import: Choose the Modules folder. Choose Insert - Module, then choose Insert - File. Choose the IDAutomationVBA file and save the module when asked.

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.

1. Excel 2000 and 2003 require a medium security setting to run macros. Open Excel and set the security level to medium by choosing Tools - Macro - Security. Office 2007 applications may require the macros to run from a trusted location. In Office 2004 for Mac, secure setting can be enabled by going to Excel - Preferences - Security and selecting Macro Security.
2. After the files have been downloaded and extracted, open the included Word Mail-Merge Document. A message may appear 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, choose to enable Macros.
3. The formula may be viewed in the cells of the "Barcode" column in the Excel spreadsheet. This formula retrieves the data to encode and passes it to the appropriate barcode function. The function will then format the data to the barcode font. This column is then passed to Word when the mail merge is started. The appropriate barcode font must be selected for the field in MS Word; in this case, it is «Barcode».
4. The barcode will not look correct and will not scan until the mail merge is performed.
5. After the merge is performed, print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation suggests the easy-to-use USB Barcode Scanners.
6. When distributing the mail-merge, the font should be embedded in Word and the Excel data source must be accessible.

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.

** 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:

• DataToEncode - this string value that 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, 2.5 and 3. The default is 2. The X dimension is determined by the font point size.
• IncludeCheckDigit - a Boolean value that determines whether a check digit should be automatically calculated and included for the DataToEncode.
• ApplyTilde - If the ApplyTilde option is set to True, characters following the tilde may be used to perform additional calculations or encode ASCII characters directly.

© 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.