Jan 082011

For those who envisage building an Excel RTD server, I have written a tutorial and open-sourced the code on SourceForge.

The following topics are covered:

  • How RTD servers work
  • Architecture
  • Excel, Multithreading and callbacks
  • Providing easy-to-read function names
  • Talking to the GoogleMaps APIs
  • Avoiding Application Domain misery
  • Embedding a GoogleMap page in an Excel Task Pane
  • Creating the Setup project
  • Changing the Excel RTD Throttle Interval
  • Utility functions
  • Building help from the source with Sandcastle

I hope that you will find it useful.

Oct 202010

Addin for Microsoft Excel which allows you to perform forward and reverse geocoding, both by address and latitude / longitude, calculate Great Circle Distances using Vincenty’s algorithm, calculate travel distances and durations and verify the results with a Google Maps Task Pane, all inside your comfortable Excel interface.

This is useful for creating GoogleMap applications to find places from a list, like this.

Implements three Excel formulas:

=Geocode(request, location)

Request is the field to return:

  • status The status of the geocode request (Fetching, OK, N matches, etc.)
  • latitude The latitude of ‘location’
  • longitude The longitude of ‘location’
  • and so on: formatted_address, country political,administrative_area_level_1 political, administrative_area_level_2 political, administrative_area_level_3 political, locality political, sublocality political, route, street_number, postal_code, types, location_type, partial_match, point_of_interest, establishment, viewpointne, viewpointsw, airport establishment transit_station, bus_station transit_station, establishment, natural_feature, neighborhood political, postal_town, premise, street_address, subpremise

Location is the name or address of a place or point of interest

=GreatCircleDistance(latitude1, longitude1, latitude2, longitude2)

Calculates the Great Circle Distance using Vincenty’s Formula, with fallback to the Haversine formula when Vincenty’s method doesn’t converge.

=Travel(request, origin, destination, mode)

Calculates the distance or duration to get from origin to destination, according to Google Directions.

Request is the field to return:

  • distance The distance in metres from origin to destination
  • duration The estimated duration from origin to destination

Origin and destination are the names or addresses of the start and finish.

Mode is the mode of transport to use: Driving, Bicycling or Walking

Free download here


Screenshot, click to enlarge:

May 112010

In the process of analysing a client’s existing database, I used Visio’s reverse-engineering tool. It works well, but the resulting diagram was an incomprehensible bowl of spaghetti. Visio does have a “Layout shapes” command, which appears to work by moving shapes with repulsive forces and the result is, not surprisingly, repulsive.

What I wanted was a tool which would unravel the spaghetti, so that I could get a grasp of the relationships, edit and revise them and layout again in an iterative process.
Searching for a solution, I found three layout programs, none of which have a Visio interface:

  1. Microsoft Automatic Graph Layout (MSAGL), formerly known as GLEE The first version, GLEE, is free whereas MSAGL costs between 99 USD and 279 USD depending on where you buy it.
  2. Tom Sawyer Layout is also a graph layout library, the price isn’t disclosed on their website.
  3. Graphviz from AT&T research labs, reputed to have the most sophisticated layout algorithms, is free.

Given that the best quality was to be found in the free library, I made the obvious choice.
It took me over a year and some 11’000 lines of VB to get Visio and Graphviz to co-exist; marrying a Unix-style command-line program with a WYSIWYG interface, both with quirks to numerous to mention, was far more challenging than I initially thought.
The result, unimaginatively called GraphVizio, is available here, I hope you’ll find it useful.

25 May 2011 Version 1.1.5 released. Improvements:

Full support for 64-bit Windows and Visio

No longer makes a Visio document ‘dirty’ when opening

Full support for UTF8. This DOT file:

graph  RootGraph {
  node [width="7.08661417322834", height="0.787401574803148", color="#000000", fillcolor="#FFFFFF", fontname=Calibri, fontsize=24, style=filled, shape=box];
  edge [color="#000000", fillcolor="#FFFFFF"];

  "English: Hello, my name is Maurice\n(and blame Google if the translations are bad)" [pos="283.704566929134,620.932913385827", label="English: Hello, my name is Maurice\n(and blame Google if the translations are bad)"];
  "Russian: Здравствуйте, меня зовут Морис" [pos="283.704566929134,526.932913385827", label="Russian: Здравствуйте, меня зовут Морис"];
  "مرحبا، اسمي موريس : Arabic" [pos="283.704566929134,432.932913385827", label="مرحبا، اسمي موريس : Arabic"];
  "Chinese: 你好,我叫莫里斯" [pos="283.704566929134,338.932913385827", label="Chinese: 你好,我叫莫里斯"];
  "שלום, שמי הוא מוריס : Hebrew" [pos="283.704566929134,244.932913385827", label="שלום, שמי הוא מוריס : Hebrew"];
  "Japanese: こんにちは、私の名前はモーリスです" [pos="283.704566929134,150.932913385826", label="Japanese: こんにちは、私の名前はモーリスです"];
  "Thai: สวัสดีชื่อของฉันคือ Maurice" [pos="283.704566929134,56.9329133858268", label="Thai: สวัสดีชื่อของฉันคือ Maurice"];

  "English: Hello, my name is Maurice\n(and blame Google if the translations are bad)"--"Russian: Здравствуйте, меня зовут Морис";
  "Russian: Здравствуйте, меня зовут Морис"--"مرحبا، اسمي موريس : Arabic";
  "مرحبا، اسمي موريس : Arabic"--"Chinese: 你好,我叫莫里斯";
  "Chinese: 你好,我叫莫里斯"--"שלום, שמי הוא מוריס : Hebrew";
  "שלום, שמי הוא מוריס : Hebrew"--"Japanese: こんにちは、私の名前はモーリスです";
  "Japanese: こんにちは、私の名前はモーリスです"--"Thai: สวัสดีชื่อของฉันคือ Maurice";

produces this Visio diagram:

Graphvizio UTF8

Aug 272009

Problem: You’re using Visual Studio to write a Browser Helper Object for Internet
Explorer and you want to add some images to the web page being displayed.

Here is an example, taken from my Affine addin. The two images I insert are shown by the arrow:

Affine in action

Finding out how to do this is trickier than expected, so here’s the recipe:

  1. Import the images into the project and create a .RC file which identifies them:

    affinehide.bmp bitmap "affinehide.bmp"
    affinefade.bmp bitmap "affinefade.bmp"

    I called this file images.rc in the images directory in my project.

  2. You’ll need RC.EXE, the resource compiler, which is in the Windows SDK. The
    2008 version for .NET 3.5 is here
  3. In your setup project’s, set the prebuild event to

    "D:Affinerc.exe" /r "d:affineimagesimages.rc"

    I copied RC.exe into my project directory because I sometimes work on a 64-bit box, where Program Files becomes Program Files (X86). Adjust the paths to suit your installation.

  4. Open your project’s .VBPROJ file and insert the three red lines shown:

    <?xml version=”1.0″ encoding="utf-8"?>
    <Project DefaultTargets="Build"

    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>

    Thanks to Wouter van Vugt for this!

  5. The HTML to insert the images is straightforward:

    <img src="res://affine.dll/#2/affinehide.bmp"

    where #2 means that the embedded object is an image
    Assuming this string is stored in the variable "buttonhtml" then the code to insert the button on the page is

    a.insertAdjacentHTML(&#34afterEnd&#34, buttonhtml)

  6. Now for the events. You need an event handler for the DHTML event itself:

    Imports mshtml
    Public Delegate Sub DHTMLEvent(ByVal e As IHTMLEventObj)
    Public Class DHTMLEventHandler
    Public Handler As DHTMLEvent
    Private Document As mshtml.IHTMLDocument
    Public Sub New(ByVal doc As mshtml.IHTMLDocument)
    Me.Document = doc
    End Sub
    Public Sub [Call]()
    Handler(CType(Document.parentWindow.event, mshtml.IHTMLEventObj))
    End Sub
    End Class

    Thanks to Rick Strahl

  7. A handler which the above will call, to actually deal with the event:

    Imports mshtml
    Module BrowserEventHandler_
    Public Sub BrowserEventHandler(ByVal e As mshtml.IHTMLEventObj)
    If e.type = "click"” AndAlso e.srcElement.tagName = "IMG" Then

  8. and finally tghe code to add in in your DocumentComplete event:

    Dim Handler As DHTMLEventHandler = New DHTMLEventHandler(doc)
    Handler.Handler = AddressOf BrowserEventHandler
    doc.onclick = Handler

Happy coding!