VBA：配列に入れた要素を抽出したい

実現したいこと

どのようにすれば、Arr(1, 1)やArr(3, 2)など、指定した要素を取り出せるのでしょうか？

発生している問題

Excelシートから必要なセルのみを抽出し、配列を作成しました。
列数は2列と決まっていて、列1が"データ名"、列2が"データ名に対応する数値"です。
行数は元データを検索しながら取得しており、「ii」としています。（つまり、配列のサイズはArr(ii, 2)となります。）

配列の作成まではできたのですが、要素の抽出ができません。
下記のコードでは、配列の行数が最大のときの要素しか残っていないようです。
（i = 10だとすると、Arr(10, 1)の要素は出力できますが、Arr(1, 1)の要素はEmpty値となってしまいます。）

どのようにすれば、Arr(1, 1)やArr(3, 2)など、指定した要素を取り出せるのでしょうか？
初心者のため、初歩的なことをお聞きしているかと思いますが、お答えいただけましたら幸いです。

該当のソースコード

VBA
1'ws = 参照元のExcel
2'i = 参照元のExcelシートの行数
3'y To y + k = 参照元のExcelシートの検索する行数の範囲
4'ii = 配列Arrの行数
5
6ii = 0
7 For i = y To y + k
8            If ws.Cells(i, 1) <> "" Then
9                ii = ii + 1
10                ReDim Arr(ii, 2)
11                Arr(ii, 1) = ws.Cells(i, 1)　'この時点ではArr(1, 1)が取得できている
12                Arr(ii, 2) = ws.Cells(i, 5)
13            End If
14    Next
15'この後でArr(1, 1)などを抽出したいが、できない

YellowGreen

2023/12/21 07:52 編集

End If が重複していませんか。 A列に空白の行があると検索する行数と配列の要素の数は、一致しなくなるのですね。方法としては、事前に検索範囲のA列の値を検査して空白でないセルの数を保存しておき ReDim Arr(1 To 保存した数, 1 To 2)などとするか、コレクションに蓄積してから配列に戻すとかでしょうか。

krrr

2023/12/21 07:55

ご回答ありがとうございます。 End If 重複していましたので修正しました。先に個数を取得する必要があるのですね。個数を取得するコードを書いてみます。ありがとうございます。

krrr

2023/12/21 08:28

こちらの件、解決しました。基本的には個数を先に取得することでこの問題は解決できること、勉強になりました。ありがとうございました。

行動規範の内容に同意します

回答2件

ベストアンサー

ReDim Arr(ii, 2)

Preserve キーワードのない ReDim ステートメントを呼び出して動的配列を再定義した場合、各要素に格納されていた値は全て失われる。
次元数が 2 以上である動的配列を Preserve キーワード付きの ReDim ステートメントによって再定義する場合、サイズの変更が可能なのは最後の次元だけである。つまり 2 次元配列の場合、1 次元目のサイズを変更することはできない。
その次元における添え字の下限値を指定しなかった場合、その次元の添え字の最小値は 0 となる。

vba
1ii = 0
2For i = y To y + k
3    If ws.Cells(i, 1) <> "" Then
4        ii = ii + 1
5        'ここでは2次元配列の行と列の関係を逆転させる。
6        'また、各次元の添え字の最小値は明示的に 1 とする。
7        ReDim Preserve Arr(1 To 2, 1 To ii)
8        Arr(1, ii) = ws.Cells(i, 1)
9        Arr(2, ii) = ws.Cells(i, 5)
10    End If
11Next
12
13'最後に2次元配列の行列を入れ替える
14Arr = WorksheetFunction.Transpose(Arr)