9

Recently I was learning how to automate the creation of a pivot table in Excel with VBA, and the implementation of the Excel.Range.Group() method struck me as odd. The fourth parameter, Periods, takes a 7 element array of Boolean values to indicate whether the grouping is by Seconds, Minutes, Hours, Days, Months, Quarters, or Years. Normally, you would see something like this done by using an Enum type whose members can be Or'd together to signify that one or more of the options is turned on - The MsgBox function is a good example of that.

For example, I would have expected the usage to be more like this... 

MyPivotTable.DataRange.Cells(1).Group Periods:=vbGroupPeriods.Days Or _
    vbGroupPeriods.Months

Instead of...

MyPivotTable.DataRange.Cells(1).Group Periods:=Array(False, False, False, _
    True, True, False, False)

I've looked around to try and understand why it's done with an Array, but have thus-far come up empty handed. So, my question is why was it done this way? Is there some limitation that I'm not seeing? Was it somebody's personal choice? Or is it just a mystery? Not trying to complain, just trying to understand.

Community
  • 1
  • 1
Drew Chapin
  • 7,779
  • 5
  • 58
  • 84

2 Answers2

2

Q1. why was it done this way?

Maybe because it gives you more flexibility? For me it is easier to type True 7 times 

Array(True, True, True, True, True, True, True)

rather than saying 

vbGroupPeriods.Seconds Or _
vbGroupPeriods.Minutes Or _
vbGroupPeriods.Hours Or _
vbGroupPeriods.Days Or _
vbGroupPeriods.Months Or _
vbGroupPeriods.Quarters Or _
vbGroupPeriods.Years

Also the sequence in the array is not very difficult to remember...

Seconds-->Minutes-->Hours-->Days-->Months-->Quarters-->Years

Q2. Is there some limitation that I'm not seeing?

No There is no limitation as such.

Q3. Was it somebody's personal choice? Or is it just a mystery?

Nope :) No Mystery. It is never someone's personal choice. There is a team specifically for Excel Development at Microsoft which decides and inculcates a specific feature/functionality.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

Adding to Siddharth Rout's answer, this is where and what I found:

[http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=153:excel-pivot-tables-grouping-group-items-group-data-and-group-date-values-using-vba&catid=79&Itemid=475][1]

Use the Group Method to group numeric items or date values. Syntax: RangeObjectVariable.Group(Start, End, By, Periods). All 4 arguments of Start, End, By & Periods are optional and explained below.

Start - the grouping starts from this value and if omitted or True indicates the first value in the field.

End - the grouping ends at this value and if omitted or True indicates the last value in the field.

By - specifies the group size for a numeric field; for a date field it indicates the number of days in case array element 4 (Days) is set to True and all other elements are False in the Periods argument, else this argument gets ignored and a default size is chosen for the group.

Periods - an array of 7 Boolean values:

  • element 1 indicates the Period of Seconds
  • element 2 is Minutes
  • element 3 is Hours
  • element 4 is Days
  • element 5 is Months
  • element 6 is Quarters
  • element 7 is Years.

An element having a Bollean value of True indicates the period for which the Group is created. This argument of Periods is valid only in respect of Date fields.

It may be noted that the RangeObject should only be a single cell otherwise the method will fail without showing any error.

Sub PivotTableGroupData1()


Dim PvtTbl As PivotTable
Dim rngGroup As Range
Set PvtTbl = Worksheets("Sheet6").PivotTables("PivotTable1")

'set range of dates to be grouped
Set rngGroup = PvtTbl.PivotFields("Dates").DataRange

'rngGroup.Cells(1) indicates the first cell in the range of rngGroup - remember    that the RangeObject in the Group Method should only be a single cell otherwise the method will fail.
rngGroup.Cells(1).Group Periods:=Array(False, False, False, True, True, True, False)

'to ungroup:
'rngGroup.Cells(1).Ungroup


End Sub

I'd like to add that it has taken me months to find the exact definition of the Periods. Thought I would share...

J. Parrish
  • 36
  • 2