01-VBA

制御構文|Excel VBA (Part.6)【For Next】ネスト編(前編)| 現役エンジニア&プログラミングスクール講師

目標

For~Nextのネストを理解して利用できる
ネストした繰り返し文の動きをイメージできる
Resizeプロパティを理解して利用できる。

For~Nextステートメントのネストの利用方法と動作

For~Nextステートメントのネストの利用方法

ネストとは入れ子構造のことでした。先ずは、どのような場面で利用されるのかを考えてみたいと思います。

例えば形式の同じ表が複数個用意されているExcelファイルで、それぞれの表に同様の計算や処理を行わせたい場合などがあります。このような場面でひとつ目の表の複数のセルで繰り返しの処理を行わせ、終わったら次の表へ、また終わったら更に次の表へと処理が移動するようなプログラムを記述する時に利用されます。

今回は上記のようなプログラムを記述してみます。次の制御構造(Part.7)ではもう少し複雑な処理のプロシージャを組んでみようと思います。

ネストされた繰り返し文のプログラムの動き

先ずは基本の動きを理解するためにシンプルなFor文のネストを確認します。

サンプルプロシージャ1

Sub NestFor1()

Dim i As Integer, j As Integer

For i = 1 To 5
    For j = 1 To 5
        Cells(i, j) = i * j
    Next j
Next i

End Sub

実行結果

動作の確認

繰り返しの入れ子では外の繰り返しが1回動くごとに、内側の繰り返すが全て動きます。

具体的には

  1. 外側の「i」が1の時「j」が1,2,3,4,5とカウントされ5回動く
  2. 外側の「i」が2の時「j」が1,2,3,4,5とカウントされ5回動く
  3. 外側の「i」が3の時「j」が1,2,3,4,5とカウントされ5回動く
  4. 外側の「i」が4の時「j」が1,2,3,4,5とカウントされ5回動く
  5. 外側の「i」が5の時「j」が1,2,3,4,5とカウントされ5回動く

となっています。

サンプルプロシージャ2

次は、冒頭であげた「形式の同じ表が複数個用意されているExcelファイルで、それぞれの表に同様の計算や処理を行わせたい場合」について参考のプロシージャを作成します。

Dim sheetName(3) As String
sheetName(1) = "Sheet1"
sheetName(2) = "Sheet2"
sheetName(3) = "Sheet3"

Sheets("Sheet4").Select
Range("C3").Select
ActiveCell.CurrentRegion.Select
Selection.ClearContents

For i = 1 To 3
    Worksheets(sheetName(i)).Select
    Range("C3").Select
    ActiveCell.CurrentRegion.Select
    tableRow = Selection.Rows.Count
    tableColumns = Selection.Columns.Count
   
    If i = 1 Then
        Selection.Copy
        Sheets("Sheet4").Select
        Range("C3").Select
        ActiveSheet.Paste
    Range("C3").Select
    ElseIf i <> 1 Then
        ActiveCell.Offset(1, 0).Resize(tableRow - 1, tableColumns).Select
        Selection.Copy
        Sheets("Sheet4").Select
        Range("C3").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
    End If

    Application.CutCopyMode = False
    
    If i = 3 Then
        ActiveCell.CurrentRegion.Select
        tableRow = Selection.Rows.Count
        tableColumns = Selection.Columns.Count
        ActiveSheet.Range("H5").Select
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For j = 1 To tableRow - 2
            ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
            ActiveCell.Offset(1, 0).Select
        Next j
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
Next i

End Sub

ネストされているFor文は下から8行目の「For j = 1 To tableRow」の部分となります。

このプログラムでは下のキャプチャのような日計計算のエクセルファイルがあったと仮定して最終的な月次の収支を出すような場面を想定しています。シートは「現金収支のシート(Sheet1)」「口座収支のシート(Sheet2)」「ガード入金(Sheet3)」の三種類があり、最終的に一枚のシート(Sheet4)へ併合して月の収支を出すものです。

それぞれの収支はキャプチャのようになっています。

これら3つの表を纏めてひとつのシートに併合します。そこで月の収支を出してみます。

実行結果

Sheet4に事前に準備していた表で月次の計算を行わせます。(この表はVBAではなく通常のワークシート関数を利用したエクセルの表です。)

補足説明部分

Selection.ClearContents…選択された範囲の値を消去します。

サンプルプロシージャ2の中身はこれまでの記事で取り上げていたものばかりなのでネストの動きさえ理解できていれば全ての動きを把握することができます。

Resizeプロパティの概要と利用方法

Resizeプロパティの概要

Resizeプロパティとは指定した行数と列数を基にして範囲選択をできるプロパティです。

指定された範囲のサイズを変更します。 サイズが変更されたセル範囲 (Range オブジェクト) を返します。

https://learn.microsoft.com/ja-jp/office/vba/api/excel.range.resize

Resizeプロパティの利用方法

Resizeプロパティは任意のセルを起点にして指定した行数と列数分の範囲を取得することができます。例えばセルB5を起点のセルとした場合、次のように記述します。

「Range(“B5”).Resize(3,3).Select」

これでセルB5からD7の範囲を取得して返してくれます。

Resizeプロパティの利用例

Resizeプロパティは一覧表を取得する場合に「見出し部分(フィールド部分)を省く」といいた処理を可能にしてくれます。

例えば、下の表で赤い枠部分を選択範囲から除外するといった場合は、次のようにResizeプロパティを利用します。

以下手順。(今回は一覧表の一番上の行を除外するときの処理)

1.取得したい表の左上を選択(アクティブセルに)する。


2.ActiveCell.CurrentRegion.Selectで一覧表の全選択を行う。


3.Selection.Rows.Countで取得した一覧表の行の数を取得する。
4.Selection.Columns.Countで取得した一覧表の列の数を取得する。


5.アクティブセルをひとつ下に移動する。

6.Resizeプロパティを利用してアクティブセルから5行6列の範囲を取得選択する。

ここで行数をひとつ減らしています。アクティブセルを既にひとつ移動しているので、このセルからの行数を取得することになります。よって取得するための行数は「6」ではなく「5」となります。


「ActiveCell.Resize(5,6).Select」

今回のプロシージャでは

ActiveCell.Offset(1, 0).Resize(tableRow – 1, tableColumns).Selectとしています。

意味:「アクティブセル.ひとつ下のセル.Resize(行数-1, 列数).選択」となります。

今回は以上です。

初心者も実践で通用!「VBA・VBS」おすすめ書籍5選 | 現役エンジニア&プログラミングスクール講師「VBA・VBS」初心者の方が実践業務の中でそれらを活用しt活躍できるために必要な知識を習得できる書籍を紹介しています。ページの下部には「おすすめのITスクール情報」「おすすめ求人サイト」について情報を掲載中。...

ブックマークのすすめ

「ほわほわぶろぐ」を常に検索するのが面倒だという方はブックマークをお勧めします。ブックマークの設定は別記事にて掲載しています。

「お気に入り」の登録・削除方法【Google Chrome / Microsoft Edge】| 現役エンジニア&プログラミングスクール講師「お気に入り」の登録・削除方法【Google Chrome / Microsoft Edge】について解説している記事です。削除方法も掲載しています。...
【パソコン選び】失敗しないための重要ポイント | 現役エンジニア&プログラミングスクール講師【パソコン選び】失敗しないための重要ポイントについての記事です。パソコンのタイプと購入時に検討すべき点・家電量販店で見かけるCPUの見方・購入者が必要とするメモリ容量・HDDとSSDについて・ディスプレイの種類・バッテリーの持ち時間や保証・Officeソフト・ウィルス対策ソフトについて書いています。...
RELATED POST
01-VBA

Excel VBA の 配列(サンプルプロシージャの利用)| 現役エンジニア&プログラミングスクール講師

2022年12月25日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site
01-VBA

制御構文|Excel VBA (Part.10)【Do Loop】実用的なプログラム編 | 現役エンジニア&プログラミングスクール講師

2022年12月2日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site
01-VBA

ExcelVBAのEndプロパティ(2)| 現役エンジニア&プログラミングスクール講師

2022年8月24日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site
01-VBA

ダイアログボックス|Excel VBA (Part.2)【MsgBox】組み込み定数編 | 現役エンジニア&プログラミングスクール講師

2022年12月3日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site
01-VBA

Excel VBA の 配列(静的配列)| 現役エンジニア&プログラミングスクール講師

2022年12月24日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site
01-VBA

ExcelVBAのActiveCellプロパティ | 現役エンジニア&プログラミングスクール講師

2022年8月15日
プログラミング学習 おすすめ書籍情報発信 パソコン初心者 エンジニア希望者 新人エンジニア IT業界への就職・転職希望者 サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site