「Excel VBAの基本はマスターしたけど、グラフやピボットテーブルなど、Excelの多彩な機能を自動化するにはどのようにコードを書けばいいのかな?」
Excelの多彩な機能を自動化したいと考えている人に向けて、実例を使ってコードの書き方を調べる手順を解説します。
Excelのメインとなる機能は表を作ることですが、他にも多彩な機能を持つ万能なアプリです。
グラフ・ピボットテーブル・オートシェイプなど以前からあるものや、テーブル・スパークライン・モダンExcelなど、挙げてみるときりがありません。
また、日頃の業務の効率を上げるためにExcelをマクロで自動化する人も少なくありません。
Excelマクロを学習できる書籍や講座もたくさん出回っていますが、プログラミングの基本とセルや関数の操作について解説したものがほとんどだと言えるでしょう。
本記事では、ピボットテーブルを作成するためのVBAコードを完成させるという目標に向け、実際に手を動かした手順を公開します。
この手順はとても再現性が高く、Excelのほとんどの機能にも応用可能です。
本記事の内容を日々のマクロ作成に活用していただけるとうれしいです。
まずはマクロ記録機能でコードを作る
下の画像はピボットテーブルを作るときに使った売上入力用のテーブルです。
![データ原本](https://sys-daddy.com/wp-content/uploads/2023/04/macro_record_01.jpg)
テーブルを原本に、マクロ記録でピボットテーブルを作ります。
行見出しに「品名」、値に「金額」を設定しました。
![マクロ記録でピボットテーブル作成](https://sys-daddy.com/wp-content/uploads/2023/04/macro_record_02.jpg)
下の画像はマクロ記録で作成されたコードです。
![マクロ記録で自動作成されたコード](https://sys-daddy.com/wp-content/uploads/2023/04/macro_record_03.jpg)
冗長でムダが多いコードになっていますが、この中からピボットテーブルを自動作成するために必要なキーワードを調べていきます。
マイクロソフト公式リファレンスを使う
コードを上から眺めるとPivotCaches.Createというワードが使えそうなので、さっそくどんな動きなのか検索します。
![メソッドの動きを調べる](https://sys-daddy.com/wp-content/uploads/2023/04/reference_01.jpg)
ヘルプメニューをクリックすると、マイクロソフト公式リファレンスが開きます。
![ヘルプメニューをクリック](https://sys-daddy.com/wp-content/uploads/2023/04/reference_02.jpg)
リンク先のマイクロソフト公式リファレンスサイトです。
![Microsoft公式リファレンス](https://sys-daddy.com/wp-content/uploads/2023/04/reference_03.jpg)
検索窓にキーワードを入力します。
「すべての~」が表示されたらそのままクリックしましょう。
![検索窓にキーワードを入力](https://sys-daddy.com/wp-content/uploads/2023/04/reference_04.jpg)
検索結果が表示されました。検索1位のページを開いてみます。
![検索結果のページ](https://sys-daddy.com/wp-content/uploads/2023/04/reference_05.jpg)
PivotCaches.Createメソッドの解説ページに飛びました。
![PivotCaches.Createメソッドの解説ページ](https://sys-daddy.com/wp-content/uploads/2023/04/reference_06.jpg)
ページをスクロールすると、パラメーターについての詳しい解説が載っています。
パラメーターは関数の引数と同じようなもので、構文を使いこなすためにも真っ先に目を通しておきたいところです。
![パラメーターの解説](https://sys-daddy.com/wp-content/uploads/2023/04/reference_07_01.jpg)
さらに下へスクロールすると、例文も掲載されています。
高レベルのコードが載っていることも多いので、目を通しておくとスキルアップに役立ちます。
![VBAの例文](https://sys-daddy.com/wp-content/uploads/2023/04/reference_13_01.jpg)
いったんパラメーターに戻り、SourceTypeに注目します。
SourceTypeパラメーターではXlPivotTableSourceTypeが設定値として使われています。
解説ページがあるようですので、クリックしてページに飛んでみましょう。
![列挙型をクリック](https://sys-daddy.com/wp-content/uploads/2023/04/reference_08_01.jpg)
XlPivotTableSourceTypeの解説ページに飛びました。
Excelだけでなく、外部データベースなどからもピボットテーブルを作ることもできるようです。
![列挙型の解説ページ](https://sys-daddy.com/wp-content/uploads/2023/04/reference_18.jpg)
列挙型の解説ページの「名前」と「値」の使い分けが特に重要です。
「名前」はExcel VBAだけに使えて、「値」はAccessからExcelを操作する時などに使う書き方です。
![列挙型の注目ポイント](https://sys-daddy.com/wp-content/uploads/2023/04/reference_09_02.jpg)
マクロ記録で自動作成されたコードを確認すると、xlDatabaseが使われています。
xlDatabaseを1に置き換えても正常に作動します。
![xlDatabaseが使われている](https://sys-daddy.com/wp-content/uploads/2023/04/reference_10.jpg)
今度はVersionに注目すると、省略可能なパラメーターとなっているようです。
![省略可能なパラメーター](https://sys-daddy.com/wp-content/uploads/2023/04/reference_11_01.jpg)
記録されたコードに書かれているVersionのところを省略しましたが、エラー発生などの異常は見られませんでした。
![パラメーターを省略してみる](https://sys-daddy.com/wp-content/uploads/2023/04/reference_12.jpg)
こんな感じで、不要なところはどんどん削除していきましょう。
試運転後に不要箇所を削除する
マクロ記録されたコードの中で不要だと思われるものをコメント化しました。
ピボットテーブルのマクロ記録の場合では、自動生成されたコードの8割近くが不要だったようです。
![不要な箇所をコメント化](https://sys-daddy.com/wp-content/uploads/2023/04/comment_block_01.jpg)
コメントを削除すると、かなりスッキリとしたコードになりました。
![コメントを削除したコード](https://sys-daddy.com/wp-content/uploads/2023/04/comment_block_02.jpg)
もちろん、動作確認は忘れずに済ませておきましょう。
従来の手順の場合は、いったんここで完了となります。
おまけ-Chat GPTなどのAIを活用する
ついでに、Chat-GPTにピボットテーブルを作るVBAコードについて聞いてみました。
マクロ記録した操作をできるだけ忠実にAIに伝えていきます。
表のデータは一部のみを貼り付けました。
![Chat GPTへのプロンプト](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_01.jpg)
以下がChatGPTの回答内容です。
![Chat GPTの回答](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_02.jpg)
コードはそのまま下に貼り付けておきます。
Sub CreatePivotTable()
'ChatGPT作成分
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
' ワークシートを取得
Set ws = Worksheets.Add
' ピボットキャッシュを作成
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
' ピボットテーブルを作成
Set pt = pc.CreatePivotTable(TableDestination:=ws.Range("A1"), TableName:="PivotTable1")
' 行見出しに「品名」を追加
Set pf = pt.PivotFields("品名")
pf.Orientation = xlRowField
' 値に「金額」の合計を追加
With pt
With .PivotFields("金額")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "金額の合計"
End With
End With
End Sub
変数や適切なコメントなどが入っていて、とても見やすいコードですよね!
さっそく期待に胸を膨らませプログラムを実行してみましたが、エラーが発生してプログラムが中断してしまいました。
![エラーで中断](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_04_01.jpg)
デバックで中断した部分を確認したところ、ピボットテーブルを作成するところが黄色くなっているようです。
![プログラムが中断した箇所](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_05.jpg)
結論からすると一行前のSourceDataが原因でした。
![中断した原因](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_06.jpg)
セル範囲をテーブル名に修正することで正しくプログラムが動作しました。
![テーブル名に変更](https://sys-daddy.com/wp-content/uploads/2023/04/chat_gpt_07.jpg)
今回は正確に伝えきれていない情報があったため、ChatGPTから一発で正解をもらえませんでした。
ChatGPTだけで自動化させるためのコードを完成させることは難易度が高く、マクロ記録とうまく掛け合わせるのが今のところ一番確実な方法のようです。
まとめ
今回は、Excelの様々な機能を自動化する際に、コードの書き方を調べる方法について紹介しました。
本記事ではピボットテーブルを例にあげて実演してみましたが、Excelの他の機能でもほとんど同じ手順で自動化することが可能です。
さらに、AIを活用する方法として、Chat GPTを使うのもとても有効な方法です。
ChatGPTはマクロ記録よりもずっときれいなコードに仕上げてくれますが、100%正しい動作を保証するものではありません。
現時点では、マクロ記録からスリムにしたコードとChatGPTで調べたコードを組み合わせ、自分のベストな回答を導き出すのが一番よい方法だと思います。
今回紹介した方法を使って、必要な機能を自動化するためのコードを学んでいけば、業務効率化につながるスキルを身につけることができるでしょう。