■PivotItems().Visible = True エラー

#久々のテーマ投稿のような気が :)

PivotTablesをVBAで操作する時の話。
『RowFieldsやColumnFieldsにAutoSort（自動並べ替えオプション）が手動以外に設定されていると、
　そのPivotItems().Visible = True（アイテムを表示する）時に失敗する』
..という不具合があります。Excel97-2003の場合。2007,2010では発生しません。

対策としては「自動並べ替えオプション」を「手動」にする事で回避できます。

そう。こちらの話。
『マクロでピボットのPivotItemsのVisible = Trueができない』
http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+201209/12090023.txt

今にして思えば
『【58912】「PivotItemクラスのVisibleプロパティを設定できません。」の解決法』
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=58912;id=excel
これも同様のケースだったんですね。
#当時は知りませんでした。力不足で申し訳ない..orz



さて、検証用コード。
97-2010で共通動作するようにPivotTableWizardメソッドを使ってみた。

Option Explicit
'新規Workbookに簡易テストデータをセットしPivotTable作成。
'RowFields("F1")の自動並べ替えを昇順に設定。
Sub pre()
　　With Workbooks.Add(xlWBATWorksheet).Sheets(1)
　　　　.Range("A1:B2").Value = [{"F1","F2";"item01",1}]
　　　　.Range("A2:B2").AutoFill .Range("A2:B10"), xlFillSeries
　　　　With .PivotTableWizard(xlDatabase, "'" & .Name & "'!A1:B10", "")
　　　　　　.PivotFields("F1").Orientation = xlRowField
　　　　　　.PivotFields("F2").Orientation = xlDataField
　　　　　　.RowFields("F1").AutoSort xlAscending, ""
　　　　End With
　　End With
End Sub
'-------------------------------------------------
'自動並べ替え昇順のままPivotItemsをLoopしてVisible = True
Sub test1()
　　Dim p As PivotItem
　　With ActiveSheet.PivotTables(1).RowFields("F1")
　　　　For Each p In .PivotItems
　　　　　　p.Visible = True
　　　　Next
　　End With
End Sub

エラー
『1004::PivotItem クラスの Visible プロパティを設定できません。』

'AutoSortメソッドで手動にするとエラーは出ません。
Sub test2()
　　Dim p As PivotItem
　　With ActiveSheet.PivotTables(1).RowFields("F1")
　　　　.AutoSort xlManual, ""　　'●
　　　　For Each p In .PivotItems
　　　　　　p.Visible = True
　　　　Next
　　End With
End Sub
'-------------------------------------------------
'念の為。
'既表示Itemを表示しようとするからエラーになっているわけではありません。
Sub test3()
　　Dim i As Long
　　With ActiveSheet.PivotTables(1).RowFields("F1")
　　　　.AutoSort xlManual, ""
　　　　For i = 2 To .PivotItems.Count
　　　　　　.PivotItems(i).Visible = False
　　　　Next
　　　　Stop
　　　　For i = 2 To .PivotItems.Count
　　　　　　.PivotItems(i).Visible = True
　　　　Next
　　　　Stop
　　　　.AutoSort xlAscending, ""
　　　　For i = 2 To .PivotItems.Count
　　　　　　.PivotItems(i).Visible = False
　　　　Next
　　　　Stop
　　　　For i = 2 To .PivotItems.Count
　　　　　　.PivotItems(i).Visible = True
　　　　Next
　　End With
End Sub

■xl2007：オートシェイプの文字が更新されない。

そんなに「気をつけたほうがいいこと」でもないんですが、
オートシェイプの文字が更新されない。 - 教えて！goo
この件の追加検証報告。


現象としては、
『Excel.Textbox（シェイプのTextbox）のTextがマクロ実行しても表示上更新されない事がある』
..というものです。

再現コード再掲。

Sub test()
　　With Workbooks.Add(xlWBATWorksheet).Sheets(1).TextBoxes.Add(100, 100, 100, 20)
　　　　.Text = "aaa"

　　　　Stop

　　　　.Text = "bbb"
　　　　Debug.Print .Text
　　　　'.Visible = False
　　　　'.Visible = True
　　End With
End Sub

test実行直後。Textが更新されていません。


選択して編集状態にすると更新されます。

リンク先スレッドでも書きましたが、メイン環境の[winXPsp3/xl2007sp2]では再現せず、
サブ環境の[winVISTAsp2/xl2007sp2]で確認できました。
メイン環境のMicrosoftUpdateをしばらくサボっていたので差異が出てます。

メインのXP環境　：xl2007sp2.12.0.6557.5000　発生しない。
サブのVISTA環境 ：xl2007sp2.12.0.6565.5003　発生。

今日、メイン環境をUpdateさせてkb１コずつ確認してみました。
『Microsoft Office Excel 2007 セキュリティ更新プログラム: KB2553073（2011.9.13）』
でバージョン.ビルドは12.0.6565.5003になるのですが、この現象は確認できません。
『2007 Microsoft Office system セキュリティ更新プログラム: KB2553089（2011.9.13）』
の適用後に発生するようになります。

　　[MS11-072] 2007 Office system (Oart.dll) セキュリティ更新プログラム (2011 年 9 月 13 日) について
　　http://support.microsoft.com/kb/2553089/ja


しばらく前に
『Office ファイル検証機能のアドイン (KB2501584) 適用後、ネットワーク共有上の Excel ファイルを開くのに時間がかかる』..
というのがありましたが、それに比べればまぁ..かわいいものなのかもしれません？ :P
..にしても、細かなビルドの違いによって挙動が変わるってのは勘弁してほしいですよね。
一応、Visibleプロパティを弄る事で対処できますが、余計なコード対応増えるのは...ねぇ...

■xl2007：条件付き書式コピー時の適用先分断

エクセルのセルをコピーした際にルールはコピーしない - 教えて！goo

ん...？
用意してたレスの書き出しがこれ。
『適用先を$D:$Dのように列全体で設定してもコピー後に適用先が分断されるのはどうも仕様のようですね。
#バグっぽい動きですが。』

2007では発生しない..との事だが私の環境では発生してしまう。

Sub test1()
　　With Sheets.Add
　　　　.Columns("A").FormatConditions.Add(xlCellValue, xlEqual, 1) _
　　　　　　　　　　　　　　　　　　　.Interior.Color = vbRed
　　　　.Range("A1").Value = 1
　　　　.Range("A1").Copy .Range("A3")
　　End With
End Sub



（環境）
OS：WindowsXP pro sp3
EXCEL：2003 11.8326.8324 sp3
(共存) 97 sr1
　　　 2000 9.0.8968 sp3
　　　 2007 12.0.6545.5000 sp2
　　　 2010 14.0.4760.1000(32ビット)

...？
しばらく悩んだ。
vista/2007のサブ環境で試してみた。
ぉぉ...発生しない...orz

2010の共存インストールが影響するのか？..とも考えてみたのだがexcel本体の機能なのでそんな事はないはず。
セーフモードで同じコードを試してみると確かに発生しない。



ここでフっと思い当たる。
『コピーして Excel 2007 で [セルを貼り付けると、条件付き書式ルールが重複してください。』
http://support.microsoft.com/kb/973823/ja
これだ♪
コピーの度に同じ条件付き書式がダブって増えていくタチ悪いバグ。
以前、回答した時にレジストリを弄くってた。
なぁんだ。ReplaceCFOnPasteキーを設定しなけりゃいいのね。
納得。



.
..
ぃゃ、まて...
ReplaceCFOnPasteを設定しないと

Sub test2()
　　With Sheets.Add.Range("A1")
　　　　.FormatConditions.Add(xlCellValue, xlEqual, 1) _
　　　　　　　　　　　　 .Interior.Color = vbRed
　　　　.Value = 1
　　　　.Copy .Range("A3")
　　　　.Copy .Range("A3")
　　　　.Copy .Range("A3")
　　End With
End Sub

再発。
...どっちか選べってことなの？......orz

■xl2007：ModifyAppliesToRangeメソッドには要注意

...というか私が抜けてるだけ？懺悔シリーズ２。

■xl2007：ModifyAppliesToRangeメソッド
re2:xl2007：ModifyAppliesToRange でTypeが変わる
ここで暢気にFor Each NextステートメントでModifyAppliesToRangeやっちゃってますが、これ大変危険です。
適用先AppliesToプロパティが同一範囲のFormatConditionに対してModifyAppliesToRangeメソッドを続けて実行すると、Excel2007が落ちます。

最小限の再現コードはこれ。

Sub test() '私の環境では確実に2007が落ちます。安易に実行しない事。
　　Dim f As FormatCondition

　　On Error Resume Next
　　With Range("A1")
　　　　.FormatConditions.Add( _
　　　　　　　　　　　　　Type:=xlExpression, Formula1:="=RC>2" _
　　　　　　　　　　　　　).Interior.ColorIndex = 35
　　　　.FormatConditions.Add( _
　　　　　　　　　　　　　Type:=xlExpression, Formula1:="=RC>1" _
　　　　　　　　　　　　　).Interior.ColorIndex = 36
　　　　For Each f In .FormatConditions
　　　　　　f.ModifyAppliesToRange Range("B1")
　　　　Next
　　End With
End Sub

普通、こんな事はしませんが、For EachでDeleteしてみます。

Sub test2()
　　Dim f As FormatCondition

　　With Range("A1")
　　　　.FormatConditions.Add( _
　　　　　　　　　　　　　Type:=xlExpression, Formula1:="=RC>2" _
　　　　　　　　　　　　　).Interior.ColorIndex = 35
　　　　.FormatConditions.Add( _
　　　　　　　　　　　　　Type:=xlExpression, Formula1:="=RC>1" _
　　　　　　　　　　　　　).Interior.ColorIndex = 36
　　　　For Each f In .FormatConditions
　　　　　　f.Delete
　　　　Next
　　End With
End Sub

『9：インデックスが有効範囲にありません。』エラーです。
考えてみりゃ、前から削除とかMoveとかしちゃダメよねorz

...
......ん？前に詰まるの？

Sub test3()
　　Dim f As FormatCondition

　　Range("A1").FormatConditions.Add( _
　　　　　　　　Type:=xlExpression, Formula1:="=RC>2" _
　　　　　　　　).Interior.ColorIndex = 35
　　Range("A1:A2").FormatConditions.Add( _
　　　　　　　　　 Type:=xlExpression, Formula1:="=RC>1" _
　　　　　　　　　 ).Interior.ColorIndex = 36
　　For Each f In Range("A1").FormatConditions
　　　　f.ModifyAppliesToRange Range("B1")
　　Next
End Sub

これは落ちないんだけどなぁ。

今いち不可解ですが...まっとうに生きたいなら

Sub test4()
　　Dim i As Long

　　With Range("A1").FormatConditions
　　　　　　　　　　.Add(Type:=xlExpression, Formula1:="=RC>2" _
　　　　　　　　　　 ).Interior.ColorIndex = 35
　　　　　　　　　　.Add(Type:=xlExpression, Formula1:="=RC>1" _
　　　　　　　　　　 ).Interior.ColorIndex = 36
　　　　For i = .Count To 1 Step -1
　　　　　　.Item(i).ModifyAppliesToRange Range("B1")
　　　　Next
　　End With
End Sub

これ？

...それにしたって、落ちなくてもいいじゃない...ねェ...orz

環境：[Windows]XP pro 5.1.2600 SP3　[EXCEL]2007 12.0.6535.5002 SP2

■AddCustomListメソッドの重複エラー

AddCustomListメソッド...ユーザー設定リストを追加する時、追加済みのリストを重複登録しようとしたらエラーになるのが仕様と思っていたんですが、違うらしい。

ヘルプには
>expression.AddCustomList(ListArray, ByRow)
>ListArray 必ず指定します。バリアント型 (Variant) の値を使用します。文字列の配列または Range オブジェクトを指定します。
とあります。

この引数ListArrayにRangeオブジェクトを指定した時だけエラーになります。
文字列の配列を指定すると、ヘルプどおり
>追加済みのリストを指定したときは、このメソッドは無効になります。

『XL2000: ファイル名を指定して実行時エラーがマクロを使用してユーザー設定リストを追加するには』
http://support.microsoft.com/kb/211811/ja
...バグだったようです。
ちなみにExcel2007でも直っていません。

（テストコード）
シートを追加しA1:A3セルに文字列セット。ユーザー設定リストを追加します。
その後、
・Rangeオブジェクトを指定した場合
・Range().Valueプロパティで指定した場合
・配列で指定した場合
を比較します。
最後に、追加したユーザー設定リストを削除しています。

Sub test()
　　Dim ws As Worksheet
　　Dim v

　　Set ws = Sheets.Add
　　ws.Range("A1:A3").Value = [{"aaa";"bbb";"ccc"}]
　　On Error Resume Next
　　With Application

　　　　'【1】まずユーザー設定リスト追加
　　　　.AddCustomList ListArray:=ws.Range("A1:A3")
　　　　MsgBox "【1】ユーザー設定リスト追加。ListCountは " & .CustomListCount

　　　　'【2】Range("A1:A3")で登録
　　　　.AddCustomList ListArray:=ws.Range("A1:A3")
　　　　MsgBox "【2】Range(""A1:A3"")で重複登録するとエラー " _
　　　　　　　　& vbLf & Err().Number & ":" & Err().Description _
　　　　　　　　& vbLf & "ListCountは " & .CustomListCount

　　　　Err().Clear

　　　　'【3】Range("A1:A3").Valueで登録
　　　　.AddCustomList ListArray:=ws.Range("A1:A3").Value
　　　　MsgBox "【3】Range(""A1:A3"").Valueでは " _
　　　　　　　　& vbLf & "エラー " & Err().Number & ":" & Err().Description _
　　　　　　　　& vbLf & "ListCountは " & .CustomListCount
　　　　v = ws.Range("A1:A3").Value

　　　　Err().Clear

　　　　'【4】配列
　　　　.AddCustomList ListArray:=v
　　　　MsgBox "【4】配列に受けてもよい " _
　　　　　　　　& vbLf & "エラー " & Err().Number & ":" & Err().Description _
　　　　　　　　& vbLf & "ListCountは " & .CustomListCount
　　　　.DeleteCustomList (.GetCustomListNum(v))
　　End With

　　Set ws = Nothing
End Sub

（結果）


ついでですが、ユーザー設定リストを使って並べ替えを行う時のバグ
『[XL2000]GetCustomListNum メソッドで間違ったリスト番号が返る』
http://support.microsoft.com/kb/134913/ja
これも、Excel2007でも直っていません。
#2007ではSortオブジェクトが追加され、仕様が変わっています。
#下位互換のSortメソッドでは、あえてバグを引きずったままのほうが良いのかもしれませんね。

（テストコード）

Sub Custom_SortTest()
　　Dim iListIndex As Long
　　Dim vSort

　　vSort = Array("b", "c", "a")
　　Application.AddCustomList ListArray:=vSort
　　iListIndex = Application.GetCustomListNum(vSort)
　　With Sheets.Add.Range("A1:A3")
　　　　.Value = [{"c";"b";"a"}]

　　　　'【1】ListNumberでSort
　　　　.Sort Key1:=.Cells, _
　　　　　　　Header:=xlNo, _
　　　　　　　OrderCustom:=iListIndex
　　　　MsgBox "【1】ListNumberでSort" _
　　　　　　　　& vbLf & Join(Application.Transpose(.Value))

　　　　'【2】ListNumber+1でSort
　　　　.Sort Key1:=.Cells, _
　　　　　　　Header:=xlNo, _
　　　　　　　OrderCustom:=iListIndex + 1
　　　　MsgBox "【2】ListNumber+1でSort" _
　　　　　　　　& vbLf & Join(Application.Transpose(.Value))
　　End With
　　Application.DeleteCustomList (iListIndex)
End Sub

（結果）