Search code examples
excelvbaqr-codebarcode

Generating 2D barcodes with Control Characters / Escape Sequences using ONLY Excel VBA

I posted a question yesterday which admittedly lacked some details. To follow the suggestion of the comments, here is an attempt at doing better:

I would like to generate 2D Barcode that allow for Control Characters such as Tab or Return in VBA.

Since the script needs to function without internet access, VBA implementations that rely on API calls can't be used.

I found a solid solution that generates the barcodes from scratch in this post.

Here is a link to the code I used, from what I understand many different Barcode Types are part of it. Alternatively to the paste in the link, you can find the original code file "barcody.bas" in this Github Repository

I managed to get the QR Code creation to work using the following formula for calling the script:

=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),"Cell Reference / String goes here",51,1,0,2)

The parameters are the following:

  • 51 is the option for QR Code. Other options are 1=EAN8/13/UPCA/UPCE, 2=two of five interleaved, 3=Code39, 50=Data Matrix, 51=QRCode
  • 1 is for graphical mode. The barcode is drawn on a Shape object. 0 for font mode. I assume you need to have the font type installed. Not as useful.
  • 0 is the parameter for the particular barcode type. For QR_Code, 0=Low Error Correction, 1=Medium Error Correction, 2=Quartile error correction, 3=high error correction.
  • 2 only applies to 1D codes. It's the buffer zones. I'm not certain what it does exactly but probably something to do with the 1D bar spaces?

However, I struggle with the Control Characters. More precisely, I would like to concatenate the contents of two cells and separate them by a "Tab" Keypress. Here is what I tried:

=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),B1&"^009"&B2,51,1,0,2)

=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),B1&"%09"&B2,51,1,0,2)

=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),B1&"<CR>"&B2,51,1,0,2)

These attempts simply encode the the Strings in the middle (e.g. ^009) directly into the QR Code so it is read as plain text.

Any help would be appreciated! Either a different script or making the one linked above work would be great :)

Solution

  • In an Excel formula, use the function Char for that:

    =EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),B1&Char(9)&B2,51,1,0,2)

    The equivalent function in VBA is Chr.

    s = activeSheet.Range("B1") & Chr(9) & activeSheet.Range("B2")

    But in VBA, you can also use predefined constants like vbTab

    s = activeSheet.Range("B1") & vbTab & activeSheet.Range("B2")