We use cookies to provide you with the best experience and functionality on our website. By using our site you agree to this. read more >   

Excel
Barcode objects with VBA

VBA Examples

Using and automate barcodes with VBA in Excel

1

You can use VisualBasic for Applications (VBA) to solve many things in Excel. Here we will show you how to embed, use and delete the ActiveBarcode control with VBA:

Embedding the ActiveBarcode Control into a Sheet:
In this example a barcode control will be placed directly above a cell. Then it looks like the barcode would be inside a cell.

First we read the cells size:

CurrentCell = "C3"
MyHeight = Range(CurrentCell).Height
MyWidth = Range(CurrentCell).Width
MyTop = Range(CurrentCell).Top
MyLeft = Range(CurrentCell).Left
Please note that a cell should have a minimum size so the barcode can fit into it. If the cell is too small a 'Size error' will be displayed. So we resize the height of the cell to 30 pixels:
' Enlarge the cell height to 30 pixels
Range(CurrentCell).RowHeight = 30
The following function will create the barcode control directly above the cell:
ActiveSheet.OLEObjects.Add(ClassType:="BARCODE.BarcodeCtrl.1", Link:=False, _
DisplayAsIcon:=False, Width:=MyWidth , Height:=MyHeight, Top:=MyTop + 2,_
Left:=MyLeft + 4).Select
To get easy access to the control we remind the name of the control in a variable named 'MyBarcode':
MyBarcode = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name
Now you can set the properties of the control as you like:
' set properties
ActiveSheet.OLEObjects(MyBarcode).Object.Font.Size = 8
ActiveSheet.OLEObjects(MyBarcode).Object.Type = 14 ' Code 128
ActiveSheet.OLEObjects(MyBarcode).Object.Text = Range("C3")
If you do not need the control anymore you can delete it from the sheet:
ActiveSheet.OLEObjects(MyBarcode).Delete
Hint: If it's necessary that Windows process upcoming events (often named as "KeepWindowsAlive") within a macro, you can force this by using the following VBA function:
DoEvents
This can be necessary, e.g. if the Control must draw itself anew.

This manual was created by Frank Horn

| Home | Shop | Download | E-Mail | Privacy Policy | Imprint | Version 6.4.0 |
Web DesignszBubble 3.1.5.503
Web design Frank Horn

Copyright
Frank HornActiveBarcode Generator
Web design
Copyright Frank Horn
&
Lars SchenkActiveBarcode Object
Web- & server component
Command line
Copyright Lars Schenk

All trademarks and product names mentioned are the property of their respective owners.