VBA

I think VBA is just amazing. It takes Excel from being just a spreadsheet, to being an amazingly powerful tool.

I'd like to share a number of things I have learned over the past 12 years or so.... I'm assuming you know something about VBA, because this is not a VBA primer, nor for total beginners. What I want to do here is share the best VBA tips and tricks I have collected over more than 10 years.

Learning VBA

Everyone will have their own opinion on this, but I have watched many people learning to program, and I've gone through this myself a few times. I've found that it can take several weeks to get a feel for a new language. At first, everything is unfamiliar, and you feel stupid. Even when you can read a program, you can't write one of your own (which is a lot like learning to speak a foreign language!).

So I suggest getting a good VBA book, and working through it a couple of times until you can start to write your own code. Then find a couple of interesting projects, keep Google close (especially the discussion Groups), and have some fun.

Various utilities


Sorting

Here are a couple of useful routines for sorting. They include a standard quicksort, plus variations that let you sort without changing the order in the original array, and also the ability to sort a particular column in a multi-dimensional array, again without having to change any of the original data around (the sort order is kept in a separate array).

Splitting big files

Here is some code for splitting and recombining big files, for that client with a limit on the size of attachments.

Stripping a delimited string

This code strips items off a delimited string, one at a time. You can specify the delimiter.

Setting/getting more than 255 characters in a textbox

See the code.

Strong encryption in VBA

You can use the strongest encryption algorithms in VBA. This code will not be anywhere near as fast as compiled C code, but it works. Get it here.

Hashing data with SHA256

Here.

Just for fun

Steganography

Now you can hide messages in worksheets (or protect your data by signing it secretly). Here.

Christmas card animation

Here

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.