エクセルでハマったこと(range(cells)

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を入れるとなぜかダメ(不思議)