Category Archives: Guest Posts

Points Of Interest


Hello World! (Sorry, I could not resist J) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.

I was inspired the other day by Chandoo’s post on his blog in regards to tax burden as well as Jared’s subsequent submission regarding service levels. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.

In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept







So far, so good – but I would like each “Series” to have a different color. I selected some data points and changed the fill color



Looking good, but I’ll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, “Series” to the chart or decide to go back and change a color – more manual work!

So I thought to myself, “Self, there must be an easier way!” The good news is that there is an easier way through VBA! Let’s cook up some code (Option Explicit VBA – Remember?)

I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.

So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.



Option Explicit
1: Sub ColorDataPoints()
2: Dim wb As Workbook
3: Dim ws As Worksheet
4: Dim i As Integer
5: Dim lBlue As Long
6: Dim lRed As Long
7: Dim lGreen As Long
8: Set wb = ThisWorkbook
9: Set ws = wb.Worksheets("Sheet2")
10: lBlue = RGB(79, 129, 189)
11: lRed = RGB(192, 0, 0)
12: lGreen = RGB(155, 187, 89)
13: With ws
14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count
15: Select Case i
16: Case 1 To 12
17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue
18: Case 14 To 25
19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed
20: Case 27 To 38
21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen
22: End Select
23: Next i
24: End With
25: 'Tidy up
26: Set ws = Nothing
27: Set wb = Nothing
28: End Sub

Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more “Series” or to change colors.

More on the Points Collection.

Download the workbook .

How do you work with points in your charts and VBA? Let us know in the comments section.

Guest Post #1 – Dynamic Funnel Chart

Reader, Bert van Zandbergen, from Beekbergen, The Netherlands (Holland) sent me his own cool creation based on the Rollover technique I created. I asked if I could post his work to this blog to which he graciously agreed.

Below is his write up. Some of the Excel functions below are in Dutch (which I think is really cool). If you’re confused, download the file first – your version of Excel will show the functions in your language of choice. And, of course, if this technique is new to you, read the the tutorial on how to do Excel rollovers

***

Dynamic Funnel Chart

by
Bert van Zandbergen
The Netherlands

Based on this file: 
Figure 1
Start with the 4 columns. The dummy had to be 100 or more for space to unfilled dummy bars. Delete the lines. Now you can change the value from “100” to “1”. Further information is visible in Figure 1.

Hyperlink:
Read also the information and explanation on the website of Chandoo.org
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ 

Figure 2

Place a hyperlinks in the cells of the hotspot – see above. 

Figure 3
Formula: Define the hotspot with the name: “ valSelOption” 
Figure 4
Go to VBA and insert a module. For more information about the instruction – see on this website, the Chandoo website and the module above. 
Figure 5 — Chart with hotspot
The hotspot is based on 10 columns combined with 42 rows. – see figure 5/6. The hotspots are linked by hyperlinks with corresponding cells in columns AI:AR.  For a special effect and an easy crossover the values are placed in a diagonal figure

See: Figure 5/6  

Figure 5 — The “hotspot”

Above the hotspots and the linked cells. Special formed to make an easy crossover.

***
A big thank you to Bert – hopefully there will be more contributions in the future!
Do you have something interesting to share? Send me an email or drop me a line on LinkedIn.