質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

7456閲覧

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

shinyakita

総合スコア39

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/09/24 00:39

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

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

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

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

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

VBA

1Sub pivot() 2' 3 Sheets.Add 4 ActiveSheet.Name = "集計結果" 'ピボット集計用シート 5 Worksheets("明細").Activate '明細シートアクティブ化 6 7'ピボットテーブル作成 8 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 9 "明細!R1C1:R116154C20", Version:=xlPivotTableVersion15).CreatePivotTable _ 10 TableDestination:="集計結果!R3C1", TableName:="ピボットテーブル1", _ 11 DefaultVersion:=xlPivotTableVersion15 12 Sheets("集計結果").Select 13 Cells(3, 1).Select 14 15'集計項目設定 16 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("部所") 17 .Orientation = xlRowField 18 .Position = 1 19 End With 20 21 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("年") 22 .Orientation = xlColumnField 23 .Position = 1 24 End With 25 26 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("分類") 27 .Orientation = xlColumnField 28 .Position = 2 29 End With 30 31 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("月日") 32 .Orientation = xlRowField 33 .Position = 2 34 End With 35 36 ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _ 37 "ピボットテーブル1").PivotFields("金額"), "合計", xlSum 38 ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _ 39 "ピボットテーブル1").PivotFields("金額"), "昨年差", xlSum 40 41 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("昨年差") 42 .Calculation = xlDifferenceFrom 43 .BaseField = "年" 44 .BaseItem = "(前の値)" 45 End With 46 47 48 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("分類") 49 .Orientation = xlColumnField 50 .Position = 2 51 End With 52 53 'With Worksheets("集計結果").PivotTables("ピボットテーブル1").PivotFields("合計") 54 ' .NumberFormat = "#,##0_ ;[赤]-#,##0 " 55 ' End With 56 57 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("昨年差") 58 .NumberFormat = "#,##0_ ;[赤]-#,##0 " 59 End With 60 61End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

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

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

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

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

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

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

vba

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

######蛇足

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

vba

1Option Explicit 2 3Sub pivot() 4 5 Dim wb As Excel.Workbook 6 Set wb = ActiveWorkbook 7 8 Dim resultSheet As Excel.Worksheet 9 Set resultSheet = wb.Worksheets.Add() 10 'Sheets.Add 11 12 resultSheet.Name = "集計結果" 'ピボット集計用シート 13 'ActiveSheet.Name = "集計結果" 'ピボット集計用シート 14 15 Dim detailSheet As Excel.Worksheet 16 Set detailSheet = wb.Worksheets.Item("明細") 17 detailSheet.Activate 18 'Worksheets("明細").Activate '明細シートアクティブ化 19 20'ピボットテーブル作成 21 Dim pivotTable1 As Excel.PivotTable 22 Set pivotTable1 = wb.PivotCaches.Create( _ 23 SourceType:=xlDatabase, _ 24 SourceData:="明細!R1C1:R116154C20", _ 25 Version:=xlPivotTableVersion15 _ 26 ).CreatePivotTable( _ 27 TableDestination:="集計結果!R3C1", _ 28 TableName:="ピボットテーブル1", _ 29 DefaultVersion:=xlPivotTableVersion15 _ 30 ) 31 32 Call Application.Goto(resultSheet.Cells.Item(3, 1)) 33 'Sheets("集計結果").Select 34 'Cells(3, 1).Select 35 36'集計項目設定 37 Dim pvFields As Excel.PivotFields 38 Set pvFields = pivotTable1.PivotFields 39 40 '入力補完が機能するように`AsPivotField`で型を設定する 41 42 With AsPivotField(pvFields, "部所") 43 .Orientation = xlRowField 44 .Position = 1 45 End With 46 47 With AsPivotField(pvFields, "年") 48 .Orientation = xlColumnField 49 .Position = 1 50 End With 51 52 With AsPivotField(pvFields, "分類") 53 .Orientation = xlColumnField 54 .Position = 2 55 End With 56 57 With AsPivotField(pvFields, "月日") 58 .Orientation = xlRowField 59 .Position = 2 60 End With 61 62 pivotTable1.AddDataField pivotTable1.PivotFields("金額"), "合計", xlSum 63 pivotTable1.AddDataField pivotTable1.PivotFields("金額"), "昨年差", xlSum 64 65 With AsPivotField(pvFields, "昨年差") 66 .Calculation = xlDifferenceFrom 67 .BaseField = "年" 68 .BaseItem = "(前の値)" 69 End With 70 71 With AsPivotField(pvFields, "分類") 72 .Orientation = xlColumnField 73 .Position = 2 74 End With 75 76 AsPivotField(pvFields, "昨年差").NumberFormat = "#,##0_ ;[Red]-#,##0 " 77 78End Sub 79 80Private Function AsPivotField(pvFields As Excel.PivotFields, Index As Variant) As Excel.PivotField 81 Set AsPivotField = pvFields.Item(Index) 82End Function

投稿2018/09/24 11:29

imihito

総合スコア2166

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shinyakita

2018/09/24 12:14

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

2018/09/24 12:27

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

2018/09/24 12:40

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

2018/09/24 13:01

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

2018/09/25 01:19

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

2018/09/25 01:54

.NumberFormatLocal = "#,##0_ ;[赤]-#,##0 ↓ご指摘の通り変更 .NumberFormatLocal = "#,##0_ ;[red]-#,##0 動きました。 ありがとうございます。
shinyakita

2018/09/25 02:05

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

2018/09/25 02:13

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

2018/09/25 06:01

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

2018/09/25 13:30

ExcelVBAer さんにも補足頂いたように、「ちゃんとした処理を作る場合はActive~系基本使わない方が良い」という意図での書き換えになります。言葉足らずですみません `ActiveSheet`に関してはもう一つ、「入力補完が効くようになる」というメリットもあります また、今回の場合であれば`detailSheet.Activate`・`Application.Goto`なども無くても多分動くとは思います
shinyakita

2018/09/26 02:05

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問