Charts and color ramps

What can you learn here?

  • ramp color scales
  • using in surface charts
  • brighten/darken

Creating Charts with colorRamps

Create a heatmap in Excel showed how to make charts using the heatmap color ramp. Now that we have a generalized Color ramp library it is straightforward to extend the charting to do this too.
 
As usual this code is implemented in the cDataSet.xlsm module downloadable from Download Complete Projects
 
You can also use a color ramp to create shape overlays for Google Maps; see Overlaying circles and heatmaps, or here for simple shapes or maps

 

Creating charts programmatically

Setting up charts manually doesn’t easily allow for the creation of a smooth transition between colors, but using automation along with a variable color ramp makes the whole thing very simple.
 
Consider this heatmap plot of a function, SIN(SQRT(x*y)/3)

It is simply a 3d surface chart, with 200 categories to give it a smooth transition between colors.
It can be created simply using the Data Manipulation Classes and Color ramp library as follows
 
Public Sub colorRampVisualize()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet(“crampviz”), , “crampviz”, True, , , True, “y/x”), _
        “crampChart”, xlSurfaceTopView, 200, “heatmaptowhite”, False
End Sub
 
We can use a different library entry, in the case a ‘terrain’ ramp, and get this
with this code
 
Public Sub colorRampTerrain()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet(“crampviz”), , “crampviz”, True, , , True, “y/x”), _
        “terrainchart”, xlSurfaceTopView, 200, “terrain”, False, 1.3
End Sub
 
Note that I use a ‘brighten’ ratio of 1.3 in this case. I thought that the terrainchart library entry was a bit dark, so you can keep the same ramp but apply a factor to the brightness. The default value is 1meaning – use the library colors as is. You can find the code for the colorramp library in cDataSet download or explained in Color ramp library.
 
See How to create a heatmap doughnut chart for another application of heatmaps to charts.
 

Creating topographic charts.

I you had a table of longitudes and latitudes against elevation, you could even produce a terrain map. Here’s an example using randomly generated data.
And here is the code
 
Public Sub colorRampElevation()
    Dim dsout As New cDataSet, ds As New cDataSet
    ‘ the formula data contains rand() function so it will recalculate-
    Application.Calculate
    ‘ copy the values elsewhere so it doesnt happen again and change the finished chart
    ds.populateData(wholeSheet(“elevate”), , , , , , True).bigCommit _
            whereIsThis(“straightcopy!a1”), True
    ‘base on the copied data
    createSurfaceChart _
        dsout.populateData(wholeSheet(“straightcopy”), , “elevate”, True, , , True, “lat/lon”), _
        “top”, , 400, “terrainnosea”, False, , False
        
End Sub

Create chart code

This is a ‘souped up’ version of the one already covered in Create a heatmap in Excel
 
Option Explicit
Public Function createSurfaceChart(ds As cDataSet, n As String, _
                    Optional xlt As Long = xlSurface, _
                    Optional targetLegends As Long = 0, _
                    Optional colorRampName As String = “heatmap”, _
                    Optional showLegend As Boolean = True, _
                    Optional brighten As Double = 1, _
                    Optional showAxis As Boolean = True) As Chart
    Dim cht As Chart, s As Series, dr As cDataRow, dc As cCell, sc As Double, x As Axis
    Dim i As Long, red As Long, blue As Long, green As Long, m As Long
    Dim ax As Axis
 
    ‘ clean up previous attempt
    deleteChart (n)
    If targetLegends > 0 Then
        sc = (ds.max) / targetLegends
    End If
    
    ‘add the chart
    Set cht = Charts.add
    With cht
        .name = n
        For i = .SeriesCollection.Count To 1 Step -1
            .SeriesCollection(i).Delete
        Next i
        Set x = .Axes(xlValue)
        If sc > 0 Then
            x.MajorUnit = sc
        End If
        x.HasMajorGridlines = False
        For i = ds.rows.Count To 1 Step -1
            Set dr = ds.rows(i)
            With .SeriesCollection.NewSeries
                .name = dr.cell(1).Where
                .XValues = ds.headingRow.Where.Offset(, 1).Resize(, dr.columns.Count – 1)
                .values = dr.Where.Offset(, 1).Resize(, dr.Where.columns.Count – 1)
            End With
        Next i
        .ChartType = xlt
               
        If Not showAxis Then
            For i = .Axes.Count To 1 Step -1
                .Axes(i).Delete
            Next i
            .Floor.ClearFormats
        End If
        With .Legend
            ‘ for some reason legendcount is not ready immediately
            Application.Wait DateAdd(“s”, 1, Now)
            For i = 1 To .LegendEntries.Count
                .LegendEntries(i).LegendKey.Interior.color = _
                    rampLibraryRGB(colorRampName, 1, .LegendEntries.Count, i, brighten)
                
            Next i
        End With
        .HasLegend = showLegend
 
    End With
    
    Set createSurfaceChart = cht
End Function
Private Function deleteChart(n As String)
    Dim cht As Chart, i As Long
 
    For i = Charts.Count To 1 Step -1
        Set cht = Charts(i)
        If Trim(LCase(cht.name)) = Trim(LCase(n)) Then
            cht.Delete
            Exit For
        End If
    Next i
 
End Function
 

Summary

For more tips like this, take a look at Get Started Snippets 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 also submit anything you want considered for publication, including any nice color ramps you come up, with on this site to our forum.  
 
 
 

Related pages