Barcode Macros & VBA Functions
for Microsoft® Office Excel®, Access® & Word® on
Windows® or MAC
Easily generate barcodes in Microsoft Office applications with IDAutomation
Barcode Macros & Native VBA Functions. These barcode macros are highly
customizable, which also means they may be more powerful and configurable
than the average user might need. For an easy-to-use alternative in
any Windows environment, IDAutomation recommends the
Barcode Add-In for Word and Excel. Both font tools may be used
royalty free with a valid license to any of
IDAutomation's
Barcode Fonts.
Index:
Barcode Macro & VBA Compatibility 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. |
The barcode macros consist of a pure VBA module with
many functions, options and customizable code,
which also means they may be more complex to implement. 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.
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 barcode fonts and VBA allow cross-platform
support in the US Western character set (ISO Latin-1) and between Windows,
MAC, Linux and others. Additional compatibility in all character
sets is provided with
ReturnType 6. When
ReturnType 6 cannot be used, MACs and users not in the US Western
character set should use the
IDAutomation
Universal Barcode Font Advantage™ with IDAutomation's
Universal Font VBA functions.
(Universal Font VBA is provided after
Purchase.)
To easily generate barcodes on Windows systems, (for example, select
and click functionality) IDAutomation recommends the
Barcode Add-In for Word and Excel.
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 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:
- 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. Watch our
How to create a barcode in Excel,
Excel 2007 or
Excel for MAC 2004 video.
- Enable a
security
setting that allows macros to run. When the document is re-opened,
choose “enable macros” if prompted.
- 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 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.
- If a different spreadsheet is used and not the sample provided,
the macros must be imported into the spreadsheet.
- Create a column in the spreadsheet for the barcode.
- Size the column to make sure it is wide enough to contain the
entire barcode.
- 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.
- 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
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.
- 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 barcode fonts when numbers need to be compressed within the barcode.
- After verifying that the text in the cell has been formatted,
select the appropriate font and set the point size to 12. In this
example, the IDAutomationC128XS font is selected. 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.
- Ensure the column is wide enough to contain the entire barcode
with some white space before and after the barcode.
- 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 Barcode
Font Package has been installed and the VBA module has been
imported into the spreadsheet.
- Enter the formula in one cell to format the data to the barcode
font as explained in the Barcode Tutorial for Excel:
- Select that cell and choose Edit - Copy.
- 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.
- 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.
- 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.
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.
- 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:
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
- Excel for MAC 2004: Because Office 2004 for Mac does not have an
import feature, users will need to copy and paste the necessary
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
Barcode Font, copy and paste the IDAutomationC128UniFunctionMAC.rtf
into the editor window.
- Excel for MAC 2008: The Visual Basic Editor feature was
removed and cannot be used with this font tool.
- 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.
Tutorial for Using VBA 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 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:
-
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.
- If a different database is being used and not the sample provided,
the macros must first be imported into
the database before proceeding.
- 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. 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.
- Open a report in design mode.
-
 Create a text box where the barcode will appear.
-
To
format the data to the 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 there is a doubt as to which function to
use, IDAutomation recommends using the
Code 128 Barcode
Fonts with the =Code128([field])
function.
- 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)
- Run 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 - 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.
- Change the font of the formula field to the appropriate barcode
font and set the point size to 12 points. This is a very important
step; if this is not done, barcodes will not be displayed.
- Print and scan the barcode(s) to verify the correct data is
encoded. If a scanner is needed to verify barcodes, IDAutomation
offers the
USB Barcode Scanner.
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 barcoding in Access, please refer to IDAutomation's
Barcode Tutorial for Microsoft
Access.
If a large amount of data needs to be encoded, IDAutomation
recommends using the
PDF417 Barcode
Font and Encoder or the
DataMatrix
Barcode Font and Encoder.
Importing & 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.
- 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:
Access 2000-2003: select
Tools -
Macro - Visual Basic Editor.
Access 2007-2010: select the Database Tools tab -
Visual Basic.
Choose the Modules folder. Choose the
IDAutomationVBA file and save the module
when asked.
Barcode Tutorial for Microsoft Word Mail-Merge:
Before starting this barcode tutorial, ensure VBA macros are the
desired implementation for a Word mail-merge. An easier method exists
when using Codabar
Barcode Fonts (for numbers only) or
Code 39 Barcode
Fonts (for numbers and uppercase letters), which are explained in
the
Barcode Integration Guide for Microsoft Word.
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 Barcode Tutorial for Excel. NOTE:
C128 ReturnTypes 6 through 9 are not currently supported in Word
mail-merges from Excel.
- 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.
- 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.
- 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».
- The barcode will not look correct and will not scan until
the mail merge is performed.
- After the merge is performed, print and scan the barcode to
verify the correct data is encoded. If a scanner is needed to verify
barcodes, IDAutomation suggests easy-to-use
USB
Barcode Scanners.
- 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.
VBA Functions for Standard
Barcode Fonts |
Barcode Type |
Method(s) & Notes
(Follow links for more information) |
Font to use |
|
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 |
IntelligentMail
(DataToEncode) ** |
IDAutomationPOSTNET
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 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. * Excel 2004 for Mac requires
the functions to be lowercase; for example: idautomation_uni_c128. |
Barcode Type |
Method(s) & Notes (Follow links for more information) |
Font to use |
|
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)
This method 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 |
| The
functions listed below
are preceded with IDAutomation_DataBar
and are only provided with the
IDAutomation
DataBar Font. |
Barcode Type |
Method(s) & Notes (Follow links for more information) |
Font to use |
|
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 |
Description of the parts of the functions:
- ApplyTilde - If the ApplyTilde
option is 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.
**
USPS Intelligent Mail and OneCode support requires the installation
of the
IDAutomationNativeFontEncoder.dll
file. 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.
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 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. |
 © Copyright 2012 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. |
|
|
|
|
