Categories: VBA基礎

EXCEL VBA　Option Baseの使い分け・Array・Join・Split関数・二次元配列・表データを格納

EXCEL VBA　Option Baseの使い分け・Array関数・Join関数・Split関数・一次・二次元配列・表データを配列に格納（Option Baseステートメント）

●はじめに

今回説明するのは、Option Baseステートメントの使い分け方法を説明いたします。EXCEL　VBAで配列を取り扱うのは難しいですが、配列の使い方を理解する事で実行速度の向上を図ることが出来ます。Option Baseは、配列の添字（インデックス番号）を０又は１からに設定しますが、Array関数・Join関数・Spiit関数などを利用する際に、どのような影響が発生するのかを説明いたします。それでは、サンプルプログラムを交えて順番に説明いたします。

●【EXCEL VBA 配列の使い方(Array LBound UBound)の利用方法は、下記を参照して下さい】

●【EXCEL VBA 文字列を分割・指定文字で区切る・カンマ区切り・配列格納（Split関数）の利用方法は、下記を参照して下さい】

●【OPTION　BASEステートメントトについては、下記を参照して下さい（Microsoft社　様）】
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/option-base-statement

●書式の説明　（Option baseステートメント）

●　Option　Baseステートメントメントを利用するには、下記の通りに設定を行います。
配列の添字の最小値を０か１に設定します。
（宣言セクションに記述します。）

・Option　Base　１　　　・・・・Option　Baseが１の場合は、配列の添字「最小値」１が設定されます。
・Option　Base　０　　　・・・・Option　Baseを設定しないと配列の添字「最小値」０が設定されます。

【注意点】

【Option Baseを設定した場合】下記の通りに配列の添字「最小値」が設定されます。
・Split関数の場合は、OptionBaseを１に設定しても０が最小値として設定されます。
・一次元配列・二次元配列共にセル範囲から配列に格納する場合は、最小値が１に設定されます。

項目	OptionBase を１設定（配列の最小値）	OptionBaseを０設定（配列の最小値）
通常の配列	１	０
Array関数	１	０
Join関数	１	０
Spiit関数	０	０
一次元配列	１	１
二次元配列	１	１

【使用例】
①Option Baseを１にした場合（通常の配列）

Option Explicit

Option Base 1  '配列番号を１から設定

Sub 配列のインデックス番号から1()

    Dim ArrayName(3) As String '文字列の配列を用意します。
    Dim I As Long
    
    ArrayName(1) = "佐藤"  '配列１に佐藤を格納する。
    ArrayName(2) = "鈴木"  '配列２に鈴木を格納する。
    ArrayName(3) = "田中"  '配列３に田中を格納する。
    
    For I = 1 To 3  'Iの変数を１～３まで繰り返す。
        ActiveSheet.Cells(I, "A") = ArrayName(I)  '格納した配列をセルに転記します。
    Next I
    
End Sub

②Option Baseを０にした場合（通常の配列）

Option Explicit
Option Base 0    '配列番号を0から設定

Sub 配列のインデント番号から0()
    
    Dim ArrayName(2) As String '文字列の配列を用意します。
    Dim I As Long
    
    ArrayName(0) = "佐藤" '配列０に佐藤を格納する。
    ArrayName(1) = "鈴木" '配列１に鈴木を格納する。
    ArrayName(2) = "田中" '配列２に田中を格納する。
    
    For I = 0 To 2 'Iの変数を0～2まで繰り返す。（※配列が０からなので）
        ActiveSheet.Cells(I + 1, "A") = ArrayName(I) '格納した配列をセルに転記します。
                    'セル位置と配列番号がずれているので＋１
    Next I
    
End Sub

※注意：Option Base0を指定した場合、配列番号とセル番号では、＋１ずれるのでセルに転記する際は、注意が必要です。

EXCEL VBA　Option Baseの使い分け・Array関数の場合（Option Baseステートメント）

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、Option　BaseステートメントとArray関数を組み合わせた場合の配列の添字（インデックス番号）の最小値についての説明を行いたいと思います。

【プログラムの流れ】
①OptionBaseで配列の添字（最小値）を設定
②バリアント型の動的配列を設定
③Array関数を使い（総務部・営業部・企画部・・・を配列に格納します。
④配列に登録した分繰り返します。
⑤アクティブにシートに配列へ格納したデータをセルに転記します。
⑥格納した配列の最後まで繰り返します。④へ

Ⅰ・Option Baseを１にした場合（Array関数）

'
'
Option Explicit

Option Base 1  '配列番号を１から設定


Sub Array関数での使い方() 'Option　Baseを１

      Dim ArrayDepartment() As Variant  'バリアント型の配列を用意します。(動的配列)
      Dim I As Long
      
      ArrayDepartment = Array("総務部", "人事部", "営業部", "企画部", "営業部", "経理部")
      'Array関数を使い個別の配列に登録されます。
      For I = 1 To UBound(ArrayDepartment)  '配列番号１～最後の配列まで繰り返します。
        ActiveSheet.Cells(I, "C") = ArrayDepartment(I)  'セルに配列されている格納内容を転記します。
      Next I
     
End Sub
'

Ⅱ・Option Baseを０にした場合（Array関数）

'
'
Option Explicit
Option Base 0    '配列番号を0から設定
'
Sub Array関数での使い方0() 'Option　Baseを0

      Dim ArrayDepartment() As Variant  'バリアント型の配列を用意します。(動的配列)
      Dim I As Long
      
      ArrayDepartment = Array("総務部", "人事部", "営業部", "企画部", "営業部", "経理部")
      'Array関数を使い個別の配列に登録されます。
      For I = 0 To UBound(ArrayDepartment)  '配列番号0～最後の配列まで繰り返します。
        ActiveSheet.Cells(I + 1, "C") = ArrayDepartment(I) 'セルに配列されている格納内容を転記します。
                 'セル位置を合わせる為に＋１
      Next I
     
End Sub
'

※注意：Option Base0を指定した場合、配列番号とセル番号では、＋１ずれるのでセルに転記する際は、注意が必要です。

EXCEL VBA　Option Baseの使い分け・Join関数の場合（Option Baseステートメント）

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、Option　BaseステートメントとJoin関数を組み合わせた場合の配列の添字（インデックス番号）の最小値についての説明を行いたいと思います。

【プログラムの流れ】
①OptionBaseで配列の添字（最小値）を設定
②String型の配列を設定
③設定した配列にデータを格納します。（東京本店・銀座支店・品川支店・・）
④Join関数を使い配列に格納したデータを結合し、結合したデータをメッセージボックスに表示します。

Ⅰ・Option Baseを１にした場合（Join関数）

'
'
Option Explicit

Option Base 1  '配列番号を１から設定

Sub Join関数での使い方1()   '

      Dim ArrayBranch(5) As String  '文字列型の配列を用意します。
      
       ArrayBranch(1) = "東京本店" '配列１←「東京本店」を格納します。
       ArrayBranch(2) = "銀座支店" '配列２←「銀座支店」を格納します。
       ArrayBranch(3) = "品川支店" '配列３←「品川支店」を格納します。
       ArrayBranch(4) = "新宿支店" '配列４←「新宿支店」を格納します。
       ArrayBranch(5) = "渋谷支店" '配列５←「渋谷支店」を格納します。
      
      MsgBox Join(ArrayBranch, "-") 'Join関数を使い配列（１）～（５）を結合します。
          
End Sub
'

※注意：Option Base0を指定した場合、配列番号とセル番号では、＋１ずれるのでセルに転記する際は、注意が必要です。

Ⅱ・Option Baseを０にした場合（Join関数）

'
'
Option Explicit
Option Base 0    '配列番号を0から設定

Sub Join関数での使い方0()    '

      Dim ArrayBranch(4) As String  '文字列型の配列を用意します。
      
       ArrayBranch(0) = "東京本店" '配列0←「東京本店」を格納します。
       ArrayBranch(1) = "銀座支店" '配列1←「銀座支店」を格納します。
       ArrayBranch(2) = "品川支店" '配列2←「品川支店」を格納します。
       ArrayBranch(3) = "新宿支店" '配列3←「新宿支店」を格納します。
       ArrayBranch(4) = "渋谷支店" '配列4←「渋谷支店」を格納します。
      
      MsgBox Join(ArrayBranch, "-") 'Join関数を使い配列（0）～（4）を結合します。
          
End Sub
'

EXCEL VBA　Option Baseの使い分け・Split関数の場合（Option Baseステートメント）

●プログラム説明　(サンプル③)

下記のサンプルプログラムは、Option　BaseステートメントとSplit関数を組み合わせた場合の配列の添字（インデックス番号）の最小値についての説明を行いたいと思います。

【プログラムの流れ】
①OptionBaseで配列の添字（最小値）を設定
②String型の動的配列を設定
③Split関数を使い配列ごとにデータを格納します。（備消品費・通信運搬費・修繕費・・・）
④Split関数で格納したデータを順番にセルへ転記します。

Ⅰ・Option Baseを１にした場合（Split関数）

'
'
Option Explicit

Option Base 1  '配列番号を１から設定
'
Sub Split関数での使い方1()  '

     Dim ArrayAccunt_title() As String '文字列型の配列を用意します。（動的配列）
     Dim I As Long
      
     ArrayAccunt_title = Split("備消品費:通信運搬費:修繕費:諸手数料:雑費:交通費:交際費", ":")
     'Split関数を使い個別の配列に格納されます。（０～６）
     For I = LBound(ArrayAccunt_title) To UBound(ArrayAccunt_title)  '全ての配列データを繰り返します。
            ActiveSheet.Cells(I + 1, "C") = ArrayAccunt_title(I) '全ての配列データをセルに転記します。
     Next I
End Sub
'

Ⅱ・Option Baseを0にした場合（Split関数）

※　Split関数では、Option Base 1又は０に設定しても動的配列で０から格納されます。プログラムは、OptionBase１・０共に同じです。

EXCEL VBA　Option Baseの使い分け・一次元配列の場合（Option Baseステートメント）

●プログラム説明　(サンプル④)

下記のサンプルプログラムは、Option　Baseステートメントと一次元配列（セルから取り込んだ）を組み合わせた場合の配列の添字（インデックス番号）の最小値についての説明を行いたいと思います。

【プログラムの流れ】
①OptionBaseで配列の添字（最小値）を設定
②Variant型の動的配列を設定
③セル範囲（A1：A7）のデータを配列に格納します。（備消品費・通信運搬費・修繕費・・・）
④格納したデータをC列に転記します。

Ⅰ・Option Baseを0にした場合（一次元配列）

'
'
Option Explicit
Option Base 0    '配列番号を0から設定

Sub 一次元配列での使い方()  '1

    Dim TempData() As Variant 'バリアント型配列を用意します。
    
    TempData = ActiveSheet.Range("A1:A7").Value  'セルA1:A7の範囲を配列に格納します。
                               '（注意）.CurrentRegionで取り込む際は、Valueは必須です。
    ActiveSheet.Range("C1:C7") = TempData  '格納したデータをセルC1：C7に転記します。
                                '配列に格納した同じセル範囲を指定します。
End Sub
'

EXCEL VBA　Option Baseの使い分け・二次元配列の場合（Option Baseステートメント）

●プログラム説明　(サンプル⑤)

下記のサンプルプログラムは、Option　Baseステートメントと二次元配列（セルから取り込んだ）を組み合わせた場合の配列の添字（インデックス番号）の最小値についての説明を行いたいと思います。

【プログラムの流れ】
①OptionBaseで配列の添字（最小値）を設定
②Variant型の動的配列を設定
③セル範囲（A1を元に自動取得）のデータを配列に格納します。（備消品費・通信運搬費・修繕費・・・）
④格納したデータをＤ列に転記します。

Ⅰ・Option Baseを0にした場合（二次元配列）

'
'
Option Explicit
Option Base 0    '配列番号を0から設定
'
Sub 二次元配列での使い方()  '1

    Dim TempData() As Variant
    Dim lRow, lCol As Long
    
    TempData = ActiveSheet.Range("A1").CurrentRegion.Value  'セルA1からなるセル範囲を2次元配列として格納します。
                               '（注意）.CurrentRegionで取り込む際は、Valueは必須です。
    lRow = UBound(TempData, 1) '二次元配列の縦方向最大値を取得します。
    lCol = UBound(TempData, 2) '二次元配列の横方向最大値を取得します。
      
    ActiveSheet.Range(Cells(1, "D"), Cells(lRow, 3 + lCol)) = TempData '格納した配列をD1を起点に転記します。
                                     '配列に格納した同じセル範囲を指定します。

End Sub

’