IDAutomation.com, Inc., Computers Hardware, Software & Services, Tampa, FL Contact IDAutomationView your cart in the IDAutomation Store  
Loading
Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft® Excel®, Access® and Word®

Visual Basic VBA Barcode Macro & Functions Tutorial
for Microsoft Office Excel, Access & Word on Windows or Mac

IDAutomation VBA Barcode Functions and Macros allow easy generation of barcodes in Microsoft Office Suite applications such as Excel, Word and Access on both Windows® and Mac operating systems. These font encoder tools can be used to format the data-to-encode into a special string that will display an accurate barcode when the appropriate font is applied to it. Supported barcode types include Code 128, Code 39, Postnet, Intelligent Mail IMb, Interleaved 2 of 5, DataBar, UPC/EAN and many others.

These encoder are highly customizable, which also means they may be more powerful and configurable than the average user might need. For a popular Windows-based alternative, consider the Barcode Add-In for Word and Excel which allows for an easy method of displaying cells of data as barcodes. IDAutomation font encoder tools may be used with a demo or purchased version of compatible IDAutomation Barcode Fonts packages.

Download IDAutomation VBA Barcode Macros & Functions  Support for the IDAutomation VBA Barcode Macros & Functions
Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft Excel, Access and Word

VBA Barcode Macro & Functions Overview

"The IDAutomation Code 128 Barcode 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.

These special Macros consist of pure VBA modules with customizable code, functions and other options that allow for advanced, high-powered dynamic and variable data barcode generation. Consequently, this also means that the implementation may be more complicated for the average user. These font encoder tools format the data-to-encode into a text string that will display a readable barcode when the appropriate IDAutomation font is applied to it.

The Macros are free to use with the purchase of any IDAutomation linear barcode font and are compatible with Microsoft Office 97, 2000, XP, 2003, 2007, 2010 and 2013 running on Windows« or Office 2004 and 2011 for Mac platforms. This tool is not compatible with Office 2008 because Mac had removed the ability to use VBA Macros in that version. IDAutomation has a variety of Mac Barcode Integration options for those using Microsoft Office 2008 for Mac.

The unique encoding in IDAutomation barcode fonts and VBA tools allow cross-platform support in the US Western character set (ISO Latin-1) and between Windows, Mac, Linux and others. When this cannot be used, Mac users and those located outside of the US Western character set should use the IDAutomation Universal Barcode Font Advantage package with related Universal Font VBA functions.

Download the VBA Barcode Macros & Functions Font Encoder Tool

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 in the downloaded file.

Note: The Visual Basic Editor feature was removed from Excel for Mac 2008, and thus this font tool cannot be used in that environment.

  • Importing
    • Excel 1997-2003: Select Tools - Macro - Visual Basic Editor.
    • Excel 2007: Add Developer tab, if not already there. Right click on the Office button in upper left corner of Excel screen and choose Customize Quick Access Toolbar. Click on Popular at the top of the list on the left and then click Show Developer Tab in the Ribbon and save. Now go to Developer - Visual Basic - File - Import File
    • Excel 2010 and 2013: Add Developer tab, if not already there. Go to File - Options - Customize Ribbon, select Developer under Main Tabs on the right hand side and click OK. Now go to Developer - Visual Basic - File - Import File. Step-by-Step Instructions
    • Excel for Mac 2004 and 2011: Office for Mac does not have an import feature, and so users must copy and paste the necessary RTF file, which are available by request after purchase of a Universal Barcode Font license, 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. 
  • Exporting in Windows: Choose Tools - Macro - Visual Basic Editor. Within the editor, choose View - Project Explorer. Highlight IDAutomationVBA within the Modules folder and choose File - Export File. Name for the exported file IDAutomationVBA.bas.

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

  • Copy the Module to Another Database: Choose the Modules folder. Highlight the IDAutomationVBA module and choose File - Save As. Save the module to an external file. Choose the database to copy the module to and click OK. This option is not available in all versions of Access.
  • Exporting: Choose the Modules folder. Highlight the IDAutomationVBA module and select File - Save As. Save the module to an external file named IDAutomationVBA.
  • Importing:
    Access 2000 - 2003: Select Tools - Macro - Visual Basic Editor.
    Access 2007 - 2013: Select the Database Tools tab - Visual Basic.
    Choose the Modules folder. Select the IDAutomationVBA file and save the module when asked.
Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft Excel, Access and Word

Excel VBA Barcode Generation Tutorial

Before starting, ensure that these VBA Macros will achieve the desired goal. If uncertain, consult the Excel Barcode Integration Guide for a variety of scenarios and product suggestions. After downloading the file and extracting its contents, open the sample Excel spreadsheet provided. This spreadsheet contains some 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 for creating barcodes if help is needed, or contact IDAutomation for additional support.

"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 barcoded 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

  1. Enable the security setting that allows Macros to run. If prompted when the document re-opens, choose Enable Macros.
    • 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 2004 for Mac settings are located at Excel - Preferences - Security - Macro Security.
    • Office 2007 applications require the Macros to run from a trusted location.
    • Signed Macros are included in the latest version of the VBA Module download. Simply choose the Trust this Publisher option and the VBA Macros will run unprompted. Data may be pasted into these files and used if the Macros are not modified.
  2. If a different spreadsheet is used other than the sample provided, the Macros must be imported into the spreadsheet.
  3. Create a column in the spreadsheet for the barcode.
  4. Size the column to make sure it is wide enough to contain the entire barcode.
  5. 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.
  6. Enter the formula in this cell that will format the data to the barcode font. If unsure which function or barcode to use, consider the Code 128 barcode fonts with the =Code128(B10,0) function where "B10" refers to the cell location of the data that is to be encoded and the ",0" formats the result of the formula to the Code 128 fonts.
  7. 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, but this is normal. In some cases, the data may need to be reformatted and may appear scrambled. This is normal for Code 128 and Interleaved 2 of 5 barcode fonts when numbers need to be compressed within the barcode.
    Make Sure the Proper Barocde Data is Being Formatted in the Excel Spreadsheet.
  8. After verifying that the text in the cell has been formatted correctly, select the appropriate font and set the point size to 12. This example selects the IDAutomationC128XS font. The XS and S sizes of IDAutomation's fonts in Code 128 and Code 39 are specifically designed to format correctly in Excel, as larger fonts will not usually format correctly in the cell.
    Apply the Barcode Font to the Text Generated by Excel Formula
  9. Ensure that the column is wide enough to contain the entire barcode with some white space before and after the barcode.
  10. Scan the printed barcodes with a handheld USB barcode scanner to verify the correct data is encoded.

Barcoding 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 Barcode 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 Barcode Tutorial for Excel:
    Entering Formulas into Excel Helps to Correctly Format a Barcode.
  2. Select that cell and choose Edit - Copy.
  3. At this point, it may be necessary to deselect any cells not needed for barcoding. Then, highlight an entire column by selecting the gray square letter-labeled area at the top of the spreadsheet and choose Edit - Paste. In this example, the button is marked B. If the spreadsheet is large, the formulas may take some time to paste and re-calculate if the spreadsheet is large.
     Select the Gray Column Identifier, B in this Case, and Choose Edit - Paste.
  4. Change the column to the appropriate barcode font. In this example, choose the IDAutomationC128S font while the entire column that contains the formula is selected. The barcodes will appear in the entire column.
    Highlight the Full Column of Data-to-Encode and Select the IDAutomationC128S Font from the Drop Down List.
  5. Confirm that the barcodes are accurate by scanning them from printed sheets, or directly from the screen. IDAutomation offers a variety of barcode readers that can perform this function with ease.

Video Tutorials

Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft Excel, Access and Word

Access VBA Macro Barcode Functions Tutorial

Enter the Function Formula in the Control Source FieldAfter the appropriate files have been downloaded and extracted, open the sample Access database provided. This database contains one table, one module and some sample reports. The module contains custom VBA code, functions and Macros. The sample database should be used as a reference if help is needed.

How to Create Barcodes in an Access Report

How to Create a Barcode in Microsoft Access 2010

View Video Full Screen on YouTube
Before starting this tutorial, ensure that VBA Macros are the desired implementation. Several methods of creating barcodes in Access are explained in the Barcode Integration Guide for Microsoft Access.
  1. If a different database is being used and not the sample provided, first import the VBA Macros into the database.
  2. The 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.
    • If prompted when the document is reopened, select Enable Macros.
      Office 2007 applications may require that the Macros run from a trusted location. Signed Macros are included in the latest version of the VBA download. Simply choose the Trust this Publisher option and the VBA Macros will run unprompted. Tables, Reports, Forms and other information may be pasted into these files and used if the Macros are not modified.
  3. Open a report in Design Mode.
  4. Create a text box where the barcode will display.
  5. To format the data to the desired barcode font 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 unsure of which function to use, consider the =Code128([field]) function with Code 128 Barcode Fonts.
    • 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],0,True)
  6. These Sample Reports are Provided in Access 2000Run the report to make sure the data is being populated from the fields and formatted to the barcode font. Strange characters may append to the beginning and ending of the data, but 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.
  7. Change the formula field font to the appropriate barcode font and set the point size to 12. If this step is not followed, generated barcodes will not display.
  8. Print and scan the barcodes to verify that the correct data is encoded. If a scanner is needed to verify barcodes, consider the IDAutomation USB Barcode Scanner.

Note: When distributing Access Databases, the associated barcode font must be installed on each computer that prints the barcodes. If this is inconvenient, the Native Barcode Generator for Access is a complete barcode generator object that stays embedded in the database, which means no fonts need to be installed on user computers. For more information about other methods of barcoding in Access, please refer to the Microsoft Access Barcode Integration Guide.

If a large amount of data needs to be encoded, the PDF417 Barcode Font and Encoder or the Data Matrix Barcode Font and Encoder may be the best options as these 2D barcode types allow for encoding of more data.

Video Tutorial Create a Barcode in Access 2010

Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft Excel, Access and Word

VBA & Macro Functions in Microsoft Word Mail-Merge

How to Microsoft Word Mail Merge Barcodes using Microsoft Access Query and Barcode Fonts

View Video Full Screen on YouTube

Before starting this barcode tutorial, ensure VBA Macros are the desired implementation for a Word mail-merge. An easier method exists when using Codabar or Code 39 fonts, and this is explained further in the Barcode Integration Guide for Microsoft Word.

Excel must be used as the data source when creating barcodes in a Microsoft Word mail merge using Macros. 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. It is best to use the last column of the spreadsheet in order to avoid merging conflicts.

To set up an Excel spreadsheet with these barcode Macros, refer to the Barcode Tutorial for Excel.

Note: C128 ReturnTypes 6 through 9 are not currently supported in Excel-Word mail-merges.

  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, go to Excel - Preferences - Security and select Macro Security to enable the secure setting.
  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 so, select Data Source for Word Mail Merge.xls as the data source. If asked, 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 to verify that the correct data has been encoded. If a scanner is needed to verify barcodes, IDAutomation suggests 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.
Home > Font Encoders > Barcode Macros & VBA Functions for Microsoft Excel, Access and Word

VBA Barcode 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_ may only be used with the IDAutomation Universal Barcode Font Advantage™ package and are supplied in the IDAutomationNativeWindowsDLLVBA.bas file provided within that package.

DataToEncode is the string data type for all functions listed below. 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.

VBA Functions for Standard Barcode Fonts
Barcode Type Barcode Function Methods and Notes Required Font
Code-11 Code11 (DataToEncode) IDAutomationC11
Code-128
(Auto Mode)
Code128 (DataToEncode, C128 ReturnType, ApplyTilde)
Human Readable text is enabled when ReturnType = 6

Example:

Code128("123456789012", 6, 0)
IDAutomationC128
Code-128
(Manual Mode)
IDAutomation recommends using Auto Mode for most situations.
Code128a (DataToEncode, C128 ReturnType)
Code128b (DataToEncode, C128 ReturnType)
Code128c (DataToEncode, C128 ReturnType)


Example:

Code128b("12345678", 6)
IDAutomationC128
Code-39 Code39 (DataToEncode)
Code39Mod43 (DataToEncode, ReturnType)

Example:
Code39Mod43("12345678", 0)
IDAutomationC39
Code-93 Code93 (DataToEncode) IDAutomationC93
Codabar Codabar (DataToEncode) IDAutomationCB
EAN-13 IDAEAN13 (DataToEncode) IDAutomationUPCEAN
EAN-8 IDAEAN8 (DataToEncode) IDAutomationUPCEAN
GS1-128 Code128 (DataToEncode, C128 ReturnType, 1)
GS1-128 is enabled in Code 128 Auto by setting ApplyTilde to True. Human Readable AIs may be created by setting the C128 ReturnType to 6. Example:

Code128("(12)3456789012", 6, 1)
IDAutomationC128
Interleaved 2 of 5 I2of5 (DataToEncode)
I2of5Mod10 (DataToEncode, ReturnType)

Example:
I2of5Mod10("123456789", 1)
IDAutomationI25
MSI / Plessey MSI (DataToEncode, ReturnType) IDAutomationMSI
RM4SCC RM4SCC (DataToEncode) IDAutomationRM
UPC-A UPCa (DataToEncode) IDAutomationUPCEAN
UPC-E UPCe (DataToEncode) IDAutomationUPCEAN
USPS IntelligentMail (IMb) IntelligentMail (DataToEncode) **
Included only in the purchased version of the USPS IMb Font Package.
IDAutomationPOSTNET
or IDAutomationIMB
or
IDAutomation_Uni
USPS Postnet Postnet (DataToEncode, ReturnType) IDAutomationPOSTNET
USPS Planet Planet (DataToEncode, ReturnType) IDAutomationPOSTNET
not applicable MOD10 (DataToEncode) not applicable
not applicable SpliceText (DataToEncode, SpacingNumber, ApplyTilde) not applicable
Functions for the Universal Barcode Font
All functions listed below are preceded with IDAutomation_Uni_.* They may only be used with the IDAutomation Universal Barcode Font Advantage™ and are supplied in the IDAutomationNativeWindowsDLLVBA.bas file provided within that package. *

Note: Excel 2004 for Mac requires the functions to be lowercase. Ex: idautomation_uni_c128

Barcode Type Universal Barcode Function Methods and Notes Required Font
Code-128 IDAutomation_Uni_C128 (DataToEncode, ApplyTilde)

Note: Code128() is the recommended method to use. Code128() is also used to create GS1-128.

IDAutomation_Uni_C128A (DataToEncode)
IDAutomation_Uni_C128B (DataToEncode)
IDAutomation_Uni_C128C (DataToEncode)

Example:
IDAutomation_Uni_C128 ("╩8100712345╩2112WH5678", TRUE)

Excel for Mac:

idautomation_uni_c128 ("╩8100712345╩2112WH5678", TRUE)
IDAutomation_Uni
not applicable C128HR (DataToEncode, ApplyTilde)
Returns text for Code 128 barcodes, such as for GS1-128.
Text Font
Code-39 IDAutomation_Uni_C39 (DataToEncode, N_Dimension, IncludeCheckDigit)

Example:
IDAutomation_Uni_C39 ("123456789", 3, TRUE )

Excel for Mac:

idautomation_uni_c39 ("123456789", 3, TRUE )
IDAutomation_Uni
Codabar IDAutomation_Uni_Codabar (DataToEncode, N_Dimension, StartChar, StopChar)

Example:
IDAutomation_Uni_Codabar ("123456789", 3,"A","B" )

Excel for Mac:
idautomation_uni_codabar ("123456789", 3,"A","B" )
IDAutomation_Uni
Interleaved 2 of 5 IDAutomation_Uni_I2of5 (DataToEncode, N_Dimension, IncludeCheckDigit) IDAutomation_Uni
MSI / Plessey IDAutomation_Uni_MSI (DataToEncode, N_Dimension, IncludeCheckDigit) IDAutomation_Uni
USPS IntelligentMail IDAutomation_Uni_IntelligentMail (DataToEncode) ** IDAutomation_Uni
USPS Postnet IDAutomation_Uni_Postnet (DataToEncode, IncludeCheckDigit) IDAutomation_Uni
USPS Planet IDAutomation_Uni_Planet (DataToEncode, IncludeCheckDigit) IDAutomation_Uni
Functions for the DataBar Barcode Font
These functions listed below are preceded with IDAutomation_DataBar and are only provided with the IDAutomation DataBar Font Advantage Package.
Barcode Type DataBar Barcode Function Methods and Notes Required Font
DataBar DataBar (DataToEncode) IDAutomation DataBar 34
DataBar Expanded DataBarExpanded (DataToEncode, 22) IDAutomation DataBar 34
DataBar Exp. Stacked DataBarExpanded (DataToEncode, Segments) IDAutomation DataBar 34
DataBar Limited DataBarLimited (DataToEncode) IDAutomation DataBar 13
DataBar Stacked DataBarStacked (DataToEncode) IDAutomation DataBar 13
DataBar Stacked Omnidirectional DataBarStackedOmniDirectional (DataToEncode) IDAutomation DataBar 34
DataBar Truncated DataBar (DataToEncode) IDAutomation DataBar 13

Barcode Function Descriptions

  • ApplyTilde: If set to "1" or "True", characters following the tilde may be used to perform additional calculations or encode ASCII characters directly.
  • DataToEncode: This string value represents the data being encoded.
  • N_Dimension: Determines the width of the wide bars when applicable, 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.
  • Segments: Reducing segments to a low, even number such as 4, 6 or 8 in a DataBar Expanded symbol can create a stacked barcode. The default is 22.

** The USPS Intelligent Mail (IMb) function is supplied in pure VBA code with the purchased version of the associated font package.

These Functions Have Been Replaced with Code 128() and are no Longer Included
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 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 )
Refer to the Code128 Barcode FAQ for more information about UCC/EAN.
 © Copyright 2014 IDAutomation.com, Inc., All Rights Reserved. Legal Notices.

 Barcode Fonts | Components | Scanners | Support | Contact Us
Training Videos on YouTube Join us on Google Plus Join us on LinkedIn Follow us on Twitter Like us on Facebook
Over 70% of Fortune 100 companies use IDAutomation's products to automate their businesses.