非表示にできる< 目次 >
学習パート
本記事のパートでは『Chapter４ -深堀』を説明します。
これまでの学習パート
Chapter 4 - セル選択の応用
カレントリージョン(CurrentRegion)
キーボード上のショートカットCTRL+A と同じ効果を得られます。
＜コードの説明＞
|
1 |
Range("B2").CurrentRegion |
＜コードの使い方＞
CurrentRegion はCopyと併用することが最も多いです。
◎実例紹介-その1
＜実例コード＞
|
1 |
Range("起点セル").CurrentRegion.Copy Range("対象セル") |
また、CurrentRegionは範囲選択になることから、
様々なセルで構成されているといえ Object変数にのみ格納できます。
◎実例紹介-その2
＜実例コード＞
|
1 2 3 |
Dim 変数名 As ObjectSet 変数名 ＝ Range(“B2”).CurrentRegion 変数名.Copy Range("A1") |
ユーズドレンジ(UsedRange)
EXCELシート全体に対して、
ショートカットCTRL+SHIFT+END とほぼ同じ 効果を得られます。
データがある全てのセル範囲を取得することができます。
＜コードの説明＞
|
1 |
Worksheets(“シート名”). UsedRange |
＜コードの使い方＞
UsedRangeはCopyと併用することが最も多いと思います。
※CurrentRegionと同様です。
◎実例紹介-その1
＜実例コード＞
|
1 |
Worksheets("シート名").UsedRange.Copy Worksheets("シート名").Range("貼付先セル") |
◎実例紹介-その2
＜実例コード＞
|
1 2 3 |
Dim 変数名 As ObjectSet 変数名 ＝ Worksheets("シート名").UsedRange 変数名.Copy Worksheets("シート名").Range("貼付先セル") |
参考：全選択コピー貼付
シート全てをコピーして違うシートに貼付したい時のコードを説明します。
コードのみの紹介となります。
全てのセルを値貼付けするためには下記のコードが最適です。
これは そのまま使用した方が良いのでマネしましょう。
|
1 2 3 4 5 6 7 |
Sub 全選択コピー貼付() Worksheets("コピー元のシート名").Cells.Copy Worksheets("コピー先のシート名").Cells.PasteSpecial xlPasteValues Application.CutCopyMode = False End Sub |
Chapter 4 -Call（別のマクロを呼び出す）
復習：VBAのプログラムの単位はSub～EndSub
「Sub～EndSub」を1つの単位にプログラムは実行されます。
詳細は、「Chapter 2 -Sub～EndSub」にありますので、
復習したい方は ご確認ください。
CallではSub～EndSubを呼び出せる
Callでは、別で作成したSub～EndSubを呼び出せます。
＜Callの使用方法＞
|
1 |
Call マクロ名 |
Callの注意点
Callを通じての変数(保存BOX)利用には 注意が必要です。
変数には有効範囲があり、
Call使用時によく間違えてしまう箇所になります。
変数の有効範囲を このタイミングで学んでおきましょう。
◎Sub ～EndSub内で宣言(Dim)した変数
有効範囲：Sub～EndSubのみ
Sub ～End Sub で囲われた間で宣言した変数(Dim a As String)は、宣言したSub ～End Sub 内のみ、有効です。
◎Sub ～EndSubの外で宣言(Dim)した変数 の場合
有効範囲：宣言したモジュール全体
Sub ～End Sub の外で宣言した変数(Dim a As String)は
宣言したモジュール内でのみ、有効です。
◎Publicで変数を宣言
有効範囲：全モジュール
突然Publicが登場しましたが、
Dim の代わりに Publicを使用することもできます。
形は「Public 変数 As 型」になります。
Publicの英語の意味は「公共」の意味となり、
「該当の変数を 標準モジュール全体で 公共利用する」と訳すと覚えやすいです。
Chapter 4 -ブレークポイント
VBAコード実行時、途中で止めて動作を確認したいときに
ブレークポイントを使用します。
使用方法は簡単。
VBE上で、一時停止したいコード横の空欄をクリックすれば良いです。
解除したい場合は、お判事箇所をクリックすればOK。
Chapter 4 -IF文（条件式）
EXCELのIF関数を知っている前提で説明します。
EXCELのIF関数を元に理解すると分かりやすいと思います。
条件式が1つの場合
◎EXCEL-IF関数の場合
IF(条件式,真(True)の場合,偽(False)の場合)
それではVBAだとどういった記載になるのかというと。
◎VBA-IF文の書き方
IF 条件式 then
真(True)の場合
Else
偽(False)の場合
End IF
関数もVBAも どちらもEXCELが親なので類似して分かりやすいと思います。
また、Elseは省略できます。
Elseを省略した場合、「偽(False)の時は処理を実行しない」の意味になります。
◆Else は省略できる
IF 条件式 then
真(True)の場合
End IF
※偽(False)の時は処理を実行しない
条件式が複数の場合
条件式が複数の場合、ElseIFが登場します。
実際の書き方をみて確認しましょう。
◆ElseIFの書き方
IF 条件式 then
真(True)の場合
ElseIF 条件式 then
真(True)の場合
Else
偽(False)の場合
End IF
こちらも Elseは省略できます。
Elseを省略した場合、「偽(False)の時は処理を実行しない」の意味になります。
◆Elseif でも Else は省略できる
IF 条件式 then
真(True)の場合
ElseIF 条件式 then
真(True)の場合
End IF
※偽(False)の時は処理を実行しない
Chapter 4 -For Next（ループ系-回数上限タイプ）
処理を10回 続けて行いたい。
そのような時に For Next を利用します。
同じ処理を X回処理したい
For Nextを使用するためには 変数(Long型)が必要です。
◆For Nextの書き方(例:10回処理をしたい の時)
Dim 変数 As Long
For 変数 = 1 to 10
' 処理をしたい内容を書く
Next
For Nextの構造理解
For Nextの基本的な構造は下記の内容を理解していればOKです。
For Nextを途中で抜け出したい時(Exit For)
「Exit For」で抜け出せます。
IF文と併用して、ある条件の時はループから抜け出したい場合に利用します。
◆Exit Forの書き方
For 変数 = start To end
'処理したい内容
'処理したい内容
Exit For
Next
Next
For Next の使用例
For 変数 = start(数値) to end(数値)の、
end(数値)に セルの最終行の数値を入れることで、
EXCELの先頭行から最終行まで 処理を行うことができます。
最終行を取得するコードは
「Cells(Rows.Count, 1).End(xlUp).Row」です。
（↑A列の最終行を取得できます）
このような組み方ができます。
◎コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub ForNextとIF文() Dim lastgyou As Long Dim i As Long '変数：lastgyou にA列の最終行(数値)を格納 lastgyou = Cells(Rows.Count, 1).End(xlUp).Row For i = 0 To lastgyou '★復習★：Cells(行,列）をあらわしている。 '条件式：A列が田中だったら、 If Cells(1 + i, 1).Value = "田中" Then 'B列にかっこいいと入力 Cells(1 + i, 2).Value = "かっこいい" '条件式：A列が鈴木だったら、 ElseIf Cells(1 + i, 1).Value = "鈴木" Then 'B列に要確認と入力し、 Cells(1 + i, 2).Value = "要確認" 'ForNextから抜ける（end値まで処理しないで抜ける） Exit For End If Next '←Nextに来るたびにiがカウントアップする。 End Sub |
Chapter 4 -Do Loop（ループ系-条件合致までタイプ）
～になるまで処理を続けて行いたい。
そのような時に Do Loop を利用します。
同じ処理を 〇〇になるまで処理したい
Do Loop には、大きく2つの書き方があります。
◎until型：条件式に合致するまでループ処理
Do
～
Loop until 条件式
◎while型：条件式に合致している間はループ処理
Do
～
Loop while 条件式
Do Loopの使用例
Do Loop until型とDo Loop while型について、
それぞれ使用例を紹介します。
本音を言えば、動作自体に差異はほぼ無いため、
好きな方を使用すれば良いと思います。
個人的にはuntil型が分かりやすいと感じているため、
こちらを使用する頻度が多いです。
◎Do Loop until型のコード例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Sub Do_Loop_until型の説明() Dim i As Long '変数 i を開始行として扱う Dim c As Long '変数 c をカウンターとして扱う '1行目からスタートの意味 i = 1 Do '★復習★：Cells(行,列）をあらわしている。 'A列が田中だったら If Cells(i, 1).Value = "田中" Then 'B列にかっこいいと入れる Cells(i, 2).Value = "かっこいい" '変数cをカウントアップしていく。 c = c + 1 End If 'iを1つカウントアップさせ行をずらす。 i = i + 1 Loop Until c = 100 '田中を100個カウントするまでループし続ける。 End Sub |
◎Do Loop while型のコード例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Sub Do_Loop_while型の説明() Dim i As Long '変数 i を開始行として扱う Dim c As Long '変数 c をカウンターとして扱う '1行目からスタートの意味 i = 1 Do '★復習★：Cells(行,列）をあらわしている。 'A列が田中だったら If Cells(i, 1).Value = "田中" Then 'B列にかっこいいと入れる Cells(i, 2).Value = "かっこいい" '変数cをカウントアップしていく。 c = c + 1 End If 'iを1つカウントアップさせ行をずらす。 i = i + 1 Loop while c < 100 ''田中のカウントが100個未満の間はループし続ける。 End Sub |
Chapter 4 -メッセージボックス
VBAコード実行完了後にポップアップで
「VBA実行完了」というメッセージを出したい場合には
Msgboxを使用します。
＜ソースコード＞
|
1 |
Msgbox "文字列" |
Chapter 4 -エラー処理
エラー回避
プログラムを作成するとどうしてもエラーが発生します。
理由は様々ですが、
「VBA作成者のPC環境と利用者のPC環境が相違しているケース」や
「OSやEXCEL自体のバージョン更新されたケース」が
主な理由だと思います。
本来であれば、様々なユースケースを想定して
プログラムを構築していく必要があるのですが、
初めの頃はそうもいきません。
そのような場合、エラー回避をする構文を利用すると
エラーを検知した時にスキップしたり、
エラー時の動作を設定出来たりが出来ます。
エラー回避する構文は２つあります。
＜エラー回避構文-2つ-＞
①On Error Resume Next
②On Error GoTo エラー時の動作
On Error Resume Next でエラー回避
On Error Resume Nextはどんなエラーが発生してもスキップする
とても危険なコードです。本来は修正すべきコードでさえも
無視して処理を再開していきます。
エラー理由が分かっている場合にのみ使用しましょう。
エラー理由が分かっている場合にのみ使用しましょう。
■注意■
On Error Resume Nextが解除されるまで、
On Error Resume Nextが解除されるまで、
Sub ～ End Subすべての範囲でエラーがスキップされます
※解除方法は同ページの「エラールーチンを解除する方法」を参照ください。
＜ソースコード＞
|
1 2 3 4 5 6 |
Sub Macro1() 'エラースキップ On Error Resume Next ‘ ここに処理をしたいコードを記載 End Sub |
On Error GoTo エラー時の動作 でエラー回避
On Error GoTo エラー時の動作は
エラー時に 指定の動作を作動させる エラー構文です。
例えば、エラー発生時にMsgboxで
「エラー発生。VBA作成者に連絡して下さい。」と表示させてから
VBAを終了させることができます。
尚、「エラー時の動作」はExit Sub～End Subの間に
記載するルールがあるため、ご注意ください。
（On Error GoTo でとんだエラー時の動作後は、End Subへ行き VBAが終了します。）
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 |
Sub Macro1() 'エラー検知時Err1を動作させる On Error GoTo Err1 ‘ここに処理をしたいコードを記載 'Err1はExit Sub～End Subの間で記載する必要があるため、ExitSubを記載。 Exit Sub 'エラー時の動作。そのままEndSubへとびVBAが終了します。 Err1: MsgBox "エラー発生。終了" End Sub |
On Error GoTo エラー時の動作後に処理を再開させる方法
On Error GoTo でとんだエラー時の動作後は、
基本的には End Subへ行き VBAが終了します。
しかし、
「エラー検知したコードを再実行したい場合」や、
「エラー検知した次のコードから再開したい場合」があります。
その場合は、
Resume もしくは Resume Next を利用すれば解決できます。
◎エラー検知したコードを再実行したい場合
Resumeを使用します。
ResumeにNextを使用しないことで「次へ」が欠落し、
エラーしたコードをひたすらResume（～処理再開）を繰り返します。
＜ソースコード＞
|
1 2 3 4 5 6 7 8 |
Sub Macro1() 'エラー検知時Err1を動作させる On Error GoTo Err1 ‘ここに処理をしたいコードを記載 Exit Sub Err1: MsgBox "エラー発生" Resume 'エラーしたコードを再実行させます。 End Sub |
◎エラー検知した次のコードから再開したい場合
Resume Next を使用します。
Resume Next により「次のコードから再開」という意味となります。
＜ソースコード＞
|
1 2 3 4 5 6 7 8 |
Sub Macro1() 'エラー検知時Err1を動作させる On Error GoTo Err1 ‘ここに処理をしたいコードを記載 Exit Sub Err1: MsgBox "エラー発生" Resume Next 'エラーしたコードの次のコードから処理再開させます。 End Sub |
エラールーチンを解除する方法
あるときはエラーを無視したい。
あるときはエラーを検知したい。
こんな時は、On Error GoTo 0 を使用します。
On Error GoTo 0 はエラールーチンを解除するコードです。
例えば、任意のコードを
On Error Resume Next と On Error GoTo 0でサンドイッチすれば、
サンドイッチした範囲のエラー無視が可能です。
このような形。
◎On Error Resume Next を解除する時
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub Macro1() 'エラースキップ On Error Resume Next ‘ ここに処理をしたいコードを記載 'On Error Resume Nextを解除する On Error GoTo 0 ‘ここからエラー検知させないコードを記載 End Sub |
◎On Erro GoTo Err1 を解除する時
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Macro1() 'エラー時 Err1へ飛ぶ On Error GoTo Err1 ‘ ここに処理をしたいコードを記載 'エラー時にErr1へ飛ばなくする On Error GoTo 0 ‘ここからエラー検知させたくないコードを記載 Exit Sub Err1: Msgbox "エラーを検知しました" Resume Next End Sub |
Chapter 4 -置換
置換のVBAコードの説明(Replace構文)
EXCEL関数のSubstitute関数で置換を行うことができますが、
VBAでは Replace構文を利用することで置換ができます。
Replace構文の形式は、Substituteほぼ同じ。
見比べながら説明します。
◎EXCEL-Substitute関数の説明
Substitute(文字列, 検索文字列, 置換文字列)
■例
A1セルが「ブラックコーヒー」で 「ブラック」を「Black」にしたい場合
■関数式
=Substitute(A1,"ブラック","Black")
◎VBA-Replace構文の説明
出力先 = Replace(文字列(セルや変数), 検索文字列, 置換文字列)
■例
A1セルが「ブラックコーヒー」で 「ブラック」を「Black」にしたい場合
■VBA構文
Range("C1").Value＝Replace(Range("A1") ,"ブラック","Black")
見比べてみたら、ほぼ同じということが分かります。
「同じならばEXCEL関数で良いのでは？」と思うかもしれませんが、
そこは使い分け次第になります。
EXCEL関数では無く Replace構文を使うメリット
VBA-Replace構文の優位性は以下の通り。
＜優位性＞
①処理速度が速い点
②置換内容を条件分岐で変更できる点
例えば、膨大な情報量をEXCEL関数で行った場合、
処理速度が遅く困ったことはありませんでしょうか？
Replace構文では、処理結果を都度出力していくため、
EXCEL関数のような関数の処理待ち時間が圧倒的に少ないです。
また、置換内容の条件分岐ですが、
EXCEL関数の場合、IF関数を重ね掛けして対応自体は可能ですが、
EXCEL関数で扱える関数の重ね掛けは上限が決まっている且つ、
重ね掛けした分 処理速度が大幅に低下するリスクを背負っています。
あまりに、重ね掛けしすぎると EXCELがフリーズする場合も。
EXCEL関数の多用による、
EXCEL速度が低下する理由やフリーズする理由は
EXCEL関数の仕様の問題で、
シートが更新されると 全ての関数が同時に 計算中に入ってしまい、
PCのメモリが圧迫され、上述の事象が発生します。
その点、VBA上での処理であれば、そのような心配はありません。
VBAコードを一つ一つ高速で実行しているだけなので、
PCのメモリを圧迫しないどころか、
出力結果をEXCELに反映させているだけなので、
出力後のデータ修正を行ってもEXCEL関数のような処理待ちは発生しにくいです。
※EXCEL関数とVBAの併用だった場合は除く。
EXCEL関数の方が コーディングする手間が少ないため、
使い勝手が良いかもしれませんが、
膨大な量を処理する場合はVBAで行うと 効率化することが多いので、
覚えておくと良いと思います。
Chapter 4 -VBAでEXCEL関数を使う
Application.WorksheetFunctionでEXCEL関数が使える
VBAでEXCEL関数を呼び出すことができます。
目に触れる機会が多いであろう「SUM関数」は以下のように使用ができます。
＜ソースコード＞
|
1 2 3 |
Sub macro1() Range("A1").Value = Application.WorksheetFunction.Sum(Range("B1:B5")) End Sub |
Application.WorksheetFunctionで関数を使う優位性
EXCEL関数で得られる結果のみが出力されるため、
とても早い です。
また、EXCEL関数は誰にでも消せてしまうため、
VBA上で全ての処理を完結させ関数をそもそも触らせないようにする場合も、
Application.WorksheetFunctionは有効です。
Chapter 4 -EXCEL以外のファイルを開く
Chapter 3でEXCELを1から開く際に
以下のコードでファイルをオープンしました。
|
1 |
Workbooks.Open "フルパス\ファイル名.拡張子" |
では、EXCEL以外を開く場合は、
それを紹介します。
＜ソースコード＞
|
1 |
CreateObject("Shell.Application").ShellExecute “フルパス” |
このコードを用いれば、フルパス先のアプリを起動できます。
また、URLをフルパス部分に差し込めばURLサイトを開くこともできます 。
◎使用例
|
1 2 3 4 5 6 7 8 9 10 11 |
'メモ帳を開く CreateObject("Shell.Application").ShellExecute “フルパス\メモ帳.txt” 'OfficeWordを開く CreateObject("Shell.Application").ShellExecute “フルパス\Word.docx” 'PDFを開く CreateObject("Shell.Application").ShellExecute “フルパス\〇〇.pdf” '指定のURLを開く CreateObject("Shell.Application").ShellExecute “https://nkmrdai.com/” |
Chapter 4-終わりに
以上でChapter4が終了です。
ここまで学習を終えた方は、
作業を圧縮できるアイデアを自分の手で実現できる領域に立ってい
あとは実践の部分です。
次回のChapter 5では実例を紹介し実際のコーディングについて
トータルで知って頂き、
実利用のイメージを固めていただければと思います。
引き続き頑張っていきましょう！
