Apr 292016
 

Disclaimer: I have never flown a model aircraft in my life. I unboxed RF7.5 at 23:30 and crashed my first trainer at 23:50.

They were an instructive 20 minutes and I’ll detail them, hopefully you might find it useful.

RF is a trivial install; pop in the DVD and run setup.exe. The only thing you might like to change is the install directory; the RF install is 6.5GB, if you’re tight on space on your C: drive you might like to put it on another disk. Otherwise, just be patient, it takes several minutes but couldn’t be easier.

If you’re in America, skip this paragraph. We Europeans will launch RF and get “Invalid or corrupt version of WMVCORE.DLL”. This is par for the course, ‘N’ versions of Windows don’t have the correct components of Windows Media Player. With a little sleight of hand you’ll find the reason for your OS and from there (for me Windows 7 N or KN editions) get the Media Feature Pack. Once you’ve installed that, re-booted your PC and uttered a spray of profanities, you’re ready to start flying. Double-click RF on the desktop and you should see something like this:

airfield1

This might not seem very impressive. Well, take off and you’ll see this:

ScreenShot1461882329

I played with my first flight simulator on an IBM PC with 2 5.5″ disks back in 1988 and I’ve seen many in-between. Here I was gob-stopped by the simulation; the specular lighting and level of detail is truly astonishing and the aircraft behaviour is incredibly realistic.

Now, a purist 3D-modeller might argue that the perspective of the fence-posts doesn’t change with the viewing angle, nonetheless when you drive the plane gently into the fence, you hear the propeller tapping the wires:

ScreenShot1461883311

The simulation could hardly be more perfect.

I’ll be back with more when I’ve got a bit more experience with what appears to be a very polished piece of software.

Mar 192013
 

Word 2007 recently balked when re-opening a file I had spent quite some time on, with “The file cannot be opened because there are problems with the contents”. Not to worry, I said to myself, the Details>>> button will tell me what’s wrong. I couldn’t have been more wrong, it said “No error detail available”. This is bad programming at its worst. If Word has figured out that the file is unreadable, it must by definition know why it can’t read it; either it can read it or it can’t and it has to know why. If one of my programmers did something like that they’d be enjoying a very long weekend, apparently not the case at Microsoft.

Some Googling later I’d discovered a raft of morons trying either to make me download some virus-riddled software or promising to repair my file for 22$. SavvyCorrupt is a prime example, he has the balls to post on SourceForge, but without publishing the source. TechRepublic has the usual collection of spam links and if you’re really have a soft spot for viruses, you might like to try wordrepairrecovery DOT com, repairmyword DOT com and all the other spammers.

The solution is remarkably simple and little-advertised: Open Office. I installed it and opened my ‘damaged’ docx file with only a slight loss in fidelity; a “Save As .DOC” and I was a happy man.

As our American friends would say, “your mileage may vary”, but it worked perfectly for me.

Aug 132012
 
Imports System.Data
' Refer to these DLLs:
'   microsoft.sqlserver.connectioninfo
'   Microsoft.SqlServer.Management.Sdk.Sfc
'   Microsoft.SqlServer.smo
'   Microsoft.SqlServer.sqlenum
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Common

Module Module1

    Sub Main()
        GetServers()
        Console.ReadLine()
    End Sub

    Private Function GetServers() As List(Of String)

        Dim servers As New List(Of String)
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)

        If dt.Rows.Count > 0 Then

            For Each dr As DataRow In dt.Rows

                Dim servername As String = dr("Name")

                If Not servers.Contains(servername) Then ' Only once per server

                    servers.Add(servername)
                    Console.WriteLine(servername)

                    Dim server As New Server(servername)
                    Try
                        ' Enumerate databases 
                        For Each db In server.Databases

                            If Not db.issystemobject Then
                                Console.WriteLine("  " & db.name)

                                ' Enumerate user tables
                                For Each t As Table In db.tables

                                    If Not t.IsSystemObject Then
                                        Console.WriteLine("    " & t.Name)
                                    End If

                                Next

                            End If

                        Next

                    Catch ex As Exception
                        Console.WriteLine("  " & ex.Message)
                        Console.WriteLine("  Maybe the SQL Server Browser Service isn't started on " & servername & "?")
                    End Try
                End If
            Next
        End If

        Return servers

    End Function


End Module

 Posted by at 10:17 pm  Tagged with:
Jan 132012
 

I’ve wasted hours trying to figure out why my embedded geocoding application doesn’t work with IE9.

This is not the first bad experience I’ve had with IE9, which frankly is one of the worst browser implementations I’ve ever seen.

If you’ve arrived here, you’re probably looking for a solution. Here’s what worked for me:

<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" >

That we have to resort to this kind of shit beggars belief.

 Posted by at 11:42 pm  Tagged with:
Jan 132012
 

This spreadsheet contains the VBA code to write an Excel range as a clean, formatted HTML page, with optional auto-refresh.
NOTE If you click on the ‘This spreadsheet’ link above and it opens a ZIP file or a set of HTML files, right-click on the link and choose Save As.

Features:

  • Correctly renders all Excel formatting, including conditional formats.
  • Renders embedded charts.
  • Generates an HTML page with tabs identical to those in the Excel file.
  • Creates clean, formatted HTML that validates to HTML 4.01 strict.
  • Optimised stylesheet to ensure minimal page weight.
  • Supports merged cells with ROWSPAN and COLSPAN.
  • Updates the webpage automatically when cells’ values are changed.
  • The webpage automatically refreshes itself at a user-supplied interval.

The result is almost pixel-perfect, compare this Excel screenshot with the HTML page it generated.

14 March 2013. Update, improved lower tab bar to stop over-spilling (thanks to Paul Palmer) and added support for 64-bit Office.

21 August 2014. Many people have asked how to insert an image in the HTML page. It is not possible to get the contents of an embedded Excel image, but it is possible to get an embedded chart. Here’s how to perform this trick:

  1. In the sheet to be published, make sure no cells are selected
  2. Excel Menu->Insert->Chart and choose any chart type. This will insert an empty chart.
  3. Move and size the empty chart as required
  4. Select the chart by clicking its border
  5. Excel Menu->Layout and click the Picture icon
  6. Select an image file (JPG, BMP, etc) and Insert

28 September 2014. Now handles Unicode text correctly.

Dec 232011
 

I though this would be trivial; turns out it’s not.

  • When you copy a formula from the clipboard, relative references are adjusted, so Range(x).Formula=Range(y).Formula doesn’t produce the expected results. The correct way is Range(x).Formula=Range(y).FormulaR1C1. Easy once you’ve been there.
  • Copying border formats with Range(x).Borders=Range(y).Borders crashes Excel. You have to copy each individual border, left, right, top, etc.
  • Similarly for fonts, you have to copy the font attributes one by one.
  • Good practice: check that the areas have compatible sizes and don’t overlap.

Nothing inordinately difficult, but time-consuming to get right. Here’s the code:

Option Explicit

' Copy cells without using the clipboard.
'
' Source is a range from which to copy values/formulas/formats.
' Dest is the destination range. Must be either
'     a single cell, the top-left of the target range. The source size (rows x columns) is copied.
'   or
'     a range exactly the same size as source. We throw an error if the shapes don't match.
'
' If 'what' is omitted, copies the values.
' what=CopyFormulas copies the formulas instead of the values.
' what=CopyFormats copies the formats.
' what=CopyFormulas+CopyFormats copies both.
'
' Examples:
'   CopyCells Range("b2:c6"), Range("h10")                                   ' Copies 8 cells (6x2) from B2 to H10
'   CopyCells Range(cells(2,2),cells(6,3)), Range("h10")                     ' Idem
'   CopyCells Range(cells(2,2),cells(6,3)), Range(cells(10,10),cells(14,12)) ' Fails, source is 6x2, dest is 6x3
'   CopyCells Range("b2:c6"), Range("b3")                                    ' Fails, source and dest intersect

Public Const CopyFormulas = 1
Public Const CopyFormats = 2
Public Sub CopyCells(source As Range, dest As Range, Optional what As Long)

    ' Turn off screen updating, wastes (a lot of) time
    Dim updating As Boolean
    updating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    If IsMissing(what) Then
        what = 0
    End If

    Dim r As Long
    Dim c As Long

    ' If destination is not a singe (r,c) top-left cell, ensure that the ranges are the same shape and size
    If dest.Rows.Count > 1 Or dest.Columns.Count > 1 Then

        If dest.Rows.Count <> source.Rows.Count Or _
           dest.Columns.Count <> source.Columns.Count Then

            Err.Raise 1000, "CopyCells", "Destination area " & dest.Rows.Count & "x" & dest.Columns.Count & _
                " is not the same shape as the source area " & source.Rows.Count & "x" & source.Columns.Count

        End If
    End If

    If Not (Intersect(source, dest) Is Nothing) Then
            Err.Raise 1000, "CopyCells", "Source area " & Replace(source.Address, "$", "") & " " & source.Rows.Count & "x" & source.Columns.Count & _
                " intersects destination area " & Replace(dest.Address, "$", "") & " " & dest.Rows.Count & "x" & dest.Columns.Count
    End If

    For r = 1 To source.Rows.Count

        For c = 1 To source.Columns.Count

            If what And CopyFormulas Then
                dest.Cells(r, c).Formula = source.Cells(r, c).FormulaR1C1
            Else
                dest.Cells(r, c).Value = source.Cells(r, c).Value
            End If

            If what And CopyFormats Then

                Dim b As Long
                For b = xlEdgeLeft To xlInsideHorizontal
                    With source.Cells(r, c).Borders(b)
                        dest.Cells(r, c).Borders(b).Weight = .Weight ' You must do this *before* linestyle
                        dest.Cells(r, c).Borders(b).LineStyle = .LineStyle
                        dest.Cells(r, c).Borders(b).ColorIndex = .ColorIndex
                        dest.Cells(r, c).Borders(b).TintAndShade = .TintAndShade
                    End With
                Next
                dest.Cells(r, c).ColumnWidth = source.Cells(r, c).ColumnWidth
                dest.Cells(r, c).Interior.Color = source.Cells(r, c).Interior.Color
                dest.Cells(r, c).Interior.Pattern = source.Cells(r, c).Interior.Pattern
                dest.Cells(r, c).HorizontalAlignment = source.Cells(r, c).HorizontalAlignment
                dest.Cells(r, c).IndentLevel = source.Cells(r, c).IndentLevel
                dest.Cells(r, c).NumberFormat = source.Cells(r, c).NumberFormat
                dest.Cells(r, c).Orientation = source.Cells(r, c).Orientation
                dest.Cells(r, c).RowHeight = source.Cells(r, c).RowHeight
                dest.Cells(r, c).UseStandardHeight = source.Cells(r, c).UseStandardHeight
                dest.Cells(r, c).UseStandardWidth = source.Cells(r, c).UseStandardWidth
                dest.Cells(r, c).VerticalAlignment = source.Cells(r, c).VerticalAlignment
                dest.Cells(r, c).WrapText = source.Cells(r, c).WrapText

                With source.Cells(r, c).Font
                    dest.Cells(r, c).Font.Background = .Background
                    dest.Cells(r, c).Font.Bold = .Bold
                    dest.Cells(r, c).Font.Color = .Color
                    dest.Cells(r, c).Font.ColorIndex = .ColorIndex
                    dest.Cells(r, c).Font.FontStyle = .FontStyle
                    dest.Cells(r, c).Font.Italic = .Italic
                    dest.Cells(r, c).Font.Shadow = .Shadow
                    dest.Cells(r, c).Font.Size = .Size
                    dest.Cells(r, c).Font.Strikethrough = .Strikethrough
                    dest.Cells(r, c).Font.Subscript = .Subscript
                    dest.Cells(r, c).Font.Superscript = .Superscript
                    dest.Cells(r, c).Font.Underline = .Underline
                End With
                On Error GoTo 0
            End If

        Next
    Next

    Application.ScreenUpdating = updating

End Sub

Sub test()
    CopyCells Range("b2:c6"), Range("h10"), CopyFormulas + CopyFormats
End Sub

 

May 102011
 

Pavel in St. Petersburg asked me if it would be possible to produce bubble diagrams like this in Excel:

UK Public Spending

Excel has had bubble diagrams sine 2003 but they are just an X-Y plot with variable-sized nodes. What Pavel was after is an automatic layout, with lines joining the nodes, along these lines:

Bubble diagram with Graphviz

Not perfect, but you get the idea, and it’s produced automatically. To do this, you’ll need Excel, Visio, Graphvizio and this zip file which contains the sample XLS, GV, VSD and JPG files.

  • Open the XLS. Column A is the node’s title, B is the title and the amount separated by a newline. Columns C, D and E specify the node’s parent, colour and amount. Column F computes the diameter of the node, in inches, from the amount:

Column G just creates Graphviz DOT statements from the values. G1 and G2 are the prelude. Copying column G into a text file called bubble1.gv, we get:

graph  RootGraph {
node [fontname=Arial, fontsize=12, style=filled];

"Total\n620" [width="3.1", height="3.1", color="gray", fillcolor="gray", shape=circle];

"Children, schools, family\n63"--"Total\n620" [color="pink"]; "Children, schools, family\n63" [width="0.315", height="0.315", color="pink", fillcolor="pink", shape=circle];

"Schools\n42"--"Children, schools, family\n63" [color="pink"]; "Schools\n42" [width="0.21", height="0.21", color="pink", fillcolor="pink", shape=circle];

...snip...

}

  • Fire up Visio. Graph>Diagram->Import Graphviz
  • To get the circular layout, Graph->Settings->Diagram->Concentric
  • Graph->Layout
  • A little tweaking of the font sizes and line thickness and you’re on your way

Enjoy!

Mar 192011
 

You have to hand it to Google’s engineers, even the best GPS will have a hard job beating this: driving directions from Nordkapp in Norway to Pan Saung in Myanmar (Burma). 14’281 kilometres will take just over a week of continuous driving:

How did I do this in Excel? GeodesiX

 Posted by at 12:28 am  Tagged with:
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

Enjoy!

Screenshot, click to enlarge: