Beyond Excel: VBA and Database Manipulation

Oct 29 2009   4:26PM GMT

Building a Library of Routines – Setup_PivotChart

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In our last post we added a function that creates Pivot Tables.  Pivot Tables are fantastic for summarizing data and automatically adding “drill down” functionality.  But people usually like to see things graphically.  The creators of XL realized this and provide functions for graphing Pivot Table results.  That’s what this next function does.  It’s purpose is to reduce the many Pivot Chart properties and methods down to just one function and as few parameters as possible to create meangingful and dynamic representations of your data that your customers can change simply by changing filters in the drop downs automatically provided.

Copy this to your modGeneral as before.  In my next post I will show you how to “snap” these routines into our Northwind data extract to create impactful graphs and dynamic drill downs. 

Function Setup_PivotChart(sChartSheet As String, sWorksheet As String, _
                          lChartType As XlChartType, sTitle As String) As Boolean
'   Setup_PivotChart:Set up a Pivot Table Chart
'   Parameters:
'       sChartSheet  - The chartsheet to be created to contain the chart
'       sWorkSheet   - The worksheet name where the Pivot Table data is
'       sChartType   - The type of chart to created
'   Example:    Setup_PivotChart "chtHrs", "pvtHrs", "BarClustered"
'     Date   Init Modification
'   01/12/06 CWH  Initial Programming
'   05/15/09 CWH  Changed sChartType to lChartType to add flexibility
    On Error GoTo ErrHandler            '
    Setup_PivotChart = Failure          'Assume the Worst
               
    Settings "Save"
    Settings "Disable"
   
'   Dim Statements
    Dim i As Integer
    Dim n As Integer
   
'   Create Chart
    Worksheets(sWorksheet).Activate
    If Not ChartExists(sChartSheet) Then
        Charts.Add
        Charts(ActiveChart.Name).Name = sChartSheet
    End If
   
    Charts(sChartSheet).Activate
    ActiveChart.SetSourceData Source:=Sheets(sWorksheet). _
        Range(Sheets(sWorksheet).PivotTables(1).RowRange.Address)
    ActiveChart.Location WHERE:=xlLocationAsNewSheet
    
'   Plot Area Formatting
    ActiveChart.PlotArea.Fill.OneColorGradient _
        Style:=msoGradientDiagonalUp, Variant:=2, Degree:=1
    ActiveChart.PlotArea.Fill.ForeColor.SchemeColor = 36
   
    ActiveChart.ChartType = lChartType
    'Chart Types - From http://msdn.microsoft.com/en-us/library/bb241008.aspx
    'Name Value Description
    'xl3DArea -4098 3D Area.
    'xl3DAreaStacked 78 3D Stacked Area.
    'xl3DAreaStacked100 79 100% Stacked Area.
    'xl3DBarClustered 60 3D Clustered Bar.
    'xl3DBarStacked 61 3D Stacked Bar.
    'xl3DBarStacked100 62 3D 100% Stacked Bar.
    'xl3DColumn -4100 3D Column.
    'xl3DColumnClustered 54 3D Clustered Column.
    'xl3DColumnStacked 55 3D Stacked Column.
    'xl3DColumnStacked100 56 3D 100% Stacked Column.
    'xl3DLine -4101 3D Line.
    'xl3DPie -4102 3D Pie.
    'xl3DPieExploded 70 Exploded 3D Pie.
    'xlArea 1 Area
    'xlAreaStacked 76 Stacked Area.
    'xlAreaStacked100 77 100% Stacked Area.
    'xlBarClustered 57 Clustered Bar.
    'xlBarOfPie 71 Bar of Pie.
    'xlBarStacked 58 Stacked Bar.
    'xlBarStacked100 59 100% Stacked Bar.
    'xlBubble 15 Bubble.
    'xlBubble3DEffect 87 Bubble with 3D effects.
    'xlColumnClustered 51 Clustered Column.
    'xlColumnStacked 52 Stacked Column.
    'xlColumnStacked100 53 100% Stacked Column.
    'xlConeBarClustered 102 Clustered Cone Bar.
    'xlConeBarStacked 103 Stacked Cone Bar.
    'xlConeBarStacked100 104 100% Stacked Cone Bar.
    'xlConeCol 105 3D Cone Column.
    'xlConeColClustered 99 Clustered Cone Column.
    'xlConeColStacked 100 Stacked Cone Column.
    'xlConeColStacked100 101 100% Stacked Cone Column.
    'xlCylinderBarClustered 95 Clustered Cylinder Bar.
    'xlCylinderBarStacked 96 Stacked Cylinder Bar.
    'xlCylinderBarStacked100 97 100% Stacked Cylinder Bar.
    'xlCylinderCol 98 3D Cylinder Column.
    'xlCylinderColClustered 92 Clustered Cone Column.
    'xlCylinderColStacked 93 Stacked Cone Column.
    'xlCylinderColStacked100 94 100% Stacked Cylinder Column.
    'xlDoughnut -4120 Doughnut.
    'xlDoughnutExploded 80 Exploded Doughnut.
    'xlLine 4 Line.
    'xlLineMarkers 65 Line with Markers.
    'xlLineMarkersStacked 66 Stacked Line with Markers.
    'xlLineMarkersStacked100 67 100% Stacked Line with Markers.
    'xlLineStacked 63 Stacked Line.
    'xlLineStacked100 64 100% Stacked Line.
    'xlPie 5 Pie.
    'xlPieExploded 69 Exploded Pie.
    'xlPieOfPie 68 Pie of Pie.
    'xlPyramidBarClustered 109 Clustered Pyramid Bar.
    'xlPyramidBarStacked 110 Stacked Pyramid Bar.
    'xlPyramidBarStacked100 111 100% Stacked Pyramid Bar.
    'xlPyramidCol 112 3D Pyramid Column.
    'xlPyramidColClustered 106 Clustered Pyramid Column.
    'xlPyramidColStacked 107 Stacked Pyramid Column.
    'xlPyramidColStacked100 108 100% Stacked Pyramid Column.
    'xlRadar -4151 Radar.
    'xlRadarFilled 82 Filled Radar.
    'xlRadarMarkers 81 Radar with Data Markers.
    'xlStockHLC 88 High-Low-Close.
    'xlStockOHLC 89 Open-High-Low-Close.
    'xlStockVHLC 90 Volume-High-Low-Close.
    'xlStockVOHLC 91 Volume-Open-High-Low-Close.
    'xlSurface 83 3D Surface.
    'xlSurfaceTopView 85 Surface (Top View).
    'xlSurfaceTopViewWireframe 86 Surface (Top View wireframe).
    'xlSurfaceWireframe 84 3D Surface (wireframe).
    'xlXYScatter -4169 Scatter.
    'xlXYScatterLines 74 Scatter with Lines.
    'xlXYScatterLinesNoMarkers 75 Scatter with Lines and No Data Markers.
    'xlXYScatterSmooth 72 Scatter with Smoothed Lines.
    'xlXYScatterSmoothNoMarkers 73 Scatter with Smoothed Lines and No Data Markers.
    
'   Series Formatting - Choose Variant 1 for Columns and Area, 2 for Bars
    For i = 1 To ActiveChart.SeriesCollection.Count
        If lChartType = xlPie Or lChartType = xl3DPie _
           Or lChartType = xl3DPieExploded Then
            On Error Resume Next
            For n = 1 To ActiveChart.SeriesCollection(i).Points.Count
                ActiveChart.SeriesCollection(i).Points(n).Fill.ForeColor. _
                    SchemeColor = _
                    Choose((n Mod 10) + 1, 2, 5, 13, 3, 6, 4, 50, 11, 18, 9)
                ActiveChart.ApplyDataLabels
                ActiveChart.SeriesCollection(i).DataLabels.Font.Bold = True
                ActiveChart.SeriesCollection(i).DataLabels.Font.Color = _
                    RGB(255, 255, 255)
                ActiveChart.SeriesCollection(i).DataLabels.Font.Size = 12
                ActiveChart.SeriesCollection(i).DataLabels.NumberFormat = _
                    "#,###.00_);[Red](#,###.00)"
            Next n
            On Error GoTo ErrHandler
        Else
            ActiveChart.SeriesCollection(i).Fill.ForeColor.SchemeColor = _
                Choose((i Mod 10) + 1, 2, 5, 13, 3, 6, 4, 50, 11, 18, 9)
        End If
    Next i
       
    With Charts(sChartSheet)
        .HasTitle = True
        .ChartTitle.Text = sTitle
    End With
   
    Setup_PivotChart = Success           'Successful finish
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Setup_PivotChart - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    Settings "Restore"
    On Error GoTo 0
End Function

How to Copy Code from this Blog to XL

  1. Open your XL spreadsheet containing modGeneral.
  2. Get to the VBE (Alt-F11)
  3. Open modGeneral in the Code Window
  4. From this post, select and copy the code
  5. Paste into the Code Window (*see next paragraph)
  6. Make any corrections to code that didn’t paste correctly
  7. From the VBE menu navigate File > Export File…
  8. Save modGeneral and remember where you saved it.

Unfortunately, the code won’t paste 100% properly.  You will have to add carriage returns and perhaps fix a few things until your code looks exactly like what you see here.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: