VBA Barcode Macro & Functions Tutorials
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 Macintosh 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, Interleaved 2 of 5, DataBar, UPC/EAN and
many others.
These tools 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.
VBA Barcode Macro & Functions Tutorials Index
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 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, Office
2000, Office XP, Office 2003, Office 2007, Office 2010 and Office 2013 running on Windows® or
Office 2004 for Macintosh platforms. This tool is not compatible with
Office 2008 because Macintosh removed the ability to use VBA macros
in that version. IDAutomation has a variety of
Macintosh Barcode Integration
options for those using Macintosh Office 2008.
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, Macintosh, Linux and others. When this cannot
be used, Macintosh 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.
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
- 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.
- If a different spreadsheet is used other than 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 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.
- 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.
- 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.
- Ensure that the column is wide enough to contain the entire
barcode with some white space before and after the barcode.
- Scan the printed barcodes with a
handheld
USB barcode scanner to verify the correct data is encoded.
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.
- 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.
- 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.
- 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.
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: Office 2004 for Mac does not
have an import feature, and so users must 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.
- 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.
Access VBA Macro Barcode Functions Tutorial
After 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
-
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.
- If a different database is being used and not the sample provided,
first import the VBA macros into the database.
- 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.
- Open a report in Design Mode.
-
 Create
a text box where the barcode will display.
- 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)
- 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, 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.
- 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.
- 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
DataMatrix
Barcode Font and Encoder may be the best options as these 2D barcode
types allow for encoding of more data.
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.
VBA & Macro Functions in MS 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 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.
- 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 so, select
Data Source for Word Mail Merge.xls
as the data source. If asked, 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 that the correct data has been 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_
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 |
IntelligentMail (DataToEncode)
** |
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
Funtion 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.
**
USPS Intelligent Mail and OneCode support requires installation
of the
IDAutomationNativeFontEncoder.dll
file. Due to the complexity of the calculations involved, pure VBA
code has not yet been produced for this function. The DLL is free to
use with a valid license purchase for the associated barcode font.
|
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. |
Back to Top
|
