RSS

Ticket or number control in Excel with VBA macros

There are no code examples on this page, but in the zipped spreadsheet (ticket-number-check.zip), which can be downloaded here, you find the VBA macros with extensive comments.

There isn't a lot of code, but too much to show on one page, and as it is an application for ticket control at a festival, it is best to see "the real thing".

Of couse it can be used for other things than just ticket control - it checks if a number is in a pre-defined number series, and for that matter you can use it for comparing banknotes.

NOTE! If you want to test the spreadsheet, you must start by making a number series. You do that by clicking the button on the worksheet "Setup" and follow the instructions - there is nothing to it.

Among other techniques the program uses a Worksheet_SelectionChange event - i.e. a macro that executes automatically, when something is changed on a worksheet.

  • There is an Auto_Open procedure that runs automatically and initializes parameters, when the spreadsheet is opened.
  • It uses Excel's built-in search function.
  • There are examples on the use of arrays and ranges, and how you can an copy array to a range in one swift operation instead of a much slower cell by cell loop.
  • The program can generate a number series, where you define the start number, increment and the total number of numbers.
  • It uses one of the functions on the page Find next empty cell.
  • And then there is a very simple UserForm.

The background

I was contacted by a festival organizer who asked, if I could make a spreadsheet that could check, if a ticket number was in a given number series (i.e. not a forgery), and if the ticket had been used before.

It sounded like a funny task and "a noble cause," so I said yes on the condition that I could make it public here as an application for a specific purpose.

How

The ticket inspector will scan a ticket number (from a bar code) into a cell, or he will type the number manually. Then he presses ENTER or leaves the cell some other way.

This automatically calls the worksheet's Worksheet_SelectionChange procedure, that checks if it was a change in the right cell, if there is anything in the cell etc.

Next the program checks if the number is in the number series and if it has been used or not. Depending on the outcome the program will pop up with a message to the ticket inspector, telling him if the ticket is valid or whatever.

If the ticket is approved, it is logged as used/shown with a time stamp. Invalid tickets are also logged.

The check takes a split second, and then the number in the input cell is deleted and the cell activated for the next scan.

I don't know exactly how the scanner works, but it emulates and writes the number exactly like a keyboard, so VBA doesn't care, and there is no special VBA code because of the scanner.

The program can also generate a number series that can be used when printing the tickets.

Other uses

By nature the program can be used to check if a number exists in a given number series or if it has been used before. It doesn't need to be a ticket control.

However if you use it for something else, it will probably be appropriate to change some of the messages to the ticket inspector. If for instance you check banknotes it would look silly with a "Ticket OK".

Related: