リンク： 

公開日：2022年9月6日

自分にとってのエクセルVBA

ブログタイトルとして「SNSとかプログラミングとかはじめます」としていますが、エクセルVBAについては2年ほど前から勉強して、今年「エクセルVBAエキスパート　スタンダード」の資格に合格しました。

といってもまだまだ分からないことだらけですし、物覚えが悪いのでせっかく覚えたコマンドも忘れて、サイトで検索しながらなのでスラスラVBAを書けるわけではありません。

また、図形やグラフの操作、印刷やメールの自動送信などは普段の仕事でもあまり使っていないのと、試験の対象範囲外だったのでほとんど覚えていません。

でも、自分で作成したプログラムが思い通りに動作し、今まで時間のかかっていた繰り返し作業が楽になると達成感と感動がすごく、もっとプログラミンをを勉強したいという気持ちになり、このブログをはじめました。

本当ならもっとVBAを極めたほうがいいのではと思うのですが、

①いってもエクセルVBAってエクセルに特化したプログラム

②VBAがシングルタスクなので、VBA実行中はエクセルが使えない

ということから、他のプログラミング言語を勉強しようと思うようになりました。

今後、順次いろんな言語を勉強していくつもりですが、まずはHTML/CSSを勉強して自分のポートフォリオを作成したいと思っています。

それまでは、自分の復習もかねて、VBAについても並行してブログアップしていきたいと思います。

リンク：

公開日：2022年9月7日

変数の宣言について

今回は、変数の宣言で仕事場の数人が間違えていた変数宣言について話したいと思います。

エクセルVBAでは、変数の宣言が必要になります。

宣言の仕方は、

Dim　変数名　as　変数型

なのですが、複数の変数をカンマ区切りで一行に記載することができます。

で、数人が宣言していた方法がこれ。

【間違い】

Dim　変数1,変数2,変数3　as　変数型

です。

これ、ダメですよね？

カンマ区切りで一行に記載することはできますが、省略できるのは最初の「Dim」だけです。

なので、正式には、

【正解】

Dim　変数1　as　変数型,変数2　as　変数型,変数3　as　変数型

となります。

【間違い】の宣言だと、変数1と変数2の型が指定されていないことになるので、勝手にVariant型になってしまいます。

他の職場ではどうか知りませんが、あるあるなのでしょうか？

今後、私がVBA勉強中につまづいた

・配列

・引数渡し

・戻り値

等についても持論ですが話していきたいと思います。

リンク：

公開日：2022年9月11日

配列とは

配列って、配列変数　というだけあって、変数の一種なんですね。

変数自体はVBA勉強し始めたときから使っていたので何となく理解していたつもりですが、当初は　配列変数　となった時点で頭の中が？？？状態でした。

通常の変数と配列変数の違いは、

●同じ変数名で複数の変数が扱える

が大きな違いとおもいますが、VBAの基本に関する書籍で調べてみると下記のような例文が記載されていました。(変数名などは書籍通りでなく自分なりに変更しています)

Dim youbi(2) as string

youbi(0)="日曜日"

youbi(1)="月曜日"

youbi(2)="火曜日"

または

Dim youbi as variant

youbi＝array("日曜日",""月曜日,"火曜日")

同じ変数名の中にインデックス番号を変え複数の変数が設定できる・・・

ですが、私の頭の中は「だから何？」でした。

・いちいち、配列の要素をプログラムに書かないといけない？

・プログラムで入力してる時点で、変数というより定数やん！

ってことで、配列がわからないというより、配列を使う意味が分からない状態でした。

配列を使いだしたきっかけ

VBAを使いだした当初は、あまり難しいプログラムも書けず、ほとんどコピペを繰り返すようなプログラムばかりでした。

そんな中、2つのファイル間のやり取りがしたくなり、

【例】

①ファイルAに、「商品番号」「商品名」「仕入れ先」「単価」の一覧表がある

②ファイルBに、1か月の売り上げを管理するファイルを作成する

「商品番号」を入力することで、ファイルAより「商品番号」を検索して「商品名」「仕入れ先」「単価」のデータをファイルBに自動入力する

というプログラムを作成しようとしました。

最初は"Findメソッド"で、

・ファイルBに入力された「商品番号」を、ファイルAで検索

・同じ行にある「商品名」「仕入れ先」「単価」を普通の変数にいれる

・ファイルBに転記する

というプログラムを作成しました。

※1.商品番号にカテゴリを入れて「A-001」のようにしたいのでとりあえずstring型を宣言

※2.同じ作業を10回繰り返す

Dim no as string 

Dim name as string

Dim custmer as string

Dim price as currency

Dim i as long

Dim search_range as range

for i=1 to 10

no=ファイルB.cells(i,1).text

set search_range=ファイルA.range("A:A").find(what:=no,lookat:=xlwhole)

name=ファイルA.search_range.offset(,1).text

custmer=ファイルA.search_range.offset(,2),text

price=ファイルA.search_range.offset(,3).value

with ファイルB

  .cells(i,2)=name

  .cells(i,3)=custmer

  .cells(i,4)=price

end with

next i

※変数に値を入れた後は、エラーを避けるために変数をクリアするプログラムを入れたほうが良いみたいですが、今回は割愛しています。

こんな感じです。

ただ、ここで問題(面倒)と思ったことは、

①変数を5つ宣言必要(カウント用の変数iは除外)

②ファイルAとファイルBを何度も行ったり来たりする

③ファイルA,Bを行ったり来たりするので、2個のファイルをずっと起動しておく必要がある

です。

そこで配列が登場しました。

とりあえず変数を配列に入れてみた(①だけ解決する方法)

問題は①から③までありますが、とりあえず①を解決するために変数を1つの変数(配列)に格納してみました。

※1.検索したrangeは個別宣言し、残りの4つのデータを配列に格納

※2.配列のインデックスは基本"0"からはじまるので、インデックス番号0～3の4つに「商品番号」「商品名」「仕入れ先」「単価」を格納

Dim data(3) as variant

Dim i as long

Dim search_range as range

for i=1 to 10

data(0)=ファイルB.cells(i,1).text 　'商品番号

set search_range=ファイルA.range("A:A") _

.find(what:=data(0),lookat:=xlwhole)

data(1)=ファイルA.search_range.offset(,1).text　'商品名

data(2)=ファイルA.search_range.offset(,2),text　'仕入れ先

data(3)=ファイルA.search_range.offset(,3).value　'単価

with ファイルB

  .cells(i,2)=data(1)

  .cells(i,3)=data(2)

  .cells(i,4)=data(3)

end with

next i

変数宣言はすっきりしました。

ただ、①でrangeの宣言は残っていますし、②、③の問題は解決していません。

今回は長くなるので、「配列を使いだしたきっかけ」として、ここまでにします。

次回②、③の解決したプログラムを紹介したいと思います。

リンク：

公開日：2022年9月12日 

配列の応用(データを配列に全部入れ)

前回VBAで配列を使いだしたきっかけについてブログしました。

今回は前回に残した課題について解決していきたいと思います。

【前回の課題】

①rangeの宣言も配列にいれる

②ファイルAとファイルBを何度も行ったり来たりする

③ファイルA,Bを行ったり来たりするので、2個のファイルをずっと起動しておく必要がある

で、自分なりに考えた結果が

●ファイルAのデータをすべて配列に格納する

●検索はfindを使用せず、for文とif文でデータの照合することで、seach_rangeの変数も宣言しない

使用する配列は、2次元配列で、とりあえず今回はデータ数がわかっている静的配列で書いていきます。

※1.ファイルAに20個のデータがある

※2,配列には、「商品番号」「商品名」「仕入れ先」「単価」の4データ

※3.findメソッドは使わず、前回のようにsearch_rangeの変数も使用しない

※4.前回同様検索してファイルBに記述する作業を10回繰り返す

Dim data(19,3) as variant

Dim no as string

Dim i as long

Dim j as long

Dim k as long

for i=1 to 10

  for j=0 to 19

    for k=0 to 3

      data(j,k)=ファイルA.cells(1+j,1+k).value

next k

  next j

j=0

k=0

with ファイルB

  no=.cells(i,1).text

    for j=0 to 19

      if no=data(j,0) then

        for k=1 to 3

          .cells(i,k+1).value=data(j,k)

        next k

      end if

    next j

end with

next i

ごめんなさい。逆にわかり難いプログラムになったかもしれません。

但し、ファイルAのデータを全て配列を格納することによって、

●②の問題であるファイルA、Bのやり取りは必要はなくなった

●③の問題であるプログラム実行中配列データが残るのでファイルAを閉じても問題ありません

※今回は「no」変数を残しましたが、

　ファイルB.cells(i,1)

で、変数にしなくても問題ないと思います。

findメソッドは、for文で繰り返し、if文で照合するように変更。

配列を使いだしてよいところは、インデックス番号で配列管理しているので、for文で「i」などのカウント用変数を用いると、インデックス番号を「足し算」「引き算」で指定できるようになるところです。

で、今回のようにデータを全て配列に格納することができる。

書籍の例文は、わかりやすくするために仕方ないとして、実際は上記プログラムのように繰り返すことにより、今回の20個のデータだけでなく、何百、何千のデータも簡単に配列に入れる事が出来ます。

新たな問題

上記で配列に全てのデータを格納しました。

但し、新たな問題が発生。

もともと、各々の変数で宣言していた時は、

●例えば「単価」はcurrency(通貨型)

で宣言していました。

しかし、上記では配列の中にいろんな型が含まれて、個別で宣言できないので「variant」型で宣言しました。

「no」を「string」型で宣言しているのに、元データのdataは「variant」って。

この宣言ではうまく動くときもありますが、「型が一致しません」のエラーになることもあります。

ここで「構造体」の登場です。

「構造体」とかは、エクセルVBAの基礎などの書籍には載ってません。

「構造体」については、今後どっかでブログしたいと思いますが、今回言いたかったのは、配列を活用しだすとやめられなくなるくらい便利ということです。

見た目は難しい感じになりますが、

●書籍等の例文にある3～10個程度の変数を手で入力するのでなく、for文を使うことで大きなデータを全て一つの配列に入れることができる

●インデックス番号(数字)で指定できるので、「足し算」「引き算」で配列の指定ができる

っていう感じです。

ちょっとややこしくなりましたが、「私が配列を使いだしたきっかけ」でした。

リンク：

公開日：2022年9月15日 

私のエクセルVBA勉強方法

私は勉強する際にサイトやYoutubeで勉強することが多いのですが、アナログ人間なので書籍も同時に購入します。(書籍があるとなんか安心してしまう)

エクセルVBAを勉強するときに最初に購入した書籍はこれです。

(サンプルファイル・無料電子版付)できる大事典 Excel VBA 2019/2016/2013&Microsoft 365

Amazon（アマゾン）

3,690〜13,134円

別のショップのリンクを追加・編集

むっちゃ分厚いです。

ので、最初から全部読むことはしません。

最初に「VBAの概要」と「目次」をみて、あとは目的に応じてその部分だけ勉強していくって感じです。

上記の書籍は、さすがに「大辞典」というだけあって、基本的な内容は全て記載されています。(引数渡し、戻り値、構造体、列挙型などは載ってなさそうですが)

逆に、たくさん網羅しすぎて各々の内容が薄い。

基本的な操作は例文もきちんと書いてくれていて良いのですが、応用するためにはこの書籍だけでは無理があり、サイトとYoutubeで勉強しています。

なので、私は

①この書籍で目的に近い項目を探す　⇒　書籍に書かれているコマンドをサイトで調べて理解を深める

②何となく覚えているけど、サイトを探しても思っていたサイトや回答が見つからない　⇒　この書籍で基本構文を見直す

みたいな感じで、一応双方を活用するようにしていますが、今では、サイトを探す方が断然多いです。

エクセルVBAエキスパートの資格取得

VBAを勉強して1年半ぐらいたったころに、「せっかく勉強したんだから資格でも取っておこうか？」と思い、エクセルVBAエキスパート　の資格を取ることに決めました。

エクセルVBAエキスパートの資格には2種類あります。

①エクセルVBAエキスパート　ベーシック

②エクセルVBAエキスパート　スタンダード

①のベーシックの主な試験内容は基本的な内容です。

1. 　VBAの基本構文

2. 　簡単な関数

3. 　変数

4. 　セル、シート、ブックの操作

5. 　If、For　next、Withステートメント

等になります。

VBAエキスパート公式テキスト Excel VBAベーシック

Amazon（アマゾン）

3,037〜9,494円

別のショップのリンクを追加・編集

無料では、下記Youtubeが試験用として説明してくれているので助かりました。

【Excel VBAエキスパート・ベーシック試験 合格講座】①効率のよい学習法、マクロとVBAの概念マクロ・VBAのスキルを認定する資格試験「VBA Expert Basic」に最短合格するための講座です。重要ポイントに絞ってわかりやすく徹底解説します。Excel画面での操作やプログラミングの様子もしっかり確認できます。合格に欠かせない、模擬問題も用意しています。1問ずつ丁寧にわかりやすく解説していますので、動...

www.youtube.com

②のスタンダードが上位資格となり主な試験内容は下記内容になります。

1. 　Functionプロシージャなどのプロシージャ関連

2. 　変数の活用・配列

3. 　Select　Case、Do　Loop、For　Each等のステートメント

4. 　ワークシート関数

5. 　検索とオートフィルタ

6. 　データの並べ替え

7. 　テーブルの操作

8. 　エラー対策

等になります。

VBAエキスパート公式テキスト Excel VBAスタンダード

Amazon（アマゾン）

2,840〜9,570円

別のショップのリンクを追加・編集

ここで、私が言いたかったのは、試験項目に

図形やグラフの操作が無い

印刷やユーザーフォームが無い

代わりに、「大辞典」ではさらっと流されていた

検索とオートフィルタ

データの並べ替え

テーブルの操作

特にテーブル操作に至っては「構造化参照」という私にとってわけのわからない言葉まで出てくる始末です。

サイトや、販売されている書籍でも「過去問題」はなく、念のため上記の「公式テキスト」を購入して勉強しました。

というのも、言いたいことがもう一つ

1回の受験費が　約1万5千円　と非常に高い！！！

正直、資格を取るかどうか迷いましたが、今働いている会社ではこの資格の

・ベーシック取得・・・奨励金　3万円

・スタンダード取得・・・奨励金　5万円(但し、ベーシック取得後はその差額

2万のみ)

私にとってはチャレンジでした・・・

ベーシックの受験を飛ばして、直接スタンダードを受験。

・1発合格で　奨励金5万－受験費1万5千＝3万5千円　獲得！　

・2回目の合格　奨励金5万ー受験日1万5千✕2回＝　2万獲得！※但し、書籍代含めるとほぼ0円！

ここまでです。これ以上はマイナスになっていく・・・

でも私は頑張った・・・つもり。ぎりぎりの得点でしたが1発合格。

けど、資格の勉強して、テーブルの操作を覚えたのは私には目から鱗でした。

最近作成するデータベースのような、VBAではテーブル操作を無茶苦茶活用しています。

違う視点から勉強するのもありかも・・・という事でした。

まだまだ勉強不足もあるのも理解しながら、資格を持っているという自信もできたので、チャレンジしてよかったと思います。

という話でした。

後半、タイトルとは異なり自慢話のようになりすみません。

リンク：

公開日：2022年10月16日  

テキストデータの取り込み

今回は、VBAで「LineInput」を使ってテキストデータの取り込みマクロを書いてみました。

【テキストデータ(都道府県別データ)】

こんな感じで、都道府県別の人口や面積が記載されたテキストデータ(.txt)をエクセルに取り込みます。

各データの間は「,」で区切られています。

先ずは「,」区切りにせず1行ずつ取込む

【プロシージャ名】

Sub テキストデータ取り込み_LineInput()

【変数の宣言】

Dim ws1 As Worksheet　　⇐データ取り込み先のワークシート名

Dim FileNumber As Integer　　⇐「input」で使用するデータファイル番号
Dim InputData As String　　⇐テキストデータを1行ずつ取込むデータの保管用の変数

Dim i As Long　⇐繰り返し用のカウンタ変数

【本文】

・ws1変数に自身のワークブック⇒「データ」ワークシートを指定

Set ws1 = ThisWorkbook.Worksheets("データ")

・FileNumberをFreeFileとして番号付け

FileNumber = FreeFile

・エクセルと同じパスに「都道府県データ.txt」を置き、取り込むテキストファイルを指定

Open ThisWorkbook.Path & "\" & "都道府県データ.txt" For Input As #FileNumber

・「do　loop」にて1行ずつデータを取り込む

i = 1
Do Until EOF(FileNumber)　　⇐「EOF」=「end　of　file」でfileの最後まで実行
    Line Input #FileNumber, InputData

・「InputData」に保管されたデータをエクセルのセルに書き出します

    ws1.Cells(i, 1) = InputData

・カウンタ変数「i」に1を加えて、「do　loop」を回す

　 i = i + 1
Loop

・最後にテキストファイルを閉じて終了

Close #FileNumber
End Sub

これで、エクセルに1行ずつ取込めます。

が、折角各データが「,」区切りにされているので、「,」区切りで取り込みます。

「,」区切りでデータを取込む

【変数に下記を追加】

Dim temp() As String　　⇐1行を「,」区切りにしたデータの仮保管用の配列
Dim data As Variant　　⇐temp配列で「,」区切りに保存されたデータの取り出し(for　eachを回すためのvariant変数)

Dim j As Long　　⇐繰り返し用のカウンタ変数をもう一つ追加

【本文】

Set ws1 = ThisWorkbook.Worksheets("データ")

FileNumber = FreeFile

Open ThisWorkbook.Path & "\" & "都道府県データ.txt" For Input As #FileNumber

i = 1
Do Until EOF(FileNumber)　　⇐「EOF」=「end　of　file」でfileの最後まで実行
    Line Input #FileNumber, InputData

※ここまでは、「,」区切り無しと同じ処理

ここからSplit関数で「,」区切りにします。

・1行ずつ取込んだ「InputData」を「Split」関数にて「,」区切りにします

    temp = Split(InputData, ",")

・temp配列で「,」区切りにしたデータを「for　each」を使って、エクセルのセルに 書き出します

※temp配列が「0」番地から始まるので、「j」を「0」から始めています

    j = 0
    For Each data In temp
        ws1.Cells(i, j + 1) = temp(j)
        j = j + 1
    Next data

・カウンタ変数「i」に1を加えて、「do　loop」を回す
　 i = i + 1
Loop

・最後にテキストファイルを閉じて終了
Close #FileNumber
End Sub

以上で、取り込んだエクセルがこんな感じになります。

※因みにテキストで保存されているデータのエンコードが下のように「ANSI」以外(UTF-8等)になっていると、エクセルに取り込む際に文字化けします

今回は、テキストデータをエクセルに取り込むVBAを作成しました。

(CSVファイルも可能です)

自分の記録メインで、このブログを見て頂いている方には分かりづらい内容になっているかもしれませんがご容赦願います。

リンク：

公開日：2022年10月17日  

開始、終了処理

VBAで処理を実行するときに、いくつかの処理をする事があります。

①Application.ScreenUpdating

マクロの速度を上げるために画面を更新/更新しないようにする。

※マクロの処理過程は見えなくなりますが、実行速度が速くなる。

②Application.Cursor

マウスポインタの表示設定

・xlDefault：標準のポインター

・xlbeam：I字型ポインター

・xlNorthwestArrow：矢印型ポインター

・xlWait：砂時計型ポインター

③Application.Calculation

計算モードの設定

・xlCalculationAutomatic：エクセルが自動で再計算

・xlCalculationManual：ユーザー要求で計算開始

・xlCalculationsemiautomatic：エクセルが自動で再計算するが、テーブル内の変更は無視

④Application.DisplayAlerts

マクロ実行中の特定の警告やメッセージを表示/非表示にする。

※ブックを保存するときの「SaveAs」メソッドで既存のファイルを上書きするときに「名前を付けて保存」ダイアログが

・DisplAlerts=True　⇒　デフォルトが「いいえ」

・DisplAlerts=False　⇒　デフォルトが「はい」

になるので注意が必要

⑤Application.EnableEvent

ブックやシートに対して指定したイベントを実行/停止ができる

※イベントプロシージャを使用していないと関係ありません

他にもあるかもしれませんが、上記が代表的な処理と思います。

【マクロ開始時】

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Cursor = xlWait
'Application.Calculation = xlCalculationManual
'Application.EnableEvent = False

【マクロ終了時】

Application.ScreenUpdating = True
Application.Cursor = xlDefault
Application.Calculation = xlCalculationAutomatic

'Application.DisplayAlerts = True
'Application.EnableEvent = True

※DisplayAlertsとEnableEventは、マクロ開始、終了時でなく、適時プログラム中に

True/Falseで実行/無効をしてよいと思うので、最初に「'」を付けてコメントアウトにしています。

今回はマクロ作成時の開始、終了時の処理について記載しました。

リンク

公開日：2023年3月30日

Chat GPT × Excel VBA

やっと長く続けていた「画像編集」の勉強がひと段落したので、これからは最近すごく気になっている「Chat GPT」を使っていろいろやっていきたいと思っていいます。

まずは私自身がある程度認知のある「Excel VBA」を復習も兼ねて「Chat GPT」を使ってプログラムを作成していきたいと思います。 

ファイル操作 1(ファイル、フォルダの作成)

今回は「ファイル操作 1」として、ファイルやフォルダの作成をしていきたいと思います。

ファイルの作成

ファイル作成のプログラムを作成する

①デスクトップにフォルダーを作成。

※作成したファイルを保存する場所を作成。他の任意の場所でもOKです。

②保存先のパスを取得する。

作成したファイルを、①で作成したフォルダに保存しますが、VBAでも保存先の指定が必要なので、フォルダのパスを取得します。

・フォルダアイコンの上で右クリック⇒「パスのコピー」をクリック。

これで、クリップボードにパスが保存されます。

③VBAを実行するエクセルファイルを作成する。

※ファイルを保存する場合は拡張子を「"Excelマクロ有効ブック(*.xlsm)"」で保存してください。

下のように

・1行目に作成したファイルを保存するパスを貼り付け。

　　先ほど「パスのコピー」したので、そのままセルB1に貼り付けでOKです。

・作成するファイル名をセルA4からA8に記入しました。

④ChatGPTを実行する。

今回は下のように

「エクセルVBAで下のプログラムを作成してください。

セルB1に記載されたパスに、セルA4からA8までの名前のエクセルファイルを作成してください」

と依頼。

・すると下のようにウインドウでVBAプログラムが作成され、その下に注意点等も教えてくれました。

また、このプログラムをコピペする場合は、ウインドウの右上に「Copy　code」と記載されているので、ここをクリックするとプログラムが簡単にコピーできます。

⑤VBE(VisualBasicEditor)にChatGPTで作成してもらったプログラムをコピペする。

・まず、新しいエクセルファイルなので、

メニューバー⇒「挿入」⇒「標準モジュール」をクリックして「標準モジュール」を作成。

・作成した標準モジュールに「Copy　code」でコピーしたプログラムを貼り付ける。

⑥何も考えずにそのまま「実行」。

実行はVBE上で、三角の実行アイコンで実施しました。

「Excelファイルが保存されました」のメッセージが表示されました。

また、フォルダ内を見るときちんとエクセルファイルが作成されました。

A列に記載した最終行を自動取得する

①ChatGPTに追加でファイル名の最終行を自動取得してもらうように依頼。

先ほどのプログラムではファイル名を「A4からA8に記載された名前」と指定しましたが、次は範囲を指定せずに「A4からA列の文字が記入されている最終行を取得して・・・」と追加で依頼。

※ChatGPTでは会話中の内容であれば、少し前の内容についても追加でチャットすることができます。

先ほどのプログラムに今回依頼した「最終行を取得」という項目を追加したプログラムが作成されました。

【実際のプログラム】

Sub CreateExcelFiles()
    Dim path As String
    Dim name As String
    Dim lastRow As Long
    Dim i As Long
   
    '保存先のパスを取得
    path = Range("B1").Value
   
    '最終行を取得
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

👆ここに「最終行の取得」のプログラムが追加されました。
   
    '各ファイルを作成
    For i = 4 To lastRow
        name = Range("A" & i).Value & ".xlsx"
        ThisWorkbook.SaveCopyAs path & "\" & name
    Next i
   
    MsgBox "Excelファイルが保存されました。"
End Sub

②プログラムを実行。

・先ほどと同じようにVBEにプログラムをコピペ。

※フォルダ内に先ほど作成したファイルが残っているので、削除しておきました。

・VBAを実行。

※先ほどはセルA8まででしたが、A10まで名前を増やして実行。

問題なく、フォルダ内にセルA10「見積書7」のファイルまで作成されました。

空きセル時のスキップを追加

①ファイル名のセルに空白セルがある場合を確認。

上のプログラムを見ると「最終行の取得」コマンドが、

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

なので、

A列の最大行から"xlup"で上に最初に文字が入力されたセルを「最終行」としています。

なので、途中のセル(A8)を空白にしてVBAを実行してみました。

下のように空白セルはファイル名無しで「.xlsx」だけでファイル保存されてしまいました。

②ChatGPTに追加で依頼。

今度はChatGPTに「ファイル名が空白の場合はスキップ」するように依頼。

またまた、同じプログラムに下の「空白の場合はスキップ」の内容が追加されたプログラムが出力されました。

【追加部分を抜粋】

    '各ファイルを作成
    For i = 4 To lastRow
        name = Range("A" & i).Value & ".xlsx"
       If name <> ".xlsx" Then

            ThisWorkbook.SaveCopyAs path & "\" & name
        End If
    Next i

👆赤字の部分で".xlsx"になっているものは次の処理がされないようにif文が追加されてます。

実行結果は下のようになり、空白セルがスキップされました。

パスが存在しない(間違っているとき)の処理を追加

①パス名を間違えた場合を想定する。

次は、パスが間違っている時(フォルダーが無いとき等)にエラーが出ないように依頼しました。

先ほどのプログラムで、パス名を間違えていると下のようにVBAのエラーが発生します。

※パスのフォルダ名の「\ChatGPT_ExcelVBA練習フォルダ」で、最後の「フォルダ」を「フォルダー」と最後に伸ばし棒「－」を追加しました。

下のようにVBAのエラーが表示されました。

②もう一度ChatGPTに「パスが無いときなどのエラーが発生した時のプログラムを追加してください」と依頼。

③やはり、先ほどまでと同じプログラムを使用して、下のプログラムが追加されました。

【追加部分を抜粋】

    'パスが存在しない場合はエラー処理
   If Dir(path, vbDirectory) = "" Then

👆パスのフォルダを取得しようとしたが ""で取得できなかった時に下の処理を実行。

        MsgBox "指定されたパスが存在しません。"
        Exit Sub
    End If

これで、パスが取得できなかった時に"指定されたパスが存在しません"というメッセージが出て、「End Sub」で処理終了になります。

きちんとメッセージが表示されました。

エラー処理とクレンジングのプログラムを追加してもらう

上では個別で、空白セルやパスの間違い時の対応を依頼しましたが、今度はまとめて

「エラー対応とクレンジングのプログラムを追加してください」

と依頼しました。

【実際のプログラム】

Sub CreateFiles()
    Dim path As String
    Dim fileName As String
    Dim lastRow As Long
    Dim i As Long
   
    '保存先のパスを取得
    path = Trim(Range("B1").Value)
   
    'パスが存在しない場合はエラー処理
    If Len(path) = 0 Then
        MsgBox "保存先のパスが入力されていません。"
        Exit Sub
    ElseIf Dir(path, vbDirectory) = "" Then
        MsgBox "指定されたパスが存在しません。"
        Exit Sub
    End If
   
    '最終行を取得
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    '各ファイルを作成
    For i = 4 To lastRow
        fileName = Trim(Range("A" & i).Value)
        If Len(fileName) > 0 Then
            fileName = Replace(fileName, "/", "")
            fileName = Replace(fileName, "\", "")
            fileName = Replace(fileName, ":", "")
            fileName = Replace(fileName, "*", "")
            fileName = Replace(fileName, "?", "")
            fileName = Replace(fileName, "<", "")
            fileName = Replace(fileName, ">", "")
            fileName = Replace(fileName, "|", "")
           
            On Error Resume Next
            ThisWorkbook.SaveCopyAs path & "\" & fileName & ".xlsx"
            If Err.Number <> 0 Then
                MsgBox "ファイル " & fileName & " の作成に失敗しました。"
            End If
            On Error GoTo 0
        End If
    Next i
   
    MsgBox "ファイルが作成されました。"
End Sub

青文字が変更された箇所ですが、下の内容が反映されています。

・パスが未入力時のメッセージ追加。

・ファイル名に使用不可文字があった場合はその文字を削除。

・「空白セルのスキップ」については、ファイル名の長さが「0(空白)」以外の時だけ処理を続けるように変更。

・あと「パス名」と「ファイル名」の変数入力に「Trim」を追加して名前の先頭と末尾に半角スペースがあった場合は削除するように変更されてます。

フォルダの作成

今度は、エクセルファイルではなく、フォルダを作成できるかを確認しました。

①下のようにエクセルのセルC4からC8にフォルダ名を追記しました。

②ChatGPTに依頼する。

ChatGPTにはその前の会話も反映されるため、最初から依頼するのではなく、今までのチャットに続けて下のように

「同じように今度はセルC4からC列に記載された名前でフォルダを作成してください」

と依頼しました。

※「同じように」ということで、パス名を記載したセルの位置や、最終行などの言葉は入れていません。

③VBAをコピペ。

先ほどファイル作成で作成したVBA(プロシージャ)の下に今回のプログラムをコピペしました。

※「Sub ****()」 のプロシージャー名から「End Sub」まで記載してくれているので そのままコピペするだけで下のように、ファイル作成プロシージャとフォルダ作成プロシージャが分けられます。

【実際のプログラム】

Sub CreateFolders()
    Dim path As String
    Dim folderName As String
    Dim lastRow As Long
    Dim i As Long
   
    '保存先のパスを取得
    path = Range("B1").Value
   
    'パスが存在しない場合はエラー処理
    If Dir(path, vbDirectory) = "" Then
        MsgBox "指定されたパスが存在しません。"
        Exit Sub
    End If
   
    '最終行を取得
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
   
    '各フォルダを作成
    For i = 4 To lastRow
        folderName = Range("C" & i).Value
        If folderName <> "" Then    👈ここで空白セルを判断

            On Error Resume Next     👈ここでエラーを無効に設定

            MkDir path & "\" & folderName 

          👆フォルダ名が空白だとエラー発生・・・でも無視して次の行へ

           If Err.Number <> 0 Then    👈エラー発生時の処理追加
                MsgBox "フォルダ " & folderName & " の作成に失敗しました。"
            End If

            On Error GoTo 0    👈エラーを有効設定に戻す
        End If
    Next i
   
    MsgBox "フォルダが作成されました。"
End Sub

上のように、

1.「最終行の取得」

2.「空白セルのスキップ」 というか、「エラー無効設定」⇒「フォルダ名が無いためエラー発生」⇒「青字部分 でエラー処理」というプログラムのようです。

3.「パス名の間違い」

の全てが対応されています

④プログラム実行。

「フォルダが作成されました」のメッセージが表示され、下のようにフォルダも問題なく作成できました。

エラー処理とクレンジングのプログラムを追加してもらう

こちらも同様に

「エラー処理とクレンジングのプラグラム」

も追加してもらうように依頼しました。

【実際のプログラム】

Sub CreateFolders()
    Dim path As String
    Dim folderName As String
    Dim lastRow As Long
    Dim i As Long
   
    '保存先のパスを取得
    path = Trim(Range("B1").Value)
   
    'パスが存在しない場合はエラー処理
    If Len(path) = 0 Then
        MsgBox "保存先のパスが入力されていません。"
        Exit Sub
    ElseIf Dir(path, vbDirectory) = "" Then
        MsgBox "指定されたパスが存在しません。"
        Exit Sub
    End If
   
    '最終行を取得
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
   
    '各フォルダを作成
    For i = 4 To lastRow
        folderName = Trim(Range("C" & i).Value)
        If Len(folderName) > 0 Then
            folderName = Replace(folderName, "/", "")
            folderName = Replace(folderName, "\", "")
            folderName = Replace(folderName, ":", "")
            folderName = Replace(folderName, "*", "")
            folderName = Replace(folderName, "?", "")
            folderName = Replace(folderName, "<", "")
            folderName = Replace(folderName, ">", "")
            folderName = Replace(folderName, "|", "")
           
            On Error Resume Next
            MkDir path & "\" & folderName
            If Err.Number <> 0 Then
                MsgBox "フォルダ " & folderName & " の作成に失敗しました。"
            End If
            On Error GoTo 0
        End If
    Next i
   
    MsgBox "フォルダが作成されました。"
End Sub

こちらも青文字が変更された箇所ですが、下の内容が反映されています。

・パスが未入力時のメッセージ追加

・ファイル名に使用不可文字があった場合はその文字を削除

・あと「パス名」と「ファイル名」の変数入力に「Trim」を追加して名前の先頭と末尾に半角スペースがあった場合は削除するように変更されてます。

マクロ付エクセルファイル保存時の注意点

注意点として、せっかく上記で作成したVBAを含むエクセルファイルを保存する場合は、必ず拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えてしまいますので要注意です。

作成したVBA付きエクセルファイルは、拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えるのでご注意ください。

あとがき

今回は、

「Chat GPT×エクセルVBA ＿ファイル操作 1(ファイル、フォルダの作成)」

として、ChatGPTでファイル作成、フォルダ作成のプログラムを作成しました。

結果として、依頼内容をきちんと伝えれば、問題なくVBAプログラムが作成できました。

今回のファイル作成は空のエクセルファイルなので、

次は「ファイル操作 2」として

・ダミーの在庫表フォームを作成

・月度ごとにファイル名を付けて保存する

ような処理ができるか試していきたいと思います。

リンク

公開日：2023年3月31日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 2(在庫表フォーム作成、ファイル名を付けて保存)

今回は「ファイル操作 2」として、在庫表フォームをVBAで作成するプログラムの作成と、その在庫表のファイル名を日付で保存する方法を試しました。

在庫表フォームの作成

ChatGPTに基本的な在庫表フォームを作成するプログラムを作成してもらう

①まずは、またまたChatGPTに下の依頼をチャットしました。

「エクセルVBAで次の項目に対応した基本的な在庫表フォームを作成するプログラムを作成してください。

また、エラー処理とクレンジングのプログラムを入れてください

月度と作成日

分類

商品名

原価

売価

在庫数」

②作成してもらったプログラムをVBEでモジュールにコピペして、実行。

上のように列幅が小さく「作成日」が「#####」表示になっています。

③列幅を設定するプログラムに変更してもらう。

再度ChatGPTに、下の依頼をしました。

「上のプログラムでAからFの列幅を25にしてください」

これで下のように列幅も見やすくなりました。

プログラムにシートの数を確認してシートの最後に「在庫表」というシートを作成するようになっています。

【実際のプログラム】

Sub CreateInventoryForm()
   
    On Error GoTo ErrorHandler

👆エラーが発生した場合は、最下部の「ErrorHandler:」に飛んでメッセージ表示するようになっています。
   
    ' シートの設定
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "在庫表"

👆ここで、現在のエクセルファイルの最後にシートを「在庫表」として作成。
   
    ' タイトルの設定
    With ws.Range("A1:F1")
        .Merge　　　　　　　⇐セルの結合
        .Value = "在庫表"　　⇐セルの値
        .Font.Size = 20　　　⇐フォントサイズ
        .Font.Bold = True　　⇐太字
        .HorizontalAlignment = xlCenter　　⇐中央揃え
    End With

    
    ' 月度と作成日の設定
    With ws.Range("A3:B3")
        .Merge
        .Value = "月度"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With

👆こちらもタイトルの設定と同様に、「月度」の「セルの結合」「フォントの設定」「中央揃え」が設定されています。

    With ws.Range("C3:D3")
        .Merge
        .Value = "作成日"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With

👆こちらも同様に「作成日」の設定がされています。
   
    ' 入力欄の設定
    With ws.Range("A4:B4")
        .Merge
        .Value = Format(Date, "yyyy/mm")
        .HorizontalAlignment = xlCenter
    End With

👆「月度」を「年/月」表示に設定。

    With ws.Range("C4:D4")
        .Merge
        .Value = Format(Now, "yyyy/mm/dd hh:mm:ss")
        .HorizontalAlignment = xlCenter
    End With

👆「作成日」を「年/月/日　時:分:秒」表示に設定。

    With ws.Range("A6:F6")
        .Value = Array("分類", "商品名", "原価", "売価", "在庫数", "")

👆多分ChatGPT依頼時に最後にスペースが入力されていたと思われ、最後に""の列も作成されています。

実際の「在庫表」も一番右の列が「列1」と表示されています。

ですので、ここの「,""」を消せば不要な「列1」は削除できます。

        .Font.Bold = True
        .HorizontalAlignment = xlCenter

👆「フォント」と「中央揃え」の設定です。

        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlMedium
    End With

👆「罫線」の設定です。

   
    ' セルの幅と高さの設定
    With ws.Columns("A:F")
        .ColumnWidth = 25
    End With

👆ここで、依頼した「列幅」を「25」に設定してくれています。

    With ws.Cells
        .EntireRow.AutoFit
        .Rows("6:6").RowHeight = 30
    End With

👆シートの行全体の「行高さ」を「自動調整」に設定、6行目のタイトル行のみ「行高さ」を「30」に設定。
   
    ' データ入力範囲の設定
    With ws.ListObjects.Add(xlSrcRange, ws.Range("A6:F6"), , xlYes)
        .Name = "在庫表"
        .TableStyle = "TableStyleMedium2"
    End With

👆テーブルを追加。

「xlSrcRange」：テーブル作成を範囲に設定。

「 ws.Range("A6:F6")」：テーブル範囲の設定。

「xlYes」：テーブルの1行目をタイトル行に設定。

後は、テーブルに名前を付けて、テーブルのスタイルを設定しています。
   
    ' クリーンアップ
    Set ws = Nothing

👆Setした変数をクリア。

プログラムが終了すると自動的にクリアされるので、無くても良いコマンドですが、クリアのプログラムを入れるのが推奨されていますね。
   
    Exit Sub

👆次の行からエラー発生時の処理になるので、「Exit　Sub」で"Subプロシージャから抜ける"ということで「End　Sub」と同じようにプログラム終了になります。
   
ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description
👆エラー発生時に、メッセージとエラー情報が表示されます。

    
End Sub

自分で作成となると、わかっていても30分くらいはかかると思いますが、ChatGPTなら3分もあれば上のようなプログラムができます。

在庫表のダミーデータを作成してもらう

VBAプログラムだけでなく、せっかく「在庫表フォーム」を作成したので、ダミーのデータも作成してもらいました。

①ChatGPTにダミーデータを一覧で作成してもらうように依頼。

「上で作成した在庫表に入力するダミーデータを文房具で20項目一覧で作成してください」

下のようにダミーデータも普通に作成してくれます。

こちらのダミーデータを「在庫表」に普通にコピペ。

こんな感じの「在庫表」を作成することができました。

「在庫表」+ 作成日 のファイル名で保存

次は、作成した在庫表を「在庫表」+作成日のファイル名で保存するプログラムを作成。

①まず保存先のフォルダパスをセルI1に入力。

①ChatGPTに

「上の在庫表を「在庫表」&作成日のファイル名でセルI1に書かれたフォルダにマクロ有効ブックとして保存するプログラムを作成してください」

と依頼。

※今回はVBAを書いているこのファイルを「名前を付けて保存」するため、ファイルの拡張子も「マクロ有効ブック(.xlsm)」でお願いしました。

【実際のプログラム】

Sub SaveInventoryTable()
    ' ファイル名を作成する
    Dim fileName As String
    fileName = "在庫表" & Format(Date, "yyyymmdd") & ".xlsm"

👆ファイル名を「在庫表」+「年月日」で作成。
   
    ' フォルダを指定する
    Dim folderPath As String
    folderPath = Range("I1").Value
   
    ' ファイルを保存する
    ActiveWorkbook.SaveAs folderPath & "\" & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

👆「FileFormat」でマクロ有効ブックを指定。
   
    ' 保存完了メッセージを表示する
    MsgBox "在庫表を保存しました。", vbInformation
End Sub

「在庫表」+ 月度 のファイル名で保存

今度は、作成した在庫表を「在庫表」+月度のファイル名で保存するプログラムを作成。

①チャットの続きなので、パスの場所は指定しなくても覚えてくれています。

「同じように「在庫表」&月度のファイル名でマクロ有効ブックとして保存するプログラムを作成してください」

と依頼。

【実際のプログラム】

Sub SaveInventoryTable()
    ' ファイル名を作成する
    Dim fileName As String
    fileName = "在庫表" & Format(Date, "yyyymm") & ".xlsm"
👆ファイル名を「在庫表」+「年月」で作成。   

    ' フォルダを指定する
    Dim folderPath As String
    folderPath = Range("I1").Value
   
    ' ファイルを保存する
    ActiveWorkbook.SaveAs folderPath & "\" & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
👆「FileFormat」でマクロ有効ブックを指定。

    
    ' 保存完了メッセージを表示する
    MsgBox "在庫表を保存しました。", vbInformation
End Sub

上のどちらの保存プログラムも問題なく保存できました。

マクロ付エクセルファイル保存時の注意点

注意点として、せっかく上記で作成したVBAを含むエクセルファイルを保存する場合は、必ず拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えてしまいますので要注意です。

作成したVBA付きエクセルファイルは、拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えるのでご注意ください。

あとがき

今回はChatGPTで、

・基本的な在庫表フォームを作成するプログラム

・在庫表を、ファイル名に作成日もしくは月度を付けて保存するプログラム

を作成しました。

また、在庫表ではダミーデータもChatGPTに作成してもらいました。

続いて、月度ごとにファイル保存する方法も試したかったのですが、今回は長くなるため次回に試してみます。

リンク

公開日：2023年4月1日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 3(在庫表を月毎に保存)

前回は「ファイル操作 2」として、「在庫表」フォームをVBAで作成するプログラムの作成と、その「在庫表」のファイル名を日付で保存する方法を試しました。

今回は、「在庫表」を月毎に複数保存するプログラムを作成します。

「在庫表」ではあまりないかもしれませんが、「計画表」などはあらかじめ何か月分か先のファイルを作成したりすることがあると思うので同じような内容で使えると思います。

在庫表を月毎に保存

前回試した保存は、開いているエクセルファイルを「作成日」もしくは「月度」で「名前を付けて保存」するので、開いているファイルしか保存できませんでした。

今回は、下のようにセルA4からA9に書かれている値をもとに、

1.「在庫表」の「月度」をセルA4以降に入力

2.「在庫表_」+1.で取得した値(月度)でファイル名を作成する

3.A列にデータのある分、全てファイルを作成する

を試してみます。

①ChatGPTに下のように依頼。

※最初うまくチャットの文章を書くことができず、意図したプログラムが作成されませんでした。

ChatGPTで意図した回答が無い場合は、すでに質問済みの内容であっても、質問済みの右側にあるアイコンを押すことで、質問を編集することが可能です。

ChatGPTで意図した回答をもらうには、質問も的確に書く必要があることを痛感しました。

質問内容を修整して、「Save & Submit」をクリックすると、再度新しい回答をしてくれます。

結果、下のようにまとめてファイルを作成できるプログラムが作成できました。

ちなみに「在庫表_1月」のファイルを開くと、「月度」が1月になってます。

同様に「在庫表_6月」のファイルを開くと、「月度」が6月になってます。

【実際のプログラム】

Sub SaveInventoryTable()
    ' シート1から値を取得する
    Dim sheet1 As Worksheet
    Set sheet1 = ThisWorkbook.Sheets("Sheet1")
    Dim lastRow As Long
    lastRow = sheet1.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
    Dim title As String
    title = sheet1.Range("A4").Value
   
    ' 在庫表シートに値をセットする
    Dim inventorySheet As Worksheet
    Set inventorySheet = ThisWorkbook.Sheets("在庫表")
    inventorySheet.Range("A4").Value = title
   
    ' ファイル名を作成する
    Dim fileName As String
    fileName = "在庫表_" & title & ".xlsm"
   
    ' フォルダを指定する
    Dim folderPath As String
    folderPath = Range("I1").Value
   
    ' ファイルを保存する
    ActiveWorkbook.SaveAs folderPath & "\" & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
    ' 保存完了メッセージを表示する
    MsgBox "在庫表を保存しました。", vbInformation

👆なぜか最初のファイル名は上で処理して、下の繰り返しプログラムはセルA5から始まっています。

なので、上の赤字部分を消して、下の "For i = 5 To lastRow"を" For i = 4 To lastRow"にしてもきちんと動作しました。

    
    ' A列に値がある場合に繰り返す
    Dim i As Long
    For i = 5 To lastRow

👆i=行番号なので、セルA5から繰り返し

        ' シート1から値を取得する
        title = sheet1.Cells(i, "A").Value
       
        ' 在庫表シートに値をセットする
        inventorySheet.Range("A4").Value = title
       
        ' ファイル名を作成する
        fileName = "在庫表_" & title & ".xlsm"
       
        ' ファイルを保存する
        ActiveWorkbook.SaveAs folderPath & "\" & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
       
        ' 保存完了メッセージを表示する
        MsgBox "在庫表を保存しました。", vbInformation

👆ここにメッセージがあるとファイルを保存するたびにメッセージが表示されるので、次の「Next i」の後ろに移動したほうが良いかもしれません。

    Next i

End Sub

マクロ付でない新しいエクセルファイルを作成して保存する

上のプログラムでは、マクロ実行に使用しているエクセルファイルをそのまま「名前を付けて保存」していますが、場合によってはマクロが不要な時があると思います。

今回は、マクロを実行するファイルを「実行ファイル」として、作成する「在庫表」ファイルは、新しいファイルで「在庫表」のシートを作成して保存する方法を試してみました。

①ChatGPTに依頼。

正直、今回は何度も質問を作り直しました。

文章力が無いので、意図したとおりのプログラムが作成できませんでしたが、最終的には箇条書きで依頼内容を書き、何とか意図したプログラムになりました。

ChatGPTで便利になるとは言え、適切に伝える文章力は大切です。

②プログラムをVBAにコピペして実行。

下のように、拡張子「.xlsx」で1月から6月までファイルを作成することができました。

・1月のファイルを確認

「月度」が「1月」になっており、シートも「在庫表」シートのみになってます。

・6月のファイルを確認

同様に「月度」が「6月」になって、シートも「在庫表」シートのみになっています。

【実際のプログラム】

Sub SaveInventoryFiles()

    Dim curSheet As Worksheet
    Dim invSheet As Worksheet
    Dim curPath As String
    Dim invName As String
    Dim newBook As Workbook
   
    Set curSheet = ThisWorkbook.Sheets("Sheet1")
    Set invSheet = ThisWorkbook.Sheets("在庫表")
    curPath = invSheet.Range("I1").Value
   
    'Sheet1のA列をループして処理を繰り返す
    Dim curRow As Long
    curRow = 4
    Do While curSheet.Range("A" & curRow).Value <> ""
  👆今回のプログラムは「curRow」が行番号で、セルA列の4行目から繰り返し処理をするようになってます。

      
        '在庫表シートにSheet1の値を貼り付ける
        invSheet.Range("A4").Value = curSheet.Range("A" & curRow).Value
       
        '新しいブックを作成する
        Set newBook = Workbooks.Add
       
        '新しいブックの最初のシート名を変更する
        newBook.Sheets(1).Name = "在庫表"
       
        '在庫表シートの内容をコピーして新しいブックに貼り付ける
        invSheet.Cells.Copy Destination:=newBook.Sheets(1).Range("A1")
       
        'ファイル名を設定して保存する
        invName = "在庫表_" & invSheet.Range("A4").Value & ".xlsx"
        newBook.SaveAs curPath & "\" & invName
       
        '新しいブックを閉じる
        newBook.Close
       
        '次の行に進む
        curRow = curRow + 1
    Loop

        ' 保存完了メッセージを表示する
        MsgBox "在庫表を保存しました。", vbInformation

👆上の赤字はChatGPTのプログラムにはありませんでした。

この状態だとプログラム終了のメッセージが無いので、ここにMsgBoxコマンドを追加したほうが良いと思います。
   
End Sub

マクロ付エクセルファイル保存時の注意点

注意点として、せっかく上記で作成したVBAを含むエクセルファイルを保存する場合は、必ず拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えてしまいますので要注意です。

作成したVBA付きエクセルファイルは、拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えるのでご注意ください。

あとがき

今回はChatGPTで、月毎にファイルを保存するプログラムを作成しました。

感想としては、「意図したプログラムを作成するには依頼する文章も大切」ということです。

特に、依頼内容が複雑になれば伝えにくいところがあるのですが、最悪箇条書きでも対応してくれます。

次は、一旦「在庫表」から離れて、「ファイル操作_4」として

・フォルダ内のファイル、フォルダの一覧を取得する

方法について試してみます

リンク

公開日：2023年4月2日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 4(ファイル、フォルダー一覧の取得)

今回は、フォルダー内のファイルやフォルダーの一覧を取得するプログラムを作成していきます。

内容として、

1.指定した階層の一覧のみ取得

2.指定した階層から下の階層のファイル、フォルダ一覧も取得

があると思いますので、この2つの方法を試していきます。

指定した階層の一覧のみ取得

①ChatGPTに下記内容でVBA作成を依頼。

「セルB1に記載されたパス内のファイルおよびフォルダの一覧を取得して、セルE4からE列に書き出すVBAを作成してください」

②作成されたプログラムをVBEに貼り付け実行する。

今回ChatGPTで作成してもらったVBAではすぐには動作せずエラーが発生しました。

●エラー内容

・作成されたVBAには、FSO(FileSystemObject)と言うファイル操作用のオブジェクトが使われていますが、今回FSOを使用する準備をしていなかったため、VBAエラーとなりました。

FSOを使えるようにする

ChatGPTにFSOを使用する方法を聞いていみました。

結果

1. VBEを開きます。

2. ツールメニューをクリックし、「参照設定」を選択します。

3. 「参照設定」ダイアログボックスが表示されます。リスト内で、Microsoft Scripting Runtimeにチェックを入れます。

4. OKをクリックしてダイアログボックスを閉じます。

の回答があり、実施することでFSOのエラーは問題なし。

ChatGPTも間違える？

今回もう一つVBAエラーが発生しました。

●エラー内容

「変数が定義されていません」

プログラムを確認してみると 「For Each」で使用している格納用変数が定義されていませんでした。

今回初めてChatGPTで簡単なミスが発生。ChatGPTでもこんなミスがあるんだと気づきました。

再度下記内容で依頼し、修正してもらってきちんと動作するようになりました。

③プログラム実行結果。

下のように、指定したセルE4以降にファイルおよびフォルダの一覧が表示されました。

実際のフォルダ内も下の通りなので一覧としては問題ありません。

ただし、この中の各フォルダの下の階層には下のように更にファイルとフォルダが存在するのですが、一覧表示されませんでした。

今回特に「指定したフォルダの階層のみ」という指定はしなかったのですが、結果的に下の階層までの一覧表示ができないプログラムのようです。

実際のVBAの内容を確認していきます。

【実際のプログラム】

Sub ListFilesAndFolders()

    ' 宣言
    Dim myfso As New FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim objSubFolder As Folder
    Dim i As Integer
   
    ' セル範囲
    Dim startCell As Range
    Dim endCell As Range
   
    ' パス
    Dim path As String
👆上は全て変数宣言です。 

    ' パス取得
    path = Range("B1").Value

👆一覧取得したいパスの指定。
   
    ' セル範囲取得
    Set startCell = Range("E4")
    Set endCell = startCell.End(xlDown)

👆一覧表示するセルの設定。「startCell」のセルE4から「xlDown」で入力されているE列の最終セルを取得してくれるプログラムになっていますが・・・

プログラムにこの変数が使用されていないので、不要な宣言です。

    ' フォルダを取得
    Set objFolder = myfso.GetFolder(path)

👆ここでFSOの「GetFolder」を使用しています。
   
    ' ファイルをループ処理
    For Each objFile In objFolder.Files
        i =i+1
        Range("E" & (i + 3)).Value = objFile.name

👆このプログラムでは「i=i+1」が先に来ています。

「i=0」にして「Range("E" & (i + 4)).Value = objFile.Name」の後に「i=i+1」と入れたほうがセルE4から始まっているのがわかりやすいと思いますが・・・

この方法でも問題なく動作はしました。

    Next objFile
   
    ' フォルダをループ処理
    For Each objSubFolder In objFolder.SubFolders
        i = i + 1
        Range("E" & (i + 3)).Value = objSubFolder.Name & " (folder)"
    Next objSubFolder

👆「ファイルをループ処理」と同じですが、先に「i=0」を入れてしまうとフォルダもまたE4セルから上書きされてしまいます。

「i」の値は「ファイルをループ処理」の続きの値が必要なので、最初の「i=i+1」を消して 「Range("E" & (i + 4)).Value = objSubFolder.Name & " (folder)"」の後に「i=i+1」でも動作します。
   
    ' メッセージ
    MsgBox "ファイルおよびフォルダの一覧を取得しました。"
   
End Sub

指定した階層のファイル、フォルダしか取得できなかった理由

FSOの「GetFolder(path)」が指定した階層のファイル、フォルダしか取得できないので今回の結果となっています。

下の階層まで一覧表示する場合は「回帰」するようなプログラムで、取得したフォルダ名で再度「GetFolder(回帰したフォルダ名)」の処理するようなプログラムを追加する必要があると思います。

指定した階層から下の階層も取得

①ChatGPTに下のように依頼する。

②作成されたプログラムをVBEに貼り付け実行する。

今回は問題なく実行できました。

③プログラム実行結果。

下のように、指定したセルE4以降に下の階層までのファイルおよびフォルダの一覧が表示されました。(確認しましたが全てのファイル、フォルダーが取得できています)

ただし、全てにパスが表示されているので見にくいです。

これもChatGPTに依頼することもできますし、エクセル操作でパスを空白に「置換」する方法でもパスを簡単に消すことは可能です。

【実際のプログラム】

Sub ListAllFilesAndFolders()

    ' 宣言
    Dim myfso As New FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim objSubFolder As Folder
    Dim i As Integer
   
    ' セル範囲
    Dim startCell As Range
    Dim endCell As Range
   
    ' パス
    Dim path As String
   
    ' パス取得
    path = Range("B1").Value
   
    ' セル範囲取得
    Set startCell = Range("E4")
    Set endCell = startCell.End(xlDown)
   
    ' フォルダを取得
    Set objFolder = myfso.GetFolder(path)
   
    ' ファイルをループ処理
    For Each objFile In objFolder.Files
        i = i + 1
        Range("E" & (i + 3)).Value = objFile.Path
    Next objFile
   
    ' サブフォルダをループ処理
    For Each objSubFolder In objFolder.SubFolders

👆「objSubFolder」はパスで指定した階層の中にあるフォルダ名(サブフォルダ)を順に格納されます。

        i = i + 1
        Range("E" & (i + 3)).Value = objSubFolder.Path & " (folder)"
       
        ' サブフォルダ内のファイルを取得
        For Each objFile In objSubFolder.Files
            i = i + 1
            Range("E" & (i + 3)).Value = objFile.Path
        Next objFile
       
        ' サブフォルダ内のサブフォルダを取得
        ListAllSubFolders objSubFolder, i

👆引数渡しで下にある別のSubプロシージャを実行します。

「Call ListAllSubFolders(objSubFolder, i)」と同じ意味です。

渡す引数は、

「objSubFolder」・・・下の階層のフォルダ名

「i」・・・行番号に使用する番号

で、下の階層のフォルダ名を別のプロシージャに渡して、別のプロシージャで同じようにそのフォルダ内ののファイル、フォルダ名を取得します。

「回帰」処理を使うために別の引数渡しのプロシージャーを作成しています。
       
    Next objSubFolder
   
    ' メッセージ
    MsgBox "ファイルおよびフォルダの一覧を取得しました。"
   
End Sub

「回帰」処理するプロシージャ。引数渡しで以下を実行

《A》

Sub ListAllSubFolders(ByRef objFolder As Folder, ByRef i As Integer)

    ' 宣言
    Dim objSubFolder As Folder
    Dim objFile As File
   
    ' サブフォルダをループ処理
    For Each objSubFolder In objFolder.SubFolders
        i = i + 1
        Range("E" & (i + 3)).Value = objSubFolder.Path & " (folder)"
       
        ' サブフォルダ内のファイルを取得
        For Each objFile In objSubFolder.Files
            i = i + 1
            Range("E" & (i + 3)).Value = objFile.Path
        Next objFile
       
        ' サブフォルダ内のサブフォルダを取得
        ListAllSubFolders objSubFolder, i  👈フォルダがなくなるまで《A》に戻って繰り返します。

👆ここが、「回帰」の処理になります。

自身のプロシージャ内で取得した下の階層のフォルダ名を、再度引数渡しで自身のプロシージャに戻すことで、

・更に下の階層のフォルダ名を取得

をフォルダがなくなるまで実施されます。

        
    Next objSubFolder

End Sub

結果

上のプログラムで下の階層のファイル、フォルダ一覧の取得することができました。

正直、私はChatGPTではなく自身でこれと同じプログラムを作成した時は理解するのにすごく時間がかかりましたが、ChatGPTではすぐに作成されてしまったので「あの時間は何だったんだろう」って感じです。

マクロ付エクセルファイル保存時の注意点

注意点として、せっかく上記で作成したVBAを含むエクセルファイルを保存する場合は、必ず拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えてしまいますので要注意です。

あとがき

今回はChatGPTで、指定したパスのファイル、フォルダ一覧を取得するプログラムを作成しました。

ChatGPTでも少しミス？がありましたが、今回も問題なくプログラムを作成することができました。

次回は、ファイル操作 5として

・テキストファイルやCSVファイルの読み込み

プログラムを作成したいと思います。

リンク

公開日：2023年4月3日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 5(テキスト、CSVファイルの読み込み)

今回は、テキストやCSVファイルのデータを読み込みするプログラムを作成していき

テキストファイルとCSVファイルの違い

こちらもChatGPTで聞いてみました。

【ChatGPTの回答】

テキストファイルとCSVファイルの主な違いは、ファイル内に保存されているデータの形式です。

• テキストファイルは、通常のテキストデータを含む単純なテキスト形式のファイルです。テキストファイルは、プログラムのソースコード、設定ファイル、ログファイルなど、多くの目的で使用されます。

• CSVファイルは、Comma Separated Values（カンマ区切り値）と呼ばれる形式でデータを保存するテキストファイルです。CSVファイルは、表形式のデータを保存するために使用され、各行には一意のレコードが含まれ、各列にはレコードのフィールドが含まれます。各フィールドは、コンマで区切られているため、ファイル名が示すように、カンマ区切り値形式で保存されます。

一般的に、CSVファイルは、Excelやデータベースなどのプログラムで簡単に読み書きでき、テキストファイルよりも構造化されたデータの表現に向いています。一方、テキストファイルは、データの構造化が必要なく、より柔軟なデータ保存形式を必要とする場合に適しています。

テキストファイルを作成

ChatGPTで「都道府県別データ」として

・都道府県名

・県庁所在地

・面積

・人口

のデータを作成して、メモ帳でテキストファイルとして保存。

CSVファイルを作成

こちらもChatGPTで同じようにCSVファイル用としてデータを作成。

テキスト用と異なり、それぞれのデータが「,」カンマ区切りになっています。

テキストファイルを読み込むVBAを作成

①ChatGPTに下記内容でVBA作成を依頼。

②プログラムを実行。

「InputBox」と指定してしまいましたが、ファイル選択するのは、「Application.GetOpenFilename」の間違いでした。

ChatGPTでは問題なく「Application.GetOpenFilename」を使用してファイル選択をするようにしてくれています。

なので、下のようにファイル選択のウインドウが開きます。

ファイルを選択してプログラムを実行すると下のように文字化けになりました。

これは、テキストファイルを保存するときに「エンコード」が「UTF-8」で作成されている為のようです。

下のように、テキストファイルで「エンコード」を「ANSI」で保存しなおすと問題なく表示されるようになります。

「UTF-8」で保存されたテキストファイルでも文字化けしないようにする

文字化け問題を解消すべく、ChatGPTで何度もプログラムを作成しましたが、残念ながら文字化けが解消されるプログラムは作成出来ませんでした。

(質問が悪いのかもしれません。)

自身でネットを確認したところ「ADODB.Streamオブジェクト」を使用すると、「UTF-8」で保存されたテキストファイルでも文字化けが解消できるとのことなのでChatGPTに「ADODB.Streamオブジェクト」を使用したプログラム作成を依頼しました。

①下のようにChatGPTに「ADODB.Streamオブジェクト」を使用して文字化けを解消するプログラムを作成依頼。

②プログラムを実行。

下のように文字化けが解消されました。

ただし、今度は、「ANSI」で保存されているテキストファイルでこのプログラムを実行すると下のように文字化けが発生。

上記を解消すべく、VBAで「UTF-8」もしくは「ANSI」を自動判別するようなプログラムを作成しようとしましたが、私の知識不足と結構面倒なプログラムになりそうなので今回は断念します。

【実際のプログラム】

※UTF-8で保存されたテキストファイルを文字化けしないようにしたプログラム

Sub ReadUTF8TextFile()
    Dim file_path As Variant
    Dim stream As Object
    Dim text As String
    Dim row_num As Integer
    Dim line As Variant
   
    ' ファイルのパスを選択
    file_path = Application.GetOpenFilename("Text Files (*.txt),*.txt")

👆テキストファイルの選択コマンド。
   
    ' キャンセルされた場合は処理を終了
    If file_path = False Then Exit Sub

👆テキストファイルの選択を「キャンセル」したときの対策もしてくれています。
   
    ' ADODB.Streamオブジェクトを作成してファイルを読み込む
    Set stream = CreateObject("ADODB.Stream")
    stream.Type = 2 ' テキスト形式
    stream.Charset = "utf-8"
    stream.Open
    stream.LoadFromFile file_path
   text = stream.ReadText(-1) ' 全部読み込む
   
    ' ファイルを閉じる
    stream.Close

👆「ADODB.Streamオブジェクト」については正直あまりわかっていません。

別途勉強しますが、今回はそのまま使用します。

    
    ' 読み込んだテキストをセルに書き込む
    row_num = 3 ' セルB3から書き込む
    For Each line In Split(text, vbCrLf)

👆「vbCrlf」は改行の意味です。

上の赤字部分で「text」変数に全てのデータを入れて、改行ごとに「line」変数に入れていくようです。

        ActiveSheet.Cells(row_num, 2).Value = line

👆ここで上で作成した改行毎に区切られた「line」変数に入っている値をセルに書き込んでいきます。

        row_num = row_num + 1
    Next
End Sub

【因みに「ADODB.Streamオブジェクト」を使用していないプログラム】

Sub ReadTextFile()
    Dim file_path As Variant
    Dim file_num As Integer
    Dim text_line As String
    Dim row_num As Integer
   
    ' ファイルのパスを選択
    file_path = Application.GetOpenFilename("Text Files (*.txt),*.txt")
   
    ' キャンセルされた場合は処理を終了
    If file_path = False Then Exit Sub
   
    ' ファイルを開く
    file_num = FreeFile()
    Open file_path For Input As file_num
   
    ' ファイルの内容を読み込んでセルに書き込む
    row_num = 3 ' セルB3から書き込む
    Do Until EOF(file_num)
        Line Input #file_num, text_line
        ActiveSheet.Cells(row_num, 2).Value = text_line
        row_num = row_num + 1
    Loop
   
    ' ファイルを閉じる
    Close file_num
End Sub

※今回「ADODB.Streamオブジェクト」を指定したため、複雑なプログラムになりましたが、単純にテキストファイルを読み込むプログラムは上のプログラムで赤字部分の「EOF」や「Line Input」を使うのが一般的だと思います。

CSVファイルを読み込むVBAを作成

①同様にChatGPTに依頼。

②プログラムを実行。

こちらも「Application.GetOpenFilename」でファイル選択ができるようになっています。

また、ファイルもCSV用としているので、ファイル形式も「CSV」が選択されています。

CSVは「,」カンマ区切りなので、「,」ごとにデータを分けて保存するようにプログラムしてくれています。

こちらもテキストファイルと同じで、「エンコード」が「UTF-8」か「ANSI」で保存されているかで、文字化けすることがあります。

下は「UTF-8」で保存されたCSVファイル用のプログラムです。

【実際のプログラム】

Sub ReadCSVFile()
    Dim file_path As Variant
    Dim stream As Object
    Dim text As String
    Dim row_num As Integer
    Dim line As Variant
    Dim cell As Variant
   
    ' ファイルのパスを選択
    file_path = Application.GetOpenFilename("CSV Files (*.csv),*.csv")
👆テキストファイルの選択コマンド。

  
    ' キャンセルされた場合は処理を終了
    If file_path = False Then Exit Sub

👆テキストファイルの選択を「キャンセル」したときの対策もしてくれています。
   
    ' ADODB.Streamオブジェクトを作成してファイルを読み込む
    Set stream = CreateObject("ADODB.Stream")
    stream.Type = 2 ' テキスト形式
    stream.Charset = "utf-8"
    stream.Open
    stream.LoadFromFile file_path
    text = stream.ReadText(-1) ' 全部読み込む
   
    ' ファイルを閉じる
    stream.Close

👆「ADODB.Streamオブジェクト」の処理。

    
    ' 読み込んだテキストをセルに書き込む
    row_num = 3 ' セルB3から書き込む
    For Each line In Split(text, vbCrLf)

👆こちらも「vbCrlf」改行で区切っています。

        Dim cells As Variant
        cells = Split(line, ",")

👆上で「vbCrlf」改行で区切っていますが、更に1行を「,」で区切っています。

        Dim col_num As Integer
        col_num = 2 ' セルBから書き込む
        For Each cell In cells
            ActiveSheet.Cells(row_num, col_num).Value = cell
            col_num = col_num + 1
        Next

👆ここで、1行のプログラムを「,」区切りにしたときに列番号に1を追加して値を入力していくようになっています。

        row_num = row_num + 1

👆次の行に行くために行番号の変数に1を追加。

    Next
End Sub

マクロ付エクセルファイル保存時の注意点

注意点として、せっかく上記で作成したVBAを含むエクセルファイルを保存する場合は、必ず拡張子を"Excelマクロ有効ブック(*.xlsm)"で保存しないとプログラムが消えてしまいますので要注意です。

あとがき

今回はChatGPTで、テキスト及びCSVファイルを読み込みするプログラムを作成しました。

私自身は多少のVBAの知識しかありませんが、根気よくChatGPTと時々サイトを利用することでそれなりのプログラムは作成できます。

ChatGPTが100%正解だとは思わないので、何の知識もなく作成されたプログラムをそのまま使用することはお勧めできません。

しかし、作成されたVBAを自身で解析し、不明点もまたChatGPTに質問できるので、独学で学ぶ場合は一人で悩むよりChatGPTを活用したほうが断然良いと思います。

次回は、ファイル操作 6として

・ファイル名やフォルダ名の一括変更

プログラムを作成したいと思います。

リンク

公開日：2023年4月4日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 6(ファイル名やフォルダ名の一括変更)

今回は、ファイルやフォルダの名前を変更するプログラムを作成していき

ファイル名を一括変更

①下のフォルダにあるファイル、「在庫表_1月」から「在庫表_6月」の6ファイルの名前を変更するプログラムを作成します。

②内容としては、

・セルB1にファイルのあるパスを記入

・A列の4行目以降に「変更前ファイル名」を記載

・B列の4行目以降に「変更ごファイル名」を記載

③この内容でいつものごとくChatGPTにVBA作成依頼をしました。

「セルB1に記入されたパスにあるA列の4行目以降に記載されているファイルをB列の4行目以降に書かれているファイル名に変更するVBAを作成してください。」

④実行結果

下のように全て「2023年」の文字が追加されたファイル名に変更されました。

【実際のプログラム】

Sub RenameFiles1()
    Dim path As String
    Dim filename As String
    Dim i As Long
   
    path = Range("B1").Value

👆パスの設定
   
    For i = 4 To cells(Rows.Count, 1).End(xlUp).Row

👆「i」の値を4からA列の最終行まで繰り返し

        filename = cells(i, 1).Value

👆「filename」変数にセル「i」行目1列(A列)の値を入れる

        Name path & "\" & filename As path & "\" & cells(i, 2).Value

👆ファイルの名前を「filename」から、セル「i」行目2列(B列)の値に変更する

    Next i
End Sub

同じ文字をファイル名に追加する場合

上でも同じ文字をファイル名に追加しましたが、「変更後のファイル名」を全て書くのが面倒なので、下のように1か所に書かれた文字を全てのファイル名に追加するように変更。

①ChatGPTに下のように依頼。

「セルB1に記入されたパスにあるA列の4行目以降に記載されているファイル名を、絶対セルB4に書かれている値を追加したファイル名に変更するVBAを作成してください。」

※ここで、「絶対セルB4」と記載しましたが、時々「相対セル」になることがあるので、念のため「セルB4は固定してください」等追加した方が良いようです。

②実行結果

下のように、この内容でもすべてのファイル名に「2024年」の文字を追加することができました。

【実際のプログラム】
Sub RenameFiles2()
    Dim path As String
    Dim filename As String
    Dim newfilename As String
    Dim i As Long
   
    path = Range("B1").Value
   
    For i = 4 To cells(Rows.Count, 1).End(xlUp).Row
        filename = cells(i, 1).Value
        newfilename = path & "\" & cells(4, 2).Value & filename

👆新たに「newFilename」変数を追加して、セル4行目2列(B列)の値+「filename」(元の名前)に追加

        Name path & "\" & filename As newfilename

👆ファイルの名前を「filename」から「newfilename」に変更

    Next i
End Sub

フォルダ名を一括変更

フォルダ名についても一括で変更するプログラムを作成しました。

フォルダ名の変更については、B4セルの値を固定してフォルダ名に追加する方法(上のファイル名変更2つ目の方法)で作成しました。

現状のフォルダは下のように「在庫表_2023年」から「在庫表_2025年」の3つのフォルダがあります。

①ChatGPTにVBA作成依頼。

「同様にB1に書かれたパスにあるA列の4行名以降に書かれたフォルダ名を絶対セルB4の値を追加したフォルダ名に変更するVBAを作成してください。」

②実行結果

こちらも下のように問題なく全てのフォルダに「文房具_」がフォルダ名に追加されました。

【実際のプログラム】

Sub RenameFolders()
    Dim path As String
    Dim foldername As String
    Dim newfoldername As String
    Dim i As Long
   
    path = Range("B1").Value
   
    For i = 4 To cells(Rows.Count, 1).End(xlUp).Row
        foldername = cells(i, 1).Value
        newfoldername = path & "\" & cells(4, 2).Value & foldername
        Name path & "\" & foldername As newfoldername

👆基本的な考え方は「ファイル名の変更」と同じです。

    Next i
End Sub

※ファイル名、フォルダ名を変更する基本的な構文は下です。

「OldName」「NewName」ともにパスも必要です。

Name OldName　As NewName

あとがき

今回はChatGPTで、ファイル名やフォルダ名を変更するプログラムを作成しました。

次回は、ファイル操作 7として

・ファイルやフォルダのコピー、移動

のプログラムを作成したいと思います。

リンク

公開日：2023年4月5日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ファイル操作 7(ファイルやフォルダのコピー、移動)

今回は、ファイルやフォルダをコピー又は移動するプログラムを作成していきます。

ファイルをコピーする

①基本情報を作成する。

・セルB1にコピー元のファイルのあるパスを記入

・セルB2にコピー先のパスを記入

・セルA4からA列にコピーするファイル名を記入

②上記の内容に基づきChatGPTにVBA作成依頼をする。

③VBA実行。

下のフォルダに

「在庫表フォームA.xlsm」と「在庫表フォームB.xlsm」というファイルがあります。

この2つのファイルを「在庫表フォーム」というフォルダにコピーできました。

【実際のプログラム】

Sub CopyFilesToNewPath()
    Dim sourcePath As String
    Dim targetPath As String
    Dim fileName As String
    Dim i As Long
   
    sourcePath = Range("B1").Value
    If Right(sourcePath, 1) <> "\" Then
        sourcePath = sourcePath & "\"
    End If
   
    targetPath = Range("B2").Value
    If Right(targetPath, 1) <> "\" Then
        targetPath = targetPath & "\"
    End If

👆上の赤字の処理は以前VBA作成した時には、自動でパスの後に「\」を付けてくれたのですが、今回のプログラムでは「\」を付けてくれなかったので、修正依頼したところ、パスの最後に「\」の有無を確認するプログラムを作成してくれました。
   
    For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row
        fileName = Cells(i, 1).Value
        If Dir(sourcePath & fileName) <> "" Then
            ' ファイルが存在する場合はコピーする
            FileCopy sourcePath & fileName, targetPath & fileName

👆ここでファイルの有無を確認し、ファイルがあれば「FileCopy」でファイルを元のパスからコピー先のパスにファイルをコピーします。

        End If
    Next i
End Sub

ただし、上のプログラムではすでに同じ名前のファイルがある場合はエラーになります。

今回は追加していませんが、If文やOnErrorなどで同名もしくはエラーが発生する場合はMsgBoxで「同じ名前のファイルが存在します」等を追加してもよいと思います。

※こちらもChatGPTに依頼すればすぐにVBA作成してくれます。

ファイルを移動する

①基本情報を作成する。

・セルB1にコピー元のファイルのあるパスを記入

・セルB2にコピー先のパスを記入

・セルA4からA列に移動するファイル名を記入

②上記の内容に基づきChatGPTにVBA作成依頼をする。

③VBA実行。

上の「ファイルのコピー」と同様に、

「在庫表フォームA.xlsm」と「在庫表フォームB.xlsm」というファイルがあります。

今度は移動なので、元のファイルはなくなりました。

「在庫表フォーム」のフォルダに移動することができました。

【実際のプログラム】

※基本的な構成はコピーと同じです。

移動というより、コピーした後元ファイルを消すというプログラムになっています。

Sub MoveFilesToNewPath()
    Dim sourcePath As String
    Dim targetPath As String
    Dim fileName As String
    Dim i As Long
   
    sourcePath = Range("B1").Value
    If Right(sourcePath, 1) <> "\" Then
        sourcePath = sourcePath & "\"
    End If
   
    targetPath = Range("B2").Value
    If Right(targetPath, 1) <> "\" Then
        targetPath = targetPath & "\"
    End If
   
    For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row
        fileName = Cells(i, 1).Value
        If Dir(sourcePath & fileName) <> "" Then
            ' ファイルが存在する場合は移動する
            FileCopy sourcePath & fileName, targetPath & fileName

👆ここまでは「ファイルのコピー」と同じプログラムになります。

下の「kill」で元のファイルを削除することで、ファイルを移動した時と同じ結果になります。
            Kill sourcePath & fileName
        End If
    Next i
End Sub

フォルダをコピーする

次はフォルダに対しても同じような処理をしていきます。

①基本情報を作成する。

・セルB1にコピー元のフォルダのあるパスを記入

・セルB2にコピー先のパスを記入

・セルA4からA列にコピーするフォルダ名を記入

「在庫表_2023年」から「在庫表_2025年」の3つのフォルダを「■旧在庫表保管フォルダ」内にコピーします。

②上記の内容に基づきChatGPTにVBA作成依頼をする。

③VBA実行。

下のフォルダにある「在庫表_2023年」から「在庫表_2025年」の3つのフォルダを「■旧在庫表保管フォルダ」にコピーします。

元のフォルダを残したまま、「■旧在庫表保管フォルダ」にコピーできました。

当然、フォルダのコピーと同様なので、このフォルダ内にあるファイル、フォルダも一緒にコピーされています。

【実際のプログラム】

※フォルダの操作には「FSO」を使用してたプログラムになっています。

Sub CopyFoldersToNewPath()
    Dim fso As Object
    Dim sourcePath As String
    Dim targetPath As String
    Dim folderName As String
    Dim i As Long
   
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    sourcePath = Range("B1").Value
    If Right(sourcePath, 1) <> "\" Then
        sourcePath = sourcePath & "\"
    End If
   
    targetPath = Range("B2").Value
    If Right(targetPath, 1) <> "\" Then
        targetPath = targetPath & "\"
    End If

👆「ファイルのコピー、移動」と同じように、パスの後ろに「\」の有無を確認してくれています。
   
    For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row
        folderName = Cells(i, 1).Value
        If fso.FolderExists(sourcePath & folderName) Then

👆「FSO」でフォルダの有無を確認して、フォルダが存在するときに下の処理をしてくれます。

            ' フォルダが存在する場合はコピーする
            fso.CopyFolder sourcePath & folderName, targetPath & folderName

👆「fos.CopyFolder」で元パスにあるフォルダをコピー先パスのフォルダにコピーしてくれます。

        End If
    Next i
   
    Set fso = Nothing
End Sub

フォルダを移動する

①基本情報を作成する。

・セルB1にコピー元のフォルダのあるパスを記入

・セルB2にコピー先のパスを記入

・セルA4からA列に移動するフォルダ名を記入

「在庫表_2023年」から「在庫表_2025年」の3つのフォルダを「■旧在庫表保管フォルダ」内に移動します。

②上記の内容に基づきChatGPTにVBA作成依頼をする。

③VBA実行。

「フォルダのコピー」と同様に「在庫表_2023年」から「在庫表_2025年」の3つのフォルダを「■旧在庫表保管フォルダ」に移動するので、元のフォルダはなくなりました。

当然、フォルダのコピーと同様なので、このフォルダ内にあるファイル、フォルダも一緒に移動されています。

「■旧在庫表保管フォルダ」の中に3つのフォルダを移動することができました。

【実際のプログラム】

Sub MoveFolders()

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim srcPath As String
Dim destPath As String
Dim folderName As String
Dim i As Long

srcPath = Range("B1").Value
destPath = Range("B2").Value

If Right(srcPath, 1) <> "\" Then
    srcPath = srcPath & "\"
End If

If Right(destPath, 1) <> "\" Then
    destPath = destPath & "\"
End If

For i = 4 To Range("A" & Rows.Count).End(xlUp).Row
    folderName = Range("A" & i).Value
   
    If FSO.FolderExists(srcPath & folderName) Then
        FSO.MoveFolder srcPath & folderName, destPath & folderName

👆「フォルダのコピー」では「fos.CopyFolder」を使用していましたが、「フォルダの移動」は「FSO.MoveFolder」を使用しています。

    End If
Next i

Set FSO = Nothing

End Sub

あとがき

今回はChatGPTで、ファイルやフォルダを移コピー、移動するプログラムを作成しました。

次回は、グラフの操作 1として、ChatGPTでダミーデータを作成し

・基本的なグラフの作成

のプログラムを作成したいと思います。

リンク

公開日：2023年4月6日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

グラフの操作 1(基本的なグラフの作成)

今回は、基本的なグラフとして折れ線グラフを作成するプログラムを作成していきます。

基本的な折れ線グラフを作成する

①基本情報を作成する。

今回は下のような簡単なグラフ作成用データを作成しました。

②ChatGPTに下のように依頼。

エクセルVBAで以下のグラフを作成するプログラムを作成してください 。

・ワークシート名は「グラフ作成」

・グラフ種類は折れ線グラフ

・B3はグラフのタイトル

・データ範囲はB5:B14

・A5:A14はX軸のデータラベル

・B4はY軸の系列名

③VBAを実行。

下のように指示通り折れ線グラフを作成できました。

【実際のプログラム】

Sub CreateLineChart()

    '変数の宣言
    Dim ws As Worksheet
    Dim chtObj As chartObject
    Dim cht As Chart
   
    'ワークシートを取得
    Set ws = ThisWorkbook.Worksheets("グラフ作成")
   
    'グラフ作成前に既存のグラフを削除する
    For Each chtObj In ws.ChartObjects
        chtObj.Delete
    Next chtObj

👆特に指示はしませんでしたが、グラフが既にある場合は削除するプログラムも入れてくれています。
   
    'グラフを作成する
    Set chtObj = ws.ChartObjects.Add(Left:=200, Top:=50, Width:=400, Height:=250)

👆上でグラフを作成する外枠「チャートオブジェクト」を作成しています。

引数にある数値は

「Left」「Top」で表示位置

「Width」「Height」でグラフのサイズ

を設定しています。

    Set cht = chtObj.Chart
   
    'グラフの種類を設定する
    cht.ChartType = xlLine
👆こちらで「折れ線グラフ」を設定。
   
    ' グラフのタイトルを設定
    chtObj.Chart.HasTitle = True
    chtObj.Chart.ChartTitle.text = ws.Range("B3")
👆グラフのタイトル「グラフ作成練習」のセルを設定。

    
    'グラフにデータを追加する
    With cht.SeriesCollection.NewSeries
        .name = ws.Range("B4")
        .Values = ws.Range("B5:B14")
        .XValues = ws.Range("A5:A14")
    End With

👆データ範囲を追加するプログラムです。
「name」で系列名

「Values」でデータ

「XValues」でX軸の範囲

でデータを追加。

    'グラフの軸を設定する
    With cht.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.text = ws.Range("A4")
    End With
   
    With cht.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.text = ws.Range("B4")
    End With
👆「xlCategory」がX軸、「xlValue」がY軸の設定になります。

「AxisTitle.text」で軸タイトルが設定できます。

    
End Sub

「ChartObjects.Add」と「AddChart2」の違い

今回グラフの作成は、「ChartObjects.Add」の方法でプログラムを作成しましたが、グラフ作成に「AddChart2」というメソッドもあるようです。

※私が参考にしている書籍には「AddChart2」が記載されていなかったので、今回は使用しませんでした。

双方の違いについてChatGPTに聞いたところ下のような回答がありましたので、参考までに記載しておきます。

【ChatGPT回答】

ChartObjects.AddメソッドとAddChart2メソッドは、基本的に同じ機能を持っていますが、いくつかの違いがあります。

1. 引数の指定方法 ChartObjects.Addメソッドでは、引数を位置やサイズを表す数値で指定する必要があります。一方、AddChart2メソッドでは、引数をChartTypeやLeft、Top、Width、Heightのような名前付き引数で指定することができます。このため、AddChart2メソッドの方が引数の指定がより簡単になっています。

2. グラフの追加 ChartObjects.Addメソッドでは、グラフを追加するときには、ChartObjectオブジェクトを作成し、その後にChartオブジェクトを取得する必要があります。一方、AddChart2メソッドでは、直接Chartオブジェクトを作成することができます。このため、AddChart2メソッドの方がコードが簡潔になっています。

3. シリーズの追加 ChartObjects.Addメソッドでは、グラフにシリーズを追加するときには、SeriesCollectionオブジェクトを作成し、その後にシリーズを追加する必要があります。一方、AddChart2メソッドでは、SeriesCollectionオブジェクトを作成することなく、直接シリーズを追加することができます。このため、AddChart2メソッドの方がコードが簡潔になっています。

これらの違いにより、AddChart2メソッドを使用すると、Chartオブジェクトをより簡単に作成することができます。ただし、AddChart2メソッドはExcel 2013以降でしか利用できないため、古いバージョンのExcelを使用している場合は、ChartObjects.Addメソッドを使用する必要があります。

とのことです。「AddChart2」勉強します・・・

あとがき

今回はChatGPTで、グラフの操作　1として「基本的なグラフの作成」のプログラムを作成しました。

次回は、グラフの操作 2として、ChatGPTでダミーデータを作成し

・2軸のグラフ作成

のプログラムを作成したいと思います。

リンク

公開日：2023年4月7日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

グラフの操作 2(2軸のグラフ作成)

今回は、グラフ作成の応用で2軸のグラフを作成するプログラムを作成していきます。

2軸のグラフ作成

①基本情報を作成する。

今回は「2023年度A店の売上推移」として、

「月度」

「集客数」を「男性」と「女性」

「売上金額」

として、数値データをChatGPTに作成依頼しました。

②ChatGPTにVBA作成依頼。

下の内容で2軸のグラフを作成するVBAを作成してください。

また、グラフの作成にAddchart2メソッドは使用しないでください。

1.シート名は「グラフ作成」

2.グラフタイトルはセルB3の値

3.X軸範囲はセルA7:A18で、X軸のタイトルはセルA5

4.1軸目の1つ目のデータ範囲はセルB7:B18で、系列名はセルB6

5.1軸目の2つ目のデータ範囲はセルC7:C18で、系列名はセルC6

6.1軸目の2つのデータは「集合縦棒」グラフで、1つ目のデータを青色、2つ目のデータを赤色

7.1軸目のY軸タイトルはセルB5

8.2軸目のデータ範囲はセルD7:D18で、系列名はセルD5、グラフ種類は折れ線グラフ

9.2軸目のY軸タイトルはセルD5

10.凡例をグラフの下に表示

③VBAを実行する。

実行結果は下のようになります。

※2軸のグラフを作成はできましたが、きちんと作成できるまでにいろいろ問題が発生しました。

私の質問が不明確なところもあると思いますが、ChatGPTのミス?もあり、意図したグラフ作成まで約30分かかりました。

自身でプログラムを全て手入力するよりは早いですが・・・

【実際のプログラム】

Sub CreateDualAxisChart()
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("グラフ作成")
   
    ' チャートオブジェクト作成
    Dim chtObj As chartObject
    Set chtObj = ws.ChartObjects.Add(Left:=300, Width:=400, Top:=50, Height:=300)
    chtObj.name = "DualAxisChart"
   
    ' データ範囲の設定
    Dim xVals As Range
    Set xVals = ws.Range("A7:A18")
   
    Dim data1 As Range
    Set data1 = ws.Range("B7:C18")
   
    Dim data2 As Range
    Set data2 = ws.Range("D7:D18")
   
    ' チャート作成
    With chtObj.Chart
        .HasTitle = True
        .ChartTitle.text = ws.Range("B3").Value
       
        ' X軸の設定
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.text = ws.Range("A5").Value
'        .SeriesCollection.NewSeries.XValues = xVals

👆赤字のコマンドがあることで、「月度」も系列に追加されてしまうため、手動でコメントアウトに修正。
       
        ' 1軸目の設定
        With .SeriesCollection.NewSeries
            .name = ws.Range("B6").Value
            .Values = data1.Columns(1)
           .Format.Fill.ForeColor.RGB = RGB(0, 0, 255) ' 青

👆最初「.Format.ForeColor.RGB」と「Fill」が無かったため「オブジェクトは、このプロパティまたはメソッドをサポートしていません」と表示されました。

            .ChartType = xlColumnClustered ' 集合縦棒
        End With

        With .SeriesCollection.NewSeries
            .name = ws.Range("C6").Value
            .Values = data1.Columns(2)
            .Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' 赤
            .ChartType = xlColumnClustered ' 集合縦棒
            .AxisGroup = 1 ' 1軸目を使用

👆上のデータは1軸目のデータにする予定でしたが、「.AxisGroup = 2」で作成されていたため、2軸目のデータになっていたので手動で修整しました。

        End With
     
        ' 1軸目Y軸の設定
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.text = ws.Range("B5").Value
       
        ' 2軸目の設定
        With .SeriesCollection.NewSeries
            .name = ws.Range("D5").Value
            .Values = data2.Columns(1)
            .ChartType = xlLineMarkers ' マーカー付き折れ線
            .AxisGroup = 2 ' 2軸目を使用
            .Format.line.ForeColor.RGB = RGB(255, 255, 0) ' 黄色
        End With

        ' 2軸目Y軸の設定
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.text = ws.Range("D5").Value
       
        ' 凡例の設定
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
    End With
   
End Sub

※その他に

・最初に「SetSourceData」で全てのデータ範囲を指定しグラフを作成

⇒その後に1軸目の2つのデータを追加

するプログラムになっていたため、1軸目に4つのデータが表示されていました。

・またまた、宣言がされていない変数が使用されている。

等の問題も発生したので、何度もChatGPTに最初からプログラムを作り直して上のプログラムを作成した後、更に上の修正を加えています。

あとがき

今回はChatGPTで、グラフの操作　2として「2軸のグラフ作成」のプログラムを作成しました。

今回はChatGPTで作成したVBAがすぐに動かない、意図したようになっていないことで、修正、時間がかかりました。

内容が複雑になると、ChatGPTへの依頼も難しくなり、理解ミスも発生するようなので、自身もある程度VBAの知識も持ってプログラムを見直せる力が必要と思われます。

次回は 印刷 1として、

・基本的な印刷方法

のプログラムを作成したいと思います。

リンク

公開日：2023年4月8日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

印刷 1(基本的な印刷方法)

今回は、基本的な印刷方法のプログラムを作成していきます。

基本的な印刷方法

①今回印刷するのは前回「2軸のグラフ」作成した部分を印刷します。

印刷範囲はセルA1からM20の範囲にしました。

②ChatGPTに作成依頼。

下の内容で印刷するVBAを作成してください

・印刷範囲・・・A1:M20

・1行目をタイトル行

・標準の余白

・印刷方向は横方向

・用紙設定はA4

・カラー印刷

・印刷前に印刷プレビューを表示する

③VBA実行結果。

下のように印刷プレビュー表示もされ、その後の印刷でも問題なく印刷されました。

【実際のプログラム】

Sub PrintWithPreviewAndPrinter()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("グラフ作成")
   
    ' 印刷範囲を指定
    Dim printRange As Range
    Set printRange = ws.Range("A1:M20")
   
    ' 印刷設定を定義
    With ws.PageSetup
        .PrintTitleRows = "$1:$1" ' 1行目をタイトル行に指定
        .Orientation = xlLandscape ' 印刷方向は横方向に指定
        .PaperSize = xlPaperA4 ' 用紙サイズはA4に指定
        .BlackAndWhite = False ' カラー印刷に指定
        .Zoom = False ' ズームを設定しない
        .FitToPagesWide = 1 ' 1ページに収める
        .FitToPagesTall = False ' 縦方向にはページ分割しない
        .LeftMargin = Application.InchesToPoints(0.7) ' 左余白を設定
        .RightMargin = Application.InchesToPoints(0.7) ' 右余白を設定
        .TopMargin = Application.InchesToPoints(0.75) ' 上余白を設定
        .BottomMargin = Application.InchesToPoints(0.75) ' 下余白を設定
        .HeaderMargin = Application.InchesToPoints(0.3) ' ヘッダー余白を設定
        .FooterMargin = Application.InchesToPoints(0.3) ' フッター余白を設定
        .CenterHorizontally = True ' 水平方向に中央寄せ
        .CenterVertically = False ' 垂直方向には寄せない
    End With
   
    ' 印刷プレビューを表示
    ws.PrintPreview
   
    ' 指定されたプリンターで印刷
    ws.PrintOut ActivePrinter:= _
        "\\PC名\プリンター名", _
        PrintToFile:=False, _
        Collate:=True, _
        PrToFileName:=vbNullString
       
End Sub

※私はVBAで印刷プログラムを作成したことがありませんでしたが、コメントアウトもきっちりしてくれているので、アレンジもできそうです。

あとがき

今回はChatGPTで、印刷　1として「基本的さ印刷方法」のプログラムを作成しました。

次回は 「ボタンを作成してVBAを実行する」プログラムを作成したいと思います。

リンク

公開日：2023年4月9日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

ボタンを作成してVBAを実行する

今回は、今までVBAをVBE内の再生ボタンで実行してきましたが、ボタンを作成して実行する方法を試していきます。

ボタンを作成してVBAを実行する

今回は、前回作成した下のエクセルで作成した「グラフ作成」のVBAを、

セルC1からD2の範囲にボタンを作成して、「グラフ作成」のVBAを簡単に実行できるようにします。

ボタンを手動で作成する場合

ボタンを手動で作成する方法は下の2種類あります。

1.ボタンを図形で作成し、「マクロの登録」をする。

2.フォームコントロールでボタンを挿入し、「マクロの登録」をする。

図形で作成

①メニューバー⇒「挿入」⇒「図形」⇒「四角形」を選択。

②ボタンを作成したい箇所をドラッグして作成する。

③図形を右クリック⇒「テキストの編集」をクリックする。

④図形の中にテキスト入力できるようにカーソルが表示されるので、「グラフ作成」と入力。

⑤再度、図形を右クリック⇒「マクロの登録」をクリック。

⑥「マクロの登録」ウインドウが開くので、作成したVBAの「プロシージャ名」を選択して「OK」を押す。

※「プロシージャ名」は、VBAを作成するときのタイトル「Sub ****()」の「****」部分です。

これで、作成した図形をクリックすることでVBAの実行ができます。

フォームコントロールの挿入で作成

基本的な方法は上の「図形で作成」と同じです。

①今度はメニューバー⇒「開発」⇒「挿入」からフォームコントロールのボタンを選択します。

②ボタンを作成したい範囲をドラッグで選択します。

範囲を選択すると自動で「マクロの登録」ウインドウが表示されるので、実行する「プロシージャ名」を選択し「OK」を押す。

③選択した範囲に下のようにボタンが作成されます。

④ボタンの上で右クリック⇒「テキストの編集」をクリック。

⑤ボタン内のテキストが修正できるようにカーソルが表示されるのでボタン名を「グラフ作成」に変更。

こちらも、作成したボタンをクリックすることでVBAの実行ができます。

ボタンを作成するVBAを作成する。

①ChatGPTにボタンの作成依頼をする。

「グラフ作成」シートのC1:D2にコマンドボタンを作成して「Sub CreateDualAxisChart()」を実行するVBAを作成してください。

※上の依頼では、セルB1のみの大きさのボタンが作成されたので、再度

「上のVBAで作成するコマンドボタンをB1:C2のセル範囲の大きさで作成してください。」

と追加依頼することで、ChatGPTで今回のVBAを作成することができました。

②VBAを実行する。

作成されたVBAを実行すると、下のようにボタンが自動で作成されました。

③ボタンをクリックする。

念のため、ボタンをクリックするとグラフが作成されたので、問題なく

「ボタンの作成」⇒「マクロの登録」

までできています。

【実際のプログラム】

Sub CreateButton()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("グラフ作成")

    ' ボタンを作成するセル範囲を指定
    Dim btnRange As Range
   Set btnRange = ws.Range("C1:D2")

👆ここでボタンの範囲設定をしているので、ここの「Range("C1:D2")」の値を変えると他の場所や大きさでボタンを作成することができます。
   
    ' ボタンを作成
    Dim btn As Button
    Set btn = ws.Buttons.Add(btnRange.Left, btnRange.Top, btnRange.Width, btnRange.Height)
    btn.Caption = "グラフ作成"
   
    ' イベントハンドラの設定
    Dim modName As String
    modName = "CreateDualAxisChart"
    With btn
        .OnAction = modName
        .Placement = xlMoveAndSize
    End With

End Sub

あとがき

今回はChatGPTで、「ボタンを作成してVBAを実行する」プログラムを作成しました。

1つのボタンの作成であれば、手動での作成も良いと思いますが、変数や繰り返し処理を組み込んでアレンジすると、まとめてボタンを作成できるのでVBA実行(自動化)フォームを作成するには良いかな思います。

次回は 「VBA実行の前処理・後処理」プログラムを作成したいと思います。

リンク

公開日：2023年4月10日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

VBA実行の前処理・後処理

前回まではエクセル操作をVBAにする勉強をしてきましたが、今回は、VBAを実行する際の、前処理・後処理についてChatGPTに聞いてみました。

VBA実行の前処理・後処理について

今回の内容は必ず必要というわけではありませんが、VBA処理中の画面更新や自動計算などの機能を一時的にオフにすることで、VBA実行時間を短縮できたりします。

ChatGPTにおすすめの前処理・後処理について聞いてみた

①今回は下のようにChatGPTに聞いてみました。

「VBAを実行するときにおすすめの前処理、後処理のプログラムはありますか」

《回答》

VBAを実行する前後に実行する前処理、後処理は、次のようなものがあります。

【前処理】

• データのバックアップを作成する

• ファイルを自動的に保存する

• ファイルの変更履歴を保存する

• ワークシートを整理し、使用していないセルをクリアする

• 必要なオブジェクトを初期化する

• ファイルのセキュリティ設定を確認する

【後処理】

• 作業中に生成された一時ファイルを削除する

• 処理中に発生したエラーのログを取得する

• ワークシートを整理して、不要なシートを削除する

• オブジェクトを解放して、メモリを解放する

• 最終的に保存してファイルを閉じる

このような前処理、後処理のプログラムを実行することで、VBAの実行を安定化させることができます。また、定期的に自動実行するように設定することで、手動で実行する手間を省くことができます。

②私は「DisplayAlerts」で警告表示を非表示にする設定することが多かったのですが、ChatGPTの回答になかったので

「DisplayAlerts」は設定しない方が良いですか

と再度聞いてみました。

※赤字は私的に重要ポイントと思ったので赤字にしました。

《回答》

DisplayAlertsプロパティは、Excelがメッセージを表示するかどうかを制御するプロパティであり、設定によっては意図しない結果が発生する可能性があります。

例えば、DisplayAlertsプロパティをFalseに設定しておくと、マクロ実行中に問題が発生しても、Excelがエラーメッセージを表示しないため、エラーの原因や詳細がわからない場合があります。

また、マクロ実行中にセキュリティ警告が表示されなくなるため、マクロによって悪意のあるコードが実行される可能性があることに注意が必要です。

そのため、DisplayAlertsプロパティを設定する際には、必要に応じて注意深く検討し、適切な設定を行うことが重要です。特に、データを保存する前にDisplayAlertsプロパティをFalseに設定してしまうと、データが失われる可能性があるため、注意が必要です。

③次に「上の内容のVBAを作成してください」と依頼。

結果、下の4項目の「前処理・後処理」を作成してくれました。

上では5項目記載されていますが、私的には下の内容で十分と思っています。

【プログラム内容】

《前処理》

Sub BeforeMacro()
    '実行前の処理をここに記述する
    Application.ScreenUpdating = False '画面更新をオフにする
    Application.Calculation = xlCalculationManual '自動計算をオフにする
    Application.EnableEvents = False 'イベントをオフにする
    ActiveSheet.DisplayPageBreaks = False '改ページプレビューをオフにする
End Sub

《後処理》
Sub AfterMacro()
    '実行後の処理をここに記述する
    Application.ScreenUpdating = True '画面更新をオンにする
    Application.Calculation = xlCalculationAutomatic '自動計算をオンにする
    Application.EnableEvents = True 'イベントをオンにする
    ActiveSheet.DisplayPageBreaks = True '改ページプレビューをオンにする
End Sub

あとがき

今回はChatGPTで、「VBA実行の前処理・後処理」について勉強しました。

プロシージャに

「前処理」

「後処理」

を作成して、今後のVBAを作成するときに、最初と最後に2つの処理を「サブルーチン」として呼び出して実行するようにしたいと思いますが、「サブルーチン」については次回勉強します。

次回は 「サブルーチンについて」勉強していきます。

リンク

公開日：2023年4月11日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

サブルーチンについて

今回は、「サブルーチン」についてChatGPTに聞いてみました。

サブルーチンについて

ChatGPTに「サブルーチンとは何ですか」と質問

《回答》

VBAのサブルーチンは、一連のコードをグループ化して、それを単一のブロックとして実行する方法です。通常、サブルーチンは関数と同様の方法で使用されますが、関数は値を返すことができますが、サブルーチンは値を返しません。

サブルーチンを使用すると、同じ手順を何度も実行する必要がある場合に、その手順を簡潔かつ効率的に記述できます。また、コードを複数の場所で再利用することもできます。

サブルーチンを作成する

今回は、前回勉強したVBA実行時の前処理、後処理のプログラムをサブルーチンとして使用します。

①標準モジュールを挿入する。

・VBEのメニューバーから「挿入」⇒「標準モジュール」をクリック。

・下のように「標準モジュール」の下に「Module1」が作成されました。

②標準モジュール名を変更する。

下のように「オブジェクト名」を変更して、挿入した「標準モジュール」を区分すると、どこにプログラムがあるか分りやすいと思います。

・今回はモジュールの名前を「A_前後処理」に変更しました。

※今回はメインのプログラムを別のモジュールで管理するために、

メニューバー⇒「挿入」⇒「標準モジュール」

を再度追加して、下のように「B_メイン」というモジュールも追加しました。

③「A_前後処理」のモジュールに、前回作成した「前後処理」のプログラムをコピペする。

今回は、「前後処理」がきちんと実行されているか分かるように、下のようにメッセージボックスを手入力で追加しました。

【前処理】

Sub BeforeMacro()
    '実行前の処理をここに記述する
    Application.ScreenUpdating = False '画面更新をオフにする
    Application.Calculation = xlCalculationManual '自動計算をオフにする
    Application.EnableEvents = False 'イベントをオフにする
    ActiveSheet.DisplayPageBreaks = False '改ページプレビューをオフにする
   
    MsgBox ("前処理を実行しました")

👆手入力で追加しました。「前処理」が実行されると、最後に「前処理を実行しました」とメッセージが表示されます。
End Sub

【後処理】
Sub AfterMacro()
    '実行後の処理をここに記述する
    Application.ScreenUpdating = True '画面更新をオンにする
    Application.Calculation = xlCalculationAutomatic '自動計算をオンにする
    Application.EnableEvents = True 'イベントをオンにする
    ActiveSheet.DisplayPageBreaks = True '改ページプレビューをオンにする
   
    MsgBox ("後処理を実行しました")

👆手入力で追加しました。「後処理」が実行されると、最後に「後処理を実行しました」とメッセージが表示されます。
End Sub

メインのプログラムからサブルーチンを実行する

①メインのプログラムを作成する。

今回は、手入力で下のような簡単なVBAを作成しました。

Sub 前処理_後処理確認()

BeforeMacro 

👆「前処理」サブルーチンを実行。

「前処理」サブルーチン名を書くことで、サブルーチンを実行できます。

「Call サブルーチン名」でもOKです。

Cells(1, 1).Value = "前処理を実行済み"

AfterMacro
👆「後処理」のサブルーチンを実行

Cells(3, 1).Value = "後処理を実行済み"

MsgBox ("プログラムを終了します")

End Sub

上記を実行すると、

1.「前処理」サブルーチンを実行⇒「前処理を実行しました」と表示される。

2.「前処理」が終了して、上のプログラムに戻り、続きの

「Cells(1, 1).Value = "前処理を実行済み"」が実行されるので、セルA1に「前処理を実行済み」と記入されます。

3.「後処理」サブルーチンを実行⇒「後処理を実行しました」と表示される。

4.「後処理」が終了して、再度上の続きのプログラム

「Cells(3, 1).Value = "前処理を実行済み"」が実行されるので、セルA3に「後処理を実行済み」と記入されます。

5.最後に「プログラムを終了します」と表示して終了。

②「Sub 前処理_後処理確認()」を実行。

・「前処理」のサブルーチンが実行され1.の「前処理を実行しました」とメッセージボックスが出ました。

・「Sub 前処理_後処理確認()」に戻り、2.のセルA1に「前処理を実行済み」と記入されました。

また。セルA1の後すぐに「後処理」のサブルーチンが実行されている為、3.の「後処理を実行しました」とメッセージボックスが出ました。

・「後処理」のサブルーチンが終了し、4.のセルA3に「後処理を実行済み」と記入されました。

また、最後に5.の「プログラムを終了します」の表示がされ、「OK」を押すと、プログラムが終了します。

あとがき

今回は「サブルーチンについて」を勉強しました。

今回もChatGPTに良いVBAサンプルを作成してもらおうと思ったのですが、意図したVBAができなかったため、自作で簡単に「サブルーチン」が動いていることを確認することにしました。

ただ、「サブルーチンとは？」等はきちんと回答してくれるので、昔のようにサイトを検索しなくてよく、時短にはなります。

次回は 「VBEのローカルウインドウとウォッチウインドウ」について勉強していきます。

リンク

公開日：2023年4月12日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。 

VBEのローカルウインドウとウォッチウインドウ

今回は、「VBEのローカルウインドウとウォッチウインドウ」について勉強していきます。

VBEのローカルウインドウとウォッチウインドウ

ローカルウインドウとウォッチウインドウについて

VBEのウインドウについて、

・ローカルウインドウ

・ウォッチウインドウ

・イミディエイトウインドウ

の3つがあります。

この3つのウインドウの違いについて、ChatGPTに質問してみました。

《回答》

VBEのローカルウィンドウ、ウォッチウィンドウ、イミディエイトウィンドウは、それぞれ以下のような特徴があります。

1.ローカルウィンドウ

ローカルウィンドウは、現在実行中のサブルーチンや関数内で宣言された変数の値を表示します。このウィンドウは自動的に更新され、実行中に変数の値を確認できます。デバッグの際に特に役立ちます。

2.ウォッチウィンドウ

ウォッチウィンドウは、現在のコードで選択された変数の値を監視することができます。このウィンドウは手動で更新する必要がありますが、複数の変数を監視することができます。この機能は、特定の変数の値を定期的に確認する必要がある場合に役立ちます。

3.ミディエイトウィンドウ

イミディエイトウィンドウは、VBAコードを実行することができるインタラクティブなウィンドウです。ここにコマンドを入力すると、VBAはすぐに実行し、その結果を表示します。このウィンドウは、特定のコードの動作を簡単にテストしたり、変数の値を簡単に確認するために使用することができます。

とのことで、それぞれのウインドウはプログラムを「デバッグ」するときに非常に便利です。

私は「イミディエイトウインドウ」はあまり使用しないので、今回は「ローカルウインドウ」と「ウォッチウインドウ」について書いていきます。

デバッグの方法

ウインドウの説明の前に、デバッグ時に便利な「ステップ実行」と「ブレークポイント」について説明します。

ステップ実行

VBEで実行したいプロシージャ内にカーソルを置き、「F8」キーを押すことで、下のように黄色くハイライトされ、「F8」キーを押すごとに1ステップずつ確認しながら

プログラムを進めることができます。

ブレークポイント

上の「ステップ実行」とは別で、途中でプログラムを止めたいところに「ブレークポイント」を置くことで、プログラムを「再生」若しくは「F5」で実行しても、下の赤色?の部分でプログラムを止めることができます。

下の左枠部をクリックするか、止めたいプログラムにカーソルを置き「F9」を押すことで「ブレークポイント」を追加することができます。

ウインドウの表示方法

各ウインドウの表示は、

メニューバー⇒「表示」をクリックし、

・イミディエイトウインドウ

・ローカルウインドウ

・ウォッチウインドウ

を押すことで表示することができます。

今回は、下のように「ローカルウインドウ」と「ウォッチウインドウ」のみ使用します。上で記載した各々のウインドウの内容を青字にしていますが、ともに「変数」の値を表示する機能です。

ローカルウインドウ

現在実行中のサブルーチンや関数内で宣言された変数の値を表示します。

ウォッチウインドウ

現在のコードで選択された変数の値を監視することができます。

今回は、下のようにVBEの下側左に「ローカルウインドウ」、右に「ウォッチウインドウ」を配置しました。

①「F8」で「ステップ実行」を開始すると、

「ローカルウインドウ」⇒全ての変数が表示されます。

「ウォッチウインドウ」⇒何も表示されていません。

②「F8」で「ステップ実行」を進めていくと、

「ローカルウインドウ」⇒プログラムで入力された変数の値が確認できます。

③「ウォッチウインドウ」を表示するには、値を知りたい変数を「ウォッチウインドウ」にドラッグすると、ドラッグした変数だけが表示されます。

・今回は「name」の変数をドラッグしました。

「ローカルウインドウ」と同じ「name」変数の値が表示されます。

・「ウォッチウインドウ」で削除したい変数は、右クリックで削除することができます。

上のように、

「ローカルウインドウ」・・・全ての変数の値

「ウオッチウインドウ」・・・見たい変数の値のみ確認が可能

です。

上では変数が少ないため、「ローカルウインドウ」でも問題がありませんが、変数が多くなると「ウォッチウインドウ」を活用した方が、見たい変数の値を抽出してみることができます。

「F8」の「ステップ実行」で変数の値を確認しながら、デバッグすることでエラーの発生内容を判断することができるようになると思います。

あとがき

今回は「VBEのローカルウインドウとウォッチウインドウ」を勉強しました。

今回もあまりChatGPTは活用しませんでしたが、自身の復習も兼ねて「VBEのローカルウインドウとウォッチウインドウ」を勉強しました。

次回は 「引数の「値渡し」と「参照渡し」」について勉強していきます。

リンク

公開日：2023年4月13日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

引数の「値渡し」と「参照渡し」

今回は、「値渡しと参照渡し」について勉強していきます。

引数の「値渡し」と「参照渡し」

まず、ChatGPTに引数の「値渡し」と「参照渡し」について聞いてみました。

ChatGPTの回答

VBAにおいて、引数の渡し方には値渡し（ByVal）と参照渡し（ByRef）の2種類があります。

値渡しの場合、引数に渡された値をコピーして渡すため、元の変数の値が変更されても、関数内で変更された値は元の変数に反映されません。

例え

ば、以下のコードでは、Subルーチン内で変数numの値が10から20に変更されても、Mainルーチンの変数numの値は変更されません。

Sub Main()

    Dim num As Integer

    num = 10

    Call ChangeValue(num)

    MsgBox num

End Sub

Sub ChangeValue(ByVal num As Integer)

    num = 20

End Sub

参照渡しの場合、引数に渡された変数のアドレスを渡すため、関数内で変数の値を変更すると、元の変数にも反映されます。

例えば、以下のコードでは、Subルーチン内で変数numの値が10から20に変更されると、Mainルーチンの変数numの値も20に変更されます。

Sub Main()
    Dim num As Integer
    num = 10
    Call ChangeValue(num)
    MsgBox num
End Sub

Sub ChangeValue(ByRef num As Integer)
    num = 20
End Sub

値渡しはデフォルトの引数の渡し方で、参照渡しを行う場合は、引数にByRefキーワードを付けて宣言します。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－

上のプログラムをVBEにコピペして、実際に「ローカルウインドウ」で変数の中身を見ながら「ステップ実行」を実施してみました。

【値渡し(ByVal)の場合】

・メインのプロシージャで、変数「num」が宣言され、値=10が入りました。

・サブルーチンに「ByVal」で変数「num」=10を値渡ししています。

その後、「num」に値=20を入れいているので、

サブルーチンに居る場合は「num」=20になりました。

※値渡しはメインルーチンの「num」=10は変更されません。

・サブルーチンが終了し、メインに戻ると「num」の値は10のままです。

・なので、MsgBoxも「10」と表示されました。

【参照渡し(ByRef)の場合】

・メインのプロシージャで、変数「num」が宣言され、値=10が入りました。

・サブルーチンに「ByVRef」で変数「num」自体を参照渡ししています。

なので、サブルーチン内で「num」の値=20に変更すると、変数「num」自体が、20に変更されます。

・サブルーチンが終了し、メインに戻ると「num」の値は20になっています。

・なので、MsgBoxも「20」と表示されました。

あとがき

今回は「引数の値渡しと参照渡し」を勉強しました。

今回もあまりChatGPTは活用しませんでしたが、自身の復習も兼ねて「引数の値渡しと参照渡し」を勉強しました。

次回は 「イベントプロシージャ」について勉強していきます。

リンク

公開日：2023年4月14日

Chat GPT × Excel VBA

今回も「Chat GPT」を使って、どんな感じで「Excel VBA」プログラムが作成できるか試していきたいと思います。

イベントプロシージャ

今回は、「イベントプロシージャ」について勉強していきます。

イベントプロシージャ

今回もまず、ChatGPTに「イベントプロシージャ」について聞いてみました。

ChatGPTの回答

VBAのイベントプロシージャとは、ExcelやWordなどのオブジェクトで特定のイベントが発生したときに、自動的に実行されるプログラムのことです。

たとえば、

ボタンがクリックされたとき、

セルの値が変更されたとき、

シートがアクティブになったとき

など、オブジェクトで定義されたイベントが発生した場合に、それに対応するイベントプロシージャが自動的に呼び出されます。

イベントプロシージャは、サブルーチンまたは関数として記述されます。

イベントプロシージャの名前は、オブジェクトの名前に続いて「_」を付けた後に、発生するイベントの名前を指定します。

たとえば、ボタンがクリックされたときに実行されるイベントプロシージャは、

ボタンの名前に続いて「_Click」という名前になります。

イベントプロシージャは、オブジェクトのプロパティやメソッドを使って、イベントが発生したときに実行される処理を記述します。

例えば、ボタンがクリックされたときに実行されるイベントプロシージャでは、ボタンのクリックに応じて何らかの処理を行うようにプログラムを書きます。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

続いて、

・ブックを開いたとき

・ブックを閉じるとき

・セルを選択したとき

に発生する「イベントプロシージャ」の書き方について質問。

ChatGPTの回答

Excel VBAで、ブックを開いたとき、閉じるとき、セルを選択した時などに発生するイベントプロシージャの書き方は以下の通りです。

【ブックを開いたときのイベントプロシージャの書き方】

Private Sub Workbook_Open()
    ' 実行したい処理をここに記述する
End Sub

【ブックを閉じるときのイベントプロシージャの書き方】

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 実行したい処理をここに記述する
End Sub

【セルを選択したときのイベントプロシージャの書き方】

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 実行したい処理をここに記述する
End Sub

これらのイベントプロシージャは、VBAエディタの対応するモジュール内に書き込みます。

たとえば、ブックを開いたときのイベントプロシージャは、ThisWorkbookモジュール内に書き込みます

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

今回は更に、ブックを開いたときに

リボンなどを表示しない等おすすめの開始処理

のVBAについて聞いてみました。

ChatGPTの回答

ブックを開いたときにリボンなどの表示をしないようにするためのVBAコードは以下の通りです。

【実際のプログラム】

Private Sub Workbook_Open()
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    Application.DisplayFullScreen = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", False)"
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

上記プログラムの内容です。

・DisplayFormulaBar：セルに入力した数式を表示するバーです。

・DisplayStatusBar：ステータスバーです。

・DisplayFullScreen：エクセルをフルスクリーンに表示します。

・ExecuteExcel4Macro：Microsoft Excel 4.0 マクロのコマンドを実行するメソッドです。

が、今回は "SHOW.TOOLBAR(""Ribbon"", False)"でリボンの設定をしています。

また、今回は フルスクリーンにはしないので「Application.DisplayFullScreen = True」はコメントアウトします。

このプログラムを「ThisWorkbook」内に記述して、エクセルファイルを再起動してみました。

【「ThisWorkbook」にOpenのイベントプロシージャを記入】

エクセルをファイルを再起動。

リボンなどが表示されなくなりました。

「メニュー画面」のシートを作成して、ボタンを配置するとアプリ感がでると思います。

ただし、これをしてしまうと、再度リボンなどを表示したいときに困ります。

なので、私は下のプロシージャも作成しておいて、「開始処理キャンセル」ボタンを追加しました。

Sub Workbook_Open_Cancel()

    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
'    Application.DisplayFullScreen = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", true)"

End Sub

これで、リボンなどを再表示することができます。

あとがき

今回は「イベントプロシージャ」を勉強しました。

今回はブックを開いたときのVBAを書きましたが、同様にブックを閉じるときに、保存忘れが無いようにVBAを書くこともできると思います。

自身で操作するだけなら特に必要ないかもしれませんが、他の方にも操作してもらうファイルを作成するときは、イベントプロシージャを利用して間違いにくい操作ができるVBAを作成するのも必要かなと思います。

次回は 続けて「イベントプロシージャ」で、セル操作のイベントについて勉強していきます。