11,477,625 members (72,823 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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 23:45pm

## 2 solutions

Rate this:
Please Sign up or sign in to vote.

## Solution 1

Hi,

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.

regards,
Comments
Member 9884524 at 6-Mar-13 18:20pm

Thaks to comment. I'll try to learn.
Rate this:
Please Sign up or sign in to vote.

## Solution 2

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

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 & ";"
Next
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
sh.Delete
End If
End If
Next

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
sh.Delete
End If
Next

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

 0 Sergey Alexandrovich Kryukov 194 1 Richard Deeming 180 2 OriginalGriff 160 3 Sascha Lefèvre 145 4 Tomas Takac 130
 0 Sergey Alexandrovich Kryukov 7,675 1 OriginalGriff 7,256 2 Sascha Lefèvre 3,034 3 Maciej Los 2,491 4 Richard Deeming 2,305

Advertise | Privacy | Mobile
Web02 | 2.8.150520.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