References

There are a lot of people who write add-ins for Excel, myself included.  On several occasions I wondered if I could use those add-ins within the VBA of another spreadsheet.  And of course you can using - you guessed it - references!  And frankly I think references are one of the most criminally under-documented features of VBA in general.

I think of references essentially as "VBA add-ins".  However references are somewhat more powerful add-ins because there is such a variety of things you can use, including things that come built in with Windows or Office.  I'll give you a couple of examples in a bit.

Firstly to have a look at references you need to remember the Tools - References menu option in VBE. This will bring up a huge list of available things you can reference.  In any spreadsheet there are a few standard ones that are ticked at the very top like the "Microsoft Excel Object Library". I'd recommend that you don't remove any of these standard ones as they are fundamental to smooth operation.

All the references that you've selected will appear at the top of this huge list, with ticks next to them. So you won't ever need to search the whole list for an elusive tick!

What references might you want?  Well one that I use regularly is the Microsoft Scripting Runtime. This gives you access to the Visual Basic Scripting Object Model, with notable inclusions the Dictionary object and the FileSystemObject.

• Dictionary objects are very similar to Collections  in VBA but with some useful additional features.  If you want to check if an item exists in your Dictionary object then use the Dictionary.Exists method.  And Dictionaries also provide you with a list of the unique IDs of its elements using the Dictionary.Keys property.
• FileSystemObject is a very powerful tool in scripting. You can recurse through Files in a folder or subfolders of a parent folder, create and delete folders and files, check if folders and files exist and so on.  It's an extremely powerful tool - I may well do a future post dedicated to it.

 Another reference I've used recently is the CAPICOM Type Library.  I needed a hash function to help me validate some data, so decided to use the MD5 algorithm.  (If you don't know what a hash function is, it's essentially just a fancy checksum but it works on text data as well.)  Adding the CAPICOM reference gives you the HashedData object, which can be used to generate a variety of hash functions.

As mentioned at the top, you can even create references to other Excel addins, giving you access to all the same functions and macros.  This can make for some helpful ways to organise and simplify code.

All in all references are an extremely powerful tool and well worth getting acquainted with.  If you're interested in investigating the Microsoft Scripting Runtime further then I'd also recommend checking out the VB script pages at www.devguru.com for a handy reference.