本記事の場合、要素が65537以上だと正常な値が取得できません。
こちらの記事が改良版です。
パーツ化とは
ExcelVBAマクロ初級者からの脱却が、処理のパーツ化(プロシージャの分割)と考えています。
マクロを作る上で基本機能をパーツ化する事で、開発時間の削減に繋がります。
処理はパーツ単位で分ける事を心掛けましょう。
分かりやすく説明すると、エクセルの「関数」と同様です。
SUM関数であれば セル内に「=SUM(1+2+3)」と記載すれば、セルには「6」と表示されますよね。
値を渡せば正しい値で返ってくる動きです。
このコードをコピペでOK
Option Base 1
'■2次元配列の1次元目を増やすモジュール
Public Function Call_RedimPreserveArray(ByVal arr As Variant, ByVal sLen As Long)
Dim temp() As Variant
temp = WorksheetFunction.Transpose(arr)
ReDim Preserve temp(UBound(temp, 1), sLen)
Call_RedimPreserveArray = WorksheetFunction.Transpose(temp)
End Function
使い方
上記をコピペでお好きなモジュール内に記載してください。
Dim ExArray As Variant
Dim sLen As Long
ReDim ExArray(5, 3)
sLen = UBound(ExArray) + 1
ExArray = Call_RedimPreserveArray(ExArray, sLen)
結果イメージ
下記のように行数が増えます。(上記サンプルでは配列にデータ格納はしてませんが、イメージです)
注意点
- Transposeの仕様上、Option Base 1を設定します。
- 二次元配列しか対応していません。(三次元配列などの多次元配列は対応できません。)
- 要素が65537以上の場合、正常に動作しません。→別配列代入が望ましいです。
- Transposeもしくは別配列代入はどちらが早いか？はこちらの記事で検証。
通常、最終次元しか増やせない
通常エクセルでは縦の列(行方向)で管理する事が多いですが、配列では横方向に増やすのが一般的です。
しかしながら、二次元配列は通常配列を増やすRedim Preserveでは
下記のように最終次元(列方向)しか増やせません。
Dim ExArray As Variant
ReDim ExArray(5, 3)
'■(5,3)を(5,5)に変えることは出来るが、
ReDim Preserve ExArray(5, 5)
'■(5,3)を(10,3)に変えることは出来ない。※エラーが発生する
'実行時エラー9 インデックスが有効範囲にありません
ReDim Preserve ExArray(10, 3)
'■(5,3)を(4,3)に要素を減らすことも出来ない。※エラーが発生する
'実行時エラー9 インデックスが有効範囲にありません
ReDim Preserve ExArray(4, 3)
エラーが出る部分をTranspose関数を上手に使用しています。
1次元目(行方向)と2次元目を一度入替して、1次元目を増やす動作が可能です。
その為、二次元配列しか対応できません。
どんな時に使える？
配列内の要素を増やしたい時(商品マスタの差分チェックをして)差分のみ抽出する場合(配列を動的に管理したい)等、配列を使用して何かしらの計算をする場合によく使います。
コメント
この発想は無かったです！
素晴らしいコードです
御見それいたしました。
これは盲点でした。
ありがとうございます。
これを知らなかったので、構造体の配列を使ってました。
サンプルソースをそのままにしてみたら、「インデックスが有効範囲にありません。」なっています。
他の何かがおかしいと考えます。原因究明の場合、有償での対応可能です。お問い合わせ欄から問い合わせください。
素晴らしいアイディアをありがとうございます！
頭良すぎます！
１．トランスポーズ関数を使うとサイズの制限があります。
・配列の2次元目のサイズが65537以上だとエラーになります。
Sub トランスポーズ()
Dim stime
stime = Timer
Dim aArr As Variant
Dim sLen As Long
ReDim aArr(1 To 100, 1 To 65537)
Dim i As Long, j As Long
For i = 1 To 100
For j = 1 To 65537
aArr(i, j) = i & ” ” & j
Next
Next
sLen = UBound(aArr) + 1
‘aArr = SizeUp(aArr, sLen)
aArr = Call_RedimPreserveArray(aArr, sLen)
Debug.Print “経過時間=” & Format(Timer – stime, “00.000”)
Debug.Print “開始日時=” & Format(Now, “yyyy/mm/dd hh:mm:ss”)
End Sub
２．トランスポーズを使うより指定されたサイズを大きくして値をコピーして対象配列の変数に返却する方が処理が速いです。
Function SizeUp(aArr As Variant, sLen As Long)
ReDim nArr(1 To sLen, 1 To UBound(aArr, 2))
Dim i As Long, j As Long
For i = 1 To UBound(aArr, 1)
For j = 1 To UBound(aArr, 2)
nArr(i, j) = aArr(i, j)
Next
Next
SizeUp = nArr
End Function
昔に作ったコードでその時はOffice2003だったと思います。
当時は2003のMAX行数までしか対応することなかったので、
そのまま使えてる認識でいましたが、結構重大ですね。
また改めて記事に致します。ご提供ありがとうございます。