EXCEL VBA Option Baseの使い分け・Array・Join・Split関数・二次元配列・表データを格納
EXCEL VBA Option Baseの使い分け・Array関数・Join関数・Split関数・一次・二次元配列・表データを配列に格納(Option Baseステートメント)
今回説明するのは、Option Baseステートメントの使い分け方法を説明いたします。EXCEL VBAで配列を取り扱うのは難しいですが、配列の使い方を理解する事で実行速度の向上を図ることが出来ます。Option Baseは、配列の添字(インデックス番号)を0又は1からに設定しますが、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ステートメントメントを利用するには、下記の通りに設定を行います。
配列の添字の最小値を0か1に設定します。
(宣言セクションに記述します。)
・Option Base 1 ・・・・Option Baseが1の場合は、配列の添字「最小値」1が設定されます。
・Option Base 0 ・・・・Option Baseを設定しないと配列の添字「最小値」0が設定されます。
【注意点】
【Option Baseを設定した場合】下記の通りに配列の添字「最小値」が設定されます。
・Split関数の場合は、OptionBaseを1に設定しても0が最小値として設定されます。
・一次元配列・二次元配列共にセル範囲から配列に格納する場合は、最小値が1に設定されます。
項目 | OptionBase を1設定 (配列の最小値) | OptionBaseを0設定 (配列の最小値) |
---|---|---|
通常の配列 | 1 | 0 |
Array関数 | 1 | 0 |
Join関数 | 1 | 0 |
Spiit関数 | 0 | 0 |
一次元配列 | 1 | 1 |
二次元配列 | 1 | 1 |
【使用例】
①Option Baseを1にした場合(通常の配列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Option Explicit Option Base 1 '配列番号を1から設定 Sub 配列のインデックス番号から1() Dim ArrayName(3) As String '文字列の配列を用意します。 Dim I As Long ArrayName(1) = "佐藤" '配列1に佐藤を格納する。 ArrayName(2) = "鈴木" '配列2に鈴木を格納する。 ArrayName(3) = "田中" '配列3に田中を格納する。 For I = 1 To 3 'Iの変数を1~3まで繰り返す。 ActiveSheet.Cells(I, "A") = ArrayName(I) '格納した配列をセルに転記します。 Next I End Sub |
②Option Baseを0にした場合(通常の配列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Option Explicit Option Base 0 '配列番号を0から設定 Sub 配列のインデント番号から0() Dim ArrayName(2) As String '文字列の配列を用意します。 Dim I As Long ArrayName(0) = "佐藤" '配列0に佐藤を格納する。 ArrayName(1) = "鈴木" '配列1に鈴木を格納する。 ArrayName(2) = "田中" '配列2に田中を格納する。 For I = 0 To 2 'Iの変数を0~2まで繰り返す。(※配列が0からなので) ActiveSheet.Cells(I + 1, "A") = ArrayName(I) '格納した配列をセルに転記します。 'セル位置と配列番号がずれているので+1 Next I End Sub |
※注意:Option Base0を指定した場合、配列番号とセル番号では、+1ずれるのでセルに転記する際は、注意が必要です。
EXCEL VBA Option Baseの使い分け・Array関数の場合(Option Baseステートメント)
下記のサンプルプログラムは、Option BaseステートメントとArray関数を組み合わせた場合の配列の添字(インデックス番号)の最小値についての説明を行いたいと思います。
【プログラムの流れ】
①OptionBaseで配列の添字(最小値)を設定
②バリアント型の動的配列を設定
③Array関数を使い(総務部・営業部・企画部・・・を配列に格納します。
④配列に登録した分繰り返します。
⑤アクティブにシートに配列へ格納したデータをセルに転記します。
⑥格納した配列の最後まで繰り返します。④へ
Ⅰ・Option Baseを1にした場合(Array関数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
' ' Option Explicit Option Base 1 '配列番号を1から設定 Sub Array関数での使い方() 'Option Baseを1 Dim ArrayDepartment() As Variant 'バリアント型の配列を用意します。(動的配列) Dim I As Long ArrayDepartment = Array("総務部", "人事部", "営業部", "企画部", "営業部", "経理部") 'Array関数を使い個別の配列に登録されます。 For I = 1 To UBound(ArrayDepartment) '配列番号1~最後の配列まで繰り返します。 ActiveSheet.Cells(I, "C") = ArrayDepartment(I) 'セルに配列されている格納内容を転記します。 Next I End Sub ' |
Ⅱ・Option Baseを0にした場合(Array関数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
' ' 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) 'セルに配列されている格納内容を転記します。 'セル位置を合わせる為に+1 Next I End Sub ' |
※注意:Option Base0を指定した場合、配列番号とセル番号では、+1ずれるのでセルに転記する際は、注意が必要です。
EXCEL VBA Option Baseの使い分け・Join関数の場合(Option Baseステートメント)
下記のサンプルプログラムは、Option BaseステートメントとJoin関数を組み合わせた場合の配列の添字(インデックス番号)の最小値についての説明を行いたいと思います。
【プログラムの流れ】
①OptionBaseで配列の添字(最小値)を設定
②String型の配列を設定
③設定した配列にデータを格納します。(東京本店・銀座支店・品川支店・・)
④Join関数を使い配列に格納したデータを結合し、結合したデータをメッセージボックスに表示します。
Ⅰ・Option Baseを1にした場合(Join関数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
' ' Option Explicit Option Base 1 '配列番号を1から設定 Sub Join関数での使い方1() ' Dim ArrayBranch(5) As String '文字列型の配列を用意します。 ArrayBranch(1) = "東京本店" '配列1←「東京本店」を格納します。 ArrayBranch(2) = "銀座支店" '配列2←「銀座支店」を格納します。 ArrayBranch(3) = "品川支店" '配列3←「品川支店」を格納します。 ArrayBranch(4) = "新宿支店" '配列4←「新宿支店」を格納します。 ArrayBranch(5) = "渋谷支店" '配列5←「渋谷支店」を格納します。 MsgBox Join(ArrayBranch, "-") 'Join関数を使い配列(1)~(5)を結合します。 End Sub ' |
※注意:Option Base0を指定した場合、配列番号とセル番号では、+1ずれるのでセルに転記する際は、注意が必要です。
Ⅱ・Option Baseを0にした場合(Join関数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
' ' 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を1にした場合(Split関数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
' ' Option Explicit Option Base 1 '配列番号を1から設定 ' Sub Split関数での使い方1() ' Dim ArrayAccunt_title() As String '文字列型の配列を用意します。(動的配列) Dim I As Long ArrayAccunt_title = Split("備消品費:通信運搬費:修繕費:諸手数料:雑費:交通費:交際費", ":") 'Split関数を使い個別の配列に格納されます。(0~6) 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又は0に設定しても動的配列で0から格納されます。プログラムは、OptionBase1・0共に同じです。
EXCEL VBA Option Baseの使い分け・一次元配列の場合(Option Baseステートメント)
下記のサンプルプログラムは、Option Baseステートメントと一次元配列(セルから取り込んだ)を組み合わせた場合の配列の添字(インデックス番号)の最小値についての説明を行いたいと思います。
【プログラムの流れ】
①OptionBaseで配列の添字(最小値)を設定
②Variant型の動的配列を設定
③セル範囲(A1:A7)のデータを配列に格納します。(備消品費・通信運搬費・修繕費・・・)
④格納したデータをC列に転記します。
Ⅰ・Option Baseを0にした場合(一次元配列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
' ' 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を元に自動取得)のデータを配列に格納します。(備消品費・通信運搬費・修繕費・・・)
④格納したデータをD列に転記します。
Ⅰ・Option Baseを0にした場合(二次元配列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ' 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 ’ |
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。