VBA in Excel, underused?

I just wrote up an application in VBA for Excel that does about 30 minutes worth of work in about 15 seconds. The sad thing is, people have been doing this process monthly for years... it seems to me that most companies don't realize that VBA in Excel can automate a lot of mundane tasks.

We've got employees in the office getting trained on the patrol service process. My desk is next to the woman who does the training, and while I keep my reference materials pretty up-to-date, the training documentation she was handing the employee she had was woefully out of date.

Now, a couple weeks back, I'd updated the documentation myself just because I was tired of the version hanging at my desk with a heavy amount of editing in pen. But the process took about 30 minutes to do, and it seemed like such a waste of time.

I can't post the code here (for confidentiality reasons, I work for a security services company after all), but the basic idea is that we have a spreadsheet which lists all of the patrol (guard) services that work for my company. Each patrol service covers an area around one or more company branch offices, and some of those patrols have access to our own software database for quick access to information regarding alarms etc. Offices that don't have this access MUST be contacted by my company with the information that they need to respond to an alarm, or they don't know about the alarm.

It's important to know which companies can read the information off our database themselves, and which can't. If you assume a company can read the information they need and they can't, an alarm could go off without triggering a response. The list needs to be updated whenever a patrol service with access begins to provide coverage for a branch or drops coverage to another patrol without access. The database is updated immediately when this happens on our side, but the reference that we keep at our desks is not always up to date.

The problem stems from the fact that the database is in Excel, but it's a user-friendly document that uses a lot of color coding and data consolidation for easy use. It's not a bad thing; when an alarm is going off somewhere, you want to be able to quickly find the information you need. However, it's not easy to break the information down into the reference sheet on our desks... the result is a lot of copy-paste and double-checking to make sure you're not incorrectly entering any data.

The VBA modules work within the Excel file I created to generate this "patrol services with access" list. When the file is opened, it immediately opens the file on the company intranet that contains the up-to-date database of patrol services. It switches to that document, and copies the pertinent data over. (For those of you Excel-heads, I couldn't use an external data import since the patrol services database contains mixed data types in columns, causing loss of data when accessed via the data import feature. The only workaround involves a registry edit that a few hundred computers not on AD would have to suffer.)

Once the most recent data is copied over, it uses a search function to pick what it identifies as corporate branch office numbers out of cells with strings containing punctuation and formatting. It strips these branches out, copies them into individual cells, and lines it up with associated data. It has to do this one cell at a time, switching back and forth between 2 Excel worksheets, searching, editing, copying, pasting. I wrote the program to adapt to changes in the database, and only stop when it's reached the patrol services that are listed in the database and don't have access to our systems.

When it's done, I just use a VLOOKUP on the first sheet to rebuild the table. I have to embed the VLOOKUP within IF and ISERROR functions because the IFERROR function isn't supported in Outlook 2003 at the office (which I just found out is getting updated next week; facepalm).

I added some message boxes to make the process user friendly. The user is prompted if they want to update the file to the intranet database (so if they're working from home it doesn't return a file not found error). Then it asks them if they want to run the update process that rebuilds the database into the file that lists only those branches that have a patrol service with database access.

I'm pretty proud of the result. The team leader for the Guards team was stunned, and loved it; she's the one that usually has to spend all that time copying and pasting, and it'll save her a bunch of time. The screen flickers like crazy while it runs, disseminating, formatting, and copying information between files and worksheets. After about 15 seconds, it's got a completely up-to-date rebuilt file that can be posted online for any user to print out whenever necessary.

It saves time, ensures accuracy, and reduces the possibility of a patrol service not being notified of an alarm event.

When I can find time, maybe I'll post a sanitized version if anyone is interested. :)