ChatGPTにVBAを作ってもらったら想像以上に大変だった件

お久しぶりです！@rilakkuma46です。2回目の投稿です。

前回はMakeを使った私的な作業改善を行いました。
今回はお仕事の作業効率を改善できないか考えてみました。
ちょうどChatGPTについて学ぶ機会があり、コードも作れることを知りました。
マクロについては何の知識もない初心者で（作ってもらったのを実行するだけ）、
こんな私でも意図したものができるのかチャレンジしてみましたので、
これを記事にしたいと思います。

経費処理を1クリックで完了させたい

ちょうど経費処理で忙しい時期なので、その1つの業務を効率化していきます。今くらいの時期は他の業務も集中し、経費処理できる期間と締め切りが決まっているので、猫の手も借りたい期間です。マクロでできるならやって欲しい、楽になりたい。
目標は「1クリックで1つの業務すべてが完了する」というものです。

現状の作業

この業務は、「とある機器の利用料を各部署に請求する」というもので、月１回・１時間程度の作業量です。業務の大まかな流れは下記の通りです。

　①機器管理システムから利用状況をエクスポート
　②利用料を算出するExcelファイル「集計表」にコピー
　③VLOOKUP関数を駆使して、人毎・部署毎に利用状況を集計
　④集計データから経費システムインポート用データを作成
　⑤経費システムにインポートし、請求処理を登録、上司に承認をもらう
　⑥承認後、各部署に利用明細を送付

できたもの

マクロ実行の動画 pic.twitter.com/gPeeh31ZIx

— Toshi82430 (@Toshi82430) August 26, 2024

下図の赤点線枠内のみマクロで実行してくれます。

1クリックですべての完了とはいきませんでしたが、今まで1時間の作業が15分くらいになりました。完成したコードはそれぞれの記事内に置いてあります。

利用したもの

・VBA
・ChatGPT　　　　VBAのコードを生成してもらいます
＊Google検索も多用しています

悪戦苦闘-1-　”何ができるの？”

そもそもVBAで何ができるのかがわかりません。Excelと関係ないこともできるのか？まずは①の作業ができるかChatGPTに呪文を唱えます。

答えの意味がわかりません。その後も「Edgeの場合は？」「Seleniumって何？」等呪文を唱え続けますが、答えの意味が理解できません。Google先生にも聞いてみると、できるけど難しそうなのでここはあきらめます。⑤も難しそう。②～④の作業をマクロにする方針に変更します。⑥もできそうですが、それほど時間がかからないのと実施タイミングが違うためこれも除外します。
ここでヒントも見つかりました。VBAコードを分割して作成し、それを順番に実行するマクロを作成するという方法です。まずは②③④それぞれのVBAコードを作成していくことにします。

参考記事
【ExcelVBA入門】マクロをパーツ化！Subプロシージャを分割して使う方法

悪戦苦闘-2-　”繰り返されるエラーの数々”

②は「csvファイルを集計表にコピー」する作業です。ChatGPTに呪文を唱えます。

ChatGPTの回答はこちら

VBA「CSVファイルを元データにコピー」

Sub CSVファイルを元データにコピー()
    Dim データフォルダ As String
    Dim ファイル名 As String
    Dim CSVファイル As Workbook
    Dim 元データシート As Worksheet
    Dim CSVシート As Worksheet
    Dim 最終行 As Long
    Dim コピー範囲 As Range
    
    ' データフォルダのパスを設定
    データフォルダ = "C:\path\to\your\data\folder\" ' ここを実際のパスに変更してください

    ' 元データシートの設定
    Set 元データシート = ThisWorkbook.Sheets("元データ")
    
    ' データフォルダ内のCSVファイルを検索
    ファイル名 = Dir(データフォルダ & "利用状況一覧*.csv")
    
    ' ファイルが見つかる限りループ
    Do While ファイル名 <> ""
        ' CSVファイルを開く
        Set CSVファイル = Workbooks.Open(データフォルダ & ファイル名)
        Set CSVシート = CSVファイル.Sheets(1) ' CSVファイルは1シートのみ
        
        ' コピー範囲を設定
        最終行 = CSVシート.Cells(Rows.Count, 1).End(xlUp).Row
        Set コピー範囲 = CSVシート.Range("A1:A" & 最終行)
        
        ' コピーして「元データ」シートに貼り付け
        コピー範囲.Copy Destination:=元データシート.Cells(元データシート.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
        
        ' CSVファイルを閉じる（保存しない）
        CSVファイル.Close SaveChanges:=False
        
        ' 次のファイルを取得
        ファイル名 = Dir
    Loop

    MsgBox "CSVファイルのデータが「元データ」シートにコピーされました。"
End Sub

おお！すごい！早速VBAにコピーします。（どこにコピーしたらいいのかもすべてChatGPTに聞きながらです）

実行してみると・・・

コピーされません。？？？
ChatGPTも完璧ではないはずなので、唱える呪文を変更しながら何度かトライするも結果は変わらず。コードの意味を理解しないと駄目なのかなと思いコードを見てみると
「ここを実際のパスに変更してください」
って書いてある・・・。しかも、コードの後にその解説をしてくれていました。すみません、見ていませんでした。あなた優秀ですね。
その後もこのパスの書き方に苦戦しながら、やっとのことで完成、コピーできました。

悪戦苦闘-3-　”エラーとの戦い再び”

続いて③「人毎に利用状態を集計して一覧表シートに出力」する作業です。先ほどと同様にChatGPTに呪文を唱えます。

ChatGPTの回答（動かなかったもの）はこちら

VBA動かなかったコード「利用者状態集計_一覧表更新」

Sub 利用者状態集計_一覧表更新()
    Dim 元データ As Worksheet
    Dim 一覧表 As Worksheet
    Dim 利用者Dict As Object
    Dim 状態Dict As Object
    Dim 利用者 As String
    Dim 状態 As String
    Dim i As Long
    Dim outputRow As Long
    Dim lastRow As Long

    ' シートの設定
    Set 元データ = Worksheets("元データ")
    Set 一覧表 = Worksheets("一覧表")

    ' 利用者ごとの状態を集計するためのディクショナリを作成
    Set 利用者Dict = CreateObject("Scripting.Dictionary")

    ' データを読み込み、集計
    For i = 2 To 元データ.Cells(Rows.Count, 3).End(xlUp).Row
        利用者 = 元データ.Cells(i, 3).Value
        状態 = 元データ.Cells(i, 5).Value

        If Not 利用者Dict.exists(利用者) Then
            ' 状態ごとの初期値を設定
            Set 状態Dict = CreateObject("Scripting.Dictionary")
            状態Dict.Add "準備中", 0
            状態Dict.Add "使用中（Ａ）", 0
            状態Dict.Add "使用中（Ｂ）", 0
            状態Dict.Add "停止", 0
            利用者Dict.Add 利用者, 状態Dict
        End If

        ' 状態別にカウントアップ
        状態Dict = 状態Dict(状態)
    Next i

    ' 一覧表シートに既存の利用者のデータを更新
    lastRow = 一覧表.Cells(Rows.Count, 1).End(xlUp).Row
    For outputRow = 2 To lastRow
        利用者 = 一覧表.Cells(outputRow, 1).Value
        If 利用者Dict.exists(利用者) Then
            Set 状態Dict = 利用者Dict(利用者)
            一覧表.Cells(outputRow, 2).Value = 状態Dict("使用中（Ａ）")
            一覧表.Cells(outputRow, 3).Value = 状態Dict("使用中（Ｂ）")
            一覧表.Cells(outputRow, 4).Value = 状態Dict("準備中")
            一覧表.Cells(outputRow, 5).Value = 状態Dict("停止")
            ' 利用者Dictから削除
            利用者Dict.Remove 利用者
        Else
            ' 利用者が存在しない場合は0を設定
            一覧表.Cells(outputRow, 2).Value = 0
            一覧表.Cells(outputRow, 3).Value = 0
            一覧表.Cells(outputRow, 4).Value = 0
            一覧表.Cells(outputRow, 5).Value = 0
        End If
    Next outputRow

    ' 一覧表シートに新しい利用者のデータを追加
    For Each 利用者 In 利用者Dict.keys
        lastRow = lastRow + 1
        Set 状態Dict = 利用者Dict(利用者)
        一覧表.Cells(lastRow, 1).Value = 利用者
        一覧表.Cells(lastRow, 2).Value = 状態Dict("使用中（Ａ）")
        一覧表.Cells(lastRow, 3).Value = 状態Dict("使用中（Ｂ）")
        一覧表.Cells(lastRow, 4).Value = 状態Dict("準備中")
        一覧表.Cells(lastRow, 5).Value = 状態Dict("停止")
    Next 利用者

    MsgBox "集計と更新が完了しました。"

End Sub

今度は大丈夫かと不安になりつつも、VBAにコピーして実行してみます。

またエラーが出ましたが、よくわかりません。今度も何か注意書きがないか探してみましたが該当する記述はなし。エラーの内容を追記して再度ChatGPTでコードを生成してもらうと、今度は「実行時エラー　450：引数の数が一致していません。または不正なプロパティを指定しています。」のエラー。また追記してコードを再生成・・・。という感じでエラーが繰り返し発生しVBAの実行がなかなか完了しません。呪文を唱え直したり、Google先生に聞いたりと悪戦苦闘。10回くらい繰り返したらなんとか動きました。

動いたコードはこちら

VBA動いたコード「利用者状態集計_一覧表更新」

Sub 利用者状態集計_一覧表更新()
    Dim ws元データ As Worksheet
    Dim ws一覧表 As Worksheet
    Dim lastRow As Long
    Dim dict As Object
    Dim 利用者 As String
    Dim 状態 As String
    Dim key As Variant ' key の型を Variant に変更
    Dim i As Long
    
    ' シートの設定
    Set ws元データ = ThisWorkbook.Sheets("元データ")
    Set ws一覧表 = ThisWorkbook.Sheets("一覧表")
    
    ' 元データシートの最終行を取得
    lastRow = ws元データ.Cells(ws元データ.Rows.Count, "C").End(xlUp).Row
    
    ' 辞書オブジェクトの作成
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' データの集計
    For i = 2 To lastRow
        利用者 = ws元データ.Cells(i, "C").Value
        状態 = ws元データ.Cells(i, "E").Value
        
        ' 利用者が辞書に存在しない場合は新しく追加
        If Not dict.exists(利用者) Then
            dict.Add 利用者, Array(0, 0, 0, 0) ' [使用中（Ａ）, 使用中（Ｂ）, 準備中, 停止]
        End If
        
        ' 状態に応じてカウントを増加
        Select Case 状態
            Case "使用中（Ａ）"
                dict(利用者)(0) = dict(利用者)(0) + 1
            Case "使用中（Ｂ）"
                dict(利用者)(1) = dict(利用者)(1) + 1
            Case "準備中"
                dict(利用者)(2) = dict(利用者)(2) + 1
            Case "停止"
                dict(利用者)(3) = dict(利用者)(3) + 1
        End Select
    Next i
    
    ' 一覧表シートをクリア
    ws一覧表.Cells.Clear
    
    ' 一覧表シートのヘッダーを設定
    ws一覧表.Range("A1:E1").Value = Array("利用者", "使用中（Ａ）", "使用中（Ｂ）", "準備中", "停止")
    
    ' 集計結果を一覧表シートに出力
    i = 2
    For Each key In dict.keys
        ws一覧表.Cells(i, 1).Value = key
        ws一覧表.Cells(i, 2).Value = dict(key)(0)
        ws一覧表.Cells(i, 3).Value = dict(key)(1)
        ws一覧表.Cells(i, 4).Value = dict(key)(2)
        ws一覧表.Cells(i, 5).Value = dict(key)(3)
        i = i + 1
    Next key
    
    MsgBox "集計が完了しました。"
End Sub

何が違うかは理解できませんが、とりあえずよしとします。

最後の④「インポート用のデータを集計表より作成」する作業もChatGPTに呪文を唱えます。

ここでも「エラー→コード再生成」を何度か繰り返し、やっと動きました。

動いたコードはこちら

VBA「データエクスポート」

Sub データエクスポート()
    Dim 集計シート As Worksheet
    Dim エクスポートファイル As Workbook
    Dim エクスポートシート As Worksheet
    Dim 最終行 As Long
    Dim i As Long
    Dim エクスポートフォルダ As String
    Dim 出力行 As Long
    Dim シート名 As String
    
    ' 集計シートの設定
    On Error Resume Next
    Set 集計シート = ThisWorkbook.Sheets("集計")
    On Error GoTo 0
    If 集計シート Is Nothing Then
        MsgBox "「集計」シートが見つかりません。"
        Exit Sub
    End If
    
    ' データフォルダのパスを設定
    エクスポートフォルダ = "C:\path\to\your\data\folder\" ' ここを実際のパスに変更してください
    
    ' エクスポートファイルの作成
    On Error Resume Next
    Application.DisplayAlerts = False
    Set エクスポートシート = Nothing
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "エクスポートデータ" Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set エクスポートファイル = Workbooks.Add
    エクスポートファイル.Sheets(1).Name = "エクスポートデータ"
    Set エクスポートシート = エクスポートファイル.Sheets("エクスポートデータ")
    
    ' ヘッダーを設定
    エクスポートシート.Cells(1, 1).Value = "B列データ"
    エクスポートシート.Cells(1, 2).Value = "J列データ"
    
    ' 「集計」シートの最終行を取得（最終行は対象外）
    最終行 = 集計シート.Cells(Rows.Count, 2).End(xlUp).Row - 1
    
    ' 出力行を初期化
    出力行 = 2
    
    ' データをコピー
    For i = 33 To 最終行
        If 集計シート.Cells(i, 10).Value <> 0 Then
            エクスポートシート.Cells(出力行, 1).Value = 集計シート.Cells(i, 2).Value
            エクスポートシート.Cells(出力行, 2).Value = 集計シート.Cells(i, 10).Value
            出力行 = 出力行 + 1
        End If
    Next i
    
    ' エクスポートファイルを保存
    On Error GoTo SaveError
    エクスポートファイル.SaveAs Filename:=エクスポートフォルダ & "エクスポートファイル.csv", FileFormat:=xlCSV
    エクスポートファイル.Close SaveChanges:=False
    
    MsgBox "データが「エクスポートファイル」にエクスポートされました。"
    Exit Sub
    
SaveError:
    MsgBox "エクスポートファイルの保存中にエラーが発生しました。"
    エクスポートファイル.Close SaveChanges:=False
End Sub

インポート用のファイルが新たに作成されました。

そして、これらのVBAをまとめて実行するマクロを同様にChatGPTに作ってもらいます。これまでに50個以上の呪文を唱えています。今度はどうか？

ChatGPTの回答はこちら

VBA「集計処理」

Sub 集計処理()
    ' エラーハンドリングの設定
    On Error GoTo ErrorHandler
    
    ' 1. CSVファイルを元データにコピー
    Call CSVファイルを元データにコピー
    
    ' 2. 利用者状態集計_一覧表更新
    Call 利用者状態集計_一覧表更新
    
    ' 3. 利用状況一覧作成
    Call 利用状況一覧作成
    
    ' 4. データエクスポートCSV
    Call データエクスポートCSV
    
    ' 処理完了メッセージ
    MsgBox "すべての処理が完了しました。"
    
    Exit Sub
    
ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description
End Sub

一発で動きました。これで完成です。

＊もう1つVBAがあるのですが、その部分は省略します。

反省とこれから

まずは実行結果が正しいのか、過去数ヶ月分のデータで試してみました。結果はお見せできませんが、手集計のものと同じです。正しく動くようです。

ここまで6時間以上かかっており、簡単にできたとは言い難いです。しかしながらマクロの知識がなくてもなんとかできたので、ChatGPTの優秀さを身をもって理解しました。本来なら、”空いた時間で他の経費処理もマクロで実行できるようにしよう”といきたいところなのですが、時間がかかりすぎてしまいました。ただマクロが正確な作業をしてくれるので、「間違えていないかな」という心理的不安はなくなり、今回はその点がプラスになりました。

今回の経験から、ChatGPTへの質問方法がわかった気がします。

ChatGPTへの質問方法
・同じチャットで質問する
・続けて質問すれば前のやりとりを踏まえて答えてくれる
・エラーが出たらありのままを質問する

この記事を書こうともう一度質問して見ると、エラーを出したコードはもう出てきません。
また、VBAを記述する場所とかコードが分割できることとか、コードを書く以外の箇所でも時間がかかっているので、マクロのある程度の知識があればもっと早くできるな、とも思いました。

最後に、①のファイルの中身が過去何回か微妙に変わっていることに気づきました。これは出力してみないとわからないので、変更を確認するマクロが必要なのか・・・。この辺がシステム化のネックですね。

（追記）「VBA」と「マクロ」の言葉の意味を理解していなかったため、文章を修正しました。