Using Google Maps I covered how to create various web based interactive maps. Of course many would like to use Excel to create maps, and the topic of chorpleth maps, has been covered many times by many others. Indeed, I would like to credit
the clearly and simply blog for the map i've included in the example downloadable in cDatSet.xlsm In One linerTo put a different spin on it, I'm going to show you how to create a one liner to plot data on a thematic map, using cDataSet and Color ramp library. Here's the output The data is just the world population, using a slow color ramp, and a fragment of the input data looks like this. The codeWell .. it's a one liner Public Sub makeWorldMap() Dim ds As New cDataSet ds _ .populateData(wholeSheet("countryList"), , , , , , True) _ .shapeRamp "World Map", "population", "shape", "country name", "S_unknown", , "slowRamp", , False End Sub Walkthrough
Public Function shapeRamp(wNameWhereShapesAre As String, _ Optional dataColumnName As String = "data", _ Optional shapeColumnName As String = "shapes", _ Optional screenTipColumnName As String = vbNullString, _ Optional unknownShapeName As String = vbNullString, _ Optional unusedShapeColor As Long = 7897995, _ Optional rampName As String = "heatmap", _ Optional showValueAsTip As Boolean = True, _ Optional complain As Boolean = True, _ Optional unusedShapeColorApply As Boolean = False) As cDataSet Let's look at that versus the arguments given in our example .shapeRamp "World Map", "population", "shape", "country name", "S_unknown", , "slowRamp", , False ,True
Using other shapesSince the map is just a series of named shapes, it doesnt have to be a map. It can be any shapes that you want to color based on the the data in the cDataSet. In this case I named a few of the shapes that make up this santa picture as below, and applied a standard heatmap color ramp using the values shown. Again it's a one liner Public Sub makeSanta() Dim ds As New cDataSet ds _ .populateData(wholeSheet("santa"), , , , , , True) _ .shapeRamp "santa" End Sub SummaryTake a look at One Liners for more tips like this. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. You can this example in the cDataSet.xlsm from Downloads Thanks again to the clearly and simply blog for the idea. You want to learn Google Apps Script?Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. If you prefer Video style learning I also have two courses available. also published by O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Get Started Snippets > One Liners >