20230125エクセルでハマったこと(range(cells)
エクセルVBA素人です。
はまったことの備忘録です。
VBAでセルに転記する時に、rangeとcellsを組み合わせることがある。
(rangeだと変数が使いにくいので、range(cells(変数))にすること多い)
さらに、配列も関係するとややこしい。
下記のコードが考えるヒントになる。
Sub ハマったrangecells() 'シートから配列作成
Dim ary As Variant
ReDim ary(1 To 3)
ary = Worksheets(1).Range("A1:C1") 'これだと2次元になる(1行でも)
Stop
End Sub
上記配列をシートから作成すると、
aryは、2次元配列になっている。
ary(1,1) = "A"とか。
で、これをまた、シートに転記してみる。
Sub ハマったrangecells追加() 'シートから配列作成→そのまま配列をシートへ転記
Dim ary As Variant
ReDim ary(1 To 3)
ary = Worksheets(1).Range("A1:C1") 'これだと2次元になる(1行でも)
Worksheets(3).Range("A1:C1") = ary 'この受け渡しだと配列の次元を気にしない
'rangeは2次元と覚えるか。
End Sub
問題なく転記できる。
だけど、配列が2次元になっていることに気づかない。
で、以下類似ケース。
Sub ハマったrangecells2() 'VBAコード上で作成した配列(問題ない)
Dim ary As Variant
ReDim ary(1 To 3)
ary = Array("A", "B", "C") 'VBAで作った配列は1次元
Stop
End Sub
これはエラーなく、array関数で格納できる。
配列は、
ary(1) = "A"
など一次元になっている。
次がはまったもの
Sub ハマったrangecells3() 'VBAコード上で作成した配列(エラー)
Dim ary As Variant
ReDim ary(1 To 3)
ary = Array("A", "B", "C") 'VBAで作った配列は1次元
'これをシートに転記
Worksheets(2).Range(Cells(1, 1), Worksheets(2).Cells(1, 3)) = arr '←実行時エラー1004
'アプリケーションの定義またはオブジェクト定義のエラー
Stop
End Sub
配列をシートに転記できない。
これは1次元の配列をワークシートに入れる時にrangeを使っているからエラーの気がする。
(次元が違うのでエラー)
でも、実は下記はrangeを使っても通る。
Sub ハマったrangecells4() 'VBAコード上で作成した配列(問題ない)
Dim ary As Variant
ReDim ary(1 To 3)
ary = Array("A", "B", "C") 'VBAで作った配列は1次元
'これをシートに転記
Worksheets(2).Range("A1").Resize(1, 3) = ary '←これは通る(が例外的と考えた方がいい?)
Stop
End Sub
これは、rangeを使っているので、次元が違って、無理かなと思ったが、通る。
rangeでも1セルの範囲だからかなとか理由不明。
下記がはまりやすいパターンかな。
Sub ハマったrangecells5() 'VBAコード上で作成した配列(エラー)
Dim ary As Variant
ReDim ary(1 To 3)
ary = Array("A", "B", "C") 'VBAで作った配列は1次元
'これをシートに転記
Worksheets(2).Range(Worksheets(2).Cells(1, 1)).Resize(1, 3) = ary '←これは通らない
'←実行時エラー1004
'アプリケーションの定義またはオブジェクト定義のエラー
Stop
End Sub
上記は、簡単に通りそうで通らない。
で、解決策。rangeを使っているので?、配列の次元を2次元に合わせる。
Sub ハマったrangecells6() '解決aryを2次元に
Dim ary As Variant
ReDim ary(1 To 1, 1 To 3)
' 一次元目が縦方向の「行」を表し、二次元目が横方向の「列」を表します
ary(1, 1) = "A"
ary(1, 2) = "B"
ary(1, 3) = "C"
'これをシートに転記
Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(1, 3)) = ary
Stop
End Sub
図
ary(1,1) = "A"
とかできている。上記のrange(cells)が通れば、かなりできるはず。
もう一つ解決策(できないのも合わせて載せてます)
Sub ハマったrangecells7() '解決aryを2次元に2(できないのもある)
Dim ary As Variant
ReDim ary(1 To 1, 1 To 3)
' 一次元目が縦方向の「行」を表し、二次元目が横方向の「列」を表します
ary(1, 1) = "A"
ary(1, 2) = "B"
ary(1, 3) = "C"
'これをシートに転記
' Worksheets(2).Range(Worksheets(2).Cells(1, 1)).Resize(UBound(ary, 1), UBound(ary, 2)) = ary ←これはダメ(なぜか)
Worksheets(2).Cells(1, 1).Resize(UBound(ary, 1), UBound(ary, 2)) = ary '←これは通る
Stop
End Sub
上記は、rangeを使うので2次元配列したのに、なせか動かず。がっかり。
仕方なく、rangeを抜いたらうまく動く。
Sub ハマったrangecells7() '解決aryを2次元に2(できないのもある)
Dim ary As Variant
ReDim ary(1 To 1, 1 To 3)
' 一次元目が縦方向の「行」を表し、二次元目が横方向の「列」を表します
ary(1, 1) = "A"
ary(1, 2) = "B"
ary(1, 3) = "C"
'これをシートに転記
' Worksheets(2).Range(Worksheets(2).Cells(1, 1)).Resize(UBound(ary, 1), UBound(ary, 2)) = ary ←これはダメ(なぜか)
Worksheets(2).Cells(1, 1).Resize(UBound(ary, 1), UBound(ary, 2)) = ary '←これは通る
Stop
End Sub
多分、rangeとcellsの関係が分かっていないせいだろう・・・。
'まとめ的なもの
'配列を配列に移すのは簡単
'シートを配列にし、それをシートに移すのも簡単(次元気にしない)
'しかし、VBAで作った配列をシートに入れる時、rangeだと配列の次元が違うことが多いので注意。
'配列を1次元にとどめたいなら結局for文で回すのが楽か。
'その他
'他にも配列をシートに転記する場合の前提としてワークシートの指定とか、1始まりとか結構面倒。
'ハマったrangecells7は、rangeを入れるとなぜかダメ(不思議)