【Excel/VBA】続・ディクショナリに配列を持たせる「代入式の左辺の関数呼び出しは、バリアント型・・・」のエラーメッセージ

前に、ディクショナリの値に配列を持たせる、をやりました。

その時に、値で持たせる配列を指定するときに、セル範囲でがばりと入れると二次元配列の形になるので扱いにくいということを書きました。

が、セル範囲で指定して、FUNCTIONで一次元配列にして返せばいいんだ、と思いつきました。

これが前に使った表

前に書いたコード

Dim dic As Dictionary
Set dic = New Dictionary

Dim i As Long, j As Long
With ActiveSheet
　For i = 2 To .Range(“A1”).End(xlDown).Row
　　Dim v As Variant
　　v = .Range(.Cells(i, 2), .Cells(i, 5)).Value
　　dic.Add .Cells(i, 1).Value, v
　Next i
End With

v = .Range(.Cells(i, 2), .Cells(i, 5)).Value

で、変数vに列BからEの一行分の値を入れて

dic.Add .Cells(i, 1).Value, v

で、ディクショナリに配列になっている変数vを入れます。

ここを直します。

Dim dic As Dictionary
Set dic = New Dictionary

Dim i As Long, j As Long
With ActiveSheet
　For i = 2 To .Range(“A1”).End(xlDown).Row
　　Dim v() As Long
　　v = getarr(.Range(.Cells(i, 2), .Cells(i, 5)).Value)
　　dic.Add .Cells(i, 1).Value, v
　Next i
End With

赤い部分を直しました。

配列を受け取る変数 v をVariantではなくLongにし、変数の後にかっこを付けました。

Function(あとで作成します)のgetarrの引数として列B～Eの一行分のセル範囲の値を渡しています。

Function部分のコードです。

Function getarr(ByVal values As Variant) As Long()

Dim arr_() As Long　　　　　　　①

Dim i As Long　　　　　　　　　②
Dim idx As Long: idx = 0 　 ③

For i = LBound(values, 2) To UBound(values, 2)　　　　④
ReDim Preserve arr_(i)　　　　 ⑤
arr_(idx) = values(1, i)　　　　　⑥
idx = idx + 1　　　　　　　　 ⑦
Next i

getarr = arr_　　　　　　　　　　⑧

End Function

①一時的に使用する変数arr_を宣言します。この変数arr_に配列用データを蓄積し、最後にgetarrに代入して配列をもとのプロシージャに返します。

②カウンタ変数iを宣言

③配列インデックスに使用する変数idxを宣言。初期値0を代入します

④配列の最初から最後までループ。ここで注意するのはただUbound(values）とすると1要素目の最後まで・・・となり、1回しかループが行われません。

UBound(values, 2)　として、2要素目の最後までループするようにします。

⑤配列用変数arrをReDimで再宣言し、要素数を変更します。ReDimのあとにPreserveをつけないと、それまでに代入した値が消えてしまうので、ご注意を。（私はよく付け忘れます）

⑥変数arr_に配列valuesの値を代入します。caluesは二次元配列なので(1,i)と要素番号を指定します。

⑦配列のインデックス番号変数idxをカウントアップします

⑧最後にgetarrに配列arr_を代入します。元のプロシージャにリターンします。

実は、初歩的、すごくあほなミス、のようなのですが、このFunctionを書くときに失敗しています。

ミスったコードは以下

Function getarr(ByVal values As Variant) As Long()
Dim idx As Long: idx = 0
Dim i As Long
For i = LBound(values, 2) To UBound(values, 2)
ReDim Preserve getarr(idx)
getarr(idx) = values(1, i)
idx = idx + 1
Next i

End Function