Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to create QR codes in Excel for FREE (3 easy ways)

QR Codes in Excel

I was recently asked to add a QR code to a document. While I had seen them before, I had never needed to create one from scratch. But, it turns out it’s pretty simple and free in Excel. So, in this post, we look at 3 easy ways to create QR codes in Excel.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch on YouTube

About QuickChart.io

All the methods in this post use an online service to generate the QR code image. We are using QuickChart.io, but others are available, such as goqr.me, and the google API.

With QuickChart, we only need a URL with the correct syntax to generate a QR code. For example, the URL below generates the QR code.

https://quickchart.io/qr?text=https://exceloffthegrid.com

Initial QR code

The section after text= is the text embedded in the QR code.

QuickChart.io has lots of additional parameters for generating the QR codes. Here is a selection of those parameters, which we use in the next section.

  • margin: the amount of white space around the QR code (default is 4)
  • size: the image size in pixels (default is 150)
  • dark: the hex color of the dark cells (default is #000000)
  • light: the hex color of the light cells & margin (default is #ffffff)
  • ecLevel: the amount of error correction, valid entries are L, M, H, Q (default is M, or H if QR code includes an image)
  • centerImageURL: the URL of an image to include in the center of the QR code
  • centerImageSizeRatio: the % of space taken up by the center image.

Note: If using a center image, test the result to ensure the QR code works. If the image covers too much of the code, it may fail to operate correctly.

Create QR codes with the IMAGE function (Excel 365)

Since late 2022, Excel 365 and Excel Online users have had the IMAGE function. If combined with a service like QuickChart, we can create dynamic QR codes in Excel.

The image function has the following syntax.

=IMAGE(source, [alt_text], [sizing], [height], [width])

The arguments are:

  • source: The URL path to the image
  • alt_text (optional): The text to describe the image for accessibility purposes
  • sizing (optional): A setting to represent how the image size should be set:
    • 0: Fit the image in the cell and maintain the aspect ratio
    • 1: Stretch or squeeze the image to fill the cell
    • 2: Keep the original image size, even if it is outside the cell
    • 3: Provide custom heights and widths
  • height (optional): Set the image height in pixels where the sizing argument is set to 3
  • width (optional): Set the image width in pixels where the sizing argument is set to 3

Want to know more about the IMAGE function? Click here

OK, let’s try this out.

Cell C2 is the URL for Excel Off The Grid. Cell C3 uses C2 to create the full URL path for QuickChart.

="https://quickchart.io/qr?text="&C2
EOTG URL

The function in cell C10 is:

=IMAGE(C3)
QR code generated from URL

That’s it; that is all it takes to generate a QR code.

If we change the text in cell C2, the QR code changes. It’s completely dynamic.

Using additional parameters

Playing with the additional parameters creates lots of different styles (all available in the download file).

QR code with an image in the center

QR code with image
https://quickchart.io/qr?text=https://exceloffthegrid.com&centerImageUrl=https://exceloffthegrid.com/wp-content/uploads/2016/10/excel-off-the-grid-logo.jpg&centerImageSizeRatio=0.5

QR Code with non-standard colors

QR code with alternative colors
https://quickchart.io/qr?text=https://exceloffthegrid.com&dark=16522E&light=DDF7E8

QR Code with lower error correction

QR Code with lower error correction
https://quickchart.io/qr?text=https://exceloffthegrid.com&ecLevel=L

Create QR codes with VBA

If you don’t have Excel 365, and not using Excel Online, then you don’t have the IMAGE function. But… there is another option using VBA.

Copy the following code into a standard code module. Then, run the code.

The macro places the QR code into cells C19:C25 of Sheet1 based on the URL in cell C3. You can change these to your requirements.

Sub GenerateQRCode()

Dim QRURL As String
Dim QRShape As Object
Dim QRLocation As Range

'Assign variables
Set QRLocation = Sheets("Sheet1").Range("C19:C25")

'Create the URL path
QRURL = Sheets("Sheet1").Range("C3").Value

'Delete the previous QR code
On Error Resume Next
Sheets("Sheet1").Shapes("QRCodeVBA").Delete
On Error GoTo 0

'Get and create the image
Set QRShape = Sheets("Sheet1").Pictures.Insert(QRURL)

'Change placement of image
With QRShape
    .Name = "QRCodeVBA"
    .Top = QRLocation.Top
    .Left = QRLocation.Left
    .Height = QRLocation.Height
    .Width = QRLocation.Height
End With

End Sub

Create QR codes with Office Scripts

So, having looked at a VBA example, the next question is… can we do the same with an Office Script. The answer is YES.

Copy the code below into the script editor. Then, run the script.

The script places the QR code into cells C27:C33 of Sheet1 based on the URL in cell C3. Change this in the code for your scenario.

async function main(workbook: ExcelScript.Workbook) {

//Assign variables
let ws = workbook.getWorksheet("Sheet1");
let QRURL = ws.getRange("C3").getValue().toString();
let QRLocation = ws.getRange("C27:C33");

//Fetch the URL Request
let QRResponse = await fetch(QRURL);

//Store response as ArrayBuffer (required for an image file).
let QRImageData = await QRResponse.arrayBuffer();

//Convert image to base64
let uInt8Array = new Uint8Array(QRImageData);
let count = uInt8Array.length;
let charCodeArr = new Array(count) as string[];
for (let i = count; i >= 0; i--) {
    charCodeArr[i] = String.fromCharCode(uInt8Array[i]);
};
let QRBase64 = btoa(charCodeArr.join(''));

//Delete the previous image
try {
    ws.getShape("QRCodeScripts").delete();
} catch {};

//Add image to worksheet
let QRShape = ws.addImage(QRBase64);
QRShape.setName("QRCodeScripts");
QRShape.setTop(QRLocation.getTop());
QRShape.setLeft(QRLocation.getLeft());
QRShape.setHeight(QRLocation.getHeight());
QRShape.setWidth(QRLocation.getHeight());

}

NOTE: I did not write all this code myself. It is based on this post by Microsoft:
https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/add-image-to-workbook

Which method to use?

Of the three methods, which should you use?

If you do not have the IMAGE function or Office Scripts, VBA is the only option.

However, if you have IMAGE and Office Scripts, then it depends on your requirements. The Office Script and VBA solutions create a static image in the sheet. Once the image is in there, it no longer depends on QuickChart.

IMAGE function is more dynamic, but requires QuickChart each time it executes.

So, if you intend to keep static versions of the QR codes, then use the Office Script or VBA method. However, if that’s not a key requirement, then IMAGE is easier to apply.

Conclusions

We’ve seen that we can easily create QR codes in Excel for free using QuickChart.io with 3 methods (IMAGE function, VBA, and Office Scripts).

No matter which method we use, QuickChart.io gives us the ability to customize the QR code with logos and color schemes.

Enjoy… happy QRing.

Related posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “How to create QR codes in Excel for FREE (3 easy ways)

Leave a Reply

Your email address will not be published. Required fields are marked *