[Home] [Overview] [Pre-registration] [Registration] [Volunteers] [Set-up] [Notes] [Updates] [Tutorials]

Contest Automation -- Developer's Notes

Application:

The applications are written in Visual Basic for Applications (VBA, Version 5 or later) running on top of Excel 97, 2000, or 2002 (Version 8 or later) under Microsoft Windows. In some cases, functionality from Excel Version 9 (Excel 2000) is used, such as non-modal windows in Volunteer Log, making that application only accessible from Version 9 or later. In general, Excel 2000 is recommended as it is more robust and will exhibit fewer problems than Excel 97. Help is provided by HTML documents, without VML or XML extensions or any required plug-ins. Any modern browser, such as Netscape or IE should suffice to access them. Optional direct printing of IAC sequence forms A, B, and C are automated by a single VBA procedure running in Visio (not required if the forms are pre-printed). No other applications are required, even though the functionality could be greatly enhanced by the use of a shared dynamic database, high speed internet connection, and a LAN between computers. However, the contest environment is temporary, dynamic, and often cost-constrained. Keeping it simple and modular seems the best course of action.

Each spreadsheet is designed to operate independently and can be used as a stand-alone application. If you have problems with one (see below), the others should operate without difficulty. Communication between spreadsheets is by text-based disk file. With different spreadsheets running on the same machine, putting them in the same disk directory will enable them to easily pass disk files back and forth. When different machines run the spreadsheets, floppy disk is a nominal and preferred method of passing disk files if something sophisticated like a LAN is not available. If a LAN is available, using a shared folder from one computer (nominally the Contest Ledger) will allow each of the other applications to share data. Import and export of data files can be made to any Windows accessible directory or device. Automatic import and export of these data files is built into the programs and eliminates the need for repeated manual intervention (in this case, the pre-registrations will be automatically uploaded to the contest ledger(s), satellite registrations can be automatically exported to the master ledger, and volunteer data can be automatically sent to the volunteer log)

Fundamental Design Features:

The programs share certain characteristic design requirements:

All VBA automated functions are accessible from a single Excel Menu Bar custom menu named for the application. The custom menu 1) will be automatically loaded on upon initialization of the workbook, 2) will be displayed only while the workbook is the active workbook, and 3) will be deleted upon unloading of the workbook. Some specialized functions specific to a given worksheet are given command buttons located on the sheet for quicker access. Some shortcut keys are currently defined, but will be gradually eliminated in favor of the other access methods.
Backup files (both standard "*.BAK" and identically formatted named archives) save and restore the entire state of the associated spreadsheet. Any clean copy of the spreadsheet (after the associated "Clear Data" VBA macro) with a restore from a backup or archive file will contain all relevant data from the time of the original backup or archive. Thus there is no need to save versions of the workbooks with specific data entered. The backup/restore file from any version of the program will work with all newer versions of the program; some data not included in the earlier version may not be present, but no errors will be generated and all data that is in the backup file will be read correctly.
Input to individual cells generally comes from 1)menu selections, 2)text boxes, 3)list boxes, or 4)copy (right click)-paste (double click) mouse actions, as opposed to direct user input (the Contest Ledger itself is a notable exception). All cells not designed to be changed by user input are protected from such change.
Input data will be entered once if used multiple times or in multiple workbooks. Previously entered data will be presented as a default choice if selected for re-entry. No piece of information will ever required to be entered twice. All data is stored in a worksheet rather than static VBA data storage; no malfunction of any VBA code can cause the loss of previously stored data so long as the underlying worksheet is available.
Any operation that can destroy data previously entered will generate an "Are you sure?" pop-up box to confirm the operation.
Outputs that are routinely needed for before, during, or after a contest are accessible from a single central "Print" dialog box for each application that includes pre-selected combinations and quantities for specific needs, such as judge clipboards.
Inter-process communication is by data file. Most data files (with the exception of standard backups) can be written to or read from any directory and is fully network-aware. All data files that routinely need to be imported from or exported to other programs on an on-going basis can be programmed to be written or read automatically on a pre-determined interval. Any data file (outside of backup files) of that can be read on top of existing data will preserve the existing data, only overwriting it if a timestamp can be used to verify the new data is actually newer than the old.

Hardware:

Computer -- i/486 (minimum) or Pentium class PC, 200MHz or higher recommended, 96MB RAM. A laptop may be a better form factor for some functions, such as Volunteer Coordinator.
OS -- Windows 2000/XP recommended, NT/Win 98/me a minimum.
I/O -- Floppy drive, printer connection.
LAN -- Ethernet adapter not required, but would be highly desirable. 802.11 wireless LAN hardware would be even better, given the nature of the contest environment.

Updates:

See the Program Updates page

Problems:

The spreadsheets and attached VBA code have been tested repeatedly in the Excel 2000 environment, but with each new bit of functionality or bug fix, previously working features can fail under some circumstances. There has been much less testing under the Excel 97 environment, so your chances of problems there are substantially higher. I don't have access to Excel 2002 (XP), so I can't make any promises there, except that Microsoft has been moderately successful in the past making Excel backwards compatible and there really aren't that many changes in Excel 2002 anyway.

If you find a bug, contact me and I'll make every attempt to fix it ASAP. This is a very good reason to download the software early and try it out with old contest data. If you're at a contest, I can't guarantee that I'll be able to fix it before the next flight, but since the spreadsheets are modular, you won't lose your Contest Ledger even if the Volunteer Log fails catastrophically. If a given function in a given spreadsheet fails to operate (normally giving a VBA error message), the Excel application will normally continue to function with no loss of data. If you make it a point to regularly back-up your data (no excuses here, the backup data function is prominently displayed in all of the application's menus!), you can normally just restart Excel even if it crashes, restore your data, and be where you were before the problem occurred. All that will be compromised is the operation of a specific function, until I can find the problem and fix it.