Excel Stuff to Share

I really love Microsoft Excel, and spend quite a bit of time when I am bored writing small VBA macros in Excel to solve a problem I may have. If you don't know much about VBA, you can have a look at my VBA page here. I decided to store some of my macros on the web to share with other liked minded people who have the same problem that I had.

Hopefully you will find something here that you like and can use.

Kris Kringle

This is a small spreadsheet I built in 2004. It automates the process of people selecting a name out of a hat to create a Kris Kringle (aka Secret Santa). It is designed to ensure that no one (not even the person running the Kris Kringle) knows who is buying presents for who. It creates a text file that is then manually emailed to each person containing the name of the other person they have to buy for.

Download Kris Kringle

Directory Lister

It is actually quite hard to get a list of all files in a folder and/or sub folders on your PC. You can use a dos command like dir > files.txt and that works fine, however the output format is not that useful. I recently wanted to get a database of all my images on my NAS drive but found the file format from the dos command was too hard to work with. So I wrote this little vba spreadsheet to solve this problem. It returns you a table showing the path name and the file name of everything in the nominated folder and sub folders. Download Directory Lister

Hoverbox Code Creator

I was doing some work in 2009 to help a friend set up his website. He needed to load a stack of images onto the web to promote his picture framing business. I did some research on the web and found a great bit of code called Hoverbox developed by Nathan Smith.

Rather than write the HTML code line by line, I decided to build an Excel workbook that would do this automatically for me. I had previously written some code to list the files in a directory and sub directories, and I also had written code to create HTML pages. So I combined these 2 ideas and code base to make this spreadsheet 'Hoverbox Code Creator'.

Here is a sample of a web page that uses the Hoverbox framework and my Excel code. Using my Excel spreadsheet, I can pump out a page like this one with 200 linked images across 15 sub directories in under 5 mins. To use this tool, download my Excel sheet here. Then go to Nathan's website, read about and Hoverbox and download the sample code set by clicking here.

Sumproduct

Sumproduct is one of the most useful and versatile functions available in Excel. I have been using Excel (and Lotus 123 before it) since the '80s, and yet I didn't stumble on this function until 2006 when browsing various Excel forums. This is not something I have written or contributed to, but it is well worth a look if you do complex lookup functions in Excel. The power of sumproduct is unbelievable.

For a really good read about sumproduct, have a look here