Beyond Excel: VBA and Database Manipulation

Oct 31 2009   10:05AM GMT

Wrapping things up

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Today I’m introducing a “wrapper” for our Pivot Table and Pivot Chart routines.  The wrapper isolates setting unique parameters from the main routine that extracts data.   Though the routine looks lengthy, it contains no logic, only parameters and instructions on how to modify it .  The instructions can be removed and not all of the parameters are required.

In this example, we create a PivotTable and chart results for the top 20 selling products with quantities broken out by state as shown in


Function Pivot_Template() As Boolean
'   Pivot_Example:  Sample Pivot Table and Pivot Chart wrapper
'   Parameters:     None 
'   Instructions:   Copy this,
'                   Change all instances of "Pivot_Template" to your routine's name
'                   Modify constants
'                   Increase dimensions of arrays if needed to accomodate more than
'                       1 row, column, etc
'                   Set variable values
'                   Delete these instructions from your routine 
'   Example:        Pivot_Template
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming    
    On Error GoTo ErrHandler                  
    Pivot_Template = Failure                  'Assume the Worst
'   NOTE TO PGMR: Modify these constants' values
    Const sWorksheet = "pvtTemplate"          'Name for the PivotTable & Worksheet
    Const sDataRange = "Data"                 'Named range containing raw data
    Const sTitle = "Top 20 Products by State" 'PivotTable's title
    Const sChartType = xlColumnStacked        'Chart type to create (Optional)
'   NOTE TO PGMR: End modification to constants' values       
'   NOTE TO PGMR: Modify array dimensions (usually not required)
'                 0 = the first element so a dimension of 1 means 2 elements
'                 Changes are required ONLY if you want MORE than 1 element
    Dim sPageFields(0) As String              '0=# of Page Fields   (Optional)
    Dim sRowFields(0) As String               '0=# of Row Fields    (Required)
    Dim sColumnFields(0) As String            '0=# of Column Fields (Recommended)
    Dim sDataFields(0, 2) As String           '0=# of Data Field    (Required)
    Dim sMaxFields(0, 2) As String            '0=# of Restrictions  (Optional)
    Dim sSortFields(0, 2) As String           '0=# of Sort fields   (Optional)
'   NOTE TO PGMR: End modifications to array dimensions 
'   NOTE TO PGMR: Set parameter values. Set to "" for optional parameters you
'                 don't want or delete the parameter line from this routine
    sPageFields(0) = "Customer ID"            'Allow filtering entire pivot on this
    sRowFields(0) = "Product Name"            'This field goes down the side
    sColumnFields(0) = "Ship State/Province"  'This field goes across the top
    sDataFields(0, 0) = "Quantity"            'This field goes in the body
    sDataFields(0, 1) = "SUM"                     'Calculation performed
    sDataFields(0, 2) = "SUM Quantity"            'Name for the calculated result
    sMaxFields(0, 0) = "Product Name"         'This field is restricted
    sMaxFields(0, 1) = 20                         'To the top n values
    sMaxFields(0, 2) = "SUM Quantity"             'based on this field's value
    sSortFields(0, 0) = "Product Name"        'This field is sorted
    sSortFields(0, 1) = "Descending"              'in Ascending/Descending order
    sSortFields(0, 2) = "SUM Quantity"            'based on this field's value
'   NOTE TO PGMR: End modification to parameter values 
'   Create the Pivot Table
    Setup_Pivot sWorksheet, sDataRange, sTitle, _
                sPageFields(), sRowFields(), sColumnFields(), sDataFields(), _
                sSortFields(), sMaxFields()
'   Create a chart based on the pivot table (Optional)
    Setup_PivotChart Replace(sWorksheet, "pvt", "cht", 1, 1), _
                             sWorksheet, sChartType, sTitle
    Pivot_Template = Success                  'Successful finish
        If Err.Number <> 0 Then MsgBox _
            "Pivot_Template - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
        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.

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:

Share this item with your network: