Other Stuff

Get started with the Windows API

VBA can use many of the powerful Windows functions used by C programmers. This workbook illustrates some uses. Always be careful, because the API can crash your PC in a flash if mishandled. Always use code written by someone you trust!

Strip fields off a delimited string

This is a utility for repeatedly stripping fields off a delimited string.

Backup your work to separate files periodically

You may never need this, but what it does, every time you save, is to check if at least 15 minutes have passed since it last made a separate backup. If so, it creates a numbered copy of your workbook in a backup folder. In this way, even if you keep saving with the same workbook name, you will get a sequence of backups recording the state of the workbook at various times.

Store and lookup data in a hastable (new)

Hashtables are great when you are reading in a whole lot of data and want to be able to find one of the items quickly, or maybe keep a running total of something for a group of people, adding them up from a big list containing duplicates. Here it is.

Excel can do pretty nice looking merges like Word (new)

Here is an example.

Trap events painlessly (new)

If you put code in an Excel event, eg worksheet_change, and you run a routine which changes the sheet, you'll trigger that same event again, and go round and round until you crash. Here's a technique for avoiding this, and it even includes a timeout in case you forget to reset the flags.

Register an OCX programmatically(new)

Here it is.

Reliable random number generator

If you use Excel's random number generator, and you use more than 16,000 numbers at a time, and you want it to be random to a couple of decimal places, read on. The Excel random numbers deteriorate quickly, and you really need something much better if you rely on those numbers to be random. Here it is.

Logging Excel

We dread getting that phone call from the client saying your workbook is crashing, and because the code is protected, you can't easily identify the problem area, especially over the phone. Here is an approach for logging what is happening in a separate textfile as you go. It includes info on the client's system config. The idea is that when the problem arises, you enable the logging (how you tell Excel to turn on logging is up to you) and then they send you the logfile after it crashes, and you can see how far it got, what input they used, etc.

Manipulating Data

There are several tricks for working with data. I've put my favourites in this workbook.

Working with Outlook

A simple example of how to send email from Excel, with and without Outlook.

Making a shortcut

It is extremely difficult to make a shortcut in code, but an internet shortcut achieves pretty much the same and is very easy to do. Try this.

Creating a linked list

Want to sort a multi-dimensional array without actually reordering the array, so that you can look items up on any one of several columns? Try this.

The Excel developer and the nuns

Only A little risque .

Speed of different variable types (new)

Are integers faster than longs? Find out here.

Finding cell precedents

Yup, VBA's Precedents command only finds them on the current sheet. Go back to Excel 4 to get around this with this example.

Fast data matching

If you're still learning VBA, you may think it's REALLY slow when you try to match different sets of data. But there are some magic speed tricks. If you're comparing cell by cell, would you like to speed up possibly hundreds of times? Look here.

Use Excel custom properties

Ever looked at the file properties (File menu)? You can set your own with this spreadsheet. BTW, with the aid of a free DLL from Microsoft, you can extract file properties from closed Office files, eg author, title, keywords etc. I haven't got around to publishing an example of this yet.

Spreadsheet auditing

I had to audit some large spreadsheet recently, and was frustrated by the lack of built in tools. I also found the VBA precedent command doesn't find precedents in other sheets. The only solution I found was to use old Excel 4 functions. Here is my collection of auditing tools.