【VBA】マクロを書くときのTips
「煩雑なコピペ作業を自動化」のタイトル通りにコピペ作業を自動化するマクロを書くことを目的に5つの記事を書きましたが、ここから自分の業務を楽にしてくれるようにマクロを進化させるのは自分しかいません。
プログラムを書いていくには、そもそもどうやって書くのだろうかと調べることも必要になってきます。そしてバグはつきものですし、意味不明なエラーがでることもしばしばです。ここではプログラム作成中に役立つTipsをいくつか載せていきます。
どうやって書くのかわからないときは「マクロの記録」
「マクロの記録」とは
他の言語であれば、何か処理したいことがあるけど書き方がわからないときはググるか書籍を探すかすると思いますが、マクロでは独自の方法があります。それが「マクロの記録」です。
記録を開始してからそのエクセル上で実施した操作を自動でプログラム化してくれるため、この記事では触れていない
- 罫線の引きたい
- フィルタを付けてソートしておきたい
- 条件付き書式を追加したい
などといった、普段エクセル上で実施している操作をプログラムで書くとすると？を知ることができます。
じゃあそれで全部自動化できるじゃんと思うかもしれないですが、セルの指定先などが固定でループ処理もできないので、これだけで完成されたマクロを作ることは難しいです。また、マクロの記録は逐次的にプログラムに落としていくので、例えば間違って関係のないセルを選択してしまうとそれもコードとして反映されてしまいます。無駄な処理が多くなってしまうので、あくまでヒントとして使うことが無難です。
「マクロの記録」の使い方
まずは開発タブの「マクロの記録」を押しましょう。
すると以下のようなポップアップが出てくるのでOKで大丈夫です。
後はプログラムを見たい操作をやってみましょう。
無駄な操作をすると余計なコードが多くなるので、ゆっくり慎重に操作しましょう。
「マクロの記録」だったボタンが「記録終了」になっているので、操作が終わったら押しましょう。
D2セルを選択してE2セルにペーストすると以下のようなコードになります。
[D2セルを選択→選択されたところをコピー→E2セルを選択→ペースト]となっていますが、E2セルにD2セルの値を代入と書けば1行で済んでしまう処理です。ヒントとして利用して、いろいろ試して改善していきましょう。
デバッグ実行で何が起きているのかを知る
VBAでもデバッグ実行を行うことができます。コードの左側をクリックすればブレークポイント（プログラム実行時に処理を中断する場所）を置くことができ、ステップイン（メソッドの中まで入っていく）、ステップオーバー（メソッドの中には入らずに次の行へいく）などで少しずつプログラムを実行することができます。
デバッグ実行をするときには変数の中身を見たい時が出てきます。数字が入っているはずなのに文字列が入っているので計算できないといったエラーが出てくる、何が入っているのだろう？といったようなときです。
[表示 ＞ ローカルウィンドウ] で変数が見れるウィンドウを表示することができます。
エラーでたときにやること
- エラーメッセージをググって何がいけないか、理解しようとしましょう。（結果わからないこともあります）
- エラー発生箇所のコードをじっくり見ましょう。スペースが抜けていたり変数名が間違っていたりしないでしょうか。多くの場合は自動で整形してくれるのですが、たまに直してくれないことがあります。
- エラーが発生するとデバッグモードになり、エラー発生箇所で止まります。変数の中身などを見てみましょう。
- エラーハンドリングをプログラムしているとハンドリングされてしまい、デバッグモードにならない場合もあります。その場合はブレークポイントを置き、少しずつ処理を実行して、エラー発生箇所を特定するところから始めましょう。
- プログラム名を書いている Sub () で黄色くなってしまったら、VBAで解釈できないメソッド名を使っている可能性があります。スペルミスをしていないか確認しましょう。
- なんだかよくわからないエラーのときは、エラー発生箇所の前にエラー発生箇所で処理しているブックやシートのActivateを入れてみましょう。Activeでなかったことから生じたエラーかもしれません。
VBAも多くの人がプログラム例や書き方をネット上で書いてくれているので、勉強には楽な言語といえます。「煩雑なコピペ作業を自動化 #1」でも述べましたが、身近な人にありがたがられるスキルですし、何より自分の作業が楽になるので、ノンプラグラマの人こそ是非、身に着けてみてください。
【VBA】煩雑なコピペ作業を自動化－#5 プログラムの改善－
前回の記事でほとんど機能的には問題なく、人の数が変わったりしたときにループの回数などを少し手直ししてあげれば問題ないと思います。ただ、いちいちプログラムを変更する必要が生じるので保守性は悪く、数十ファイルと処理対象が増えると結構時間がかかり、性能的にもいまいちです。ここではその点を改善していこうと思います。
- 5-1. エクセルが処理している様が見えないようにする
- 5-2. 処理ファイル数をマッピング表から自動で取得する
- 5-3. 実行時にプログラムを開かなくてもいいようにする
- 5-4. フォーマット変更に強くする
- 5-5. 配列で性能改善
5-1. エクセルが処理している様が見えないようにする
前回のプログラムを実行すると入力フォーマットのファイルが開いては消え、開いては消えを繰り返すと思います。これは表示させているだけで時間を使うので、見えないようにします。やり方は簡単です。プログラムの最初に
Application.ScreenUpdating = False
と書いておくだけです。これで実行時にいちいちファイルが開いては閉じ、ということがなくなります。
5-2. 処理ファイル数をマッピング表から自動で取得する
回答者数が増えたときはマッピング表の名前を追加していく必要があります。また、プログラム内のfor文のループの数を増やさないといけません。このfor文の方の修正はなくすことができます。
Dim ansNum As Integer '回答者数 '~~~ ansNum = wsans.Cells(1, 1).End(xlDown).Row '~~~ For i = 2 To ansNum '~~~ Next
整数変数 ansNum を宣言しておき、wsans.Cells(1, 1) から .End(xlDown) （「Ctrl + ↓」を押したときと同じ動作）をした時の .Row 行番号を取得して、ansNum に代入する。という処理で常に回答者シートのマッピング表の一番下の行を取得するようにします。
5-3. 実行時にプログラムを開かなくてもいいようにする
ボタンを作成して、対応を付けます。
開発タブ ＞ 挿入 ＞ ボタンを選択 ＞ プログラムの名前（Subの後から()前まで）を選択してOK
これでボタンにマクロが対応するようになります。以降、ボタンをクリックすると登録したマクロが実行されるようになります。
5-4. フォーマット変更に強くする
列挙型変数という変数を作成します。
プログラムを書き始めの Sub() よりも上に、LISTという列挙型変数の宣言を追加します。日本語で書いているので変な感じがしますが、LIST.名前 と書くと1が、LIST.入力日と書くと2が返るようになります。同様に数字が増え、LIST.趣味は5になります。
Public Enum LIST 名前 = 1 入力日 職業 出身地 趣味 End Enum
合わせてもう一つ、入力フォーマット側の列挙型変数も作っておきましょう。
Public Enum QUES 職業 = 5 出身地 趣味 End Enum
これだけではいいことはほとんどないのですが、これを使ってコピペしていく箇所のプログラムを変更しています。
【before】
'コピー&ペーストしていく（一覧のセルに値を代入していく） wslist.Cells(p, 1) = ws.Cells(1, 2) '名前 wslist.Cells(p, 2) = ws.Cells(2, 2) '入力日 wslist.Cells(p, 3) = ws.Cells(5, 3) '職業 wslist.Cells(p, 4) = ws.Cells(6, 3) '出身地 wslist.Cells(p, 5) = ws.Cells(7, 3) '趣味
【after】
'コピー&ペーストしていく（一覧のセルに値を代入していく） wslist.Cells(p, LIST.名前) = ws.Cells(1, 2) '名前 wslist.Cells(p, LIST.入力日) = ws.Cells(2, 2) '入力日 wslist.Cells(p, LIST.職業) = ws.Cells(QUES.職業, 3) '職業 wslist.Cells(p, LIST.出身地) = ws.Cells(QUES.出身地, 3) '出身地 wslist.Cells(p, LIST.趣味) = ws.Cells(QUES.趣味, 3) '趣味
いいことは二つあると思います。
- 何をコピーしているところなのかわかりやすいので、プログラム改修時に間違いが起きにくい。
- 質問項目を追加したい時、列挙型変数への項目追加とコピペ部分の追加のみで改修が済む
もし、列挙型変数を使わない状態で質問項目が追加、しかも間に追加された場合どうなるでしょう。Cellsで指定していた数字を一つずつ増やして、転記先をずらしてあげないといけません。列挙型変数を使用していれば、列挙型変数の間に入れるだけで自動で番号ずらしが完了するので、楽になります。
5-5. 配列で性能改善
少しアドバンスな内容かもしれません。数十人と回答者が増えてくるとだんだんと処理時間が長くなっていき、再実行する時間コストが気になってくることがあります。エクセルのVBAにおいて、入力（あるセルに値を書くこと）はかなり時間を使う行為になります。配列を使うことでこの入力操作をファイルごとに一回だけにすることができます。
今までのプログラムでは名前～趣味で計5回、セルへの入力を行っていますが、これをいったん配列に格納するように変更します。
注意すべきはVBAにおける配列のデフォルト開始番号は0です。エクセルの列番号と対応させると楽なので、「1 to ~」と記載し、1始まりの配列にしてしまったほうよいです。
配列宣言時にも列挙型変数を使っておくことで、改修時に面倒になる番号ずらしの手間を減らしておきます。
Dim answer(LIST.名前 to LIST.趣味) As String '回答内容の配列を宣言 '~~~ 'コピー&ペーストしていく（いったん配列に格納する） answer(LIST.名前) = ws.Cells(1, 2) '名前 answer(LIST.入力日) = ws.Cells(2, 2) '入力日 answer(LIST.職業) = ws.Cells(QUES.職業, 3) '職業 answer(LIST.出身地) = ws.Cells(QUES.出身地, 3) '出身地 answer(LIST.趣味) = ws.Cells(QUES.趣味, 3) '趣味 wslist.Activate '一覧に貼り付ける wslist.Range(Cells(p, LIST.名前), Cells(p, LIST.趣味)) = answer
上記のようにいったん配列に格納した後に、範囲指定をして配列の中身を一気に貼り付けるようにします。貼り付ける前の wslist.Activate はこれを書かないとRangeメソッドが失敗してしまうために追加しています。正直、アクティブでないシートのRangeメソッドは失敗してしまうから、という説明しかできません。（私の理解不足です。）
諸々追加したプログラムは以下のとおり
Sub ans() Application.ScreenUpdating = False Dim wblist As Workbook 'コピー先のブック Dim wslist As Worksheet '一覧シート Dim wsans As Worksheet '回答者シート Dim wb As Workbook 'コピー元のブック Dim ws As Worksheet 'アンケートシート Dim foldPath As String 'コピー元のフォルダパス Dim fileName As String 'コピー元のファイル名 Dim p As Integer 'コピー先ポインタ Dim ansNum As Integer '回答者数 Dim answer(LIST.名前 to LIST.趣味) As String '回答内容の配列を宣言 '一覧のブックを設定 Set wblist = Workbooks("アンケート一覧.xlsm") Set wslist = wblist.Worksheets("一覧") Set wsans = wblist.Worksheets("回答者") ansNum = wsans.Cells(1, 1).End(xlDown).Row '入力フォーマットのブックを設定 foldPath = "C:\Users\ttakeuchi13\Desktop\VBA\アンケート結果" p = 2 '転記先の開始位置 For i = 2 To ansNum fileName = "入力フォーマット_" & wsans.Cells(i, 1) & ".xlsx" Workbooks.Open (foldPath & "\" & fileName) Set wb = Workbooks(fileName) Set ws = wb.Worksheets("アンケート") 'コピー&ペーストしていく（いったん配列に格納する） answer(LIST.名前) = ws.Cells(1, 2) '名前 answer(LIST.入力日) = ws.Cells(2, 2) '入力日 answer(LIST.職業) = ws.Cells(QUES.職業, 3) '職業 answer(LIST.出身地) = ws.Cells(QUES.出身地, 3) '出身地 answer(LIST.趣味) = ws.Cells(QUES.趣味, 3) '趣味 wslist.Activate '一覧に貼り付ける wslist.Range(Cells(p, LIST.名前), Cells(p, LIST.趣味)) = answer p = p + 1 '転記先を下にずらす 'コピー元は保存しないで閉じる wb.Close savechanges:=False Next '今回は閉じずに保存するだけ wblist.Save End Sub
【VBA】煩雑なコピペ作業を自動化－#4 複数ファイルの記載を一覧化する－
ではいよいよ、あるきまったフォーマットのファイルを一覧化するプログラムを作成していきたいと思います。
問題を定義して、その後に書いていくところを順に説明していきたいと思います。
- Q. 自動化したい作業
- A-1. コピー先の一覧ファイルを作る
- A-2. まずは1ファイルをコピペする
- A-3. ループを追加して全ファイル処理できるようにしよう
- A-4. 【完成】一覧への記載行側のループ処理
Q. 自動化したい作業
以下のような簡単なアンケートフォーマットがあるとします。
たくさんの人（ファイル量産が面倒なので今回は3人）が回答してくれたアンケートが「アンケート結果」フォルダに入っているとします。この中身を一覧にする作業を自動化してください。
A-1. コピー先の一覧ファイルを作る
何はともあれまとめる一覧を作らないといけません。項目が網羅的にはいっていればいいので、
こんな一覧にしておきます。
また、今回はこのファイルに直接プログラムを書くので、「.xlsm」形式で保存しておきます。
A-2. まずは1ファイルをコピペする
まずは動かせるものを作るのがなによりなので、1ファイルだけコピペするプログラムを組んでみます。
- 「Alt + F11」でエディタを開きましょう
- 「プロジェクト - VBAproject」枠の中で右クリック ＞ 挿入 ＞ 標準モジュールを選択
- プログラムを書いていく（前記事のプログラムも参考に）
- 一覧化するファイルとコピー元の入力フォーマットの二つのWorkbookオブジェクトとWorksheetオブジェクトが必要になります。
- コピペするところは地道にセル同士の対応を考えてセルを指定していきます。
Sub ans() Dim wblist As Workbook '一覧のブック Dim wslist As Worksheet '一覧シート Dim wb As Workbook 'コピー元（入力フォーマット）のブック Dim ws As Worksheet 'コピー元のアンケートシート Dim foldPath As String 'アンケート結果フォルダのパス Dim fileName As String '入力フォーマットのファイル名 '一覧のブックを設定 Set wblist = Workbooks("アンケート一覧.xlsm") Set wslist = wblist.Worksheets("一覧") '入力フォーマットのブックを設定 foldPath = "C:\Users\user1\Desktop\アンケート結果" fileName = "入力フォーマット_Aさん.xlsx" Workbooks.Open (foldPath & "\" & fileName) Set wb = Workbooks(fileName) Set ws = wb.Worksheets("アンケート") 'コピー&ペーストしていく（一覧のセルに値を代入していく） wslist.Cells(2, 1) = ws.Cells(1, 2) '名前 wslist.Cells(2, 2) = ws.Cells(2, 2) '入力日 wslist.Cells(2, 3) = ws.Cells(5, 3) '職業 wslist.Cells(2, 4) = ws.Cells(6, 3) '出身地 wslist.Cells(2, 5) = ws.Cells(7, 3) '趣味 'コピー元は保存しないで閉じる wb.Close savechanges:=False '今回は閉じずに保存するだけ wblist.Save End Sub
これを書いて実行をすると
こんな感じで値が入力された状態になります。あとはこれをBさん、Cさんと繰り返していけるようループを作れれば完成です。
ちなみに入力日が変な数字になってますがこれはエクセルの書式を日付に変更すれば直ります。
A-3. ループを追加して全ファイル処理できるようにしよう
A-2まででAさんのファイルは一覧に転記できました。これをBさん、Cさんと繰り返すことができれば実行するだけでどんなに人が多くても一覧は一瞬で作れるようになります。ただし、ここで問題になるのが、ファイル名に数字がない点です。前記事の問題ではファイル名に数字があったので、それを使ってループ処理を回すことができました。今回はAさん、Bさんと数字ではないものが固定ファイル名についているので、やり方を工夫しなくてはいけません。ここでは二つの方法を紹介します。
A-3-1. マッピング表を作ってループ
一つ目の方法が１番目がAさん、二番目がBさんといったマッピング表をエクセル上に作成し、その表でループする方法です。
処理するファイル数と下記のようにどんなファイル名かわかっている場合はこの方法を推奨します。余計なファイルなどを処理しないためにも、ちょっとカッコ悪いですがファイル名をきちんと指定するのがおすすめです。
- 入力データシート_販売管理.xlsx
- 入力データシート_在庫管理.xlsx
- 入力データシート_仕入管理.xlsx
- アンケートシート_鈴木.xlsx
- アンケートシート_田中.xlsx
- アンケートシート_佐藤.xlsx
面倒ではありますが、一覧化するファイルの別シートに対象文言の一覧を作成しましょう。今回の場合は以下の感じです。
そしてループする際には順にこの「回答者」シートのセルを一つずつ使ってファイル名を変更していけばよいのです。
Dim wsans As Worksheet Set wsans = wblist.Worksheets("回答者") for i = 2 to 4 fileName = "入力フォーマット_" & wsans.Cells(i, 1) & ".xlsx" 'ファイルの処理を記載 Next
こうすると i = 1 の時：Aさん、i = 2 の時：Bさん、i = 3 の時：Cさん、となっていきます。
A-3-2. フォルダの中のファイルを全部処理
もう一つはフォルダの中のファイルをすべて処理する方法。処理するファイル数がいつも違う場合や、ファイル名が固定ではない場合はこの方法を使います。
Dir()コマンドを使って、プログラムは以下のようになります。
fileName = Dir(foldPath & "*.*") '1番目のファイル名を取得 '最初のファイルの処理を記載 Do Until fileName = "" 'ファイル名が空欄になるまで繰り返す fileName = Dir() '次のファイル名を取得 'ファイルの処理を記載 Loop
A-4. 【完成】一覧への記載行側のループ処理
次に、コピー元の方はファイルを次々に読むことが来ましたが、このままだと一覧に転記する先がずれていかないので、常に2行目に上書きしていってしまうことになります。これは別に変数（p(ポインターの略)）を設けて、ループするごとに +1 していき、転記先の行を少しずつずらすようにします。
Sub ans() Dim wblist As Workbook Dim wslist As Worksheet Dim wsans As Worksheet Dim wb As Workbook Dim ws As Worksheet Dim foldPath As String Dim fileName As String Dim p As Integer '一覧のブックを設定 Set wblist = Workbooks("アンケート一覧.xlsm") Set wslist = wblist.Worksheets("一覧") Set wsans = wblist.Worksheets("回答者") '入力フォーマットのブックを設定 foldPath = "C:\Users\user1\Desktop\アンケート結果" p = 2 '転記先の開始位置 For i = 2 To 4 fileName = "入力フォーマット_" & wsans.Cells(i, 1) & ".xlsx" Workbooks.Open (foldPath & "\" & fileName) Set wb = Workbooks(fileName) Set ws = wb.Worksheets("アンケート") 'コピー&ペーストしていく（一覧のセルに値を代入していく） wslist.Cells(p, 1) = ws.Cells(1, 2) '名前 wslist.Cells(p, 2) = ws.Cells(2, 2) '入力日 wslist.Cells(p, 3) = ws.Cells(5, 3) '職業 wslist.Cells(p, 4) = ws.Cells(6, 3) '出身地 wslist.Cells(p, 5) = ws.Cells(7, 3) '趣味 p = p + 1 '転記先を下にずらす 'コピー元は保存しないで閉じる wb.Close savechanges:=False Next '今回は閉じずに保存するだけ wblist.Save End Sub
これを実行すると
こうなれば成功です。（入力日の書式は手動で変換しています。）
【VBA】煩雑なコピペ作業を自動化－#3 ブックレベルの処理－
VBAによるコピペ作業の自動化、三つ目の記事です。
この記事までで、実は結構なことができるようになります。
3-1. ブック、シートの指定、設定
複数のブックやシートを操作する場合はまずはどのブック、シートを操作するの？ってことを指定してあげないといけません。
前回の記事で
Cells(1, 1).Select
といった記載をしていましたが、これはまじめに省略しないで書くと以下のようになります。すべて同じ意味です。
ActiveWorkbook.ActiveSheet.Cells(1, 1).Select ThisWorkbook.ActiveSheet.Cells(1, 1).Select Workbooks("ブック名").Worksheets("シート名").Cells(1, 1).Select
Activeというのは今操作しているブックやシートということで、Thisというのはこのプログラムを書いているブックということです。
Activeなブックやシートは複数のブックやシートを操作するときには処理状況によって具体的なブックやシートが変わるので、可読性や保守性の面から使用しないほうが良いです。
省略できるなら書かない方が楽なのですが、AブックからBブックにコピーしたいとなるとそうもいきません。でも毎回「Workbooks("ブック名").Worksheets("シート名")」書くのは大変だし、エクセルのファイル名とか変わったら全部直さないといけないの？となります。
そこでブックやシートの設定（Set）です。
Dim wb As Workbook 'ワークブックオブジェクトを宣言 Dim ws As Worksheet 'ワークシートオブジェクトを宣言 Set wb = Workbooks("ブック名") 'ブックを変数「wb」に設定 Set ws = wb.Worksheets("シート名") 'シートを変数「ws」に設定 ws.Cells(1, 1).Select
「'」はVBAのコメントの書き方です。
このようにブックやシートを変数に格納して以降はそれを使うようにすればOKです。またシートの登録の際、wbで「どのブックか」も指定しているので、wsを使うときはwbをいちいち指定する必要はありません。
3-2. ブックを開く、閉じる
ブックを開いたり閉じたりしていきます。
これは簡単で、開きたいブックのパスを指定して「Open」メソッドに渡してあげるだけです。
Workbooks.Open ("ファイルパス")
例えば
Workbooks.Open ("C:\Users\user1\Desktop\ファイル名.xlsx")
フォントの関係でバックスラッシュになってますが、きっと￥マークになると思います。￥マークで大丈夫です。
このままですと違うファイルを開きたいときはいちいちプログラムを直さないといけなくなるので、
Dim ws As Worksheet 'ワークシートオブジェクトを宣言 Dim filePath As String 'ファイルパスを入れる文字列を宣言 Set ws = ThisWorkbook.Worksheets("シート名") 'シートを設定 filePath = ws.Cells(1, 1) 'シートのA1セルにファイルパスを入力しておく Workbooks.Open (filePath) 'シートのA1セルにファイルパスのファイルを開く
ファイルパスを別変数にしておけば、A1セルのファイルパスを書き変えるだけでプログラムを直さずにいろんなファイルが開けます。ファイルパスはフォルダまでではなく、エクセルファイルのファイル名まで書いてあげる必要がある点が注意です。
閉じる場合は以下を書くだけです。
wb.Close
このメソッドには閉じるときに保存するかどうかのオプションが用意されていて保存したくないときは
wb.Close savechanges:=False
とします。savechanges を書かない状態でプログラム内で変更があったブックを wb.Close すると、×ボタンを押して閉じたときと同じように保存するかどうか聞かれるウィンドウが開いてしまいます。（プログラムがいったん止まります。）
3-3. ブックをコピー
コピーには「FileCopy」を使います。
FileCopy [コピー元ファイルパス], [コピー先ファイルパス]
という使い方になります。
Dim ws As Worksheet 'ワークシートオブジェクトを宣言 Dim foldPath As String 'フォルダパスを入れる文字列を宣言 Set ws = ThisWorkbook.Worksheets("シート名") 'シートを設定（ブックはThisを使って省略） foldPath = ws.Cells(1, 1) 'シートのA1セルにフォルダパスを入力しておく FileCopy foldPath & "\" & "サンプルブック.xlsx", foldPath & "\" & "サンプルブック_コピー.xlsx"
前回と多少書き方を変えています。ファイル名まで含めたパスではなく、コピーを行うフォルダまでのパスにし、ブックの名前を文字列結合します。こうすることで同じフォルダの中で複数のファイル操作をする場合に、ファイル名だけを変更することができます。
&はVBAで文字列を結合するときの演算子です。
3-sample. 複数のファイルをコピーし、各ファイルを編集する
というプログラムを書いてみます。
ファイル名でループできるようにファイル名にはコピー時に連番などを付与しておきます。
Sub ans() Dim wb As Workbook Dim ws As Worksheet Dim foldPath As String Dim fileName As String Dim sheetName As String foldPath = "C:\Users\user1\Desktop" FileCopy foldPath & "\" & "サンプルブック1.xlsx", foldPath & "\" & "サンプルブック2.xlsx" For i = 1 To 2 fileName = "サンプルブック" & i & ".xlsx" sheetName = "Sheet1" Workbooks.Open (foldPath & "\" & fileName) Set wb = Workbooks(fileName) Set ws = wb.Worksheets(sheetName) ws.Cells(1, 1) = fileName wb.Close savechanges:=True Next End Sub
以上です。複数のブックに対してその中身を自動で更新できるようになりました。
【VBA】煩雑なコピペ作業を自動化－#2 ブック内の処理－
VBAによるコピペ作業の自動化、二つ目の記事です。
VBAを触ったことがあるよって人はここからかもっと先の記事から読んでもいいかもしれません。
VBAってそもそも何ぞやって人は一つ前の記事から確認してみてください。
- 2-1. セル・範囲の選択
- 2-2. コピー&ペースト
- 2-3. 行・列の挿入・削除
- 2-4. 最大行数の取得
- 2-5. 基本構文（if, for）
- 2-exm. ループ処理でコピペをするプログラムを書いてみよう
2-1. セル・範囲の選択
エクセルを使うときにセルをクリックしたり、ドラッグして複数のセルを選択したりしますよね。
それをプログラム上でどう書くのかってのが以下になります。
◆セルを選択
◇単一
Range("A1").Select Cells(1, 1).Select
◇複数
Range("A1:B3").Select Range(Cells(1, 1), Cells(3, 2)).Select
◆行を選択
◇単一
Range("1:1").Select Rows(1).Select
◇複数
Range("1:2").Select Range(Rows(1), Rows(2)).Select
◆列を選択
◇単一
Range("A:A").Select Columns(1).Select
◇複数
Range("A:B").Select Range(Columns(1), Columns(2)).Select
Rangeで書いた方が書く量が減って簡単ですが、ループ処理をするときに列方向へのループが（A→B→Cとなって）難しくなります。また、Cellsで書くときは Cells(行, 列) とRangeの時とは逆なので、注意です。
2-2. コピー&ペースト
いよいよコピー&ペーストです。といってもそんなに難しくありません。
2-1 で Select と書いたところを Copy にすればコピーができ、PasteSpecial とすると貼り付けができます。A1セルをB1セルにコピー&ペーストしたいときは以下の通りです。
Cells(1, 1).Copy Cells(1, 2).PasteSpecial
値の貼り付けであれば、こんな書き方をしてもいいです。
Cells(1, 2) = ws.Cells(1, 1)
右側のB1の値を左のA1に入れているという見方になります。
ペースト側ですが、何がスペシャルかというと、ペーストするときは値だけとか書式だけとか全部とかいろいろ選べると思いますが、この点がスペシャルになります。
PasteSpecial で調べるといろいろ出てくるので、必要に応じて調べてください。
おそらく値貼り付けだけわかれば事足りることがほとんどだと思います。
Cells(1, 2).PasteSpecial Paste:=xlPasteValues
2-3. 行・列の挿入・削除
さらりといきます。挿入は Insert、削除は Delete です。
一覧の行の間に入れたいときや、下にフォーマットを伸ばしたい時に使います。
Rows(2).Insert Rows(2).Delete Columns(2).Insert Columns(2).Delete
2-4. 最大行数の取得
ループをしたいときの終点を見つけるときに使います。一番下の行番号が変わる場合はこれを使ってループの終点を見つけます。
ちなみに薄々気が付いてきているかもしれませんが、VBAのコードは一行一行がほぼエクセル上で行う操作に対応しています。
今回の場合は Ctrl + ↑ や Ctrl + ↓ に対応しています。
まず、最大行数を入れる変数を宣言します。
Dim r As Integer
変数の宣言には Dim とまず書きます。rはrowのrです（変数名は何でもいいです）。行数は整数なので、 As Integer と書きます。
r = Cells(10000, 1).End(xlUp).Row
A10000セルをクリックした後（Cells(10000, 1)）に Ctrl + ↑ （.End(xlUp)）をし、その時の行数（.Row）を r に入れる（=）、という意味になります。
これだけだと実行しても、スンッとしかならないので、
MsgBox r
を追加して実行してみましょう。r の中身が見えると思います。
2-5. 基本構文（if, for）
最後に基本構文の内、よく使う二つを紹介しておきます。
◆for
iを1から一つずつ増やして r まで増やし、r の時の処理までやったら終わる。
For i = 1 To r [処理内容（iを行番号を指定する場所に使うなどする）] Next
◆if
条件式が合っている時だけ処理内容を実行する。
Elseで条件に合っていないときの処理やElseIfで条件に合っていないときに別の条件に合うときの処理を書くことができる。
if [条件式（Cells(1, 1)=1 など）] Then [処理内容] （Else） （ElseIf [条件式] Then） End If
【VBA】煩雑なコピペ作業を自動化－#1 VBAとは～プログラム実行－
どんな人に読んでほしいか
- たくさんの人に書いてもらったアンケートや申請書
- テスト内容ごとに分かれた大量のテスト仕様書や入力データシート
すべて別ファイルで保存されていて一つ一つ開かないと中が見れないので大変。一つのエクセルに一覧化されてたら情報が管理しやすいけど、コピー&ペーストも大変。なんてことはないでしょうか。
RPAやAIなどIT業界にいれば、言葉を聞くようになってからかなり久しいですが、導入までのハードルからまだまだ誰でも使ってるなんて域には達していないように思います。
RPAなどなんかすごそうなものに頼らずともいつも使っているエクセルを使ってプログラムを書けば、結構できることの幅は広いのです。
この記事はたくさんのエクセルやCSVファイルの管理に四苦八苦している人に向けて、簡単に一覧化できたり、ピボットテーブルだとなんかうまくできない表の変換だったりができるようになって、イライラする作業から解放されることを目的にしています。
できれば、プログラムを触ったことのない人もできるようになれればと思います。
最初の記事はそもそもVBAってなんぞ？何ができるの？ってとこから始めたいと思います。
1-1. VBAとは？何ができるの？
マクロという言葉ならば、お聞きしたことがあるかもしれません。人によってはVBという言葉も。言葉の説明にはいいブログがあったので、こちらを参照してください。
www.sejuku.net
ここではVBAのいいところやVBAのできることを記載します。
1-1-1. VBAのいいところ
なんといっても、エクセルだけでできること。
エクセルはきっとどんな企業でも学生でも使っていて、普通のプログラミング言語と違って本当に誰でも使っているので、開発環境がすでに手元にあるというのがいいところです。
正直動作が不安定なところもあったり、エラーが意味不明だったりで、商用にする言語としては微妙で、それゆえになんとなくVBAの言語地位が低いように感じているのですが、内部利用ではこれほど導入が簡単な言語もありません。
この記事で取り上げて作れるようになってもらう機能も本当に大したことない機能だと思うのですが、本当にびっくりするぐらいありがたがれることが多いです。新人や事務処理の多い人はある程度使えるようになっていた方がいいです。
1-1-2. VBAで何ができるの？
結構いろんなことができます。Internet Explorerを自動で操作したり、他のOffice製品（特にAccessなど）を操作したり、VBAじゃなくても分析ツールでできますが、重回帰分析などもできます。この記事では大量にある統一化されたフォーマットのエクセルブックを一つの一覧にまとめるまで、を記載します。
1-2. エディタを開こう
前置きが長くなりましたが、さっそく何か書いてみましょう。
まず、エクセルを開いてください。
そこから以下を参照して、「開発」タブが表示されるようにしましょう。
support.microsoft.com
開発タブを選択 ＞ VisualBasic というボタンをクリックして、
こんな画面が出れば成功です。
ちなみに「Alt + F11」でいきなりこの画面を開くことができます。
1-3. 実行してみよう（メッセージボックス表示）
何はともあれ、ひとまず何か実行してみましょう。
左上の「プロジェクト - VBAproject」ってタイトルの枠の中を右クリックして
挿入 ＞ 標準モジュールを選択
そうすると灰色だった右側の大きい枠に「Module1」が開かれた状態になります。
Sub test()
これを書いて Enter しましょう。すると End Sub が自動で表示されると思います。
Sub test() ～ End Sub が test という名前のプログラムの中身を書く場所になります。
間に
MsgBox "ようこそ自動化の入口へ"
と打ってみましょう。大文字とかは意識しなくても勝手に直してくれるはずです。
こんな感じになります。
ここまで書いたら、
実行 ＞ Sub/ユーザフォームの実行 か
書式の下くらいにある、緑三角の再生ボタンみたいなのを押しましょう。
こんなウィンドウが出てくれば成功です。
1-4. ブックの保存
ファイルの拡張子が .xls の時はそんなことなかったのですが、
今のエクセルではVBAのプログラムが入ったブックは .xlsx とは違う拡張子で保存する必要があります。
名前を付けて保存で .xlsm という拡張子で保存してください。
今回はここまでです。
次回は一つのエクセルの中でできることを書いていきます。
自己紹介
ご挨拶
はじめまして。隅田川の河口付近で働いているシステムエンジニアです。
インプットばかりしていても意味がないとはよく聞くものの、私自身「アウトプット大全」などなど本を読んでインプットしたままで、やはりアウトプットしないままでおりました。
人様の目にさらされるという恐怖心、自分なんかが書いたものをネット上に載せて何の意味があるのかという気持ちやせっかく書くならば読んでもらいたいという期待も持つ反面、厳しくこき下ろされたら嫌だなという意味のない自尊心から、さらにアウトプットすることから自分を遠ざけていました。
個人的な転機もあり、このままではいけないと知財の棚卸をと割り切り、記事として残していこうと思います。
趣味
バスケットボールを中学生からずっとやっています。センターですが背が低すぎるので、フックシュートを得意にしてきました。今はスリーポイントを鍛え中です。最近少し入るようになってきて、市民権を得てきている気がします。
お酒はウィスキー、料理、アニメ、ゲームが好き。安いウィスキーの記録や燻製料理、大人向けのおすすめアニメ、なんて趣味の話もかけたらなんて思います。
お仕事
LinkedInを参照してください。
