sample file
create QR codes

1. Introduction

In a German forum, someone asked how to create QR codes with VBA.
The first answers made use of online QR-code generators.
VBA only took care of sending data, retrieving results and displaying them in an Office application.
Flotter Feger had found a real VBA application.
It consisted of no less than 6 different Barcodes/QR-codes.
The code is designed - as far as I know - by alois zingl in 2016.

The code seemed to be aimed at always coping with all situations: universalism.
I don't think that's practical.
Most people only want one type of graphical information carrier (QR code or Barcode).
In addition, the code uses graphical elements in Excel.
In the case of large QR codes, these images inflate the file considerably and noticeably slow down the operation.

I simplified one of the codes and put it in an Excel file.
The QR code is produced using conditional formatting.

I'm not going to try to explain the code; it's far too mathematical.
The code does contain explanatory comments by the original author.

2. Explanation of the sample file

The Excel file contains 1 worksheet 'QR-code' (codename: 'Sheet')
Enter the text of the QR-code in cell A1.
Enter or Tab activates the code (the code inthe event Worksheet_Change()).

A QR-code consists of a maximum of 177 * 177 pixels.
That's why the Range B3:FP180 has a conditional formatting rule:
- cellvalue 0: backgroundcolour white
- cellvalue 1: backgroundcolour black

The code adapts the height and width of columns and rows that match the QR-code's size.
The code assumes 100% zoom of the worksheet.

The QR-code will also be stored as graphical item in the clipboard, so you will be able to paste it in any other application you like (Ctrl-V).

The QR-code will also be stored as a PNG file in the same directory as the Excel file.
The filename is identical to the text that has been converted in QR-code after cleaning the text for forbidden characters in filenames.

QR-code has 5 variants.
The binary variant is implemented in this file, since it has the least constraints to the text to be converted.

The code uses conditional formatting in Excel.
The code is therefore much faster compared to the use of graphical elements.

If you need QR codes in other Office-applications you can preferentially generate them in Excel.

3. QR-codes by Userform

You can download a file with which you can generate QR codes in a Userform:

QR codes in Userform

4. QR codes by a Userform in Word

In this file you can generate QR codes in a Word Userform.
This Word file contains a similar Userform to the Userform in the Excel file in the previous paragraph.
The QR code will be shown in the Userform.
As soon as you close the Userform the Word file contains an image representing the QR code.

QR-codes in Word Userform