【Chapter 4-深堀】超初心者から始めるEXCEL VBA【1週間で分かる】

学習パート

本記事のパートでは『Chapter４ -深堀』を説明します。

これまでの学習パート

: 【1週間で分かる】超初心者から始めるEXCEL VBA入門～応用講座【まとめ】
本記事で目指すもの結論：1週間の学習でマクロが作れるようになる！（1日2時間×5日間=10時間の学習）私自身、独学でVBAスキルを取得しましたが、事務仕事で本当に必要だった知識は一部でした ...

: 【Chapter 1-準備/体験】超初心者から始めるEXCEL VBA【1週間で分かる】
学習パート本記事のパートでは『Chapter １ -準備・体験』を説明します。 Chapter1-はじめにそもそも「マクロ」「VBA」「VBE」って何？意味は簡単です。＜説明＞ ...

: 【Chapter 2-基本/動作】超初心者から始めるEXCEL VBA【1週間で分かる】
学習パート本記事のパートでは『Chapter 2 -知識・基本動作』を説明します。これまでの学習パート本学習パートまでに以下のパートを完了させましょう。 https://nkmrdai.com/ ...

: 【Chapter 3-基本/応用】超初心者から始めるEXCEL VBA【1週間で分かる】
学習パート本記事のパートでは『Chapter 3 -基本/応用』を説明します。これまでの学習パート本学習パートまでに以下のパートを完了させましょう。 https://nkmrdai.com/%e ...

Chapter 4 - セル選択の応用

カレントリージョン(CurrentRegion)

キーボード上のショートカットCTRL+A と同じ効果を得られます。

＜コードの説明＞

Range("B2").CurrentRegion

＜コードの使い方＞

CurrentRegion はCopyと併用することが最も多いです。

◎実例紹介-その1

＜実例コード＞

Range("起点セル").CurrentRegion.Copy Range("対象セル")

また、CurrentRegionは範囲選択になることから、

様々なセルで構成されているといえ Object変数にのみ格納できます。

◎実例紹介-その2

＜実例コード＞

Dim 変数名 As ObjectSet
　  変数名 ＝ Range(“B2”).CurrentRegion
　　変数名.Copy Range("A1")

ユーズドレンジ(UsedRange)

EXCELシート全体に対して、

ショートカットCTRL+SHIFT+END とほぼ同じ効果を得られます。

データがある全てのセル範囲を取得することができます。

＜コードの説明＞

Worksheets(“シート名”). UsedRange

＜コードの使い方＞

UsedRangeはCopyと併用することが最も多いと思います。

※CurrentRegionと同様です。

◎実例紹介-その1

＜実例コード＞

Worksheets("シート名").UsedRange.Copy  Worksheets("シート名").Range("貼付先セル")

◎実例紹介-その2

＜実例コード＞

Dim 変数名 As ObjectSet
　   変数名 ＝ Worksheets("シート名").UsedRange
　　 変数名.Copy  Worksheets("シート名").Range("貼付先セル")

参考：全選択コピー貼付

シート全てをコピーして違うシートに貼付したい時のコードを説明します。

コードのみの紹介となります。

全てのセルを値貼付けするためには下記のコードが最適です。
これはそのまま使用した方が良いのでマネしましょう。

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の使用方法＞

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

For Next の使用例

For 変数 = start(数値) to end(数値)の、

end(数値)にセルの最終行の数値を入れることで、

EXCELの先頭行から最終行まで処理を行うことができます。

最終行を取得するコードは

「Cells(Rows.Count, 1).End(xlUp).Row」です。

（↑A列の最終行を取得できます）

このような組み方ができます。

◎コード

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型：条件式に合致するまでループ処理

　～

Loop until 条件式

◎while型：条件式に合致している間はループ処理

　～

Loop while 条件式

Do Loopの使用例

Do Loop until型とDo Loop while型について、

それぞれ使用例を紹介します。

本音を言えば、動作自体に差異はほぼ無いため、

好きな方を使用すれば良いと思います。

個人的にはuntil型が分かりやすいと感じているため、

こちらを使用する頻度が多いです。

◎Do Loop until型のコード例

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型のコード例

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を使用します。

＜ソースコード＞

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が解除されるまで、

Sub ～ End Subすべての範囲でエラーがスキップされます

※解除方法は同ページの「エラールーチンを解除する方法」を参照ください。

＜ソースコード＞

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が終了します。）

＜ソースコード＞

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（～処理再開）を繰り返します。

＜ソースコード＞

Sub Macro1()　'エラー検知時Err1を動作させる　　On Error GoTo Err1 
　　　　‘ここに処理をしたいコードを記載
　    Exit Sub
 Err1:
 　MsgBox "エラー発生" 
　 Resume   'エラーしたコードを再実行させます。

End Sub

◎エラー検知した次のコードから再開したい場合

Resume Next を使用します。

Resume Next により「次のコードから再開」という意味となります。

＜ソースコード＞

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 を解除する時

＜ソースコード＞

Sub Macro1()
　'エラースキップ
　　On Error Resume Next

　　　‘ ここに処理をしたいコードを記載

　'On Error Resume Nextを解除する
　　On Error GoTo 0

　　　‘ここからエラー検知させないコードを記載 

End Sub

◎On Erro GoTo Err1 を解除する時

＜ソースコード＞

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関数」は以下のように使用ができます。

＜ソースコード＞

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から開く際に

以下のコードでファイルをオープンしました。

Workbooks.Open　"フルパス\ファイル名.拡張子"

では、EXCEL以外を開く場合は、どのようなコードを使えばよいか。

それを紹介します。

＜ソースコード＞

CreateObject("Shell.Application").ShellExecute   “フルパス”

このコードを用いれば、フルパス先のアプリを起動できます。

また、URLをフルパス部分に差し込めばURLサイトを開くこともできます。

◎使用例

'メモ帳を開く
  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では実例を紹介し実際のコーディングについて

トータルで知って頂き、

実利用のイメージを固めていただければと思います。

引き続き頑張っていきましょう！

次学習パートへ

: 【Chapter 5-実例/紹介】超初心者から始めるEXCEL VBA 【1週間で分かる】
学習パート本記事のパートでは『Chapter 5 -実例/紹介』を説明します。本パートでは、Chapter1～4で学んだことをベースに実例のコードを紹介していきます。これまでの学習パートこれま ...