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 配列の使い方(Array LBound UBound)

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

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ステートメントメントを利用するには、下記の通りに設定を行います。
配列の添字の最小値を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設定
(配列の最小値)
通常の配列
Array関数
Join関数
Spiit関数
一次元配列
二次元配列

 

【使用例】
①Option Baseを1にした場合(通常の配列)


②Option Baseを0にした場合(通常の配列)

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

 

 

 

 

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

 

 

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

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

 

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

 

Ⅰ・Option Baseを1にした場合(Array関数)

 

Ⅱ・Option Baseを0にした場合(Array関数)


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

 

 

 

 

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

 

 

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

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

 

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

 

Ⅰ・Option Baseを1にした場合(Join関数)


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

Ⅱ・Option Baseを0にした場合(Join関数)

 

 

 

 

 

 

 

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

 

 

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

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


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

 

Ⅰ・Option Baseを1にした場合(Split関数)

 

 

Ⅱ・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にした場合(一次元配列)

 

 

 

 

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

 

 

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

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

 

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

 

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

 

 

 

 

 

 

 

 

 

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。