A very standard requirement is to convert geographical data stored in a spreadsheet into a map. The simplest way to achieve this is to convert the data into a KML file and then load this into Google Earth or a similar mapping/GIS application.

Converting raw co-ordinates into KML isn't a difficult task but it is time-consuming and  its repetitive nature makes it ideal for implementation as a macro. In this article we create a VBA macro that will work with almost any version of Excel. (You can download the spreadsheet and macro from the CodeBin - see the end of the article for details.)

If you want to know more about KML before moving on see: KML in Google Maps and Earth and if you want to know more about using Google Earth try: Getting started with Google Earth.  If you want to know more about VBA macros in general then see: Automating Excel.

The data

The spreadsheet is assumed to contain two columns of latitude and longitude data. In addition the example data, a series of earthquakes (well microtremors to be less alarmist) that occurred in July 2010 in the Alboran Sea, South-East  Spain. There is a third column for  earthquake magnitude. (The spreadsheet is available for download.)

The macro takes each of the co-ordinates. creates a KML placemark at the location and a custom symbol - a triangle in this case. The size of the symbol is set by the magnitude of the earthquake but you can easily customise the macro to provide fixed size symbols or link the size to some other data.

The raw data in this case came from the database maintained by the IGN (Instituto Geográfico Nacional) and column A is the catalog number of the events.

The data

To get started we need to create a new Excel macro.  exactly how you do this varies a lot between different versions of Excel. Macros are also usually turned off for security reasons.

In most versions of Excel the command to get the Visual Basic Editor started is Tools, Macros, Visual Basic Editor.

To get to the Visual Basic Editor in Excel 2010 is more complicated - see  Getting started with Excel VBA.

However once you get the Visual Basic Editor started it looks the same and is used in the same way in all versions of Excel.

The easiest way to create a user interface is to use a UserForm. A UserForm can contains buttons, textboxes and you can use one to create a fairly sophisticated user interface. To add a UserForm to the project use the command Insert,UserForm.

Once you have the UserForm you can use the Toolbox to add a Button, three small textboxes and one large textbox. You can also place labels and other "decoration" to make the form look more professional and easy to use.

The UserForm

The idea of the user interface is that when the program is started the user can set the column letters that the Latitude, Longitude and Magnitude are stored in. When this is done the Generated KML button is clicked the program generates the KML and it is stored in the large textbox on the right. The user can then select it and use copy and paste to transfer it to a mapping program to a text editor to subsequently save as a KML file. It would be quite easy to add a button or a menu option to save the KML in a file - but copy and paste is easy and versatile.

The program

The entire program is contained in the button's click event handler - nothing happens until the user clicks the  Generate KML button. The click handler can be generated automatically by double clicking on the button in the UserForm editor.

The first task is to retrieve the column letters stored in each of the textboxes to discover which columns the data is stored in. We also need to convert the column letters into column numbers to make working with the data stored in the cells easier. The simplest way to convert a character to a numeric value is to use the Asc functon which returns the ASCII code for the letter. To make sure that A is converted to 1 we also have to subtract the ASCII code for A.This gives:

Private Sub CommandButton1_Click()

Dim LatCol As Integer
LatCol = Asc(TextBox1.Text)-Asc("A")+1
Dim LongCol As Integer
LongCol = Asc(TextBox2.Text)-Asc("A")+1
Dim MagCol As Integer
MagCol = Asc(TextBox4.Text)-Asc("A")+1

We can now start generating the KML as it always starts in the same way. To do this we make use of a custom subroutine outputLine which adds the text to the textbox with a carriage return at the end - that is it outputs a line:

Private Sub outputLine(s As String)
 TextBox3 = TextBox3 & s & vbCr
End Sub

The vbCr is a standard constant that contains the character code for carriage return.

Using this subroutine we can output the first three lines of KML:

Call outputLine("<xml version='1.0'
                     encoding='UTF-8'>")
Call outputLine("<kml xmlns
    ='http://earth.google.com/kml/2.2'>")
Call outputLine("<Document>")

<ASIN:0789743647>

<ASIN:1430218290>

<ASIN:0321525590>