Click here to Skip to main content
11,705,610 members (54,741 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: VBScript
At first, I apologize if this question is not apropriate here.
But this "QRCodeSample.exe" looks very good!
I'd like to use "ThoughtWorks.QRCode.dll" as a function in excel.
Would someone please show me sample code for VBA? Way of ENCODE is enough for me.
Posted 5-Mar-13 22:45pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1


I am not sure whether the binary distribution is complied in a manner to allow it to be used from VBA. Here you can find the great tutorial on how to create C#/VB.NET assembly for use with VBA. Download the ThoughtWork's QRCode library source and follow the instruction given in the above mentioned article and you should be able to use the dll from VBA.

Member 9884524 at 6-Mar-13 18:20pm
Thaks to comment. I'll try to learn.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

This is a minimalistic example of how to create qr code on excel

Dim wsh As Worksheet
Set wsh = Excel.ActiveSheet

Dim shp As Shape
Set shp = wsh.Shapes.AddOLEObject("STROKESCRIBE.StrokeScribeCtrl.1")

shp.LockAspectRatio = msoFalse
shp.Width = Application.CentimetersToPoints(3)
shp.Height = shp.Width
shp.Left = Application.InchesToPoints(1)
shp.Top = Application.InchesToPoints(1)

Dim qr_code As StrokeScribe
Set qr_code = shp.OLEFormat.Object.Object
qr_code.Alphabet = QRCODE
qr_code.QrECL = M
qr_code.FontColor = RGB(0, 0, 255)

Dim data As String
data = wsh.Range("A1").Text
qr_code.Text = data

To encode a range of cells in QR Code, modify the code as following:

Dim data As String
rng = wsh.Range("A1:D1")
For Each cell In rng
data = data & cell & ";"
qr_code.Text = data

You can put any delimiter between cell values. To insert ASCII TAB, replace ";" with Chr(9).

To encode ASCII control characters as a plain text, use tilde sequences:

qr_code.ProcessTilde = True
qr_code.Text = "TEXT1~0x0d~0x0aTEXT2" ' ~0x0d~0x0a are hexadecimal codes 0D, 0A of ASCII

'The same text is encoded using Chr() function:
qr_code.Text = "TEXT1" & Chr(13) & Chr(10) & "TEXT2"

To delete all barcode objects placed on the active Excel worksheet, use this VBA code:

Dim sh As Shape
For Each sh In Excel.ActiveSheet.Shapes
If sh.Type = msoOLEControlObject Then
If sh.OLEFormat.progID = "STROKESCRIBE.StrokeScribeCtrl.1" Then
End If
End If

The ActiveX doesn't draw a text label below 2D barcodes - it's not required by ISO standards. If you really need to put a text label near QR Code, use the following VBA code:

Dim sh_label As Shape
Set sh_label = wsh.Shapes.AddLabel(msoTextOrientationHorizontal, 0, 0, 0, 0)

sh_label.TextFrame.Characters.Text = "text label - text label - text label"

sh_label.TextFrame.AutoSize = False
sh_label.TextFrame.HorizontalAlignment = xlHAlignCenter

sh_label.Left = shp.Left
sh_label.Top = shp.Top + shp.Height
sh_label.Width = shp.Width
sh_label.Height = Application.InchesToPoints(0.5)

sh_label.TextFrame.AutoMargins = False
sh_label.TextFrame.MarginTop = 0
sh_label.TextFrame.MarginBottom = 0
sh_label.TextFrame.MarginLeft = 0
sh_label.TextFrame.MarginRight = 0

QR Code with a text label

To delete all text labels from a worksheet, use the following code:

Dim sh As Shape
For Each sh In Excel.ActiveSheet.Shapes
If sh.Type = msoTextBox Then
End If

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 229
1 Sergey Alexandrovich Kryukov 100
2 Inimicos 60
3 ProgramFOX 50
4 Richard MacCutchan 45
0 OriginalGriff 9,053
1 Sergey Alexandrovich Kryukov 8,347
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,626

Advertise | Privacy | Mobile
Web01 | 2.8.150819.1 | Last Updated 29 Oct 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100