Overview

Automating?

Visual Basic

The Destination Builder was developed using a software language known as Visual Basic for Applications (VBA) which runs from within Microsoft Excel.  The Destination Builder therefore presents the familiar look and feel of MS Excel to users but the actual engine that drives the features and functions goes far beyond what native Excel is or will ever be capable of.

This section provides readers with information regarding the day to day operating limitations of native MS Excel and how VBA unlocks these (often cumbersome) restrictions.

VBA makes it possible to create a Tour Management and Operations system that contains more features and safeguards than one can expect from a standalone database driven operating system.  In addition one has the added benefit of familiarity and flexibility that is offered by MS Excel.

The Need to Automate

• I am a household and wish to keep track of my family's birthdays.

  • Do I need to automate?

    • No of course not.  A wall mounted birthday calendar will suffice.

  • Can I use MS Excel?

    • Yes of course.  A simple Excel spreadsheet will do just fine.

• I am an Engineering company with a staff compliment of 50.  I manufacture and supply widgets to my 1000 clients.

  • Do I need to automate?

    • It is a very good idea.

  • Can I use MS Excel?

    • No, not unless you use VBA as well.  The amount of data you have makes it too cumbersome to keep track of everything in native MS Excel.

• I am a bank with 100 000 employees and 3 million customers.

  • Do I need to Automate

    • Yes of course you need to.  Anything short of full on automation will be a disaster.

  • Can I use MS Excel?

    • No, reporting and data analysis aside you need a fully fledged SQL (or similar) database system.

So what are the needs of Tour Operators?  Somewhere in the middle, comparable to that of the Engineering company.  You might already be using Excel but it is obvious to you that the shortfalls of MS Excel are not giving you the control you need.  You therefore have one of two choices:

• Develop or buy a fully fledged stand alone database system with the associated foreignness &  re training that is required.  Keep using MS Excel for quick calculations and for analysis purposes

• Develop or buy a fully fledged VBA-Excel integrated system.  Keep using what is already familiar for day to day use, for quick calculations and for analysis purposes.

Automating Microsoft Office with VBA

Visual Basic for Applications (VBA) was developed specifically to manipulate MS Office applications directly with unlimited (or close to unlimited) flexibility.  With VBA a spreadsheet becomes the users interface to manipulate information within the limits of MS Excel.  From there VBA can be used to open and close documents or applications such as MS Word, restrict access or assign user rights, send data from one spreadsheet or workbook to another, send e-mails, perform complex calculations, create and display menus or simply change font colours.

Can one delete the contents of ones entire hard drive using:

• MS Excel or Word? - No, of course not.

• VBA? - Yes, with about 2 minutes worth of coding... activated by simply opening the document containing the code.  User remains completely unaware.

In recognition of the power of VBA, Microsoft have macro security levels set to maximum safety by default for its entire Office Suite simply to prevent VBA malware from attacking your system.

Many antivirus programs consider (by default) office documents with VBA code to be threats to your system and deletes or quarantines them immediately.
 

The Limits of Excel

• Limited Data

  • An MS Excel 2003 Workbook can only hold 256 sheets per workbook and 65 000 rows of data per sheet.  This is in excess of 16 million rows of data.  Considering the ability of VBA to handle multiple workbooks with ease and the total number of services that you (as Tour Operator) will need is unlikely to exceed 30 000 in number, one can see that we are not even scratching the surface of VBA or native Excel's limits.

  • For MS Excel 2007 the above mentioned restictions no longer apply.

• Multi User Access

  • Workbook sharing aside, MS Excel allows only one person to edit any one workbook at a given time.  Through VBA we control multiple read/write sessions thereby eliminating the problem.

• Database Validation

  • Native MS Excel does make it possible to restrict to entering information in a predetermined format.  This can however be circumvented by users with the correct security clearance, who have the know how and are intent on sabotage.  Using VBA the control is taken away from the user so that we can ensure that your data is safe and structured correctly.  Entering incorrect dates such as April 31 or 30 February is therefore not possible.

• Sorting

  • It is possible to sort data incorrectly in MS Excel thereby destroying any means of reordering the data correctly.  By using VBA to protect sheets in specific ways we prevent users from sorting data in undesirable ways.

The Benefits of Excel

• Familiarity

  • Ms Excel is known and trusted all over the world.  One has to go far today to find consultants who do not know how to use Excel.  New staff therefore already have what they need to get cracking.  Builder Training can be kept to a minimum.

• Analysis

  • It is widely recognized that MS Excel is the data viewing and analysis tool to use.  Most stand alone software products offer an "Export to Excel" feature.  This usually results in a data dump leaving the user to do sorting and restructuring before any analysis can start.  By using VBA, Builder data is already sorted & stored in the correct format.  The analysis tools in the Builder can therefore give you results for analysis in graphical format for immediate interpretation.

• Redundancy

  • By using a variety of Workbooks to store data, the Builder in itself already contains backups of your critical data.

• System Crashes

  • Barring system wide network crashes, issues with one user on one machine does not affect other users on other machines.  Although Builder files are accessed via your network from the central server, MS Excel runs on your local machine.  Opening or loading a workbook therefore opens and loads the files on your local machine.  Network traffic can therefore be kept to a minimum.

• Light weight

  • Using multiple workbooks means that users do not have to open a database containing all data at all times.  Users only access & load what they need when they need it.

• Remote Office functionality

  • Small satellite offices do not have to have expensive permanent internet connections to the main office.  Ad hoc email facilities are more than sufficient to run and maintain both offices.

Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation.
The Destination Builder© 2005 source code, working methodology and related documents remains the property of P3Projects.
Unauthorised use and duplication is strictly prohibited in any form.