Transferring my VBA memoirs

A couple of years ago I set up a pbworks wiki to save bits of VBA code, added a few bits and then promptly forgot it existed and what the password was.

I figure this would be a more convenient space to store them where I might discover them more often… And make this blog a bit more useful.

Edit starred place descriptions in Google Maps

I love the integration between Google services on the web and on Android. Perhaps my most used example (after email) is starred places on Google Maps.

The usual workflow is this:

• Find a place on gmaps in the web browser
• Put a star against it on the map
• Fire up gmaps for android 
• Find the starred place and then go there

My favourite part is that once stars are synced, they persist in the android map meaning I don't need data and can work with offline maps. Handy for when I'm overseas with limited data...

The only issue I found was that if the star was placed against an address instead of a business (or other point of interest on gmaps), the address was the only info I had to go on and I would have to try and remember what it was at that particular address I wanted to visit.

I thought this would always be the way but a short search led me to this thread and the revelation that it needn't be. Strangely, that nugget has been all but ignored by subsequent posters.

So as suggested, once you have placed a few stars on the map, go to www.google.com/bookmarks and you should receive a list of starred places (most recent additions at the top).

Here you can edit the description, add labels and further notes. Magically, the star label in maps for android will update to be the same as the description you just entered.

Resurrection

So, three years pass without a post. Let's give it another try...

Custom Number Formatting in Excel

Undoubtedly one of Excel's sneakiest features, custom formatting unlocks a whole array of possibilities for formatting where conditional formatting is either not necessary or not available, for example in a chart's axes or data labels.

Read Jon Peltier's post on the topic for a good treatise on the subject and check the Microsoft Knowledge Base page for a full list of syntax.

Posting blog articles with Word

I stumbled upon this feature in Word 2007 this morning. It seems that Microsoft and Google have agreed to play nicely for a change.

Posting from word is relatively easy - you select Blogger from the list of supported blog providers and give it your login details. Now you just type your post and hit the 'Publish' button and it will appear.

There are other options for blog providers, including Live Spaces, WordPress and SharePoint and also support for MetaWebLog and Atom APIs

Ideally I would have liked to give some screen grabs to demonstrate this but what I have found is that there is no easy way to get pictures to your blog. If you use Blogger though, you aren't able to upload pictures with your post.

It seems possible to use a third-party host such as PhotoBucket insofar as there are configuration options there to set the upload URLs but after much playing around, I haven't been able to get them working, mostly due to the fact that these sites don't allow for FTP uploading without a premium account.

Even manually uploading and getting a URL for the picture isn't helpful as Word doesn't seem to want to let you insert the picture from a weblink.

I guess I'll keep on playing with it...

Tagged / Labelled emails

I have a lot of email. A lot...

I used to file it in discrete folders inside folders inside other folders - stored away in case I should ever need it in future (and I often do). The trouble with the folders is it can take a while to find what you're looking for. In addition you eventually end up with a huge list of folders to navigate through in order to file something away.

I am talking about my work email here but the situation was closely emulated in my personal email. One day though, I got sick of the amount of spam I was receiving and decided to pack it in start afresh. I opened a shiny new gmail account and it was at this point my eyes were opened to tagged emails (called labels in gmail).

Now I have a flat archive with great search capability and things are tagged according to what they are. Ok so I still have a huge list of tags but everything is archived in the same folder so it takes no time to sweep email in there once it's categorised. Best of all, I can find things without trawling through ten folders first.

The next task was to categorise my work email and keep it up-to-date. More on that later.

Needless to say this new method (for me anyway) is more flexible and in many ways more powerful, especially when using Outlook's Search Folders.


Thanks to gmail and 43 Folders (link) where I first read about GTD and which shaped my thinking about this topic and others.

Use VBA to delete a file

This morning's annoyance was with Excel 2007. Somehow I'd created a file in the XLSTART folder (surprising as it's on a business drive to which I don't have access). To those of you that don't know, anything in this folder will be opened when excel starts.

So, my dilemma is this: How do I delete a file when I don't have access to it through Windows Explorer?

As I was in excel that got me into this mess, I figured it should be used to get out. A quick google revealed how to determine the name and path of the file I had open (link) and then how to perform basic file functions using VBA (link).

From that I put together a quick VBA macro to delete the file. I added a few message boxes along the way to check the path and then to give me the option to back out if I got cold feet at the last minute.

User names have been changed to protect the innocent.

Sub DeleteDefaultFile()

Dim Answer As String

MsgBox "Currently in " & CurDir
ChDir "C:\Users\######\AppData\Roaming\Microsoft\Excel\XLSTART\"
MsgBox "Now in " & CurDir

If Dir("C:\Users\######\AppData\Roaming\Microsoft\Excel\XLSTART\Sheet1.xls") <> "" Then
Answer = MsgBox("Delete the file?", vbYesNo)
Select Case Answer
Case vbYes
Kill "Sheet1.xls"
Case vbNo
MsgBox "User Aborted"
Exit Sub
End Select
Else: MsgBox "The file does not exist", vbOKOnly + vbExclamation
End If

End Sub

For some reason, inlining didn't copy to the blogger so it's all flat, even if I manually put the spaces in. Sorry.