Vulture alert…

Okay, seriously... what is going on?

Over the past week, I've put my phone on do not disturb.  I've had at least a half dozen calls trying to sell me everything from payroll systems, to box storage and shredding, to offers to help me reduce my American Express costs.

Is this a final sales push of the quarter, or something? I seriously need call display here...

Converting External Links to Values

I had to do some real quick budgeting work today, and needed to build a template to work from.  The easiest way was to grab a heavily linked workbook we had, and convert all the links to values so that I could work some different assumptions on it.  The thing is that I didn't want to just copy and past values, as I wanted all the other formulas to work, I just didn't want to point to external files any more.

I ended up coding this to work as follows:

[vb]Sub KillLinks()
Dim ws As Worksheet
Dim cl As Range

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
Application.StatusBar = _
"Removing external links from " & ws.Name
With ws.UsedRange
Set cl = .Find(":\", LookIn:=xlFormulas)
If Not cl Is Nothing Then
cl.Value = cl.Value
Set cl = .FindNext(cl)
Loop While Not cl Is Nothing
End If
End With
Next ws

With Application
.ScreenUpdating = True
.StatusBar = False
End With
End Sub[/vb]

Worked fairly well, and quickly, giving me the feedback of how far it had progressed.

I'm curious to any comments on this, though.  Is there an easier way?

Excel inter-version styles quirk

I found this by accident...

I still work between Excel 2007 and Excel 2003 depending on the tasks I'm doing. Reason being is that I find some things easier to navigate, and also have tools built in 2003 that I haven't had the chance yet to migrate to 2007. (Yes, this from the guy who wrote the book on the Ribbon!)

So the other day I went to use one of my files in 2003 that I'd previously used in 2007. Upon going to modify a style, I found out that I had a TON of styles in 2003 that I never created.

(If you don't know what Styles are, or how to use them, see here.)


So it turns out that if you open a 2003 workbook in 2007 and save it, all of the 2007 styles are automatically merged into the file.  Open it again in 2003, and you've got them all in the list.

I haven't actually tried using any of the 2007 styles in the 2003 workbook, so can't speak to how well they translate.  (I don't really use the built in styles much.)

Activating a Tab on the Ribbon

If you're working with the Office 2007 Ribbon, you may have come to the conclusion that there are areas of, the RibbonX object model which are kind of weak. One of the areas that lacks in the RibbonX API is the ability to programatically activate a specific tab.

The good news is that there is a way to select Ribbon tabs using VBA. The bad news is that we have to do it using SendKeys. Why is that bad news? Well... SendKeys does pretty much exactly that... it sends keystrokes to the user interface. This means that it isn't totally reliable, as users can mess up what windows may be active, send other keystrokes, or whatever. Regardless, it's the only way, so here's how it works:

Activating Built in Ribbon Tabs using VBA

In Excel, to activate the Home Tab using VBA, you'd use:

Application.SendKeys "%H%"
End Sub[/code]

In Word:
WordBasic.SendKeys "%H%"
End Sub[/code]

What this does is send the ALT key (using the %), then the H key, then the ALT key again (the final %). The reason that we send the second Alt key is to clear the ALT key from being active. Alternately, you can also use {RETURN} in place of the second %, which will work as well.

You can test this yourself, by pressing those keystrokes manually. This also lets you figure out exactly which keys to use for which tabs. 😉

Activating custom Ribbon tabs at application startup

Now, for reference, the remaining portion of this post was covered in our book RibbonX - Customizing the Office 2007 Ribbon on page 404 under the heading "Activating a Tab at Startup", and is geared towards Excel. You can sub in your own tabs and adjust the VBA for Word based on what I showed above. (It's slightly adapted here, but only slightly.)

To activate your own tab at startup, you'd use something like the following XML. (Pay attention to the keytip.)


And then the following line of VBA gets inserted into the onLoad routine:

[code]Application.SendKeys "%UN{RETURN}"[/code]

size Attributes in dynamicMenu

A user asked a question in Experts Exchange asking if it was possible to use a dynamicMenu in the officeMenu.  The answer is that yes, you can, but this person couldn't get it to work.

The issue appears to be with the following code:



Specifically, it was the size attribute causing the issue.  If this same code were used in a tab, it wouldn't be an issue.  The officeMenu, however, will not let you control the size.

This isn't the first time I've run into something like this.  It's in this kind of situation where the CustomUI editor is key to solving issues.  By taking your XML code and validating it in the CustomUI editor, you can identify these issues immediately.

Social Networking

CNet posted a link to Royal Pingdom today, which was somewhat interesting. The actual article is on the popularity of certain social networking sites by world regions. To me, the interesting part was the world pictures, and just how many social networking sites we deal with today.

I'm a member of LinkedIn (which I actually do update,) and Facebook (which I rarely go to,) and have been invited to Orkut (although I never signed up.) MySpace I never got in to, and the rest I haven't even heard of.

To me, LinkedIn is the exception to this, but I find these kind of sites to be incredible time wasters without adding much value to me. Sure, they allow you to keep in touch with friends, and I'll admit that I find out more about my brother's life through Facebook than I do by phone calls, but still. They're just one more avenue to contact me, disrupt me and distract me from doing productive things.

Don't get me wrong, I'm not against Social networking. I personally prefer doing mine in forums though. But then, what can you say... I AM an accountant. Maybe being social just isn't in my blood. 😉

Go TomTom Go!

A couple of weeks ago, I took the most relaxing vacation of my life.

My family and I went to the Okanagan Valley and spent some time in Osoyoos BC at Walnut Beach Resort.  It was purely awesome, and I think we're just going to have to go back there next year.

So what made it so relaxing?  From the time I left work on the Friday afternoon until I returned to work on the Monday a couple of weeks later, my laptop was turned on twice... both times it was so my wife could read her blog posts.  For the very first time in my life, I shut off for my vacation.  It was something I badly needed to do, but honestly never thought I could.  I didn't even check my email on my smartphone or anything.  Pure bliss, seriously.  (The place was awesome too, as was the weather.)

One of the things that I did do on my vacation was to really use my TomTom Go 920 on a regular basis.  I'd used it to drive to/from work (like I don't know the way), and it seemed to work pretty well.  On vacation, though, now this was a serious test!  So here's my feedback on GPS (this is the first one I've ever owned), and some specifc TomTom features.

"Go Confidently"

This is actually TomTom's tagline, and couldn't be more appropriate, as far as I'm concerned.  When driving around, I never had to worry about getting lost, as TomTom could always get me back on route to my ultimate destination.  It allowed me to drive to Victoria last weekend with no concerns about having to read a map, and allowed us to find our hotel with ease in a city I'd never spent time in.  One day, when we showed up an hour early for a B&B, we decided to go exploring.  Not a concern in the world as we headed off the beaten track.  We saw some REALLY out of the way countryside, TomTom constantly re-routing to let us know how to get back to the B&B when we were ready.  I'd NEVER have done that, or seen those roads, without TomTom on board.  Very cool.


This isn't a knock on TomTom so much as it is on general GPS technology.  The device will get you close, but you need to do the final looking for your destination.  The route is easy, but the last 50-100 metres can be a little confusing.  I think it's because a lot of places are plotted on the map by their street address, but as we all know, that sometimes doesn't actually match exactly to their physical address.  (Driveway on the side road, mall stores are particularly difficult, and addresses where houses are tight together don't have to the foot accuracy.)  I hope that over the next few years that this can be improved overall, and would further suggest that it will be user communities that make this happen.  And fortunately TomTom has done some work in this area.


Correcting the actual location address of a "Point of Interest"(POI) is pretty easy in TomTom.  I managed to "move" the Fairwinds Centre (where I work) to it's actual address fairly easily.  (It was off by about 100metres when I first got it.

Unfortunately, while moving POI's is easy, I had bigger hopes for updating the map.  TomTom has "mapshare", which leads you to believe that this should be easy, but I'm not convinced.  My subdivision is not accurate, with a new road being put in that isn't on the map, and another road being moved.  Neither road is straight, which adds complications.  I was really hoping I could delete two roads and re-create them in the right place.  It looked like I did, but those changes don't seem to take effect, despite the fact that I made them, and have asked to have all map corrections.

Adding roads at all is painful.  (Why can't I just turn something on to say "start here and track me as a drive" then "end here"?)  The worst is that without the immediate feedback on my map, I have no reason to give this feedback.  I'll correct all of Nanaimo if you let me... just let me!

Distant POI's

Not all POI's are in TomTom's database, so you need an easy way to find remote POI's.  I struggled in this area as well, as I seemed to need to know which city things were close to.  So I'm trying to locate Fintry Provincial Park, which is across the water from Kelowna.  I tried looking up FIN with Kelowna as the closest point... no luck.  Then I tried near Westbank... no luck.  Now, I know about where it is, but I couldn't find a way to pull up a BC map to try and drill down.  The software needs to have a way to do this.

Itinerary Planning

This is kind of a neat feature, but again needs some work, I think.  I laid out our entire vacation in the planner, then saved it.  And since it has the abilty to set waypoints along the route, I did exactly that.  Great, but...

I treated my waypoints as somewhere I wanted to stop.  TomTom seems to treat them as somewhere to pass by.  I suppose this makes sense, but it doesn't totally work due to the accuracy issue I mentioned above.  At one point, we stopped at Manning Park and, in fact, drove all through it.  I'm not sure where the address TomTom picks up for Manning actually is, but it never marked as visited.  So as we're driving away, it tells me I need to pull a U-Turn.  It took me a bit to figure out that Manning was never marked as visited.

By contrast, in Naramata, where the wineries are next door to each other, it was marking them as visited before we even found them!

Problem solved by marking them all as destinations, as I actually had to manually select each destination I wanted to visit.  I guess if you just want to use the waypoint to route yourself a specifc way, then it would work well.

A really good thing with the planner, though, is that you can save and recall multiple itineraries.  So we ended up setting one up for each day we did wine tours.  I can look back in TomTom and see which wineries we went too, and where they were.  Yes, we hit that many!  🙂

Funny Things

While TomTom had a TON of wineries in it's list, the Okanagan Valley has many new ones.  Because of this, we had to set some destinations without the aid of a POI.  I don't know who you'd point at for this, but I found that the local maps seemed to have roads by local names, where TomTom had different names.  (i.e 325th Street was shown on a local map as #11 Road.)


Love the fact that you can customize the voices, and the "Quick Start" screen, but I wish you could add specific items to the Quick Start.  There are a few things you can add, but they are pretty specific.  I'd like to be able to choose what I want there, such as a "Next Itinerary Destination" button.

QuickGPSFix is awesome.  Where the GPS Unit would normally take a minute to locate satellites, if you sync to the QuickGPSFix, it downloads the satellite positions for you from your PC.  Then when you get in your car it takes about 5 seconds.  Pretty cool there.

The hands-free functionality works pretty well when linked to my Bluetooth phone, although my wife tells me that I sometimes sound like I'm underwater.  Moving the GPS closer did fix that a bit, but you can only move it so close before you start getting it into your driving view.

The only other comment I'd make on the phone is that it's not ideal if you are making a call to somewhere that has an extension.  If you can quickly call up a screen on TomTom to dial the extension after the call has been answered, I haven't found it.  (Not that I've generally put a lot of attention to it, as I've been driving.)


Any qualms I've raised above are minor compared to the awesome benefits of having this puppy in the car.  I love it, and would recommend it to anyone.  😀