Using and automating barcodes with VBA in Excel

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:="ACTIVEBARCODE.BarcodeCtrl.1", Link:=False, _
 DisplayAsIcon:=False, Width:=MyWidth , Height:=MyHeight, Top:=MyTop + 2,_
 Left:=MyLeft + 4).Select

You can directly link the barcode object with a cell:

ActiveSheet.OLEObjects(MyBarcode).LinkedCell = "B7"

To get easy access to the control we store the name of the control in the variable MyBarcode:

MyBarcode = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name

Now you can use the properties and methods of the barcode object 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 is necessary for Windows to process upcoming events (often referred to 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 new.