Excel VBA Get Column Index
27 Mar

Excel VBA Get Column Index

Over the last few months I’ve been doing more work in excel than is good for anyone’s health, trying to create a database best suited for access. Still, in the real world, you have to work on the tools available.

The biggest problem I found was getting column indexes for the relevant information. The quickest method is just to hard code the indexes inside you code A1 B1 etc… However this doesn’t help if you end up needing to add a new column cause you’ll find your self having to search your code for ever occurrence of B1 and changing it to D1 and so on.

So the solution I came up with is a function to search and excel worksheet for a column name and return the correct letters. Now seeing as you probably need to run the same search multiple times its a performance boost to cache these results.

The code included here does just that. All you need to do is define ‘columnIndexRefernce’ globally and set it as a new collection outwith this function, but once thats been done you can call fndDataSheetColumn any time you want the correct column.


Automatically generate GPG revocation certificates
26 Mar

Automatically generate GPG revocation certificates

OpenGPG establishes trust using the web-of-trust. If I trust you and you trust him, I can probably trust them too. This only works if I can trust you though.

Our keys are important and maintaining them is vital, after all they tell the world we said this. Once a key has been made and published that really it, it is now in the world till everything ends. So if you no longer have access to the key you have to let people know to stop using it too, this is call revocation. It’s a special signature you can sign your key with that will make it as revoked. Once a key get revoked no one will encrypt to using it any more. The problem comes from the need to access the secret key in order to generate these and if you have lost the secret key you can no longer revoke it, unless you did as you should have and created these revocation certificates before hand.

Too many people put this off or worse yet forget too, so I have created a small bash script to automate the process. You can download it bellow and see the source as well. Once downloaded you just need to change the KEYS variable to reflect they keys you wish to generate the certificates for. It will also backup your private and public keys – You have to keep these safe!