ＶＢＡの配列の格納について

質問

エクセルのＶＢＡで、セルのデータを配列に格納するスピードを向上したいと思います。

例えば、A1～A10000のセルにデータを書き出す場合、
For 行番号 = 1 To 10000
Cells(行番号, 1).Value = 1
Next

よりも、一旦、配列に書き込んだ後、一気にセルに書き込んだ方法が早いのですが、
For 行番号 = 1 To 10000
HAIRETU(行番号, 1) = 1
Next
Range("A1:A10000").Value = HAIRETU()

今度は、A1～A10000に書き込んだデータを、再度、配列に格納する場合、
For 行番号 = 1 To 10000
HAIRETU(行番号, 1) = Cells(行番号, 1).Value
Next

とすると時間がかかるので、
HAIRETU() = Range("A1:A10000").Value
というような処理をしたいのですが、うまくいきません。

よい方法がありましたら、ご教授ください。
よろしくお願いします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

ワークシートからデータが渡される場合は、明示的な配列変数に格納するなら、最初にVariant 型の配列変数の宣言をしないといけません。

例：

Sub Test1
Dim HAIRETU() As Variant
HAIRETU() = Range("A1:A10000").Value

End Sub

通常は、以下のようになるのでしょうね。

Sub Test2
Dim HAIRETU As Variant
HAIRETU = Range("A1", Range("A65536").End(xlUp)).Value
'格納した大きさ
MsgBox UBound(HAIRETU(), 1) & " X " & UBound(HAIRETU(), 2)

End Sub

でも、これは、一種の２次元配列ですから、当然、コードがややこしくなります。それは、ご承知の上で使われているものだと思います。

>Dim myData(10)
>Set myData = Range("a1")

申し訳ないけれど、配列の宣言をして、オブジェクト変数は格納できないように思います。
オブジェクト変数を配列のようにするには、Collection にしないといけません。ただ、かなり重くなってしまいます。

添え字に関しては、以下のようになります。

Sub Test3

Dim HAIRETU() As Variant '配列変数の宣言
'一次限の添え字の上限確保
i = Range("A1", Range("A65536").End(xlUp)).Rows.Count

ReDim HAIRETU(i, 1) '動的配列の再割り当て
HAIRETU(i, 1) = Range("A1", Range("A65536").End(xlUp)).Value
End Sub

なお、二次元配列の場合は、Variant 型しか受け付けないはずです。したがって、メモリの消費量は多くなります。

お礼

詳しく説明してくださってありがとうございます。
オブジェクト変数など、知らない単語を理解するのに時間がかかり、お礼が遅くなりました。
どうもありがとうございました。

回答No.5

BLUEPIXY

#3>LONGでは無理なのでしょうか？
ムリだと思います。
その他については、Wendy02 さんが解説して下さっているので略

お礼

回答、どうもありがとうございました。

回答No.3

BLUEPIXY

Dim HAIRETU As Variant
Dim 行番号

HAIRETU = Range("A1:A10000")

For 行番号 = 1 To 10000
Debug.Print HAIRETU(行番号, 1)
Next

お礼

回答、どうもありがとうございました。

補足

ありがとうございます。動作できました。
Variant型を使うのは初めてなのですが、ReDim HAIRETU(1 To 10000, 1 To 2)のように、配列の次元を指定する必要はないのでしょうか？
また、VariantをLONGに変えるとエラーになりますが、LONGでは無理なのでしょうか？

回答No.2

zap35

＃０１です。スペルミスです。

Dim myData(10)
Set myData = Range("a1")

ですね。なお後者の方を少し補足すると、mydata(2, 2)は Cells(2, 2)と同じ値を持ちます。

お礼

回答、どうもありがとうございました。

補足

Set myData = Range("a1")により、シート全体を配列に格納できることが理解できました。
Dim myData(10)の10の値は、どのような意味を持っているのでしょうか？
試しに、Sub macro4()のDim myDate(10)をDim myDate(1)に置き換えても、同じ実行結果になりました。

回答No.1

zap35

以下で試してみてください

Sub macro4()
Dim myDate(10)
Set mydata = Range("a1")
MsgBox ("1 " & mydata(1))
MsgBox ("2 " & mydata(2))
MsgBox ("3 " & mydata(3))
MsgBox ("4 " & mydata(4))
MsgBox ("5 " & mydata(5))
End Sub

または
Sub macro5()
Dim myDate
Set mydata = Range("a1")
MsgBox ("1 " & mydata(1, 1))
MsgBox ("2 " & mydata(2, 1))
MsgBox ("3 " & mydata(3, 1))
MsgBox ("4 " & mydata(4, 1))
MsgBox ("5 " & mydata(5, 1))
End Sub