エクセルVBA配列の初期化と使い方

VBA配列は、Excelの開発に大きな役割を果たします。配列を適切に使用すると、作業効率が向上します。次にはVBA配列を説明します。

具体的な使い方

VBA配列の宣言方法

以下は、配列のいくつかの一般的な定義方法であり、1次元配列の定義、2次元配列の定義、直接割り当て定義、Array関数定義の呼び出し、Excelワークシートメモリ配列の呼び出しです。

'直接定義で配列に値を割り当てる

'一一次元定数配列の定義
Sub arrDemo1()
Dim arr(2) As Variant '配列
arr(0) = "vba"
arr(1) = 100
arr(2) = 3.14
MsgBox arr(0)
End Sub

'二次元定数配列の定義
Sub arrDemo2()
Dim arr(1, 1) As Variant 'Dim arr(0 To 1, 0 To 1) As Variant
arr(0, 0) = "apple"
arr(0, 1) = "banana"
arr(1, 0) = "pear"
arr(1, 1) = "grape"
For i = 0 To 1
For j = 0 To 1
MsgBox arr(i, j)
Next
Next
End Sub


'array関数で定数配列を作成する

'一次元配列
Sub arrayDemo3()
Dim arr As Variant '配列
arr = Array("vba", 100, 3.14)
MsgBox arr(0)
End Sub

'二次元配列
Sub arrayDemo4()
Dim arr As Variant '配列
arr = Array(Array("张三", 100), Array("李四", 76), Array("王五", 80))
MsgBox arr(1)(1)
End Sub


'Excelワークシートに格納された配列を呼び出す

' 一次元配列[{"A",1,"C"}]
'二次元配列[{"a",10;"b",20;"c",30}]
Sub mylook()
Dim arr
arr = [{"a",10;"b",20;"c",30}]
Range("a1:b3") = arr
MsgBox Application.WorksheetFunction.VLookup("b", arr, 2, 0) 'vlookupを呼び出すときに2番目の引数として使用できる
End Sub


'動的配列定義方法

Sub arrDemo5()
Dim arr1() '動的配列を宣言する（動的とはサイズを固定しない）
Dim arr2 'Variant型の変数を宣言する
arr1 = Range("a1:b2") 'セル範囲A1：B2の値を配列arr1に入力する
arr2 = Range("a1:b2") 'セル範囲A1：B2の値を配列arr2に入力する
MsgBox arr1(1, 1) 'arr配列の1行目または1列目にある値を読み取る
MsgBox arr2(2, 2) 'arr配列の2行目または2列目にある値を読み取る
End Sub

配列の割り当てと演算

'セルデータを配列に読み取り、演算を実行し、セルに値を割り当てる

Sub arr_calculate()
Dim arr '変数を宣言し、セルデータを入力する
Dim i%
arr = Range("a2:d5") 'セルデータをarrに移動し、4列4行が含まれる
For i = 1 To 4 'arr配列を反復する
arr(i, 4) = arr(i, 3) * arr(i, 2) '配列の4列目（金額）=3列目* 2列目
Next i
Range("a2:d5") = arr '配列をセルに返す
End Sub

3、配列の結合（join）と分割（split）

'配列の結合（join）と分割（split）

Sub join_demo()
Dim a As Variant
Dim b As Variant
' Join using spaces
a = Array("Red", "Blue", "Yellow")
b = Join(a, "")
MsgBox ("The value of b is :" & b) 'Red Bule Yellow
' Join using $
b = Join(a, "$") 'Red$Bule$Yellow
MsgBox ("The Join result after using delimiter is : " & b)
End Sub

Sub split_demo()
Dim a As Variant
Dim b As Variant
a = Split("Red$Blue$Yellow", "$") 'a = Array("red"，"blue"，"yellow")
b = UBound(a)
For i = 0 To b
MsgBox a(i)
Next
End Sub

配列のフィルタ（Filter）

'vba配列のフィルタ

Sub arr_filter()
arr = Array("ABC", "F", "D", "CA", "ER")
arr1 = VBA.Filter(arr, "A", True) 'Aを含むすべての値をフィルタして新しい配列を作成する
arr2 = VBA.Filter(arr, "A", False) 'Aを含まないすべての値をフィルタして新しい配列を作成する
MsgBox Join(arr1, ",") 'フィルタ結果を表示する
End Sub

配列次元の変換（Transpose）

'配列次元の変換

'一次元を二次元に変換する
Sub arr_tranpose1()
arr = Array(10, "vba", 2, "b", 3)
arr1 = Application.Transpose(arr)
MsgBox arr1(2, 1) '変換された配列は複数行の2次元配列である
End Sub

'2次元配列を1次元に変換する 'ヒント：変換する場合、N行目の配列のみを直接1次元配列に変換できる
Sub arr_tranpose2()
arr2 = Range("A1:B5")
arr3 = Application.Transpose(Application.Index(arr2, , 2)) 'arr2の2列目のデータを取得し、1次元配列に変換する
MsgBox arr3(4)
End Sub

'セルの内容を「-」で接続する
Sub join_transpose_demo()
arr = Range("A1:C1")
arr1 = Range("A1:A5")
MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")
MsgBox Join(Application.Transpose(arr1), "-")
End Sub

配列を使用して、すべてのワークシート名のカスタム関数を取得する

'配列を使用して、すべてのワークシート名のカスタム関数を取得する

Function getSheetsname(id)
Dim i%, arr()
k = Sheets.Count
ReDim arr(1 To k)
For i = 1 To k
arr(i) = Sheets(i).Name
Next
getSheetsname = Application.Index(arr, id)
End Function

演算効率を向上させるため、配列に値を割り当てる

'演算効率を向上させるため、配列に値を割り当てる

'2.03秒
Sub dataInput()
Dim start As Double
start = Timer
Dim i&
For i = 1 To 30000
Cells(i, 1) = i
Next
MsgBox "プログラムの実行時間は" & Format(Timer - start, "0.00") & "秒"
End Sub

'0.12秒
Sub dataInputArr()
Dim start As Double
start = Timer
Dim i&, arr(1 To 30000) As String
For i = 1 To 30000
arr(i) = i
Next
Range("a1:a30000").Value = Application.Transpose(arr)
MsgBox "プログラムの実行時間は" & Format(Timer - start, "0.00") & "秒"
End Sub

'0.09秒
Sub dataInputArr2()
Dim start As Double
start = Timer
Dim i&, arr(1 To 30000, 1 To 1) As String
For i = 1 To 30000
arr(i, 1) = i
Next
Range("a1:a30000").Value = arr
MsgBox "プログラムの実行時間は" & Format(Timer - start, "0.00") & "秒"
End Sub

Excel関数と配列の共有

VBAで使用可能なVBA関数がある以外、多くのExcelワークシート関数を呼び出し、配列を分割、検索、および分析することもできます。ワークシート関数を呼び出すと、反復条件を省略し、操作効率を向上させることができます。

配列の限界値

MaxとMin

ワークシート関数のMaxとMinは、最大値と最小値を求める関数であり、配列の最大値と最小値はVBAでも求めることができます。例えば：

Sub t()
arr = Array(1, 35, 4, 13)
MsgBox Application.Max(arr) '最大値
MsgBox Application.Min(arr) '最小値
End Sub

largeとsmall

ワークシート関数のlargeとsmallは、一連の数値のN番目に大きい数値とN番目に小さい数値を返し、VBA配列にも適用できます。例えば：

Sub t1()
arr = Array(1, 35, 4, 13)
MsgBox Application.Large(arr, 2) '2番目に大きい数値
MsgBox Application.Small(arr, 2) '2番目に小さい数値
End Sub

配列の統計と合計

Sum

Sum関数は、ワークシートに配列を合計できる以外、VBA配列も合計できます。例えば：

Sub t2()
arr = Array(1, 35, 4, 13)
MsgBox Application.Sum(arr) '配列を合計する
End Sub

CountとCounta

CountとCountaは、配列内の数値の文字数または数値+文字列の文字数を合計できます。

Sub t3()
arr = Array(1, 35, "a", 4, 13, "b")
MsgBox Application.Count(arr) '数字の文字列が4を返す
MsgBox Application.CountA(arr) ‘文字列と数値の文字数を返す
End Sub

配列の検索と分割

Match検索配列

Match関数は、一連の数値内の指定された値の位置を検索できます。また、VBA配列の検索にも使用できます。例えば：

Sub t4()
arr = Array(1, 35, 4, 13)
MsgBox Application.Match(4, arr, 0) '配列Arr内の4の位置を検索する
End Sub

Index分割配列

配列の分割はVBAでは難しい問題です。配列を行で分割する場合、反復以外API関数のみを使用して配列を分割できます。 幸い、ワークシートのindex関数を使用して、配列を列で分割できます。つまり、複数の列で構成される配列です。任意の列を分割して、新しい配列を作成できます。やり方は：Application.Index(配列, , 列目)

Sub t2()
arr2 = Range("A1:B4") ‘セル範囲A1：B4の値を配列arr2に入力する
arr3 = Application.Index(arr2, , 2) '配列の2列目を分割し、arr3に入力する。新しい配列は2次元配列である
MsgBox arr3(2, 1) '新しい配列の2行目の値を取得する
End Sub

配列次元の変換

Transpose変換配列はワークシートの行と列を変換できます。この機能はVBAでも実現できます。

1次元を2次元に変換する

Sub t9()
arr = Array(1, 35, "a", 4, 13, "b")
arr1 = Application.Transpose(arr)
MsgBox arr1(2, 1) ‘変換された配列は、一列で複数行の2次元配列である
End Sub

2次元配列を1次元に変換する

Sub t2()
arr2 = Range("A1:B4")
arr3 = Application.transpose(Application.Index(arr2, , 2)) 'arr2の2列目のデータを取得し、それを1次元配列に変換する
MsgBox arr3(2,)
End Sub

ヒント：変換する時に1列でN行の配列のみ1次元配列に直接変換できます。

問題

a1:c1の内容を「-」で結合したいのですが、どうして次のコードでtransposeが2回に使用されましたか？

Sub t10()
arr = Range("A1:C1")
MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")
End Sub

まとめ

VBA配列は、セル領域のデータを読み取り、配列に値を割り当ててから、配列関数またはExcelにある関数を使用して関連の操作を実行します。さらに、配列は割り当ての演算にも効率的です。自分で試しましょう。