ChatGPTを使用したエクセルマクロ開発のある一例。

本題

今日もきょうで・・・ChatGPTくんとの戦いが幕をあける。

最初に、やりたいことをなるべく詳細に記載します。

You

エクセル　VBAの作成です。
概要：ファイルを読み込んですべてのシートを、読込元のファイルに追加します。
流れ： （ここでフローチャートを一個ずつ説明するように記載して行きます）
０．「現在の顧客提供データシートはすべて削除され、新たに読み込まれます」というメッセージを出し、OKなら継続、NOなら下記の実施を中断します。
マクロがあるファイルの　全シートを確認して、BASE_　で始まるファイルは すべて削除します。
１．ファイルを開く　の　標準Windowを開きます。
開く場所は、マクロを実行しているファイルと同じフォルダを開きます。
ファイルを開くがクリックされます。
２．クリックされたファイルのすべてのシートを読込ます。 その際にシート名は　BASE_　という文字を元のシート名に付け加えて作成します。
３．「顧客提供ファイルの読込が完了しました」のメッセージを出します。

最初の回答が出てきます。

ChatGPT

Excel VBAを使用して、指定された操作を自動化するマクロの作成方法を説明します。このマクロは以下のステップに従います：
（意図をどのように汲み取ったか、念の為に書いてきます）
（ここで、違う内容だったら、指示の出し方が悪いので、反省してください。
ビジネス能力ー説明能力を鍛える必要があります。）

  1. ユーザーに確認メッセージを表示し、OKなら続行、NOなら操作を中断します。
  2. 現在のワークブックからBASE_で始まるすべてのシートを削除します。
  3. ファイル選択ダイアログを開き、ユーザーがファイルを選択できるようにします。
  4. 選択されたファイルのすべてのシートを読み込み、シート名の前にBASE_を付けて現在のワークブックに追加します。
  5. 完了メッセージを表示します。

以下は、上記の要件を満たすVBAコードの例です。

COPYできるソースコードが出てきます。

ちょっと不要な説明も・・・そこはわかっているのに・・・

このコードを使用するには、ExcelのVBAエディタを開き（ExcelでAlt + F11を押します）、新しいモジュール（挿入 > モジュール）にコードを貼り付けてください。マクロを実行するには、VBAエディタ内でこのサブプロシージャを選択し、F5キーを押すか、マクロを実行するためのボタンやショートカットを設定します。

このコードは、基本的なエラーハンドリングを提供し、基本的なユーザーインターフェースのフィードバックを通じてプロセスをガイドします。より高度なエラーハンドリングやカスタマイズが必要な場合は、適宜コードを調整してください。

あ、これだと・・・ちょっと修正したい。

User

You

上記でシートは、後ろに追加ではなく、HENKANというシートの後ろに追加してください。 HENKAN　というシートがなければ、TOPというシートの後ろに追加してください。

ChatGPT

指定された要件に従って、シートを特定のシートの後ろに追加するためにコードを修正します。HENKANシートが存在する場合は、そのシートの後ろに新しいシートを追加し、存在しない場合はTOPシートの後ろに追加します。以下に修正したVBAコードを示します。

vbaCopy code

修正できました。

またまた、不要な説明。親切だが・・・

この修正により、HENKANまたはTOPシートの存在をチェックし、存在する場合はそのシートの直後に読み込まれたシートを追加します。On Error Resume Nextを使用してtargetSheetの設定時にエラーを回避し、該当するシートが見つからない場合にはNothingをtargetSheetに設定します。その後、エラーハンドリングを通常モードに戻すためにOn Error GoTo 0を使用しています。

このコードも同様に、ExcelのVBAエディタ内の新しいモジュールに貼り付けて使用してください。マクロの実行方法は前述のとおりです。

追加のマクロを要求（おかわり！）

User

You

新規エクセルマクロです。
概要：マクロがあるファイルのすべてのシート名を読み込み条件に従い、 シート名を「HENKAN」シートに書き込みます。
処理：
１．マクロがあるファイルのすべてのシート名を一時テーブルに読み込みます。
２．上記をシート名順でソートします。
３．一時テーブルを最初から読み込み 　BASE_　で始まるシートは　HENKANシートの　B5から下に順番に記載。 　MAHO_　で始まるシートは　HENKANシートの　C5から下に順番に記載。 　それ以外のシートは記載しません。
４．HENKAN上のシート名をクリックすると、各シートのA1に飛ぶようにします。
5．HENKANシートをautofitします。 6．シート名抽出が完了しました。　とメッセージをだします。

追加マクロに返答

ChatGPT

指定された要件を満たすExcel VBAマクロを作成するためのステップバイステップガイドです。このマクロは、ワークブック内のすべてのシート名を読み取り、条件に基づいて「HENKAN」シートにそれらの名前を書き込みます。また、ハイパーリンクを使用して、クリックすると対応するシートにジャンプする機能を追加します。

vbaCopy code　省略します。

このコードは、以下の機能を実装します：

うざいので、ここも省略します。

ちょっと修正

User

You

下記の部分は　HENKANシートの　B列、C列　5行目から下の範囲をクリアするようにしてください。 ‘ HENKANシートをクリア henkanSheet.Cells.Clear この部分だけの修正コードを記載してください。

こう記載しないと、また、全部書き始める可能性があるので・・・

ChatGPT

指定された要件に基づいて、HENKANシートのB列とC列の5行目から下の範囲をクリアするためのコードの修正部分は以下の通りです。

vbaCopy code

' HENKANシートのB列とC列、5行目から下の範囲をクリア henkanSheet.Range("B5:B" & henkanSheet.Rows.Count).Clear henkanSheet.Range("C5:C" & henkanSheet.Rows.Count).Clear

このコードは・・・（コードの説明）

エラー出たぞ！

User

You

Set col(i) = col(j)　ここで「オブジェクトが必要です」のメッセージ出ました

ChatGPT

「オブジェクトが必要です」というエラーメッセージは、Setキーワードを使用して値型（例えば文字列や数値など）を割り当てようとした場合に発生します。Setはオブジェクトへの参照を割り当てる際に使用されますが、コレクション内の要素が値型の場合（この場合はシート名としての文字列）、Setを使用するのは不適切です。

SortCollection関数内で発生するこの問題を修正するために、Setキーワードを使用せずに要素を交換する必要があります。以下は修正されたSortCollection関数のコードです。

エラー出すなら、最初からエラー出ないコードだせないのか・・・

vbaCopy code：修正コードを記載してくる。

Function ・・・

修正しながらも、適切なアプローチでないと言う！！？


しかし、上記のコードはコレクション内の要素を正しく交換するための適切なアプローチではありません。（後略）

もしシート名のソートが必要な場合は、配列を使用してソート処理を行い、その結果を使用して操作を続ける方が適切です。

最初から回答出して欲しいなぁ。
最初の指示も「一時配列」て書いてたよね・・・

User

You

では、ListSheetNames　の　’ すべてのシート名をコレクションに追加　を ‘ すべてのシート名を配列に追加　として、書き直してください。

ChatGPT

シート名を配列に読み込んでソートし、その後で条件に従って「HENKAN」シートに書き込むようにListSheetNamesマクロを書き直します。配列を使用することで、ソート処理が簡単になり、処理速度も向上します。

以下に修正したコードを示します。

後略

さらに、マクロに機能を追加させる！

やりとりで出てきた「シート名」「マクロ名」「変数名」などはそれなりに理解してくれています。

User

You

ListSheetNamesToArray　で　MAHO_で始まるシートの場合、そのシートの 1行目（見出し行）を読み込んで、HENKANシートの　G列から、順に右側に 対象シートの1行目を記載してください。 ただし、G列から右側でセルに値が入っている場合は、タイトル行の転記は行いません。 この条件で ElseIf Left(sheetNames(i), 5) = “MAHO_” Then henkanSheet.Hyperlinks.Add Anchor:=henkanSheet.Cells(mahoRow, 3), Address:=””, SubAddress:=”‘” & sheetNames(i) & “‘!A1”, TextToDisplay:=sheetNames(i) mahoRow = mahoRow + 1 End If この部分から呼び出す、サブプロシージャを作成してください。 また、上記部分のみ、作成した、サブプロシージャを呼び出す様に変えてください。

いったん回答出してきますが・・・

ChatGPT

要求された条件に従って、MAHO_で始まるシートの1行目（見出し行）を読み込み、HENKANシートのG列から右側に順に転記する機能を実装するためには、まず対象のシートから見出し行を読み取り、次にそれをHENKANシートに記載するための専用のサブプロシージャを作成する必要があります。また、指定された部分を新しく作成するサブプロシージャを呼び出すように変更します。

以下に、MAHO_で始まるシートの見出し行をHENKANシートに転記するためのサブプロシージャと、そのサブプロシージャを呼び出すように修正したListSheetNamesToArrayマクロの一部を示します。

MAHO_見出し行を転記するサブプロシージャ

vbaCopy code

' G列から右側で最初の空白セルを見つける
While henkanSheet.Cells(1, targetColumn).Value <> ""
　targetColumn = targetColumn + 1
Wend

無限ループだしやがる！

User

You

CopyMAHOHeaders　で ‘ G列から右側で最初の空白セルを見つける While henkanSheet.Cells(1, targetColumn).Value <> “” targetColumn = targetColumn + 1 Wend は無限ループ？ChatGPT

ChatGPT

ご指摘の通り、CopyMAHOHeaders関数の指定された部分には無限ループのリスクが含まれています。これは、targetColumnがExcelシートの列の最大数を超える可能性がある場合に発生します。Excelの列の最大数はバージョンによって異なりますが、例えばExcel 2016以降では16,384列までサポートされています（列XFDまで）。この問題を防ぐために、ループに終了条件を追加する必要があります。

以下は、無限ループのリスクを回避するために改良したCopyMAHOHeaders関数です。

vbaCopy code

' G列から右側で最初の空白セルを見つける、Excelの列の最大数を超えないようにする

ちょっとイライラ・・・

User

You

‘ G列から右側で最初の空白セルを見つける While henkanSheet.Cells(1, targetColumn).Value <> “” targetColumn = targetColumn + 1 Wend この部分を消去すると、どうなりますか？ChatGPT

ChatGPT

' G列から右側で最初の空白セルを見つけるという部分をCopyMAHOHeaders関数から消去すると、その関数はHENKANシートのG列から始めて、シート名がMAHO_で始まる各シートの1行目（見出し行）を常に同じ場所に転記しようとします。この変更により、以下の問題が発生します：

  1. 見出しの上書き
  2. 複数シートの見出しデータの欠落
  3. データの整合性の問題：MAHO_シートごとに新しい位置に見出しを転記することで、HENKANシート上でどの見出しがどのシートに属するかを簡単に追跡できます。この部分を消去すると、そのような整合性が保たれなくなり、どの見出しがどのMAHO_シートに対応するかが不明確になります。

したがって、この部分を削除すると・・・

結局、ChatGPTに修正観点を教える・・(^^;

User

You

文章からもイライラがでていますね・・・

ListSheetNamesToArray　で　MAHO_　で始まるシートのシート名を記載した段階で、HENKANシートの何行目なのかのデータを持っています。 その行数を　CopyMAHOHeaders　に渡してあげれば 3のデータの整合性は問題なくなりますよね？ その行数の、G列以降に書き込みに行く、対象セルにデータがあるかどうか判断すれば、 問題ないのでは？ChatGPT

ChatGPT

はい、その通りです。MAHO_で始まるシート名をHENKANシートに記載した際に使用した行数をCopyMAHOHeaders関数に渡すことで・・・

このアプローチを実装するためには、CopyMAHOHeaders関数の引数に行番号を追加し、それを使用してHENKANシートの適切な位置にデータを書き込むようにします。以下に修正したコード例を示します。

CopyMAHOHeaders関数の修正

vbaCopy code

つまりあれこれやりとりしながら、chatGPTが出したソースコードをざくざく読み取って、修正ポイントなども的確に指示してあげると、使い物になるソースコードが出来てきます。

AIで全部できるのはまだまだ先かもしれません。

今の段階では、使う側にもかなりのスキルが必要なのは確かです。