The VBA module barcode-vba-macro-only (mentioned by Sebastian Ferry in the comments) is a pure VBA 1D / 2D code generator created by Jiri Gabriel under the MIT license in 2013.
The code is not very easy to understand, but many comments have been translated from Czech to English in the above version.
To use it on a sheet, simply copy or import barcody.bas into your VBA in the module. On the worksheet, enter the following function:
=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),A2,51,1,0,2)
Usage is as follows:
- Leave
CELL("SHEET) and CELL("ADDRESS") as they are, just pointing to the worksheet and cell address, you have the formula- A2 is a cell in which you have a string to encode. In my case, this is cell A2. You can pass “Text” with quotes to do the same. The presence of a cell makes it more dynamic.
- 51 is an option for a QR code. Other options: 1 = EAN8 / 13 / UPCA / UPCE, 2 = two out of five alternating, 3 = Code39, 50 = Data Matrix, 51 = QRCode
- 1 for graphics mode. The barcode is drawn on the Shape object. 0 for font mode. I assume you need to set the font type. Not so helpful.
- 0 - parameter for a particular type of barcode. For QR_Code, 0 = Correction with low error, 1 = Correction of average error, 2 = Quarterly error correction, 3 = high error correction.
- 2 applies only to 1D codes. These are buffer zones. I'm not sure what it does for sure, but probably has something to do with the 1D bar?
I added wrapper functions to make it a pure VBA function call, and not use it as a formula in a worksheet:
Public Sub RenderQRCode(workSheetName As String, cellLocation As String, textValue As String) Dim s_param As String Dim s_encoded As String Dim xSheet As Worksheet Dim QRShapeName As String Dim QRLabelName As String s_param = "mode=Q" s_encoded = qr_gen(textValue, s_param) Call DrawQRCode(s_encoded, workSheetName, cellLocation) Set xSheet = Worksheets(workSheetName) QRShapeName = "BC" & "$" & Left(cellLocation, 1) _ & "$" & Right(cellLocation, Len(cellLocation) - 1) & "#GR" QRLabelName = QRShapeName & "_Label" With xSheet.Shapes(QRShapeName) .Width = 30 .Height = 30 End With On Error Resume Next If Not (xSheet.Shapes(QRLabelName) Is Nothing) Then xSheet.Shapes(QRLabelName).Delete End If xSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _ xSheet.Shapes(QRShapeName).Left+35, _ xSheet.Shapes(QRShapeName).Top, _ Len(textValue) * 6, 30) _ .Name = QRLabelName With xSheet.Shapes(QRLabelName) .Line.Visible = msoFalse .TextFrame2.TextRange.Font.Name = "Arial" .TextFrame2.TextRange.Font.Size = 9 .TextFrame.Characters.Text = textValue .TextFrame2.VerticalAnchor = msoAnchorMiddle End With End Sub Sub DrawQRCode(xBC As String, workSheetName As String, rangeName As String, Optional xNam As String) Dim xShape As Shape, xBkgr As Shape Dim xSheet As Worksheet Dim xRange As Range, xCell As Range Dim xAddr As String Dim xPosOldX As Double, xPosOldY As Double Dim xSizeOldW As Double, xSizeOldH As Double Dim x, y, m, dm, a As Double Dim b%, n%, w%, p$, s$, h%, g% Set xSheet = Worksheets(workSheetName) Set xRange = Worksheets(workSheetName).Range(rangeName) xAddr = xRange.Address xPosOldX = xRange.Left xPosOldY = xRange.Top xSizeOldW = 0 xSizeOldH = 0 s = "BC" & xAddr & "#GR" x = 0
With this shell, you can simply invoke the QRCode rendering by calling it in VBA:
Call RenderQRCode("Sheet1", "A13", "QR Value")
Just enter the sheet name, cell and QR_value. The QR form will be drawn at the location you specify.
You can play with this section of the code to resize the QR
With xSheet.Shapes(QRShapeName) .Width = 30 'change your size .Height = 30 'change your size End With