セル範囲から配列変数へ転記

今度はセル範囲を配列変数に一気に格納します。



 セル範囲から配列変数に格納するのは若干問題があります。
前ページのサンプルの逆ですから、

'***************************************************************************************************
'   セル範囲から配列変数への転記サンプル                            Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'04/02/11(1.00)新規作成
'16/11/19(1.10)*.xlsm化
'20/01/17(1.11)記述整理等
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ シート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：TEST9
'* 機能　　：セル範囲から配列変数への転記
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2004年02月11日
'* 作成者　：井上　治
'* 更新日　：2020年01月17日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：ブックを明示していないのでアクティブなブックに対して作用します
'***************************************************************************************************
Sub TEST9()
    '-----------------------------------------------------------------------------------------------
    Dim objSh1 As Worksheet                                         ' Sheet2
    Dim tblVal(1 To 3, 1 To 4) As String                            ' 3×4の2次元配列

    Set objSh1 = Worksheets("Sheet1")
    ' セル範囲を一気に配列に転記
    tblVal = objSh1.Cells(1, 1).Resize(3, 4).Value
    ' 処理結果をメッセージ表示
    MsgBox "tblVal(1, 1) = " & tblVal(1, 1) & vbCr & _
           "tblVal(1, 2) = " & tblVal(1, 2) & vbCr & _
           "tblVal(1, 3) = " & tblVal(1, 3) & vbCr & _
           "tblVal(1, 4) = " & tblVal(1, 4) & vbCr & _
           "tblVal(2, 1) = " & tblVal(2, 1) & vbCr & _
           "tblVal(2, 2) = " & tblVal(2, 2) & vbCr & _
           "tblVal(2, 3) = " & tblVal(2, 3) & vbCr & _
           "tblVal(2, 4) = " & tblVal(2, 4) & vbCr & _
           "tblVal(3, 1) = " & tblVal(3, 1) & vbCr & _
           "tblVal(3, 2) = " & tblVal(3, 2) & vbCr & _
           "tblVal(3, 3) = " & tblVal(3, 3) & vbCr & _
           "tblVal(3, 4) = " & tblVal(3, 4)
End Sub
このようになるのですが、これだと、
セル範囲から配列変数に格納する(コンパイルエラー)
コンパイルエラーになってしまいます。

そこで、宣言での配列はあきらめてArray関数と同じようにセルからの転記まかせにしてみます。

'***************************************************************************************************
'   ■■■ シート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：TEST9
'* 機能　　：セル範囲から配列変数への転記
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2004年02月11日
'* 作成者　：井上　治
'* 更新日　：2020年01月17日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：ブックを明示していないのでアクティブなブックに対して作用します
'***************************************************************************************************
Sub TEST9()
    '-----------------------------------------------------------------------------------------------
    Dim objSh1 As Worksheet                                         ' Sheet2
'    Dim tblVal(1 To 3, 1 To 4) As String                            ' 3×4の2次元配列
    Dim tblVal As Variant                                           ' 配列で宣言するとエラーになる

    Set objSh1 = Worksheets("Sheet1")
    ' セル範囲を一気に配列に転記
    tblVal = objSh1.Cells(1, 1).Resize(3, 4).Value
    ' 処理結果をメッセージ表示
    MsgBox "tblVal(1, 1) = " & tblVal(1, 1) & vbCr & _
           "tblVal(1, 2) = " & tblVal(1, 2) & vbCr & _
           "tblVal(1, 3) = " & tblVal(1, 3) & vbCr & _
           "tblVal(1, 4) = " & tblVal(1, 4) & vbCr & _
           "tblVal(2, 1) = " & tblVal(2, 1) & vbCr & _
           "tblVal(2, 2) = " & tblVal(2, 2) & vbCr & _
           "tblVal(2, 3) = " & tblVal(2, 3) & vbCr & _
           "tblVal(2, 4) = " & tblVal(2, 4) & vbCr & _
           "tblVal(3, 1) = " & tblVal(3, 1) & vbCr & _
           "tblVal(3, 2) = " & tblVal(3, 2) & vbCr & _
           "tblVal(3, 3) = " & tblVal(3, 3) & vbCr & _
           "tblVal(3, 4) = " & tblVal(3, 4)
End Sub
これだと、コンパイルエラーも発生せず、
セル範囲から配列変数に格納する(処理結果)
このように処理されます。



 ※なお、通常のArray関数と違い、セル範囲から配列格納した場合は、配列要素は行列ともに「1」から始まります。

おかしな現象が見つかりました。(2022年6月発見)



 おかしな現象
(この画像をクリックすると、このサンプルがダウンロードができます。)
これは当サイトに質問メールをいただいたものから、文面の内容をこちらでワークシートに起こしたものです。
このように簡単な表が同じシート上に上下に並んでいるものです。



 ここで、下の表のセル範囲を配列に取り出して扱う中で「おかしな現象」が起きました。
まず、ソースコードを提示します。

'***************************************************************************************************
'  シートto配列1(テスト)
'***************************************************************************************************
Sub シートto配列1()
    '-----------------------------------------------------------------------------------------------
    Dim objIchiran As Range                                         ' 下の表のセル範囲
    Dim vntIchiran1(1 To 1, 1 To 3) As Variant                      ' 年齢一覧1
    Dim vntIchiran2 As Variant                                      ' 年齢一覧2
    Dim vntIchiran3 As Variant                                      ' 年齢一覧3
    Dim strResult As String                                         ' 結果表示用
    ' 対象セル範囲の取得
    Set objIchiran = ThisWorkbook.Worksheets("Sheet1").Range("$A$10:$C$20")
    ' 検査値の取得
    With objIchiran
        ' 年齢一覧1
        vntIchiran1(1, 1) = .Cells(1, 1).Value
        vntIchiran1(1, 2) = .Cells(1, 2).Value
        vntIchiran1(1, 3) = .Cells(1, 3).Value
        ' 年齢一覧2
        vntIchiran2 = .Range("$A$1:$C$1").Value
        ' 年齢一覧3
        vntIchiran3 = .Range(.Cells(1, 1), .Cells(1, 3)).Value  ' ←19行(№10)が取り込まれる！？
    End With
    ' 処理結果のメッセージ編集
    strResult = "年齢一覧1"
    strResult = strResult & vbTab & vntIchiran1(1, 1)
    strResult = strResult & vbTab & vntIchiran1(1, 2)
    strResult = strResult & vbTab & vntIchiran1(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧2"
    strResult = strResult & vbTab & vntIchiran2(1, 1)
    strResult = strResult & vbTab & vntIchiran2(1, 2)
    strResult = strResult & vbTab & vntIchiran2(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧3"
    strResult = strResult & vbTab & vntIchiran3(1, 1)
    strResult = strResult & vbTab & vntIchiran3(1, 2)
    strResult = strResult & vbTab & vntIchiran3(1, 3)
    ' 処理結果の表示
    MsgBox strResult
End Sub
こういったコードです。



 

    ' 対象セル範囲の取得
    Set objIchiran = ThisWorkbook.Worksheets("Sheet1").Range("$A$10:$C$20")
まず、ここで対象セル範囲($A$10:$C$20)をRangeオブジェクト(objIchiran)に取得します。
おかしな現象
対象セル範囲($A$10:$C$20)は赤枠の範囲になります。



 次にRangeオブジェクト(objIchiran)から値を3種類の配列に取り出します。

    ' 検査値の取得
    With objIchiran
        ' 年齢一覧1
        vntIchiran1(1, 1) = .Cells(1, 1).Value
        vntIchiran1(1, 2) = .Cells(1, 2).Value
        vntIchiran1(1, 3) = .Cells(1, 3).Value
        ' 年齢一覧2
        vntIchiran2 = .Range("$A$1:$C$1").Value
        ' 年齢一覧3
        vntIchiran3 = .Range(.Cells(1, 1), .Cells(1, 3)).Value  ' ←19行(№10)が取り込まれる！？
    End With
以降のコードはこれらの結果のメッセージ表示です。



 さて、先に実行結果を見て下さい。
おかしな現象
取り出した3種類の配列のうち「年齢一覧3(vntIchiran3)」だけがおかしな結果になっています。
どれも配列の1件目を表示させるはずですが、 「年齢一覧3(vntIchiran3)」は10件目が表示されてしまいます。
試しにCellsプロパティの第1引数を「0」とか「2」とかにしてみても、 9行分ズレた結果のままになります。

では、ここでこのような「上下２表」ではなく、単純な「１表」で試してみます。
おかしな現象
(この画像をクリックすると、このサンプルがダウンロードができます。)



 

'***************************************************************************************************
'  シートto配列2(テスト)
'***************************************************************************************************
Sub シートto配列2()
    '-----------------------------------------------------------------------------------------------
    Dim objIchiran As Range                                         ' 下の表のセル範囲
    Dim vntIchiran1(1 To 1, 1 To 3) As Variant                      ' 年齢一覧1
    Dim vntIchiran2 As Variant                                      ' 年齢一覧2
    Dim vntIchiran3 As Variant                                      ' 年齢一覧3
    Dim strResult As String                                         ' 結果表示用
    ' 対象セル範囲の取得
    Set objIchiran = ThisWorkbook.Worksheets("Sheet1").Range("$A$2:$C$12")
    ' 検査値の取得
    With objIchiran
        ' 年齢一覧1
        vntIchiran1(1, 1) = .Cells(1, 1).Value
        vntIchiran1(1, 2) = .Cells(1, 2).Value
        vntIchiran1(1, 3) = .Cells(1, 3).Value
        ' 年齢一覧2
        vntIchiran2 = .Range("$A$1:$C$1").Value
        ' 年齢一覧3
        vntIchiran3 = .Range(.Cells(1, 1), .Cells(1, 3)).Value  ' ←3行(№2)が取り込まれる！？
    End With
    ' 処理結果のメッセージ編集
    strResult = "年齢一覧1"
    strResult = strResult & vbTab & vntIchiran1(1, 1)
    strResult = strResult & vbTab & vntIchiran1(1, 2)
    strResult = strResult & vbTab & vntIchiran1(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧2"
    strResult = strResult & vbTab & vntIchiran2(1, 1)
    strResult = strResult & vbTab & vntIchiran2(1, 2)
    strResult = strResult & vbTab & vntIchiran2(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧3"
    strResult = strResult & vbTab & vntIchiran3(1, 1)
    strResult = strResult & vbTab & vntIchiran3(1, 2)
    strResult = strResult & vbTab & vntIchiran3(1, 3)
    ' 処理結果の表示
    MsgBox strResult
End Sub
コードはこのようになります。「対象セル範囲の取得」以外は変更ありません。
実行すると、
おかしな現象
このような結果になりました。
「年齢一覧3(vntIchiran3)」は2件目が表示されてしまいます。

結果として、先の「上下２表」と同様に最初に取り出した「対象セル範囲」の手前までの行数分がズレてしまいます。
このことからすると、シート内の一部のセル範囲を取り出した中から、さらにRangeとCellsを組み合わせた部分取り出しを行なうとこのような事象が発生するようです。
Excel365(32ビット版、64ビット版とも)の環境ですが、 これは以前のバージョンでも起きることなのでしょうか？
私自身も今までこのような使い方はしなかったので、不思議になりました。

上記の「おかしな現象」が解決しました。(2023年7月)



 2023年7月4日にtinei様から本件についての「解釈」についての連絡をいただき、この「おかしな現象」について納得いく説明が得られました。
いただいた内容は「この件について、相対の相対だと思います。」ということでした。



 VBAでセル範囲の参照を行なう時、そのセル範囲が何かの値で変動する場合に「.Range」の中に「.Cells」を書く方法をよく使います。 ですが、ここでの「.」のWithブロックの参照先は「ワークシート」のはずです。



 「ワークシート」であれば起点はA1セルであり、「Cellsプロパティ」は「常にワークシート上での絶対参照」のように誤解してしまうのですね。
今回の問題になったWithブロックの参照先は「セル範囲」であってワークシート上では下に9行ズレた範囲になっているのです。



 こうすれば良いでしょう。(年齢一覧4)

Option Explicit

'***************************************************************************************************
'  シートto配列3(テスト)
'***************************************************************************************************
Sub シートto配列3()
    '-----------------------------------------------------------------------------------------------
    Dim objSh As Worksheet                                          ' 対象シート
    Dim objIchiran As Range                                         ' 下の表のセル範囲
    Dim vntIchiran1(1 To 1, 1 To 3) As Variant                      ' 年齢一覧1
    Dim vntIchiran2 As Variant                                      ' 年齢一覧2
    Dim vntIchiran3 As Variant                                      ' 年齢一覧3
    Dim vntIchiran4 As Variant                                      ' 年齢一覧4
    Dim strResult As String                                         ' 結果表示用
    ' 対象シートの取得
    Set objSh = ThisWorkbook.Worksheets("Sheet1")
    ' 対象セル範囲の取得
    Set objIchiran = objSh.Range("$A$10:$C$20")
    ' 検査値の取得
    With objIchiran
        ' 年齢一覧1
        vntIchiran1(1, 1) = .Cells(1, 1).Value
        vntIchiran1(1, 2) = .Cells(1, 2).Value
        vntIchiran1(1, 3) = .Cells(1, 3).Value
        ' 年齢一覧2
        vntIchiran2 = .Range("$A$1:$C$1").Value
        ' 年齢一覧3
        vntIchiran3 = .Range(.Cells(1, 1), .Cells(1, 3)).Value  ' ←19行(№10)が取り込まれる！？
        '-------------------------------------------------------------※追加
        ' 年齢一覧4
        vntIchiran4 = .Range(objSh.Cells(1, 1), objSh.Cells(1, 3)).Value
        '-------------------------------------------------------------※追加
    End With
    ' 処理結果のメッセージ編集
    strResult = "年齢一覧1"
    strResult = strResult & vbTab & vntIchiran1(1, 1)
    strResult = strResult & vbTab & vntIchiran1(1, 2)
    strResult = strResult & vbTab & vntIchiran1(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧2"
    strResult = strResult & vbTab & vntIchiran2(1, 1)
    strResult = strResult & vbTab & vntIchiran2(1, 2)
    strResult = strResult & vbTab & vntIchiran2(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧3"
    strResult = strResult & vbTab & vntIchiran3(1, 1)
    strResult = strResult & vbTab & vntIchiran3(1, 2)
    strResult = strResult & vbTab & vntIchiran3(1, 3)
    strResult = strResult & vbCrLf
    '-----------------------------
    strResult = strResult & "年齢一覧4"
    strResult = strResult & vbTab & vntIchiran4(1, 1)
    strResult = strResult & vbTab & vntIchiran4(1, 2)
    strResult = strResult & vbTab & vntIchiran4(1, 3)
    ' 処理結果の表示
    MsgBox strResult
End Sub
実行すると、
おかしな現象⇒解決
(この画像をクリックすると、このサンプルがダウンロードができます。)
このような結果になりました。
「年齢一覧4」は、

        ' 年齢一覧4
        vntIchiran4 = .Range(objSh.Cells(1, 1), objSh.Cells(1, 3)).Value
というように記述しており、「Cellsプロパティ」はワークシートが参照先であることを明示させています。



 セル範囲を参照元にした中での「Cellsプロパティ」の利用では引数は相対参照として動作することに注意する必要があります。

但し、結果で見て「そういうものなんだ．．．」としているだけで気味が悪かったので再度調べて見ました。
MSサイトに「Range.Cells プロパティ (Excel)」というページがあって、2023/04/07更新時点では以下のように説明されています。



 おかしな現象⇒MSの説明2023/04/07



 ですが、このコードを実行すると、斜体フォントの設定先はセルC3:E7なので「説明」か「動作」のいずれかが誤りだということになります。
Cellsプロパティの引数が指すセルが、親のRangeによってシフトしてしまうのはどう説明するものなのでしょう？



 本件はMS側に質問中です。