Display Data values on the stacked column chart using vba

Question

I have a data table where there are month in the 15th row of the chart and various company names in the "B" column from "B17:B25". The sample table look like the figure below enter image description here

Now I wrote a code which will capture the values and output a stacked column chart like this: enter image description here

This is the code I wrote:

Sub getchart()
y = Format(Now, "ww")

    Dim ws As Worksheet
    Dim aCell As Range, Rng As Range, bCell As Range, sRng As Range, fRng As Range
    Dim col As Long, lRow As Long, srow As Long
    Dim colName As String
    Dim wsTemp As Worksheet
    Dim oChrt As ChartObject
    Dim sheetname As String

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Calculation")
    With ws
        Set aCell = .Range("D16:BC16").Find(What:=y, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        Set bCell = .Range("B:B").Find(What:="Total no of Consultants", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If Not aCell Is Nothing Then
            col = aCell.Column
            colName = Split(.Cells(, col).Address, "$")(1)
        Else
            MsgBox "Nov Not Found"
        End If
        If Not bCell Is Nothing Then
            srow = bCell.Row
            x = srow - 1
        Else
            MsgBox "Nov Not Found"
        End If
     Set sRng = .Range(colName & "17:" & colName & x)
     Debug.Print sRng.Address
     Set fRng = .Range("B" & "17:" & "B" & x)
     Debug.Print fRng.Address
    End With
    '~~> Set the sheet where you have the charts data
     ActiveWorkbook.Worksheets("Calculation").Activate
    '~~> This is your charts range
    Set Rng = Range("D17:G25")

    '~~> Delete the temp sheeet if it is there
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("TempOutput").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    '~~> Add a new temp sheet
    Set wsTemp = ThisWorkbook.Sheets.Add

    With wsTemp
        '~~> Give it a name so that we can delete it as shown above
        '~~> This is just a precaution in case `wsTemp.Delete` fails below
        .Name = "TempOutput"

        '~~~> Add the chart
        Set oChrt = .ChartObjects.Add _
            (Left:=5, Width:=650, Top:=20, Height:=350)

        '~~> Set the chart's source data and type
        '~~> Change as applicable
        With oChrt.Chart
            .SetSourceData Source:=Rng
            .ChartType = xlColumnStacked
            .HasTitle = True
            '.Legend.LegendEntries (fRng)
            .HasLegend = True
            .ChartTitle.Text = "Total # Consultants and Spread"
           ' .SeriesCollection(1).DataLabels.ShowValues = True

       For intSeries = 1 To .SeriesCollection.Count
        .SeriesCollection(intSeries).Name = fRng.Cells(intSeries, 1)
        Next

        End With
    End With



End Sub

Now what I want is I have to Display the data values of each bar in the column of the stacked column chart. SO how to write a vba code to display the data values.

Please help me with this

teylyn teylyn · Accepted Answer · 2015-01-27T10:38:24

You can easily record a macro, select the series and add data labels to the series. That will give you the code you need and you can adjust it to your needs.

The real problem arises when the stacked column data points are too small to actually show the data labels. In a stacked column chart, the data label can only be displayed inside the data point (i.e. the colored rectangle that represents the data).

Before you go about automating the data label creation, take a copy of your file and add the data labels manually. Then take a close look a the user experience: What does the chart look like with data labels on very narrow data points? Looking at your screenshot, if you add data labels to the columns at X position 1, 5 and 6 -- How does that look?

You'll find that the answer is: Not pretty. -- And according to your screenshot, you are not even plotting all the data in the table.

So, you really want to go back to the drawing board and plan for a better data visualization. Because the chart you have right now does not communicate well.

If you want to learn about top notch data visualisation, grab any Stephen Few book. If you want to apply the principles of good data viz in Excel, take a look at Jon Peltier's site, where anything that can be done with Excel charts is explained in detail.

This is not the answer you want to hear, but frankly, I don't see how to add data labels to these tiny data points in the column and make that easy to understand for the reader of the chart.

Display Data values on the stacked column chart using vba

1 Answers