配列を利用する:Excel VBA入門

スポンサードリンク

2021/06/13

配列を利用する	定数を利用する	セルの値を配列に読み込む
データ型	配列を初期化する(Erase)

配列の利用　　　 topへ

配列を使うと一つの変数に複数の値を格納できます。
このサイトでプロシージャ名にセル番地を使用しているため、エラーが発生するとの指摘がありました。
セル参照と競合するプロシージャ名は使用しないのが好ましいので　rei701　などを　rei_701　といった形式(間にアンダーバーを入れました)に書き直しました。
他のページでも同様なことがありますのでご注意ください。
(2019/2/11 に掲示板で kazuさんから指摘を受け、2/12にこのページは書き換えました。ありがとうございました。)

配列の宣言　　　topへ

配列には宣言時に要素数を固定する(固定長配列)とコードの途中で変化させるもの(動的配列)があります。

固定長配列

Dim A(5) As Integer　と宣言すると、要素数はA(0)～A(5)の6要素になります。0から始まります。
()内の数値をインデックス番号といいます。
【例】

Sub rei_701()
　Dim I As Integer
　Dim A(5) As Integer
　　For I = 0 To 5
　　　　A(I) = I
　　Next I
　　For I = 0 To 5
　　　　Cells(I + 1, 1).Value = A(I) * 10
　　　　'Range("A" & I + 1).Value = A(I) * 10
　　Next I
End Sub

実行結果

Option Base 1　を記述して、Dim A(5) As Integer と宣言すると、A(1)～A(5)の5要素となります。1から始まります。

【例】

Option Base 1
Sub rei_701()
　Dim I As Integer
　Dim A(5) As Integer
　　For I = 1 To 5
　　　　A(I) = I
　　Next I
　　For I = 1 To 5
　　　　Cells(I, 1).Value = A(I) * 10
　　　　'Range("A" & I).Value = A(I) * 10
　　Next I
End Sub

実行結果

Dim A(3 To 5) As Integer と宣言すると、A(3)～A(5)の3要素となります。 3から始まります。

【例】

Sub rei_701()
　Dim I As Integer
　Dim A(3 To 5) As Integer
　　For I = 3 To 5
　　　　A(I) = I
　　Next I
　　For I = 3 To 5
　　　　Cells(I, 1).Value = A(I) * 10
　　　　'Range("A" & I).Value = A(I) * 10
　　Next I
End Sub

実行結果

配列のインデックス番号は最小値をLBound関数,最大値をUBound関数で取り出せます。
よって、下のように書くことができます。

Sub rei_701()
　Dim I As Integer
　Dim A(3 To 5) As Integer
　　For I = LBound(A) To UBound(A)
　　　　A(I) = I
　　Next I
　　For I = LBound(A) To UBound(A)
　　　　Cells(I, 1).Value = A(I) * 10
　　　　'Range("A" & I).Value = A(I) * 10
　　Next I
End Sub

Array関数を利用する場合は、データの型をVariantとします。
ただし、要素数を固定することはできず、動的配列にします。

【例】

Sub rei_701()
　Dim I As Integer
　Dim B As Variant
　B = Array(0, 1, 2, 3, 4, 5)
　　For I = LBound(B) To UBound(B)
　　　　Cells(I + 1, 1).Value = B(I) * 10
　　　　'Range("A" & I).Value = B(I) * 10
　　Next I
End Sub

実行結果

動的配列

最初はDim C As Integer と要素数を固定せずに宣言します。
コード中でReDim C(5) のようにインデックス番号を設定します。
【例】

Sub rei_701()
　Dim I As Integer
　Dim lRow As Integer
　Dim C() As Integer
　lRow = Cells(Rows.Count, 1).End(xlUp).Row　'A列の最終行番号を求めています
　ReDim C(lRow)
　　For I = 1 To lRow
　　　　C(I) = Cells(I, 1).Value * 10
　　Next I
　　For I = 1 To lRow
　　　　Cells(I, 2).Value = C(I)
　　Next I
End Sub

実行結果

配列の値を保持したまま要素数を変更する

A1:A5まであらかじめ入力されている値を配列Cに読込む。
A5,A6に値を入力後、配列の要素数を変更してさらにCへ追加して読み込む例です。

Sub rei_701()
　Dim I As Integer
　Dim lRow As Integer, lRow2 As Integer
　Dim C() As Integer
　lRow = Cells(Rows.Count, 1).End(xlUp).Row
　ReDim C(lRow)
　　For I = 1 To lRow
　　　　C(I) = Cells(I, 1).Value * 10
　　Next I
　Cells(lRow + 1, 1).Value = 50
　Cells(lRow + 2, 1).Value = 60
'
　lRow2 = Cells(Rows.Count, 1).End(xlUp).Row
　ReDim Preserve C(lRow2)
'
　　For I = lRow + 1 To lRow2
　　　　C(I) = Cells(I, 1).Value * 10
　　Next I
　　For I = 1 To lRow2
　　　　Cells(I, 2).Value = C(I)
　　Next I
End Sub

実行結果

なお、ReDim Preserve C(lRow2)ではなく、ReDim C(lRow2)とすると、それまで配列に納められていた値は失われます。

Sub rei_701()
　Dim I As Integer
　Dim lRow As Integer, lRow2 As Integer
　Dim C() As Integer
　lRow = Cells(Rows.Count, 1).End(xlUp).Row
　ReDim C(lRow)
　　For I = 1 To lRow
　　　　C(I) = Cells(I, 1).Value * 10
　　Next I
　Cells(lRow + 1, 1).Value = 50
　Cells(lRow + 2, 1).Value = 60
'
　lRow2 = Cells(Rows.Count, 1).End(xlUp).Row
　ReDim C(lRow2)
'
　　For I = lRow + 1 To lRow2
　　　　C(I) = Cells(I, 1).Value * 10
　　Next I
　　For I = 1 To lRow2
　　　　Cells(I, 2).Value = C(I)
　　Next I
End Sub

実行結果

定数の宣言　　　 topへ

決まった値がコード内で何度も使用される時は定数として宣言します。

Const 定数名 AS データ型 = 値　のようにConstステートメントを使って宣言します。

【例】定数 Zei に消費税5%を設定しています。　5%は0.05としています。
下記の例では消費税はINTで切り捨てています。

Sub rei_702()
　Dim I As Integer
　Const Zei As Single = 0.05
　　For I = 2 To 5
　　　　Cells(I, 2).Value = Int(Cells(I, 1).Value * Zei)
　　Next I
End Sub

実行結果

セル範囲の値を配列に読み込んで処理する　　　 topへ

セル範囲の値を配列に読込み、また別の列に書き出す例です。
- 配列のデータ型は Variant になります。
  
  Sub rei_705()
  　Dim A As Variant
  　A = Range("A1:B5").Value
  　Range("D1:E5").Value = A
  End Sub

セル範囲の値を逐次チェックし、同じ値があったらその隣のセルの値を書き出す。というのを例にしてみます。
配列を使うと処理速度が速くなるのが分かります。
記載している秒数は使用しているPCによって異なります。(ここで使用しているPCでの秒数です)
処理時間が異なることのイメージとして記載しています。

【例】
A1:A60000まで1～5,000の数値を繰り返して入力します。
B1:B60000は1～60,000の数値が連続値で入力しています。

Sub sample_data()
　Dim i As Long
　Dim j As Long
　Dim cn As Long

　For j = 1 To 12
　　For i = 1 To 5000
　　　cn = cn + 1
　　　Cells(i + (j - 1) * 5000, 1).Value = i
　　　Cells(i + (j - 1) * 5000, 2).Value = cn
　　Next i
　Next j

上の例は値を順次書き込んでいるので、ちょっと時間を要します。
時短するため、値を配列に入れてから、シートのセルに一気に書き込む例です。

Sub sample2()
　Dim myData(1 To 60000, 1 To 2)
　Dim i As Long
　Dim j As Long
　Dim cn As Long

　For j = 1 To 12
　　For i = 1 To 5000
　　　cn = cn + 1
　　　myData(i + (j - 1) * 5000, 1) = i
　　　myData(i + (j - 1) * 5000, 2) = cn
　　Next i
　Next j
　Range("A1:B60000").Value = myData
End Sub

A列で「5,000」の値が見つかったら、D列にB列の値を書き出します。

For～Nextで逐次セル値をチェックします。

Sub rei_706()
　Dim i As Long, myCnt As Long
　　For i = 1 To 60000
　　　　If Range("A" & i).Value = 5000 Then
　　　　　　myCnt = myCnt + 1
　　　　　　Range("D" & myCnt).Value = Range("B" & i).Value
　　　　End If
　　Next i
End Sub

計算結果：約2.5秒

For Each～Nextで逐次セル値をチェックします。

Sub rei_707()
　Dim c As Range, myCnt As Long
　Dim myTime
　　For Each c In Range("A1:A60000")
　　　　If c.Value = 5000 Then
　　　　　　myCnt = myCnt + 1
　　　　　　Range("D" & myCnt).Value = c.Offset(0, 1).Value
　　　　End If
　　Next c
End Sub

計算結果：約1.3秒

配列に読み込んでから処理します。

Sub rei_708()
　Dim A As Variant
　Dim i As Long, myCnt As Long
　A = Range("A1:B60000").Value
　　For i = 1 To 60000
　　　　If A(i, 1) = 5000 Then
　　　　　　myCnt = myCnt + 1
　　　　　　Range("D" & myCnt).Value = A(i, 2)
　　　　End If
　　Next i
End Sub

計算結果：約0.5秒

データ型　　　 topへ

表記	意味	型宣言文字	データ範囲	Byte数
Byte	バイト型		0～255	1
Boolean	ブール型		TrueまたはFalse	2
Integer	整数型	%	-32,768　～　32,767	2
Long	長整数型	&	-2,147,483,648 ～ 2,147,483,647	4
Currency	通貨型	@	-922,337,203,685,477.5808～　922,337,203,685,477.5807	8
Single	単精度浮動小数点型	!	-3.402823E38～-1.401298E-45(負の値) 1.401298E-45～3.402823E38(正の値)	4
Double	倍精度浮動小数点型	#	-1.79769313486232E308～-4.94065645841247E-324(負の値) 4.94065645841247E-324～1.79769313486232E308(正の値)	8
Date	日付型		西暦1900年1月1日～西暦9999年12月31日	8
String	固定長文字列型	$	0～約64KB	1L+2L
String	可変長文字列型		0～2GB	10+L
Variant	バリアント型		数値(Double) 文字列(可変長文字列)	16 22+L
Object	オブジェクト型		オブジェクトを参照する	4
Decimal	10進			14

配列の初期化(Eraseステートメント)　　　topへ

固定サイズの配列の場合は要素を再初期化し、動的配列の場合は割り当てたメモリを解放します。

配列の型	Erase ステートメントの実行結果
静的数値配列	要素はすべて0に設定されます
静的文字列配列 (可変長)	要素はすべて長さ0の文字列「""」に設定されます
静的文字列配列 (固定長)	要素はすべて0に設定されます
静的バリアント型 (Variant)	配列要素はすべてEmpty値に設定されます
ユーザー定義型配列	各要素は、別個の変数として設定されます
オブジェクト配列	要素はすべて特別な値Nothingに設定されます

スポンサードリンク

よねさんのWordとExcelの小部屋｜Excel(エクセル)講座の総目次｜Excel(エクセル) VBA入門：目次｜配列の利用

PageViewCounter
　 Counter
　Since2006/2/27

配列を利用する:Excel VBA入門

配列の利用 topへ

配列の宣言 topへ

固定長配列

動的配列

定数の宣言 topへ

セル範囲の値を配列に読み込んで処理する topへ

データ型 topへ