Excel VBA: Open Source QR Code Generator

Here’s a quick preview of an open-source QR code generator.

Excel VBA: Open Source QR Code Generator (Quick View)

Function Generate_QR(QR_Value As String)
    Dim URL As String
    Dim MyCell As Range
    Set MyCell = Application.Caller
    URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & QR_Value
    On Error Resume Next
      ThisWorkSheet.Pictures("My_QR_CODE_" & MyCell.Address(False, False)).Delete
    On Error GoTo 0
    ThisWorkSheet.Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
     .Name = "My_QR_CODE_" & My_Cell.Address(False, False)
     .Left = MyCell.Left + 5
     .Top = MyCell.Top + 5
    End With
    GenerateQR = ""
End Function

VBA QR Code Generator Open Source: 5 Easy Steps to Generate

We have an Excel workbook with a worksheet containing some values in a column. The next column is left open to generate QR codes based on the values.

Step 1 – Opening Visual Basic Window

Press Alt + F11 on your keyboard to open the Visual Basic window.

Step 2 – Inserting a New Module

Go to the Insert option in the toolbar.
Click on Module.
A new module called Module1 will be inserted.

Step 3 – Putting the VBA Code

Put the following VBA Code in the module.

VBA Code:

Function Generate_QR(QR_Value As String)
    Dim URL As String
    Dim MyCell As Range
    Set MyCell = Application.Caller
    URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & QR_Value
    On Error Resume Next
      ThisWorkSheet.Pictures("My_QR_CODE_" & MyCell.Address(False, False)).Delete
    On Error GoTo 0
    ThisWorkSheet.Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
     .Name = "My_QR_CODE_" & My_Cell.Address(False, False)
     .Left = MyCell.Left + 5
     .Top = MyCell.Top + 5
    End With
    GenerateQR = ""
End Function

We’ve created a user-defined function called Generate QR. It takes a string value as the argument and returns a QR code in response to that value.

Step 4 – Saving Workbook as a .xlsm File

Return to the workbook and save it as an Excel Macro-Enabled Workbook.

Step 5 – The Output

Enter this formula in cell C3:

=Generate_QR(B3)

Hit Enter. An open-source QR code will be entered into the cell.

Do the same for the rest of the cells.

Things to Remember

We are generating the QR codes from an open-source library on the internet. You will need an internet connection to get the results.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Open Source QR Code Generator.xlsm

<< Go Back To QR Code in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Function GenerateQR(qrcode_value As String) Dim URL As String Dim My_Cell As Range Set My_Cell = Application.Caller URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcode_value On Error Resume Next ActiveSheet.Pictures("My_QR_CODE_" & My_Cell.Address(False, False)).Delete On Error GoTo 0 ActiveSheet.Pictures.Insert(URL).Select With Selection.ShapeRange(1) .Name = "My_QR_CODE_" & My_Cell.Address(False, False) .Left = My_Cell.Left + 5 .Top = My_Cell.Top + 5 End With GenerateQR = "" End Function

Function GenerateQR2(QR_Value As String) Dim URL As String Dim MyCell As Range Set MyCell = Application.Caller URL = "https://chart.googleapis.com/chart?chs=100x100&cht=qr&chl=" & QR_Value On Error Resume Next ThisWorkbook.Sheets(1).Pictures("My_QR_CODE_" & MyCell.Address(False, False)).Delete On Error GoTo 0 ThisWorkbook.Sheets(1).Pictures.Insert(URL).Select With Selection.ShapeRange(1) .Name = "My_QR_CODE_" & MyCell.Address(False, False) .Left = MyCell.Left + 5 .Top = MyCell.Top + 5 End With GenerateQR2 = "" End Function

Sub GenerateQRCode() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as necessary Dim cell As Range Dim qrString As String qrString = "" ' Loop through the cells you want to include in the QR code For Each cell In ws.Range("A1:A10") ' Specify your range qrString = qrString & cell.Value & " " ' Concatenate values with a separator if needed Next cell ' Generate QR Code Dim qrCode As Object Set qrCode = ws.Pictures.Insert("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & qrString) With qrCode .ShapeRange.LockAspectRatio = msoFalse .Width = 150 .Height = 150 .Left = ws.Cells(1, 3).Left .Top = ws.Cells(1, 3).Top End With End Sub

12 Comments

Ashley Nov 3, 2022 at 4:30 AM

Hi Rifat – this doesn’t seem to work. The Google charts API may be the issue. Have you tested this recently?
thanks,
Ashley

Sabrina Ayon Nov 6, 2022 at 2:56 PM

Hello, ASHLEY!
Thanks for your comment.
Yes. Unfortunately, the google charts API is currently broken. You can use the following API which I updated in the article.

https://chart.googleapis.com/chart?chs=100×100&&cht=qr&chl=

Try out this code below.

Hope this will help you!
Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

Xiong Yang Jan 3, 2023 at 3:41 AM

Is it possible to get the QR code to have a label displayed either in the middle of the QR code or below it?
- Reply
  Mashhura Jan 4, 2023 at 4:50 PM
  
  Hi XIONG YANG,
  Thanks for your comment. I am replying on behalf of ExcelDemy. You can not add a label directly in the QR Code. But, you can add a label to the cell where you are inserting the QR Code by following these simple steps.
  Step-01: Write the following VBA code in the module instead of the code that is provided in this article.
  Function GenerateQR(qrcode_value As String)
  Dim URL As String
  Dim My_Cell As Range
  Set My_Cell = Application.Caller
  URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcode_value
  On Error Resume Next
  ActiveSheet.Pictures("My_QR_CODE_" & My_Cell.Address(False, False)).Delete
  On Error GoTo 0
  ActiveSheet.Pictures.Insert(URL).Select
  With Selection.ShapeRange(1)
  .Name = "My_QR_CODE_" & My_Cell.Address(False, False)
  .Left = My_Cell.Left + 5
  .Top = My_Cell.Top + 5
  End With
  GenerateQR = ActiveCell.Offset(0, -1).Value
  End Function
  
  Now, Save the code and go back to your worksheet.
  Step-02: Select the cells where you want the QR Codes >> go to the Home tab >> select Bottom Align.
  
  Step-03: Select the cell where you want the QR Code >> write the following formula in that cell.
  =GenerateQR(B5)
  
  Next, press Enter and you will get the QR Code with the Label.
  
  Step-04: Finally, get the QR Codes for other data in the same way.
  
  I hope this will help you to solve your problem. Please let me know if you have other queries.
  Regards
  Mashhura,
  ExcelDemy.
George Jan 19, 2023 at 6:01 AM

Why can not be made while character “+” in tthe text ?
- Reply
  Rafiul Haq Jan 24, 2023 at 12:22 PM
  
  Greetings George. I am replying on behalf of ExcelDemy. There was an issue with the code in the file. I have updated the file. This works perfectly fine for a “+” character in the text.
Smith Nov 2, 2023 at 8:03 PM

Hello, I have a problem with the script. It doesn’t download qrcode from google. Error #ARG!
- Reply
  Yousuf Khan Nov 5, 2023 at 1:02 PM
  Hello Smith,
  
  Thanks for sharing your experience with us. The #ARG! error (Argument Error) in Excel generally occurs when a function or formula receives incorrect or invalid arguments. In the above code, I have noticed a few varibale name mismatches. Such as:
  
  The function is defined as “Generate_QR” but it is assigned the name “GenerateQR” in the code. “My_Cell” should be “MyCell“. Also, The URL construction has double ampersands (“&&“) which are not needed.
  
  As a result, the VBA script is returning these errors. Thanks for pointing out this to us. We greatly appreciate your feedback.
  
  I have fixed the issues of the code. Try the below code instead:
```
Function GenerateQR2(QR_Value As String)
    Dim URL As String
    Dim MyCell As Range
    Set MyCell = Application.Caller
    URL = "https://chart.googleapis.com/chart?chs=100x100&cht=qr&chl=" & QR_Value
    On Error Resume Next
    ThisWorkbook.Sheets(1).Pictures("My_QR_CODE_" & MyCell.Address(False, False)).Delete
    On Error GoTo 0
    ThisWorkbook.Sheets(1).Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
        .Name = "My_QR_CODE_" & MyCell.Address(False, False)
        .Left = MyCell.Left + 5
        .Top = MyCell.Top + 5
    End With
    GenerateQR2 = ""
End Function
```
  Regards,
  Yousuf Khan Shovon
valen Apr 12, 2024 at 1:04 PM

Hi There,

If any update about the URL….??, Seemed, the link is broken

Thank’s
Valen
- Reply
  Shamima Sultana Apr 16, 2024 at 8:40 AM
  
  Hello Valen,
  
  Can you mention the URL, please? In our site all URL’s of this article are working perfectly.
  
  Regards
  ExcelDemy

Aleksandar May 9, 2024 at 5:19 PM

Hello,
is there a possibility that the data from all the cells is one QR code ?

Shamima Sultana May 12, 2024 at 4:26 PM

Hello Aleksandar,

You can concatenate the cell values into a single string to get one QR code from different cell values.

You can use the following code:

Sub GenerateQRCode() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as necessary Dim cell As Range Dim qrString As String qrString = "" ' Loop through the cells you want to include in the QR code For Each cell In ws.Range("A1:A10") ' Specify your range qrString = qrString & cell.Value & " " ' Concatenate values with a separator if needed Next cell ' Generate QR Code Dim qrCode As Object Set qrCode = ws.Pictures.Insert("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & qrString) With qrCode .ShapeRange.LockAspectRatio = msoFalse .Width = 150 .Height = 150 .Left = ws.Cells(1, 3).Left .Top = ws.Cells(1, 3).Top End With End Sub

Regards
ExcelDemy