February 2011 Archives
When creating applications for data entry, saving keystrokes for the user is
a worthy goal. One common task is entering addresses, which can be made easier and more reliable with zip code lookup. The idea is that the user only need enter the zip code and the application uses it to lookup the state and city information automatically.
Note: if you cannot see the form, please click here.
The problem is that there are over 80,000 zip codes in the USA. This is too many to attack this problem with formulas. The performance would not be acceptable. This task is tailor made for VBA. In a similar technique as that used in the Partial Match Database Lookup, this project uses the AdvancedFilter method of the Range object.
The technique is so fast that it barely blinks at this task, and happily creates dynamic lists of relevant city and towns, on the fly for each zip code entered - and these lists are used to feed validation drop lists.
The attached workbook contains virtually every zip code and community in the USA as a data source, which adds about 1.75 MB to each workbook that the data are used in - not very much in today's world.
Another benefit of this technique is that it requires very little coding. The workbook demonstrates how to use Structured Table References inside VBA Shortcut Range Referencing. I think the solution is elegant an highly useful.
Here's the file:
This incredible technique is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning.
I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.
This Spring I will be offering the course again. There's already 150 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!