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.
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.
EDIT: Jan 2014. Well 10 years on, I decided to update the file. New features include
- improved coding (I am better now)
- better file creation
- email to all users feature (for Outlook)
Shortcut Menu Manager
This is a spreadsheet I built in 2006. I used it to add and delete menu items from Excel (ver 2003 and before). When Microsoft moved to the Ribbon Bar instead of the menu bars, I trashed the sheet as worthless. Recently I was browsing an Excel forum and someone was asking about adding macros to shortcut menus. It suddenly occurred to me that this tool may still work for shortcut menus. So I dusted it off, and indeed it did. So I have modified the orginal sheet and packaged it up as a shortcut menu management tool. Hopefully the brief instructions are enough for you to work it out. I use this tool to put my favourite macros on the Excel shortcut menus.
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
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