ユーザー設定リストでフィールドのアイテムを並べ替える方法の別案

前回の記事にもちょっと書いたのですが、ピボットテーブルの状態で行エリアとデータエリアを指定しての並べ替え処理が XL2007 では、できなくなってしまいました。

これに関連して、「アイテムの並べ替えについて」で、フィールドのアイテムを並べ替える方法として「Ｄ．ユーザー設定リストを使う。（AddDataList）」を使う方法を紹介していましたが、これも業務で使っているマクロブックに実装していたので、XL2007 でも動作するように処理を見直す必要がありました。

前の記事で紹介していた方法は、

１．レイアウトを組む。
２．任意のリストをユーザー設定リストに登録する。
３．指定した位置を並べ変える。
４．（ユーザー設定リストを削除する。

という手順で処理をさせていました。XL2007 では、３の処理ができなくて別の方法を考えなければいけなくなったのですが、とても単純な方法でできてしまったのです。
それは、

１．任意のリストをユーザー設定リストに登録する。
２．レイアウトを組む。
３．（ユーザー設定リストを削除する。）

という処理で、アイテムがユーザー設定リストの並びと同じになります。しかも、この方法は、今までのバージョンでも使えます。推測するにピボットテーブルでは「レイアウトを組む前にユーザー設定リストに登録しておけば、それを参照してアイテムが並べ替えられて表示される」という仕組みになっているようです。

たまたま、マクロ処理の検証中に、エラーを起こし、再実行でリストがユーザー設定リストに登録されたままピボットテーブルを作成したら、すでにいつもと違う並びになっていたのです。知らなかったのは私だけで、皆さんは、すでにご存知の処理だったかもしれませんね。
早速、マクロブックを修正したのはいうまでもありません。

今までどうしてこの処理に気づかなかったのかな、と考えてみると、XL2003 までは、ピボットテーブルの範囲を指定して「データ＞並べ替え」のユーザー設定リストによる並べ替えができてしまっていた、ということと、マクロブックの配布を始めた頃は、ＯＳが安定していなかったので、ユーザー設定リストに登録したまま処理の途中で止まったら困ると思い、並べ替えの直前で登録し、使い終わったらすぐ削除する、という処理手順にしたことが、今まで気づかなかった原因かな、と思ってます。

こういう思い込みの処理は、まだあるかもしれないなぁ、とマクロブックの検証を進めているところです。

　◇　◇　◇　◇　◇

今回のサンプルマクロは、PvtTblSort4_AddCustomList3 と PvtTblSort4_AddCustomList4 で、ユーザー設定リストに登録しておくことにより、その並びで表示されることを検証したマクロです。PvtTblSortLayout は、二つのマクロから呼び出されてレイアウトを組みます。

PvtTblSort4_AddCustomList3 は、入荷地リストだけユーザー設定リストに登録したものです。PvtTblSort4_AddCustomList4 は、入荷地リストと品名リストをユーザー設定リストに登録しています。レイアウトの組み方は同じですが、品名リストの非登録／登録でアイテムの並びが違うことが確認できると思います。

※データのない方は、こちらのマクロ（Sub PvtTblData1）で作成してください。

　◇　◇　◇　◇　◇

Sub PvtTblSortLayout()
'並べ替え用基本レイアウト

　　Dim PvtSht As Worksheet

　　On Error Resume Next
　　Set PvtSht = Worksheets("PvtSht")
　　On Error GoTo 0
　　If PvtSht Is Nothing Then
　　　　Set PvtSht = Worksheets.Add
　　　　PvtSht.Name = "PvtSht"
　　Else
　　　　With PvtSht.Cells
　　　　　　.Clear
　　　　　　.ColumnWidth = .Parent.StandardWidth
　　　　　　Application.Goto Reference:=.Item(1), Scroll:=True
　　　　End With
　　End If

　　With PvtSht.PivotTableWizard(SourceType:=xlDatabase, _
　　　　　　SourceData:="Database", TableDestination:="PvtSht!R1C1")
　　　　.AddFields RowFields:=Array("入荷地", "品名")
　　　　With .PivotFields("金額")
　　　　　　.Orientation = xlDataField
　　　　　　.Name = "金額計"
　　　　　　.NumberFormat = "#,##0_ "
　　　　End With
　　End With
　　Set PvtSht = Nothing

End Sub

---

Sub PvtTblSort4_AddCustomList3()
'ユーザー設定リスト（AddCustomList）で並べ替え

　　Dim ListData As Variant

'"入荷地"リストを変数に取得
　　ListData = Array("函館", "仙台", "成田", "横浜", "名古屋", "神戸", "門司")

'"入荷地"リストをユーザー設定リストに追加
　　With Application
　　　　If .GetCustomListNum(Listarray:=ListData) = 0 Then
　　　　　　.AddCustomList Listarray:=ListData
　　　　End If
　　End With

'ピボットテーブルの作成
　　Call PvtTblSortLayout

'追加したユーザー設定リストを消去
　　With Application
　　　　.DeleteCustomList ListNum:=.GetCustomListNum(Listarray:=ListData)
　　End With

End Sub

---

Sub PvtTblSort4_AddCustomList4()
'ユーザー設定リスト（AddCustomList）で並べ替え

　　Dim ListData1 As Variant
　　Dim ListData2 As Variant
　　Dim TargetList As Variant

'"入荷地"リストと"品名"リストを変数に取得
　　ListData1 = Array("函館", "仙台", "成田", "横浜", "名古屋", "神戸", "門司")
　　ListData2 = Array("レモン", "バナナ", "パイナップル", "オレンジ", "グレープフルーツ", "キウイ")

'"入荷地"リストと"品名"リストをユーザー設定リストに追加
　　For Each TargetList In Array(ListData1, ListData2)
　　　　With Application
　　　　　　If .GetCustomListNum(Listarray:=TargetList) = 0 Then
　　　　　　　　.AddCustomList Listarray:=TargetList
　　　　　　End If
　　　　End With
　　Next TargetList

'ピボットテーブルの作成
　　Call PvtTblSortLayout

'追加したユーザー設定リストを消去
　　With Application
　　　　.DeleteCustomList ListNum:=.GetCustomListNum(Listarray:=ListData1)
　　　　.DeleteCustomList ListNum:=.GetCustomListNum(Listarray:=ListData2)
　　End With

End Sub