VBAをこれから学びたい人には、おそらくこのようなことが頭の中に浮かんでくるのではないでしょうか？
「VBAを使ってExcelでの作業を自動化したいけど、一体何を自動化すれば良いのか？」
この問いを更に掘り下げてみました。
Excelで文章を書いたりグラフを作ったりすることもあるかもしれませんが、最も行われているのは表作りです。
思い通りの表を作るためには、「セル範囲を指定できること」が必要です。
そこで今回は、VBAを使って表を作るときの基本となる、セル範囲の指定について書いてみました。
もくじ
この記事を書いている私は、約10年前からExcelVBAを使っています。
使い始めの時は、マクロ記録のみに頼った範囲指定を行っていました。
マクロ記録だけでは思い通りの表を作れず、運用し始めては何度も作り直す作業を繰り返してきました。
しかし、正しいセル範囲の指定方法を学び、実務で活用することにより手直し回数が激減しました。
今回紹介する方法をマスター出来れば、項目数を増やした場合にも、行数や列数を自動で調整できるようになります。
また、今回はセル選択の応用にも触れてみました。
Accessなど他のアプリからExcelのセルを取得する方法です。
VBAでExcelの表作りを自動化できるようになりたい方に、少しでも今回の内容が役に立てれば幸いです。
マクロ記録でもセル範囲を指定できるが…
マクロ記録でA1セルからD10セルまでを選択した場合、
Range(“A1:D10”).select
と記録されます。
「“A1:D10”」は文字列です。
マクロ記録と同じ方法でセル範囲を指定し直すためには、かっこの中の文字列を変えなければいけません。
作りたい表の行数や列数はいつも同じではありません。
Rangeの中を「”A1:D10”」といった文字列で指定するのは不十分なので、少し手を加える必要があります。
基本はCellsと変数を組み合わせてセル範囲を指定
まずは、Rangeの中に開始セルと終了セルを入れてあげるとよいです。
Range(Cells(1,1),Cells(10,4)).Select
上のように書き換えても、マクロ記録と同じ動作になります。
Cellsの第一引数は行で、第二引数は列です。
更に、変数を一緒に活用すると、変化に強いプログラムコードになります。
Cellsを使ったサンプルコードを実際に実行してみましょう。
Sub Get_Range1()
Dim i As Integer '変数iを指定
For i = 0 To 9 '0から9まで繰り返し
Range(Cells(i + 1, 1), Cells(i + 1, i + 1)).Select
Selection.Interior.ColorIndex = 3
Next
End Sub
サンプルコードはそのまま実行すると一瞬で終わってしまいますので、必ずステップインで動きを確認しましょう。
また、最近このようなツイートをしたところ、20人近くの方が共感してくれています。
Excelのマクロ記録では、
— カピオ@システム担当の父親 (@sys_dad) December 13, 2020
セル範囲がRange（"A1:B1"）
のような感じで記録されますが、
更に成長するためには
Cellsを覚えるべき。
数値の変数を使えば、
自由に範囲を選択できます。
過去の自分の学習を振り返ると、
これを知らなかったために伸び悩みました。
OffsetやResizeも活用して自由自在にセル範囲を指定
Rangeを使わずにセル範囲を選択できるようになると、様々な場面で活用できます。
それは、CellsにOffsetやResizeを組み合わせる方法です。
Offsetは原点から何マスずらすか、Resizeは何マス分範囲を広げるか指定します。
実際にサンプルコードを実行してみましょう。
Sub Get_Range2()
Dim i As Integer, j As Integer '変数iとjを指定
For i = 0 To 19 'iを繰り返し
For j = 0 To 9 'jを繰り返し
Cells(1, 1).Offset(i, j).Value = i * 10 + j
Next j
Next i
End Sub
行数と列数分だけ繰り返すので、二重ループにしています。
実行してみると、このように値が貼り付けられます。
OffsetやResizeを組み合わせてデータを貼り付ける方法は、データベースからワークシートに欲しいデータのみを選んで反映させる場合に重宝します。
VBAで行や列を指定する
Excelを操作する時に、行や列を選択することがありますよね？
マクロ記録の場合はセル範囲と同じように文字列で記録されますが、数値に書き直した方が制御しやすいプログラムになります。
1行や1列を選択する
1行や1列をマクロ記録すると、
Columns("B").Select
Rows("3").Select
と記録されます。
ColumnsやRowsを数値で指定する場合は、
Columns(2).Select
Rows(3).Select
となります。
複数行や複数列を選択する
複数行や複数列をマクロ記録した場合は、下のようになります。
Columns("A:E").Select
Rows("1:4").Select
文字列を数値に指定しなおすこともできますが、ここで注意しないといけないことがあります。
Columns(1:5).Select
Columns(1,5).Select
上の場合はエラーになってしまいます。
このようにRangeを組み合わせて使えば、エラーを起こしません。
Range(Columns(1), Columns(5)).Select
Range (Rows(1), Rows(4)).Select
他のアプリからExcelのセル範囲を指定する
AccessやVB.NETなどの別のアプリからExcelを操作することも出来ますが、マクロ記録のコピペのみでは正しく動かすことができません。
以下の2つのことに注意する必要があります。
シートから指定し直す
先ほど行や列を指定する方法について指定しましたが、他アプリから操作できるようにするには、下のようにシートから指定しなおさないといけません。
Sheets(”Sheet1”).Range(Columns(1), Columns(5)).Select
Sheets(”Sheet1”).Range (Rows(1), Rows(4)).Select
ここでは、"Sheet1"と文字列指定していますが、ワークシートの場合は好みでも大丈夫です。
シートの枚数が増減する場合は数値(シート番号)で指定する必要がありますが、枚数を増やしすぎると使いづらくなります。
少ない枚数でシート名で指定してあげるくらいが使いやすいです。
設定「xl～」を数値に修正する
例えばマクロ記録で表の最終行を取得した場合、
Selection.End(xlDown).Select
と記録されます。
他のアプリで同じコードでは正しく動きません。
そんな時にはヘルプを活用します。
まずは表示タブからオブジェクトブラウザーを表示させましょう。
RangeクラスのEndメンバーを選択します。
そのまま右クリックし、ヘルプをクリックします。
マイクロソフトの公式リファレンスが開き、対象のページが表示されます。
表示されているページ内の、データ型「XlDirection」をクリックします。
移動方向についての値が表示されます。
ここでは、xlDownは、「-4121」となっています。
Selection.End(-4121).Select
と修正して実行してみると、マクロ記録と同じ動作になります。
まとめ
今回は、Excel VBAでセルを操作する方法について紹介しました。
Excel VBAを使いこなすには、セル範囲を思い通りに操作できなければなりません。
基本はマクロ記録されたものをそのまま使うと、冗長なメンテナンスしづらいコードになってしまいます。
- 文字列を数値に書き換える
- 条件分岐・繰り返し・変数をフル活用する
上のことを行うと、少ない行数で複雑なセルの操作を行うことが出来ます。
また、Excel VBAのスキルが伸びてくると、将来は他のアプリからExcelを操作したくなります。
そんな時は、今からヘルプに慣れておけば大きく役に立ちます。
本日もありがとうございました。