VBAでセルの書式設定について

Question

excel2013にてVBAでピボットテーブルの操作コードを書いております。

NunberFormatを使いカンマ及びマイナスは赤字したいのですがエラーが出てしまいます。
エラー内容
実行時エラー'1004':
PivotFieldクラスのNunberFormatプロパティを設定できません。

sheets("明細")に参照データが入っておりsheets("集計結果")にピボットテーブルを作成しております。

最終行から２つのwithで書式設定しております。
1つめはシートを指定
2つ目アクティブシート
を試してみましたがどちらもエラーがでました。

お知恵をお貸しください。

Sub pivot()
'
    Sheets.Add
    ActiveSheet.Name = "集計結果" 'ピボット集計用シート
    Worksheets("明細").Activate  '明細シートアクティブ化

'ピボットテーブル作成
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "明細!R1C1:R116154C20", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="集計結果!R3C1", TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", _
        DefaultVersion:=xlPivotTableVersion15
    Sheets("集計結果").Select
    Cells(3, 1).Select
    
'集計項目設定
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("部所")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("年")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("分類")
        .Orientation = xlColumnField
        .Position = 2
    End With
    
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("月日")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
        "ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("金額"), "合計", xlSum
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
        "ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("金額"), "昨年差", xlSum
    
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("昨年差")
        .Calculation = xlDifferenceFrom
        .BaseField = "年"
        .BaseItem = "(前の値)"
    End With
   
    
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("分類")
        .Orientation = xlColumnField
        .Position = 2
    End With
              
   'With Worksheets("集計結果").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計")
   '     .NumberFormat = "#,##0_ ;[赤]-#,##0 "
   ' End With
          
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("昨年差")
         .NumberFormat = "#,##0_ ;[赤]-#,##0 "
    End With
        
End Sub

Answer 1

実際の動作は確認出来ていませんが……

恐らく以下の記事の内容に関連する話ですね。

NumberFormatLocalとNumberFormatの違い:ExcelVBA Rangeオブジェクト-表示形式

言語に依存しない表示形式を指定すべき所（NumberFormat）に、[赤]など言語に依存した表示形式を設定したためだと思われます。

PivotFieldのプロパティを見ると、NumberFormatはありますがNumberFormatLocalが無いので、#,##0_ ;[赤]-#,##0 を言語に依存しない形式にすれば良いと思います。

試しに以下のプロシージャを実行すると、#,##0_ ;[Red]-#,##0 が出力されたので、NumberFormatにこちらを設定したらどうなるでしょうか。

Sub test()
    With ActiveCell
        .NumberFormatLocal = "#,##0_ ;[赤]-#,##0 " '言語依存の形式で表示形式を設定
        Debug.Print .NumberFormat '言語に依存しない形式で表示形式を取得
    End With 'ActiveCell
End Sub

######蛇足

ちょっと書き方が気になったので自分なりに整理してみたコードです（動作未確認）

Option Explicit

Sub pivot()
    
    Dim wb As Excel.Workbook
    Set wb = ActiveWorkbook
    
    Dim resultSheet As Excel.Worksheet
    Set resultSheet = wb.Worksheets.Add()
    'Sheets.Add
    
    resultSheet.Name = "集計結果" 'ピボット集計用シート
    'ActiveSheet.Name = "集計結果" 'ピボット集計用シート
    
    Dim detailSheet As Excel.Worksheet
    Set detailSheet = wb.Worksheets.Item("明細")
    detailSheet.Activate
    'Worksheets("明細").Activate  '明細シートアクティブ化

'ピボットテーブル作成
    Dim pivotTable1 As Excel.PivotTable
    Set pivotTable1 = wb.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:="明細!R1C1:R116154C20", _
            Version:=xlPivotTableVersion15 _
        ).CreatePivotTable( _
            TableDestination:="集計結果!R3C1", _
            TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", _
            DefaultVersion:=xlPivotTableVersion15 _
        )
    
    Call Application.Goto(resultSheet.Cells.Item(3, 1))
    'Sheets("集計結果").Select
    'Cells(3, 1).Select

'集計項目設定
    Dim pvFields As Excel.PivotFields
    Set pvFields = pivotTable1.PivotFields
    
    '入力補完が機能するように`AsPivotField`で型を設定する
    
    With AsPivotField(pvFields, "部所")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With AsPivotField(pvFields, "年")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    With AsPivotField(pvFields, "分類")
        .Orientation = xlColumnField
        .Position = 2
    End With
    
    With AsPivotField(pvFields, "月日")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    pivotTable1.AddDataField pivotTable1.PivotFields("金額"), "合計", xlSum
    pivotTable1.AddDataField pivotTable1.PivotFields("金額"), "昨年差", xlSum
    
    With AsPivotField(pvFields, "昨年差")
        .Calculation = xlDifferenceFrom
        .BaseField = "年"
        .BaseItem = "(前の値)"
    End With
    
    With AsPivotField(pvFields, "分類")
        .Orientation = xlColumnField
        .Position = 2
    End With
    
    AsPivotField(pvFields, "昨年差").NumberFormat = "#,##0_ ;[Red]-#,##0 "
    
End Sub

Private Function AsPivotField(pvFields As Excel.PivotFields, Index As Variant) As Excel.PivotField
    Set AsPivotField = pvFields.Item(Index)
End Function

Comments

[shinyakita]

ありがとうございます。
コードまで修正して頂き助かります。
まだ、試していませんが、自分のコードとの違いを勉強させて頂きます。
関数を作り変数に代入していますね。
処理が速くなるのでしょうか？

[imihito]

関数については、処理速度自体は遅くなります（体感できるかはともかくとして）
`PivotFields()`の返り値がObjectで入力補完が効かないため、入力補完を効かせるために（VBAが型を認識できるように）関数にしています。
Withブロックの中で「.」を入力したときの挙動を確認するとわかりやすいと思います。

[shinyakita]

ありがとうございます。
関数は逆に遅くなるのですね。
挙動確認しながら勉強させていただきます。

[imihito]

書き方にもよりますが、この関数であれば何万回も実行した時にコンマ1秒変わる、程度の差なので
処理速度よりは、処理を見やすく・書きやすく・間違えにくくするという方面を意識しています

[shinyakita]

間違ったときにすぐにわかる可視化は大切ですよね。
私はまだまだそこまでのレベルにすらいってない初心者ですが、参考にさせてもらい勉強致します。

[shinyakita]

.NumberFormatLocal = "#,##0_ ;[赤]-#,##0
↓ご指摘の通り変更
.NumberFormatLocal = "#,##0_ ;[red]-#,##0

動きました。
ありがとうございます。

[shinyakita]

オブジェクト化してactiveシートの選択を間違えないようにする工夫をしてあるのでしょうか？

[ExcelVBAer]

ちゃんとしたものを作るのであれば、
Active〇〇(Sheet,Book,Cell)は極力使わない(本当に必要な時のみ使用)
とした方がよいですよ～

[shinyakita]

はい。わかりました。
オブジェクトとしてSetしそのプロパティでActiveにするイメージですね。

[imihito]

ExcelVBAer さんにも補足頂いたように、「ちゃんとした処理を作る場合はActive～系基本使わない方が良い」という意図での書き換えになります。言葉足らずですみません
`ActiveSheet`に関してはもう一つ、「入力補完が効くようになる」というメリットもあります

また、今回の場合であれば`detailSheet.Activate`・`Application.Goto`なども無くても多分動くとは思います

[shinyakita]

ありがとうございます。
少し理解できてきたと思います。
まだまだ初心者なので、ぼんやりとですがすごく参考になりました。