今日もきょうで・・・ChatGPTくんとの戦いが幕をあける。
最初に、やりたいことをなるべく詳細に記載します。
You
エクセル VBAの作成です。
概要:ファイルを読み込んですべてのシートを、読込元のファイルに追加します。
流れ: (ここでフローチャートを一個ずつ説明するように記載して行きます)
0.「現在の顧客提供データシートはすべて削除され、新たに読み込まれます」というメッセージを出し、OKなら継続、NOなら下記の実施を中断します。
マクロがあるファイルの 全シートを確認して、BASE_ で始まるファイルは すべて削除します。
1.ファイルを開く の 標準Windowを開きます。
開く場所は、マクロを実行しているファイルと同じフォルダを開きます。
ファイルを開くがクリックされます。
2.クリックされたファイルのすべてのシートを読込ます。 その際にシート名は BASE_ という文字を元のシート名に付け加えて作成します。
3.「顧客提供ファイルの読込が完了しました」のメッセージを出します。
最初の回答が出てきます。
ChatGPT
Excel VBAを使用して、指定された操作を自動化するマクロの作成方法を説明します。このマクロは以下のステップに従います:
(意図をどのように汲み取ったか、念の為に書いてきます)
(ここで、違う内容だったら、指示の出し方が悪いので、反省してください。
ビジネス能力ー説明能力を鍛える必要があります。)
- ユーザーに確認メッセージを表示し、OKなら続行、NOなら操作を中断します。
- 現在のワークブックから
BASE_
で始まるすべてのシートを削除します。 - ファイル選択ダイアログを開き、ユーザーがファイルを選択できるようにします。
- 選択されたファイルのすべてのシートを読み込み、シート名の前に
BASE_
を付けて現在のワークブックに追加します。 - 完了メッセージを表示します。
以下は、上記の要件を満たすVBAコードの例です。
COPYできるソースコードが出てきます。
ちょっと不要な説明も・・・そこはわかっているのに・・・
このコードを使用するには、ExcelのVBAエディタを開き(ExcelでAlt
+ F11
を押します)、新しいモジュール(挿入
> モジュール
)にコードを貼り付けてください。マクロを実行するには、VBAエディタ内でこのサブプロシージャを選択し、F5キーを押すか、マクロを実行するためのボタンやショートカットを設定します。
このコードは、基本的なエラーハンドリングを提供し、基本的なユーザーインターフェースのフィードバックを通じてプロセスをガイドします。より高度なエラーハンドリングやカスタマイズが必要な場合は、適宜コードを調整してください。
あ、これだと・・・ちょっと修正したい。
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エディタ内の新しいモジュールに貼り付けて使用してください。マクロの実行方法は前述のとおりです。
追加のマクロを要求(おかわり!)
You
新規エクセルマクロです。
概要:マクロがあるファイルのすべてのシート名を読み込み条件に従い、 シート名を「HENKAN」シートに書き込みます。
処理:
1.マクロがあるファイルのすべてのシート名を一時テーブルに読み込みます。
2.上記をシート名順でソートします。
3.一時テーブルを最初から読み込み BASE_ で始まるシートは HENKANシートの B5から下に順番に記載。 MAHO_ で始まるシートは HENKANシートの C5から下に順番に記載。 それ以外のシートは記載しません。
4.HENKAN上のシート名をクリックすると、各シートのA1に飛ぶようにします。
5.HENKANシートをautofitします。 6.シート名抽出が完了しました。 とメッセージをだします。
追加マクロに返答
ChatGPT
指定された要件を満たすExcel VBAマクロを作成するためのステップバイステップガイドです。このマクロは、ワークブック内のすべてのシート名を読み取り、条件に基づいて「HENKAN」シートにそれらの名前を書き込みます。また、ハイパーリンクを使用して、クリックすると対応するシートにジャンプする機能を追加します。
vbaCopy code 省略します。
このコードは、以下の機能を実装します:
うざいので、ここも省略します。
ちょっと修正
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
このコードは・・・(コードの説明)
エラー出たぞ!
You
Set col(i) = col(j) ここで「オブジェクトが必要です」のメッセージ出ました
ChatGPT
「オブジェクトが必要です」というエラーメッセージは、Set
キーワードを使用して値型(例えば文字列や数値など)を割り当てようとした場合に発生します。Set
はオブジェクトへの参照を割り当てる際に使用されますが、コレクション内の要素が値型の場合(この場合はシート名としての文字列)、Set
を使用するのは不適切です。
SortCollection
関数内で発生するこの問題を修正するために、Set
キーワードを使用せずに要素を交換する必要があります。以下は修正されたSortCollection
関数のコードです。
エラー出すなら、最初からエラー出ないコードだせないのか・・・
vbaCopy code:修正コードを記載してくる。
Function ・・・
修正しながらも、適切なアプローチでないと言う!!?
しかし、上記のコードはコレクション内の要素を正しく交換するための適切なアプローチではありません。(後略)
もしシート名のソートが必要な場合は、配列を使用してソート処理を行い、その結果を使用して操作を続ける方が適切です。
最初から回答出して欲しいなぁ。
最初の指示も「一時配列」て書いてたよね・・・
You
では、ListSheetNames の ’ すべてのシート名をコレクションに追加 を ‘ すべてのシート名を配列に追加 として、書き直してください。
ChatGPT
シート名を配列に読み込んでソートし、その後で条件に従って「HENKAN」シートに書き込むようにListSheetNames
マクロを書き直します。配列を使用することで、ソート処理が簡単になり、処理速度も向上します。
以下に修正したコードを示します。
後略
さらに、マクロに機能を追加させる!
やりとりで出てきた「シート名」「マクロ名」「変数名」などはそれなりに理解してくれています。
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
無限ループだしやがる!
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の列の最大数を超えないようにする
ちょっとイライラ・・・
You
‘ G列から右側で最初の空白セルを見つける While henkanSheet.Cells(1, targetColumn).Value <> “” targetColumn = targetColumn + 1 Wend この部分を消去すると、どうなりますか?ChatGPT
ChatGPT
' G列から右側で最初の空白セルを見つける
という部分をCopyMAHOHeaders
関数から消去すると、その関数はHENKAN
シートのG列から始めて、シート名がMAHO_
で始まる各シートの1行目(見出し行)を常に同じ場所に転記しようとします。この変更により、以下の問題が発生します:
- 見出しの上書き
- 複数シートの見出しデータの欠落
- データの整合性の問題:
MAHO_
シートごとに新しい位置に見出しを転記することで、HENKAN
シート上でどの見出しがどのシートに属するかを簡単に追跡できます。この部分を消去すると、そのような整合性が保たれなくなり、どの見出しがどのMAHO_
シートに対応するかが不明確になります。
したがって、この部分を削除すると・・・
結局、ChatGPTに修正観点を教える・・(^^;
You
文章からもイライラがでていますね・・・
ListSheetNamesToArray で MAHO_ で始まるシートのシート名を記載した段階で、HENKANシートの何行目なのかのデータを持っています。 その行数を CopyMAHOHeaders に渡してあげれば 3のデータの整合性は問題なくなりますよね? その行数の、G列以降に書き込みに行く、対象セルにデータがあるかどうか判断すれば、 問題ないのでは?ChatGPT
ChatGPT
はい、その通りです。MAHO_
で始まるシート名をHENKAN
シートに記載した際に使用した行数をCopyMAHOHeaders
関数に渡すことで・・・
このアプローチを実装するためには、CopyMAHOHeaders
関数の引数に行番号を追加し、それを使用してHENKAN
シートの適切な位置にデータを書き込むようにします。以下に修正したコード例を示します。
CopyMAHOHeaders関数の修正
vbaCopy code
つまりあれこれやりとりしながら、chatGPTが出したソースコードをざくざく読み取って、修正ポイントなども的確に指示してあげると、使い物になるソースコードが出来てきます。
AIで全部できるのはまだまだ先かもしれません。
今の段階では、使う側にもかなりのスキルが必要なのは確かです。