QR codes are one of the easiest ways to share important information.
Let’s say you are working in a company and organizing a meeting for all sales teams in different countries. Now, you listed all the details and instructions on a web page.
If you convert this WEB URL into a QR code, you can share it easily with the entire team; no one needs to remember the URL. The best way to generate a QR code is to use an Excel QR Code generator.
That’s why I will share a few methods you can use in this tutorial today. These methods cover all the Excel versions. So you can use the other methods if you don’t have the latest Office 365.
Create a QR Code Generator with VBA Code in Excel
This method works in all versions of Excel. So, you don’t need to worry about whether you are using an old version like 2010 or 2013. But you need to understand how to add this code to your workbook to use it.
Function InsertQRCode(QRData As String) As String
Dim QRCodeURL As String
Dim TargetCell As Range
Dim QRImage As Picture
' Set the cell where the formula is called from
Set TargetCell = Application.Caller
' Construct the URL for generating the QR code
QRCodeURL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & QRData
' Delete the existing QR code picture if it exists.
On Error Resume Next
ActiveSheet.Pictures("QRCode_" & TargetCell.Address(False, False)).Delete
On Error GoTo 0
' Insert the QR code image from the URL
Set QRImage = ActiveSheet.Pictures.Insert(QRCodeURL)
' Customize the picture properties and positioning.
With QRImage
.Name = "QRCode_" & TargetCell.Address(False, False)
.Left = TargetCell.Left + 2
.Top = TargetCell.Top + 2
.Width = TargetCell.Width - 4 ' Adjust the size to fit within the cell
.Height = TargetCell.Height - 4 ' Adjust the size to fit within the cell
End With
' Return an empty string as the function result.
InsertQRCode = ""
End Function
Here are the steps to get a QR code with the code.
- First, you need to activate the developer tab and click on the “Visual Basic Editor” to open the editor.
- Now, in the Visual Basic Editor, go to the Insert menu and click on Module to insert a new module.
- Next, on the right side of the Window, you have the code Window, and this is where you need to paste the code I have shared above.
- After that, you need to close the Visual Basic Editor and return to the workbook.
- In the end, insert the function “=InsertQRCode(“excelchamps.com”)” and hit enter to get the QR code in the cell.
If you want to use this custom function in all the workbooks to create a QR code, you can use a personal macro workbook, which allows you to add all the custom functions in one place.
QUICK NOTE – Using a custom QR code function, you can generate codes in bulk in a few seconds.
How does this code work, and how do I edit it?
This code is written to generate and insert a QR code directly into a cell where you insert the function. It takes the text you provide (like a URL or any other text) and creates a QR code.
This code is written to generate and insert a QR code directly into a cell where you insert the function. It takes the text you provide (like a URL or any other text) and creates a QR code.
The code starts by figuring out which cell you have entered the function, then uses an API from (https://quickchart.io/qr?text=) to generate the QR code based on the specified values.
Thanks to quickchart.io, you have many parameters that you can use to customize your QR code. I generated a QR code with an image at the center in the example below.
Here is the code for the custom function we used to generate the above QR code.
=InsertQRCode("excelchamps.com", "https://cdn-icons-png.flaticon.com/128/1365/1365725.png")
Function InsertQRCode(QRData As String, CenterIMG As String) As String
Dim QRCodeURL As String
Dim TargetCell As Range
Dim QRImage As Picture
' Set the cell where the formula is called from
Set TargetCell = Application.Caller
' Construct the URL for generating the QR code
QRCodeURL = "https://quickchart.io/qr?text=" & QRData & "¢erImageUrl=" & CenterIMG
' Delete the existing QR code picture if it exists
On Error Resume Next
ActiveSheet.Pictures("QRCode_" & TargetCell.Address(False, False)).Delete
On Error GoTo 0
' Insert the QR code image from the URL
Set QRImage = ActiveSheet.Pictures.Insert(QRCodeURL)
' Customize the picture properties and positioning
With QRImage
.Name = "QRCode_" & TargetCell.Address(False, False)
.Left = TargetCell.Left + 2
.Top = TargetCell.Top + 2
.Width = TargetCell.Width - 4 ' Adjust the size to fit within the cell
.Height = TargetCell.Height - 4 ' Adjust the size to fit within the cell
End With
' Return an empty string as the function result
InsertQRCode = ""
End Function Quick Tip – Creating a QR Generator Template in Excel has a lot of benefits; let’s say you want to create QR codes in bulk. You can do it in Excel within seconds without any problem. You can create QR codes in various cells once you add the custom formula to your workbook.
Read Also: Generate Random Letters in Excel / Generate Random Groups
Create a QR Code using the IMAGE Function.
IMAGE is a new function in Excel that allows you to get an image from a web source. With IMAGE, you can use the API to generate a QR code.
For this, we are going to use the same API as https://quickchart.io. You can see in the example below that I used IMAGE, and then with the API link, I used the values from cell A2, where I have the URL of our website.
If you are using Office 365, you can only use the IMAGE function, but the best part is that you can change the QR’s size with the cell’s Height. Yes, when you change the Height of the cell OR increase its size with it,
If you want to customize your QR code, you can change the checkout parameters I shared earlier.
Note – The value in cell A2 could be anything, like a website link or some data, and the formula combines it with the URL to generate a QR code.
Apart from this, you can also learn all IMAGE function syntax to make changes in the formula. Here’s the syntax:
IMAGE(source, [alt_text], [sizing], [Height], [Width])
- source: The URL or file path of the image you want to display. This is where you need to add the API URL to create a QR code in the cell.
- [alt_text]: You can add an alternate text to display if the QR image can’t be shown in the cell.
- [sizing]: You can define a number here to specify the size of the QR code image in the cell. Specify 0 to fit the image into the cell while maintaining the aspect ratio, 1 to change the size of the code image according to the Height and Width of the cell, 2 to use the same image as per the API you have specified, 3 to define a custom height and width according to the height and width arguments.
- [Height]: Specify the Height only when using 3 in the sizing argument.
- [Width}: Specify the Width only when using 3 in the sizing argument.
All the arguments in the square brackets [] are optional in the above syntax.
IMPORTANT NOTE: When you use the IMAGE functions to create a QR code, the code is generated within the cell because it is a result returned from the formula. But when you use the custom function we used earlier in this tutorial, it generates an image of the code, which you can move anywhere in your worksheet.
You can use the paste special option to convert this QR code into an image.
Select the cell and copy it from it; after that, go to the Home tab, click on Paste > Paste Special, and then click on Picture.
You can also Paste a picture as a link, which updates the image if the QR changes in the original cell.
You can also use the keyboard shortcut Alt, H, V, U or Alt, H, V, I.
Using Third-Party Add-In to Create a QR Code within Excel
If you don’t want to use any of the methods we used earlier in this tutorial, you have one more option: use the third-party Add-Ins.
Yes, you have this option, and the best part is that these add-ins have many options to customize the QR report.
- First, you need to find and install the add-in. I have found a really good add-in with options to help with this. So, you need to go to the “Developer Tab” and click on the “Add-In.”
- After that, click on “Add-Ins.” In the dialog box, click on the “Store” tab.
- Then click the “Add” button for the “QR4Office” in the search bar for QR.
- In the end, it will open a side pane for you to generate a QR code.
In this pane, you have, as I said, a lot of options to use.
You can see all the options you can customize from there, and you can click on the Insert button to insert an image of the QR into the worksheet.
Apart from a QR, creating barcodes in Excel can be a valuable tool for inventory management, asset tracking, or any application that requires quick data entry using barcode scanners. Here’s a complete guide to Barcodes in Excel.