Excel VBA: Open Source QR Code Generator

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how to use Excel VBA as 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


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

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

Data Set to Generate Open Source QR Code Using Excel VBA

Our objective today is to generate QR codes based on the values using Excel VBA.


⧪ Step 1: Opening Visual Basic Window

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

Opening VBA Window to Generate Open Source QR Code Using Excel VBA


⧪ Step 2: Inserting a New Module

Go to the Insert > Module 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

vba qr code generator open source


⧪ Step 4: Saving Workbook as a .xlsm File

Next, return to the workbook and save it as an Excel Macro-Enabled Workbook.

Saving Workbook to Generate Open Source QR Code Using Excel VBA


⧪ Step 5: The Output

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.

Come back to your worksheet and enter this formula in a cell:

=Generate_QR(D2)

Here, D2 is a cell that contains a value. You enter your one.

Then click ENTER. An open-source QR code will be entered into the cell.

Output to Generate Open Source QR Code Using Excel VBA

Do the same for the rest of the cells.

Read More: How to Scan QR Code to Excel Spreadsheet


Things to Remember

We are generating the QR codes from some open sources on the internet. Therefore, the internet connection of your computer must remain active for the process to accomplish smoothly.


Download Practice Workbook

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


Conclusion

Therefore, this is the process to generate open-source QR codes using VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

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

    • 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.

      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

      Hope this will help you!
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy
      .

  2. Is it possible to get the QR code to have a label displayed either in the middle of the QR code or below it?

    • 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.

  3. Why can not be made while character “+” in tthe text ?

    • 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.

  4. Hello, I have a problem with the script. It doesn’t download qrcode from google. Error #ARG!

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo