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!


OpenPGP: GPG Cheat Sheet
24 Mar

OpenPGP: GPG Cheat Sheet

You can’t have enough cheat sheets on the net. Well you probably can, but I still wanted to add my own to the mix. I use the GnuPG command line for almost everything, bar actually sending email so this is a nice little reminder to myself what I’m doing

Creating a new Key First steps

gpg --gen-key

Browsing your keyring What have you got
  • List all public keys gpg --list-public-keys
  • List all private keys gpg --list-secret-keys
  • List everyone who has signed a key gpg --list-sig (0xKEYID)
  • Get the full fingerprint gpg --fingerprint (0xKEYID)
  • Export your public key to a file gpg --armor --export (0xKEYID)
  • Upload a key to the keyserver. Good for new keys, or after signing someone else’s
    • Using the default Key server gpg --send-keys (0xKEYID)
    • Specifying a Key server gpg --keyserver --send-keys (0xKEYID)
  • Export/Backup you private key `gpg –armor –export-secret-keys (0xKEYID)
Searching for a key
  • Finding someones key
    • Using the default Key server gpg --search-keys
    • Specifying a Key server gpg --keyserver --search-keys
  • Encrypt a file for someone, by their email gpg --encrypt filename.txt --recipient
  • Encrypt a file for multiplie people, by their email addresses – It’s usually a good idea to encyrpt to your own key as well or you will not be able to decrypt the file latergpg --encrypt filename.txt --recipient --recipient
  • Encrypt a file for transmission over text – email, IRC, Jabber etc.gpg --armour --encrypt filename.txt --recipient --recipient
  • Decrypting a file gpg --output filename.txt --decrypt filename.txt.asc
Import keys You need to get them somewhere
  • Importing from a text file gpg --import publickey.asc
  • Restore a backup of a private key gpg --allow-secret-key-import --import privatekey.asc
Keys Maintenance Revoking
  • Creating a revocation certificate. You must has the private key to do this, if you have lost your private key, well thats when problems kick in gpg --output revoke.asc --gen-revoke 0xKEYID
  • To revoke a the key all you need do is import the revoke.asc into your keyring gpg --import revoke.asc
  • To make sure everyone knows your keys been revoked you need to publish the updated public keygpg --keyserver --send-keys (0xKEYID)
Keys Maintenance Key Signing
  • You need to edit the key gpg --edit-key 0xKEYID

From here ‘help’ will give you a list of your options, but to sign a key you can ether type ‘sign’ or ‘tsign’. The man pages give a better indication of what the difference is ‘man gpg’ but ‘sign’ is usually sufficent. After they key is signed type ‘save’ and ‘quit’ then you can ether send the key to a keyserver for download by its owner of export the public key and send it by other means, this usually means encrypted email.

Signing and Verifying files
  • To sign a file with your default key use this gpg --detach-sign --armour filename.txt
  • To verify a signed file but put the output from above filename.txt.asc gpg --verify filename.txt.asc
Rooting the Google Nexus7, in Linux
20 Mar

Rooting the Google Nexus7, in Linux

In a previous post I talked about installing CyanogenMod 7.1 Alpha 3 on my HP TouchPad, but since Santa was very nice to me this year I now have a Nexu7, and of course I want to root it. Most of the toolkits and instructions I found talked about doing this thru Windows, but I’m a Linux guy and don’t want to install Windows for what will take less than an hour. So further Googling came up with a way of doing it thru Linux.


Like every site before me, be warned. Follow this guide at your own risk. Rooting, unlocking and installing new ROMs does invalidate your warranty and risk causing damage to your data and/or device. While nothing bad has happened to me followed these instructions, you can’t discount the idea entirely. So BACK UP YOUR DATA – If you don’t Murphy’s Law says you will need it

The Toolkit

There are alot of ways to ‘manually’ unlock your device, but all the ones I was able to find involved downloading the Android SDK. This is a good way to do things if you ether know what your doing, or want a better understanding of the steps involved. However I wanted it done quick, and at the time I was on my netbook and seen no need to install the entire SDK. So I chose to use a toolkit.

The next problem was finding one. The vast majority of kits run under Windows, as previously stated I’m a penguin at heart and needed to find a Linux kit. tatelucas, member of the XDA-Developers forum – if you’ve never checked it out you really should – was there with the solution: Universal Nexus Linux Toolkit, formerly named galaxy-nexus-linux-toolkit. At the time of writing his toolkit supports the

  • Nexus 4 mako
  • Nexus 10 manta
  • Nexus 7 (WiFi) grouper
  • Galaxy Nexus (GSM) maguro
  • Galaxy Nexus (Verizon) toro
  • Galaxy Nexus (Sprint) toroplus
  • Nexus S (worldwide version, i9020t and i9023) crespo
  • Nexus S (850MHz version, i9020a) crespo
  • Nexus S (Korea version, m200) crespo
  • Nexus S 4G (d720) crespo4g

however I have only tested it on the device I have, the Nexus 7 grouper. This toolkit allows you to unlock and re-lock the bootloader, get root access, install ClockworkMod recovery both touch or standard version and, if you feel the need, reinstall the Google stock rom.

Requirements So what do you need?

  • Android Debugging Enabled
  • Universal Nexus Linux Toolkit – The best method for this is using git, but you could also browse the repository down manually download the files, I will be using git

Downloading from git Just in-case you don’t know how

Getting Root

Now that we have a copy of the files, in my case stored in “~/RootNexus7/galaxy-nexus-linux-toolkit”, change to the stable source, the folder called “stable”, and we can start the install.

From this point on the installation is straight forward. Tatelucas has made the interface really easy to use and the on-screen instructions detail each option and tell you want you need to be doing on the table.

Now you have root

If everthing worked as expected you should now have root access to your Nexus. The first thing I would recommend you do now is install a proper/full backup package. My app of choice is Titanium Backup ★ root. After that… do a little Googling, the platform is now your oyster – enjoy

If you like this toolkit, why not buy the developer a coffee?

Donate to tatelucas with PayPal

Setup your own Firefox Sync Server
15 Mar

Setup your own Firefox Sync Server

As most people know I hate using someone else’s service when I can host my own and since I’ve started using Firefox for Android that’s no exception.

Firefox allows you to sync your preferences, bookmarks and add-on settings between devices using the Firefox Sync Service, but they default servers are hosted by Mozilla. This isn’t a problem for me as they encrypt all data in the client before its transmitted to their servers, and if you don’t trust the client then there are bigger problems. Still I like to ‘roll-my-own’ when ever I can and Mozilla have made running you own sync server very easy.

The instructions bellow are based on my current install. Which is Ubuntu 12.04.1 LTS, MySQL and Apache2. These steps worked with out any problems for me – please let me know in the comments how you get on and if you find any problems or have to take any additional steps I missed out.

As yet Mozilla don’t release any binary packages for their server, so to install one what I’ll be doing it checking out the latest release from their repository and building it in-place

Prerequisites These are the things you’ll need

Like most software, the server is made up of several components that all work together to form the final application. In-order for them to work you will need to make sure your system has these installed, or install them now:

To install these on my Ubuntu machine I just make the this request of Apt-get:

The Build Use the source

Now that your machine is ready you need to get a copy of the latest source code from

No we have the source we can use the build command to make an Python environment isolated from the rest of the system. It’s into this environment all the required dependences are installed :

In a unique step for I even got make to ran the testsuite on the compiled source:

In the end this worked out to my advantage because it found a missing dependency. Using the pip command I installed pysqlite into the new python environment:

Running test again produced no error so it’s safe to move onto the configuration

The Configuration Make it work

To configure the server you only need to edit the ini-like setup file “./etc/sync.conf“. The is well commented and you will probably be able to make seance of it. In the most part the defaults worked fine for me, but you do have to change a few things.

The first is the fallback_node URL. This is the client visible URL of your service and make sure node-assignment and user registration works correctly. The default value has port 5000 specified in the URL. This is in-case you are running in standalone server mode, I am not as I will be putting Apache in front of the server so I just took it out:

Out-the-box the server will use sqlite files for the user and storage database. You’ll see in the configuration file these are being stored in “/tmp” this is obliviously an terrible idea for a production server, so next well move them to a more appropriate place:

Apache Running behind a Web Server

At this point the server already has a server built in which I’ve not talked about, partly this is because while it is good testing it should not be used in a production environment but mostly its because I never had any cause to run it.

For production server you will need a web server which is compatible with WSGI protocal. These include, but are probably not limited to:

  • Apache – Combined with mod_wsgi
  • NGinx – With Gunicorn or uWSGI
  • lighttpd – With flup, using the fcgi or scgi protocol

I will be talking about the first option as my server was already configured for it.

Create a new Apache configuration file for your server, bellow is an example of the configuration I used:

That should be you done. Just reload your Apache server to activate the new host and start playing:

Security & User Registration It’s not a dirty word you know!

After installation and Apache setup your ready to rock and or roll! However in the default configuration the server support new user registration using the Firefox built in interface. Now unless you are going to make your server public to the world it’s a good idea to disable this after you have setup your own Firefox instance:

Getting Logitech Custom Mouse Buttons Working Within Linux
13 Mar

Getting Logitech Custom Mouse Buttons Working Within Linux

Having lived in a Windows environment for the past couple of years I’ve gotten somewhat used to my routines and short cuts, most of which are missing or changed now that I’m 95% Ubuntu. The most notable absentee are all the extra buttons on my Logitech MX Revolution mouse, which I have mapped to a vast array or custom key stroked. So, lest I forget, this is how I have got these custom buttons working correctly.

All these buttons are detected by the latest kernel (which as of writing is)

This is great news because all we need to do now is map each button against the desired application/keystroke.

My system of choice is Ubuntu 10.10 as of writing, this has since changed to Fedora so all the command line and install command listed are correct for this distributions as of writing. If you find other commands work on other distributions leave a not in the command and I will be sure to update the main article.

The Install Instructions for a fresh install

  1. We should install xbindkeys. This will re-map mouse and keyboard inputs so the install is…

The goal is to configure the mouse buttons to send key combinations to activate o

ther desktop or application functionalities. Technically all xbindkeys is doing is executing an application in response to a keystroke or mouse button.

  1. So in-order to map a mouse button to a keystroke we have to install an application called xte, which in Ubuntu comes as part of the xautomation package which can be installed like this

  1. Now we need to create a configuration file for xbindkey, which can be done like this…

  1. We need to edit this file in a text editor. I use nano, but vim, kate or gedit are just as good but you can of course use what ever editor you would prefer to…

The xbindkeys configuration file has a very simple format…

  1. So we need to add our button-to-key configurations, but first we need to know what ‘events’ the mouse buttons are triggering before we can remap them. For this we can use xev, this program is a key and mouse event sniffer. It runs by opening a small window. You can now start pressing keyboard or mouse buttons over the window and see if they are detected. For example the forward button on my Logitech Revolution MX looks something like this:

Bellow I’ve included a table for the Logitech MX mouse, these will probably be if you have a newer, or older mouse

Mouse Button Event Code xbindkeys Code
Thumb Scroll Up 13 b:13
Thumb Scroll Click 17 b:17
Thumb Scroll Down 15 b:15
Thumb Button Up 9 b:9
Thumb Button Down 8 b:8
Left Button 1 b:1
Right Button 2 b:2
Scroll Up 4 b:4
Scroll Down 5 b:5
  1. Now that we have a list of all our button codes we can move on to actually writing the configuration file. For example I have this to map the Thumb Button Up and Thumb Button Down buttons to switching workspace in my Gnome shell, these keycombinations are specific to my setup so you will need to chnage them. What I’m doing here is pressing the ‘Windows’ key ‘Left Control’ then ether ‘Down’ or ‘Up’ then releasing the other two keys…

The Rapup Now we make it all work

Thats all there is. Once you have setup your /.xbindkeysrc confirguration file, you just need to configure xbindkeys to run automatically on system startup. This is going to be different depending on your windows manager, but here are the steps for KDE and Gnome

  1. There’s a tool in GNOME 3 which allows you to add, modify and remove autostart entries and you can run it by executing from a terminal or from the ALT+F2 dialog. Just run gnome-session-properties
  2. Click on “Add”
  3. Write ‘Xbindkeys’ in the Name and ‘/usr/bin/xbindkeys’ as the Command and press OK.
  4. Done!
  1. Open System Settings.
  2. Go to Advanced tab -> Autostart.
  3. Click on “Add Program…”.
  4. Write ‘/usr/bin/xbindkeys’ and press OK. A new dialog pops up. Press OK again.
  5. Done!