Standard or higher

Excel
Barcode objects with VBA

Using and automating 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:="ACTIVEBARCODE.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 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'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.