IT系/VBA/基本/配列

Last-modified: 2020-07-18 (土) 09:03:27

目次

概要

配列に関するVBAの基本知識・TIPS。
基本、参考リンクの内容をまとめたもの。

配列とは

  • 配列は、値を格納するために多くの区画を持つ1つの変数。
  • 要は、同じデータ型の変数の箱を複数つなげたもの。
  • 配列が格納しているすべての値を参照する場合は配列全体を参照することもできる。
  • 配列のインデックス番号（配列内の位置。添字ともいう）を指定することで個々の要素を参照することもできる。
  • 配列のインデックス番号の最小値は、配列宣言時に指定しなければ0になる。
  • 以下のようにOption Baseステートメントをモジュール先頭に記述することで、インデックスの最小値を1に変更可能（混乱のもとになるので非推奨）。 
    Option Base 1

静的配列と動的配列

  • 静的配列：配列変数宣言時に要素数（最大インデックス番号）を指定する。配列の要素数が、決まっていて不変で良い場合に使う。 
    ' 静的配列の宣言
Dim ary(3) As String    ' 宣言後、要素数を変更できない
  • 動的配列：配列変数宣言時に要素数を指定しない。実行時点で要素数を決める場合や、実行途中で要素数を増減させる場合に使う。 
    ' 動的配列の宣言
Dim ary() As String     ' 宣言後、要素数を変更可能

配列の宣言

  • 1次元配列 
    Dim MyArray(10) As Long    ' 11個要素を持つ、１次元の配列
MyArray(0) = 1             ' 値を入れる
  • 多次元配列 
    ' 2次元配列の宣言
Dim MyArray(10, 5) As String
    • 次元数の最大値は60
  • 要素の下限を指定した宣言 
    Dim MyArray(1 To 10) As String    ' 配列の要素は、1～10の10個

動的配列の要素数の変更

動的配列の要素数を変更するには、ReDimステートメントを使用する。

  • 構文 
    ReDim [Preserve] varname(subscripts) [As type]
  • Preserve：既存の配列に格納されている値を失うことなく、配列の最後の次元の要素数を変更する場合に指定。
  • subscripts：配列変数の次元を指定。[lower To] upper [,[lower To] upper] . . .のように指定する。
  • 用例 
    ' 要素数の変更
Dim ary() as String
Redim Preserve ary(3)
Redim Preserve ary(5)
' 変数を指定することも可能
Dim length As Long: length = 3
Dim ary() As Variant
ReDim ary(length) As Variant

Preserveの指定

  • 変更できるのは動的配列の最後の次元のサイズに限られる（()内の一番最後（一番右）の次元の要素数のみ変更可能ということ）。
  • Preserveを使用した場合、動的配列のサイズを変更するために変えられるのは、添字（インデックス）の上限。
  • 添字（インデックス）の下限（最小値）を変更しようとすると、エラーが発生。
  • 次元数は変更できない。
  • 変数が配列として定義されておらず、Variantとだけ定義されている場合に、ReDimで配列として使う場合は、添字の下限も変更可能。

配列変数の初期化時の値

  • 数値変数は、数値の0
  • 可変長文字列は、長さ0の文字列 ("")
  • 固定長文字列は、 文字コード0のvbNullChar
  • ブール変数は、False
  • バリアント型変数は、Empty値

配列に関連する関数

  • LBound関数
    配列の指定された次元で使用できる最小の添字を、長整数型 (Long)の値で返す。
    LBound(arrayname[, dimension])
  • UBound関数
    配列の指定された次元で使用できる添字の最大値を、長整数型 (Long) の値で返す。
    UBound(arrayname[, dimension])
  • Array関数
    配列が格納されたバリアント型 (Variant) の値を返す。
    Array(arglist)
    引数 arglist には、値のリストをカンマ (,) で区切って指定。
  • IsArray関数
    変数が配列であるかどうかを調べ、結果をブール型 (Boolean) で返す。
    IsArray(varname)
  • Join関数
    配列に含まれる各要素の内部文字列を結合して作成される文字列を返す。
    Join(sourcearray [, delimiter])
  • Filter関数
    指定されたフィルタ条件に基づいた文字列配列のサブセットを含むゼロベースの配列を返す。
    Filter(sourcesrray, match[, include[, compare]])

配列の要素番号（添字）の最大値・最小値 

' 次元省略（1次元の要素の最小最大取得）
ary_max = UBound(ary)
ary_min = LBound(ary)
'２次元配列の全次元の全要素に0を設定
For i = LBound(ary, 1) To UBound(ary, 1)
　　For j = LBound(ary, 2) To UBound(ary, 2)
　　　　ary(i, j) = 0
　　Next j
Next i

Variant配列作成 

' ary(0) = 10  ary(1) = 20 ary(2) = 30
Dim ary
ary= Array(10, 20, 30)

配列チェック 

Dim ary1
Dim ary2(5)
MsgBox IsArray(ary1)    'False
MsgBox IsArray(ary2)    'True

配列内要素の文字列結合 

Dim i As Long
Dim MyArray(1 To 3)
For i = 1 To 3
    MyArray(i) = i
Next
Debug.Print Join(MyArray, ",")    ' 1,2,3

配列のフィルタリング 

Dim MyArray
MyArray = Array("東京都", "神奈川県", "千葉県", "茨城県", "大阪府")
MsgBox Join(Filter(MyArray, "県"), ",")    ' 神奈川県,千葉県,茨城県

配列の再初期化・解放

  • Eraseステートメント
    固定サイズの配列の場合は要素を再初期化し、動的配列の場合は割り当てたメモリを解放する。
    Erase arraylist
' 静的配列
Dim NumArray(10) As Long
Dim StrVarArray(10) As String
Dim StrFixArray(10) As String * 10
Dim VarArray(10) As Variant
Dim ObjArray(10) As Object
Erase NumArray 'すべて0に
Erase StrVarArray 'すべて長さ0の文字列("")に
Erase StrFixArray 'すべて0に
Erase VarArray 'すべてEmpty値に
Erase ObjArray 'すべてNothingに
' 動的配列
Dim DynamicArray() As Long
ReDim DynamicArray(10)
Erase DynamicArray 'メモリを解放

配列のループ処理

通常のFor文でインデックスを指定して各要素にアクセスする方法もあるが、For Each文を使うこともできる。

'  For文
Dim i As Long
For i = LBound(arr) To UBound(arr)
    Debug.Print CStr(arr(i))
Next i
' For Each文
Dim v
For Each v In arr
    Debug.Print CStr(v)
Next

セル範囲を配列に代入

  • セル範囲をVariant型変数に入れる事で、配列を作成することができる。
  • 配列をセル範囲にまとめて出力することもできる。
  • VBAを高速処理したい時の必須テクニック。
     
Dim MyArray
MyArray = Range("A1:B100")    ' セル範囲⇒配列。MyArray(1 To 100, 1 To 2)。LBoundは1になる点に注意。
'
' ・・・処理・・・
'
Range("A1:B100") = MyArray    ' 配列⇒セル範囲。

Variant型配列

  • バリアント型変数は、どんなデータでも格納できる万能の型。
  • 整数や文字列だけでなく、オブジェクトを格納することも可能。
  • 配列も格納できる。
  • 事前に要素数がわかっていても、要素数が固定されている配列に代入することはできない。 
    Dim Member(2) As String, i As Long
Member = Split("tanaka,suzuki,yamada", ",")    ' エラー
  • 他のプログラミング言語では、配列に配列を代入できるものもあるが、VBAではできない。
  • VBAでは配列を受け取る場合、一般的にバリアント型変数に代入する。 
    Dim buf(3) As String, Member As Variant
buf(1) = "tanaka"
buf(2) = "suzuki"
buf(3) = "yamada"
Member = buf
MsgBox Member(2)    ' suzuki

配列をバリアント型変数に入れる

  • Split関数
    指定された数のサブ文字列が含まれる 0 ベースの 1 次元配列を返す。
    Split(expression, [ delimiter, [ limit], [ compare ]]) 
    Dim Member As Variant, i As Long
Member = Split("tanaka,suzuki,yamada", ",")
  • ApplicationオブジェクトのGetOpenFilenameメソッド
    ユーザーからファイル名を取得するために、[ファイルを開く] ダイアログ ボックスを表示。
    引数MultiSelectにTrueを指定すると複数のファイルを選択できるようになるが、このときの返り値も配列形式（1 ベースの 1 次元配列）。
    expression.GetOpenFilename (FileFilter, FilterIndex, Title, ButtonText, MultiSelect) 
    Dim OpenFiles As Variant, i As Long
OpenFiles = Application.GetOpenFilename(MultiSelect:=True)

Variant型２次元配列の１次元目の要素数を変更する方法

  • Preserveキーワードで要素数を変更できるのは「最終次元」に限られる。
  • ２次元配列の１次元目の要素数を直接変更する方法はない。
  • Transpose関数を使って１次元と２次元を逆転させて拡張を行う方法がある。
    expression.Transpose (Arg1)
    この方法を使うと、添え字が1始まりに勝手に変更されるのに注意。 
    '2次元配列の1次元目の長さを変更する関数
'orgArray … 二次元配列
'legthTo … 1次元目の長さ
Function RedimPreserve2D(ByVal orgArray, ByVal lengthTo)
    Dim transedArray()
    transedArray = WorksheetFunction.Transpose(orgArray)
    ReDim Preserve transedArray(1 To UBound(transedArray, 1), 1 To lengthTo)
    RedimPreserve2D = WorksheetFunction.Transpose(transedArray)
End Function

ジャグ配列

  • Javaでは、多次元配列＝配列の入れ子。
  • VBAの多次元配列は、配列の入れ子ではない。
    arr(i)(j)ではなく、arr(i, j)。
  • 配列に配列を格納する構造のものをジャグ配列という。
  • VBAでは、Variant型配列に配列を格納することでジャグ配列を作ることもできる。

配列のメモリ配置

  • C系の言語は行優先（row major order）で、Fortran系の言語は列優先（column major order）。
  • VBAはもともとBasic＝Fortranの派生言語のため、列優先。
  • 詳しくはRow- and column-major order - Wikipedia参照。
  • 以下のようなコードで配置を確認可能。 
    Dim a() As Long
ReDim a(0 To 1, 0 To 2)
Debug.Print 0, 0, VarPtr(a(0, 0))
Debug.Print 0, 1, VarPtr(a(0, 1))
Debug.Print 0, 2, VarPtr(a(0, 2))
Debug.Print 1, 0, VarPtr(a(1, 0))
Debug.Print 1, 1, VarPtr(a(1, 1))
Debug.Print 1, 2, VarPtr(a(1, 2))
  • 上記のコードで確認すると次のような配置になっていることが確認できる。
    位置000408121620
    変数a(0,0)a(1,0)a(0,1)a(1,1)a(2,0)a(2,1)
  • a(0,0)の隣はa(0,1)ではなく、a(1,0)となる。最後の次元が同じもの同士を隣同士に固めて配置される。
  • この配置であれば、最後の添え字数を変更するだけであれば、内部処理としては、添え字数を変更しても再配置処理は単純なコピーで済むので、高速に処理可能。
  • 上記の理由からVBAの動的配列は最後の次元の配列数しか変えられない仕様となっている。

配列とコレクションの違い

配列のデータ構造

  • Long型の配列を作成すると、メモリ上には単に直列にデータが並ぶ。VBAのLong型のサイズは4バイトなので、ちょうど4バイトずつ隙間なく配置される。
    アドレスデータ
    000010
    000420
    000830
    001240
  • 隙間なく並べることで、添え字(インデックス)が大きくなっても、データの格納位置を瞬時に割り出すことが可能。
  • すなわち、先頭アドレスに(添え字×型のデータサイズ)を加えることでアドレスが求まる。
  • 上記配列Arr(3)の場合、先頭アドレス0000、添え字は3で、Long型のサイズは4なので、3 × 4 = 12 でアドレス位置は、0000 + 12 = 0012 の計算となる。
  • 逆に言えば、配列の添え字(インデックス)を先頭アドレスからのオフセットとして利用するためには、隙間なくデータが並んでいる必要がある。
  • 静的配列は宣言時にサイズを決め、そのあとサイズ変更ができないが、これはつまり、最初にメモリの連続した領域を確保してしまう必要があるため。
  • 動的配列はReDimでサイズ変更ができるが、あれも厳密にいえば別の領域に新たな連続したメモリ領域を確保して配列を再作成している。
  • Preserveを付けないと中身が消えてしまうという説明がなされるが、正確に言えばPreserveを付けると旧メモリ領域からデータをコピーしてくれるということ。

コレクションのデータ構造

  • Collectionという名称はMicrosoftが付けたものであるが、データ構造の一般名称としては「連結リスト」または単に「リスト」と呼ばれている。
  • リスト構造では、以下のようにデータと次のデータ位置を示すアドレスがセットで格納されている。
    アドレスデータ
    000010
    00040024
    0008
    001230
    00160036
    0020
    002420
    00280012
    0032
  • 上記の場合、0000：10　⇒　0024：20　⇒　0012：30　とアドレスを辿ってデータを参照する。
  • リストの場合、配列と違って隙間なくデータを並べておく必要がなく、空いているアドレスにデータを追加することができる。
  • 新たにデータを追加したい場合は任意の場所にデータを追加し、前の要素のアドレス欄を書き換えれば良い。
  • データの挿入も、前の要素のアドレス欄を書き換えてから、挿入する要素のアドレス欄に次の要素のアドレスを格納すれば良い。
  • 削除もアドレス欄の書き換えだけで済む。
  • データを参照するにはリストの開始位置から順にリストを辿っていく。配列のように添え字からの計算で格納位置を求めることはできない。
    だから理屈上は、後ろのほうに追加されたデータのほうが参照するのに時間がかかることになる。

メリット・デメリット

配列のメリットとして高速であるという説明がなされることがあるが、実務で扱う数万件程度のデータなら大差ないので速度はそれほどアピールポイントにはならない。

配列のメリット

  • ArrayやSplitなどで動的に生成できること
  • Join関数で結合できること
  • 2次元配列のセルとの相互転記ができること
  • 宣言時に型を決められるためオブジェクト型の配列にしたときにドットでプロパティとメソッドの入力候補が表示されること

コレクションのメリット

  • データの追加・削除・挿入が容易であること
  • キー文字列を設定でき、インデックスの変わりにキーを使ってデータ参照できること

ソート

  • 数値ならWorksheetFunction.Smallメソッド、WorksheetFunction.Largeメソッドを使用してソート。 
    For i = UBound(srcArr) To LBound(srcArr)       '昇順で並べ替える
    destArr(i) = WorksheetFunction.Small(srcArr, i + 1)
Next i
  • 「Ariawase」のArrSort()を使う。
  • .NET FrameworkのArrayListクラスを利用してソート。 
    Dim aryList As Object, v
Set aryList = CreateObject("System.Collections.ArrayList")
For Each v In ary
    Call aryList.Add(v)
Next
aryList.Sort
ary = aryList.ToArray
  • シートに張り付けて、RangeオブジェクトのSortメソッドでソート後、配列に戻す。
  • 自前でソートする。

重複の削除

  • 「Ariawase」のArrUniq()を使う。
  • Dictionaryを使用した重複削除。 
    ' Dictionaryを使用するので、Microsoft Scripting Runtimeの参照設定が必要
Function DeleteDuplicateItem(ary() As Variant) As Variant()
    Dim dic As New Dictionary
    Dim i As Long
    For i = 0 To UBound(ary)
        If dic.Exists(ary(i)) = False Then
            dic.Add ary(i), ary(i)
        End If
    Next i
    DeleteDuplicateItem = dic.Keys
    Set dic = Nothing
End Function

配列のマージ

  • 「Ariawase」のArrConcat()を使う。
  • 一度文字列として結合し、再度分割する方法。区切り文字は配列内で使用していない文字とする必要あり。 
    Dim a, b, c
a = Array(1, 2, 3)
b = Array(4, 5, 6)
c = Split(Join(a, "@") & "@" & Join(b, "@"), "@")

配列のコピー

配列をコピーする場合、コピー先が静的配列か動的配列かでコピーする方法が異なる。
静的配列の場合、各要素は繰り返し処理によってコピーする。
動的配列の場合、繰り返し処理による同様のコピーもできるが、代入による設定も可能。
代入での設定は、代入先が動的配列であることと、型が一致していることが条件。

  • ループさせて配列の要素毎に複写するやり方（静的配列） 
    Dim srcArr(10) As Integer
Dim destArr (10) As Integer
Dim iAs Integer
For i= 0 To UBound(i)
    destArr (i) = srcArr(i)
Next i
  • 動的配列に代入 
    Dim srcArr(10) As Integer
Dim destArr As Variant
destArr = srcArr
  • 配列のコピーは参照渡しの言語もある（Java, VB.net等）が、VBA（VB6）では値渡しになる。 
    Dim arr1() As String
Dim arr2() As String
ReDim arr1(1)
arr1(0) = "aaa"
arr1(1) = "bbb"
arr2= arr1
arr1(0) = "ccc"
Debug.Print arr1(0) & ":" & arr2(0)    ' ccc:aaa

配列の合計値

  • WorksheetFunction.Sum()メソッドを使用して合計値を算出。
    ちなみに、SumIf、SumIfsは配列を指定できずRange指定のみ。
  • 「Ariawase」のArrFold()を使って合計値を算出。
  • Forループで計算 
    Dim v, total As Long
For Each v In ary
    total = total + CLng(v)
Next

配列の平均値

  • WorksheetFunction.Average()メソッドを使用して平均値を算出。
  • 「Ariawase」のArrFold()を使って合計値を算出し、要素数で割る。
  • Forループで合計値を算出し、要素数で割る。

配列の中央値

  • WorksheetFunction.Medianメソッドを使用して中央値を算出。
  • 配列をソート後、自前で計算。 
    Dim cnt, iHalf
cnt = UBound(arr) - LBound(arr)
iHalf = Fix(cnt  / 2)
If (cnt + 1) Mod 2 = 0 Then
    ' 配列の中央２つの値の平均
    ret = (arr(iHalf) + arr(iHalf + 1)) / 2
Else
    ' 配列の中央の値
    ret = arr(iHalf)
End If

配列に関するライブラリ

「Ariawase」の「ArrayEx」クラス

いげ太氏の作成したVBAライブラリ「Ariawase」に含まれる「ArrayEx」クラス。
自動拡張配列。Variant型一次元配列をReDimでのサイズ変更を意識せず、要素追加できるようにしたもの。

  • AddVal(ByVal val As Variant)メソッドでプリミティブ型の要素を追加
  • AddObj(ByVal obj As Variant)メソッドでオブジェクトの要素を追加
  • ToArray() As VariantメソッドでVariant型一次元配列に変換
  • New ⇒ AddVal() /AddObj() で動的要素追加 ⇒ ToArray() で配列に変換という流れで使用

「Ariawase」の「Core」モジュール

いげ太氏の作成したVBAライブラリ「Ariawase」の「Core」モジュールには、配列関連のプロシージャがある。

  • ArrRank()
    配列の次元を返却する。
  • ArrLen()
    配列の長さ（要素数）を返却する。次元を指定可能。デフォルトは１次元。
  • ArrEquals()
    ２つの一次元配列を比較し、イコールであればTrueを返却する。
  • ArrCompare()
    ２つの一次元配列（x,y）を比較し、x=yであれば0、x<yであれば-1、x>yであれば1を返却する。
  • ArrIndexOf()
    一次元配列から指定した値と一致する要素のインデックスを返却する。値の捜査開始位置と要素数を任意指定可能。
  • ArrRev()
    一次元配列の要素の並び順を反転させる。
  • ArrSort()
    一次元配列のソートを行う。デフォルトは昇順。
  • ArrUniq()
    一次元配列の重複要素を排除する。
  • ArrConcat()
    一次元配列と一次元配列を合体（マージ）して返却する。
  • ArrSlice()
    一次元配列をスライス（一部を配列として切り出）して返却する。
  • ArrFlatten()
    ジャグ配列のフラット化。ジャグ配列を一次元配列に変換して返却する。
  • ArrToClct()
    一次元配列をCollectionオブジェクトに変換する。
  • ClctToArr()
    Collectionオブジェクトを一次元配列に変換する。
  • JagArrToArr2D()
    ジャグ配列を二次元配列に変換して返却する。
  • Arr2DToJagArr()
    二次元配列をジャグ配列に変換して返却する。

「Ariawase」の「Ext」モジュール

いげ太氏の作成したVBAライブラリ「Ariawase」の「Ext」モジュールにも、配列関連のプロシージャがある。配列を使った高階関数的処理を実現している模様。

  • CreateAssocArray()
    連想配列を作成する。
  • AssocArrToDict()
    連想配列をDictionaryオブジェクトに変換する。
  • DictToAssocArr()
    Dictionaryオブジェクトを連想配列に変換する。
  • EnumeratorToArr()
    繰り返し処理（For Each）可能なオブジェクトを一次元配列に変換する。
  • ArrRange()
    指定した範囲の数値の配列を作成する。Pythonのrange()関数と同じようなことができる。
  • ArrMap()
    配列の全要素に対して指定したファンクションの処理を適用する。Pythonのmap()関数と同じようなことができる。
  • ArrZip()
    ２つの配列に対して指定したファンクションの処理を適用し１つの配列を返す。
  • ArrFilter()
    配列に対して指定したフィルタ処理用のファンクションを適用し、条件に一致する要素のみの配列を返す。
  • ArrGroupBy()
    配列に対して指定したグルーピング処理用のファンクションを適用し、結果を返す。うまく動かなさそう。
  • ArrFold()
    畳み込み関数。配列内の要素に対して指定した演算用ファンクションを適用し、その結果と次の要素にまたファンクションを適用するといった処理を繰り返し最終的な結果を返す。
  • ArrScan()
    配列の各要素に対して繰り返し指定した演算用ファンクションを適用し、累積した計算結果のリストを返す。
  • ArrUnfold()
    Foldの逆。初期値から指定した演算用ファンクションを適用しリストを生成する。

TIPS

別ページの一覧を入れる。

'IT系/VBA/基本/配列/' には、下位層のページがありません。

参考リンク集

重複を恐れないリンク集。

動画

その他メモ