メモ

*　ExcelVBAの二次元配列を一次元と二次元要素の入替に利用。
*　ExcelVBAで簡単なツールを作る機会が意外とあって重宝したのでメモ

使用の目的

*　二次元配列の場合は、一次元配列の添字を宣言時に「1 to 10」のようにしていた場合に、それ以外に変更することができない。

*　aryという二次元配列を「1 to 10,1 to 5」と宣言した場合、行列として捉えるならば、10行あって5列あると認識できる。宣言後にやっぱり列は６行必要だった場合は、ReDim Preserve ary(1 to 10, 1 to 6)で宣言し直すことができる。Preserveを記載することで既に配列にデータを設定している場合は、データも保持できる。

*　行が実は11行必要だった場合に、ReDim Preserve ary(1 to 11, 1 to 5)で宣言し直すことができない。CSVをExcelに読み込む際など全て配列に入れてしまいたい場合などで個人的には良くあるケース。何行あるのか不明なので、仮置きで100行分の箱をあらかじめ宣言して、100以上になった場合は都度、配列を拡張していきたい。だけどエラーが発生するためこのままでは対応できない。

解決策

*　行列を一旦逆で二次元配列を作成することで上記の問題を解決できる。↑で記載の例を使用すると「1 to 5,1 to 10」の配列を宣言しておく。注意する点は、配列にデータを格納するときに列行で格納すること。

*　このように宣言することで行は可変で配列数を増加させることができる。ReDim Preserve ary(1 to 5, 1 to 11)

*　最終的にデータを格納し終わった列行の配列を元々想定していた行列の形の配列に戻す際に使用する。

①　シンプルなパターンのソースコード

※①で使用しているTranspose関数には使用制限があります

●Transpose関数でエラーとなるケースについて

・配列の要素の中に255文字を超える長さの文字列が存在する（私はこれが原因でした）
・配列のどの次元でも要素数（Index数）が65536を超える場合
　⇒①のソースコードで下記のエラーが表示される場合、②のソースコードに置き換えて試してみて下さい。

●どんなエラーが表示される？

・型が一致しません。

'===================================================================================================
'配列1次と2次を入れ替える。行列変換処理
'
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'[引数]
'ary_1_count          : 変換後の行(1次配列)の要素数
'ary_2_count          : 変換後の列(2次配列)の要素数
'ary_before           : 変換前の2次元配列
'
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'[戻り値]
'Variant              : 一次元と二次元を入れ替えた配列
'===================================================================================================
Function aryClmRowTransform(ByVal ary_1_count As Long _
                            , ByVal ary_2_count As Long _
                            , ByRef ary_before As Variant _
                            ) as Variant
'--------------------------------------------------------------------------------------------------

     '行列変換前の配列のスリム化(余計な行の削除)
     ReDim Preserve ary_before(1 To ary_2_count, 1 To ary_1_count)
     aryClmRowTransform = WorksheetFunction.Transpose(ary_before)

End Function

②　①でエラーが発生する場合の代用ソースコード

'===================================================================================================
'配列1次と2次を入れ替える。行列変換処理
'
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'[引数]
'ary_1_count          : 変換後の行(1次配列)の要素数
'ary_2_count          : 変換後の列(2次配列)の要素数
'ary_before           : 変換前の2次元配列
'
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'[戻り値]
'Variant              : 一次元と二次元を入れ替えた配列
'===================================================================================================
Function aryClmRowTransform(ByVal ary_1_count As Long _
                            , ByVal ary_2_count As Long _
                            , ByRef ary_before As Variant _
                            ) as Variant
'--------------------------------------------------------------------------------------------------

     '行列変換前の配列のスリム化(余計な行の削除)
     ReDim Preserve ary_before(1 To ary_2_count, 1 To ary_1_count)
     Dim aryAfter As Variant
     ReDim aryAfter(1 To ary_1_count, 1 To ary_2_count)
     
     Dim r As Long
     Dim c As Long
     For r = 1 To ary_1_count
         For c = 1 To ary_2_count
             aryAfter(r, c) = ary_before(c, r)
         Next c
     Next r
     aryClmRowTransform = aryAfter
End Function

@radames1000 さまのコメントから、
以下の通り更新しました。

'■コメントアウトした行
     'Dim r As Long
     'Dim c As Long
     'ReDim aryAfter(1 to ary_1_count, 1 to ary_2_count)
     'For r = 1 To ary_1_count
     '    For c = 1 To ary_2_count
     '        aryAfter(r, c) = ary_before(c, r)
     '    Next c
     'Next r

'■追加した行
     aryClmRowTransform = WorksheetFunction.Transpose(ary_before)

終わりに

もっとこうしたら良いとか、そこおかしいのとか等あったら教えてください。

更新履歴

2021/01/14　@radames1000 さまのコメントから処理を更新
2021/02/17　Transpose関数には制限があり、制限に引っかかってしまった場合の対応を追加