セル範囲を変数に入れるということ

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

セル範囲を変数に入れて(格納して)、その変数内で何かの操作をするーーこれ、意外と多くの方がやっています。でも、おそらく、ちゃんと理解している人は少ないと思いますので、注意点やポイントなどを解説します。ただし、この件を詳しくやると、いずれにしても「VBAの基礎」に関わってきますから、すごく長～い話＆難し～い話になります。なので、(私にしては)サラッと書きます。あらかじめご容赦ください。

何はともあれ、理解していただきたいのは、セル範囲を配列に入れてから操作したからといって、それだけで「マクロは速くならない」ということです。この件は、下記のページで詳しく解説していますので、ぜひご覧ください。

VBA高速化テクニック「配列を使う」

ちなみに、私が自分で作るマクロで、セル範囲を一度配列に入れるようなことは、ほとんどしません。そんなことをしなくても、私が作るマクロは速いです(笑)。

オブジェクト変数に入れる

まず、しっかりと認識していただきたいのは「セル範囲を変数に入れる方法は2つある」ということです。

  1. オブジェクト変数に入れる
  2. バリアント型変数に入れる

両者は、まったく意味が違いますし、当然その後の考え方や操作も異なります。でも、具体的なコードは似ています。

【オブジェクト変数】

Sub Macro1()
    Dim A As Range
    Set A = Range("A1:C4")
    MsgBox A(2, 3)
End Sub

【バリアント型変数】

Sub Macro1()
    Dim A As Variant
    A = Range("A1:C4")
    MsgBox A(2, 3)
End Sub

上記2つのコードでしたら結果は同じになります。しかし、それは"たまたま"です。毎回同じになるわけではありません。だって、そもそもやっていることが異なるのですから。書き方というか、コードを見て判断するのでしたら、まず「代入するとき、Setを使っている」かどうかです。Setが書かれていたら、それは100%オブジェクト変数です。あと、その変数(ここではA)の型でも判断できますが、オブジェクト変数であっても、Variant型で宣言されることもあります。

さて、まずは「オブジェクト変数に入れる」やり方のうち、多くの人が間違えているところからです。とりあえず、次のコードをご覧ください。

Sub Macro2()
    Dim R As Range, ws As Worksheet
    Set R = Range("A1")
    Set ws = Sheets("Sheet1")
    ws.R.Value = 100
End Sub

何をしたいのか分かりますか？要するに「Sheet1のセルA1に100を代入する」というコードです。いや、それを期待したけど間違っているコードです。もちろん、実行するとエラーになります。

言うまでもありませんけど「値を代入できない」というレベルの話ではありません。この書き方ではセルを特定できないです。理由を解説します。まず、オブジェクト変数とは何かって話ですけど、実はこれだけでも長～い解説になります。なのでここでは簡単に「何かのオブジェクトに"別名"をつける仕組み」だと理解してください。上記のコードで「Set ○○ = ××」部分がオブジェクト変数への代入です。××に○○という"別名"を設定したとイメージしてください。

さて、問題は下記のコードです。

Set R = Range("A1")

これ、どういう意味ですか？何をしていますか？正確に言えますか？

  • オブジェクト変数Rに、セルA1を代入している
  • セルA1に、Rという"別名"を設定している

どちらも不正解です。私の「VBAベーシック セミナー」では、このへんの基礎を、午前中の2時間かけて丁寧に解説していますので、セミナーを受けた方でしたら、何を間違えているのか一目瞭然でしょう。未受講の方へ向けて、簡単に解説します。

VBAの中は3種類に分類できるんですけど、そのうちの一つ「Excelを操作するとき」に使うのがオブジェクト式です。オブジェクト式は、大きく分けて次の2パターンに分類できます。

  • 対象.様子 = 値
  • 対象.命令

このうち「対象」は、階層構造になっています。何かの対象を指定するときは、原則として、すべての階層構造を省略せずに書かなければなりません。これがVBAのルールです。たとえば、棒グラフの中で、ひとつの棒だけ表面を赤く塗りつぶすには、次のように書きます。

グラフとかオートシェイプなどの階層構造は、このように超複雑です。とはいえ、多くのマクロで一般的によく扱われる対象(オブジェクト)は「セル」でしょう。セルは、VBAの専門用語で「Rangeオブジェクト」と呼ばれます。では、セルの階層構造を考えてみましょう。セルは必ず、どこかのワークシートに属しています。どのワークシートにも属していないセルなんて、あり得ませんよね。そして、ワークシートは必ず、どこかのブックに属しています。したがって、セルは必ず

ブック＞ワークシート＞セル

という階層構造になっています。ちなみに、ブックの上(親)の階層はExcelです。VBAの専門用語で表すとApplicationです。VBAから見てExcelは、絶対に1つしか存在しませんので、ある意味"基点"みたいなものです。なので、当たり前すぎて誰も書きません。さて、ブック＞ワークシート＞セル という階層構造をVBAで記述すると、たとえば次のようになります。

Workbooks("Book1,xlsx").Sheets("Sheet1").Range("A1")

先に書いたとおり、VBAでは「すべての階層構造を省略せずに書かなければならない」というのが原則です。しかし、それでは大変なことになります。たとえば、セルA1の値をセルA2に代入するという簡単な操作であっても、次のようなコードになってしまいます。

Workbooks("Book1,xlsx").Sheets("Sheet1").Range("A2").Value = Workbooks("Book1,xlsx").Sheets("Sheet1").Range("A1").Value

煩雑です。とても、やってられません。なので、この「ブック＞ワークシート＞セル」に限っては、特例が認められています。それは「省略してもいいよ」という特別ルールです。

省略できるのは「ブック」と「ワークシート」だけです。「セル」まで省略してしまったら、どこのことだか分からなくなります。さて「ブック」を省略して、いきなり「ワークシート」から記述した場合、その「ワークシート」はアクティブブックの「ワークシート」と認識されます。

「ブック」と「ワークシート」の両方を省略すると、その「セル」はアクティブシートのセルとみなされます。言うまでもありませんが、アクティブシートの上(親)階層はアクティブブックです。

さて、ここまで理解した上で、オブジェクト変数の話に戻りましょう。先にご紹介したエラーになるコードをもう一度お見せします。

Sub Macro2()
    Dim R As Range, ws As Worksheet
    Set R = Range("A1")
    Set ws = Sheets("Sheet1")
    ws.R.Value = 100
End Sub

この中の

Set R = Range("A1")

は、何をしていますか？「オブジェクト変数Rに、セルA1を入れている」のではありません。だって、さっき言ったじゃないですか。どのワークシートにも属していないセルなんて、あり得ないって。そこはみなさん納得したはずです。本来セルは「ブック＞ワークシート＞セル」という階層構造で表されます。しかし、このうち「ブック」と「ワークシート」は省略できます。省略すると、それぞれ「アクティブブックの」「アクティブシートの」とみなされます。つまり、

Set R = Range("A1")

は

Set R = アクティブブック.アクティブシート.Range("A1")

という意味です。同様に

Set ws = Sheets("Sheet1")

は

Set ws = アクティブブック.Sheets("Sheet1")

です。となれば、次の

ws.R

って、こういうことになっちゃいます。

アクティブブック.Sheets("Sheet1").アクティブブック.アクティブシート.Range("A1")

もうね、何を言いたいのか。グチャグチャのメチャクチャです。だからエラーになるのは当然です。

  • 対象(オブジェクト)は階層構造になっている
  • 本来はすべての階層を必ず記述しなければならない
  • しかし「ブック＞ワークシート＞セル」だけは例外
  • ブックやワークシートを省略するとアクティブ～になる
  • オブジェクト変数にセルを代入すると、ワークシートやブックも含まれる

というVBAの基本をしっかり認識してください。本当は、この件について、まだまだ語りたいことはあるのですが、このへんにしておきます。

Rangeオブジェクトの難しさ

この話は、書くかどうか迷ったのですが、チラッとだけ書きます。ただし、超絶難しい話ですから、くれぐれも"なぜ、そうなるのか"を理解しようなどとは思わないでください。頭が混乱した方は(たぶん全員混乱するでしょう)、見なかったことにしていただいてもけっこうです。次の「バリアント型変数に入れて配列として操作する」と対比する目的で書きます。

上図のように「4行×3列」のデータがあったとします。このセル範囲A1:C4をオブジェクト変数に入れます。

Sub Macro4()
    Dim A As Range
    Set A = Range("A1:C4")
End Sub

セル範囲A1:C4に「A」というニックネームをつけたようなイメージです。この「A」について、いくつか調べてみましょう。

Sub Macro4()
    Dim A As Range
    Set A = Range("A1:C4")
    MsgBox "行数：" & A.Rows.Count & vbCrLf & _
           "列数：" & A.Columns.Count & vbCrLf & _
           "1つめ：" & A(1) & vbCrLf & _
           "2つめ：" & A(2)
End Sub

ここで注目していただきたいのが

A(1)
A(2)

といった書き方です。セル範囲を一度変数に入れる目的は、その後で変数内のデータを"ひとつずつ"何か処理したいからです。ということは、当然「A(1)」とか「A(2)」みたいな操作が必要になってきます。この「A(1)」って、要するに「Range("A1:C4")(1)」なんですけど、さてみなさん、「Range("A1:C4")(1)」なんて書き方したことありますか？この書き方を解説したページを知っていますか？そしてあなたは、この意味を理解していますか？おそらく"よく分からない"のでは。でも、セル範囲を変数に入れて、その変数を使ってデータを操作するということは、こういう超難しい操作が求められているということです。

さあ、覚悟はよろしいですか？ここから、超絶にややこしい話をします。知恵熱が出てきた方は見なかったことにして、冷えピタでも貼って休んでてくださいね。まずは、次のコードをご覧ください。

Sub Macro5()
    Dim A As Range
    Set A = Range("A1:C4")
    MsgBox "A(1)：" & A(1) & vbCrLf & _
           "A(5)：" & A(5) & vbCrLf & _
           "A(9)：" & A(9)
End Sub

結果はこうなります。まぁ、これは比較的分かりやすいです。

次は、これです。

Sub Macro6()
    Dim A As Range
    Set A = Range("A1:C4")
    MsgBox "A(1,3)：" & A(1, 3) & vbCrLf & _
           "A(2,1)：" & A(2, 1) & vbCrLf & _
           "A(3,2)：" & A(3, 2)
End Sub

実行結果は、下図です。これも、ちょっと勘の鋭い方なら理解できるでしょう。

最後に、ダメ押しです。

Sub Macro7()
    Dim A As Range
    Set A = Range("A1:C4")
    A(13) = "2022/4/5"
    A(5, 2) = "水瀬"
    A.Range("C5") = 50
End Sub

どうですか？上記3つのマクロについて、胸をはって、コードの意味を正確に説明できる方がいらしたら、ぜひご連絡ください。あなたとは美味い酒が飲めそうです。池袋の裏やか、渋谷の米心あたりで、ぜひRangeオブジェクトについて語り明かしましょう。

まず最初のMacro5からいきます。「A(1)」というのは、要するに「Range("A1:C4")(1)」と同じ意味です。Range("A1:C4")はRangeオブジェクトを返します。Rangeオブジェクトの既定プロパティは「Itemプロパティ」です。Itemプロパティは既定のプロパティですから省略できます。つまり「Range("A1:C4")(1)」というのは本当は「Range("A1:C4").Item(1)」という意味です。さて、Itemプロパティには引数が2つ用意されています。

Item(行番号, 列番号)

ちなみに英語表記でしたら「Item(RowIndex, ColumnIndex)」です。このうち、引数「行番号」は必須です。必ず指定します。引数「列番号」は省略可能です。さて、必須である引数「行番号」ですが、次の引数「列番号」を指定したかどうかで意味が異なります。引数「列番号」を省略して、引数「行番号」だけを指定した場合、その「行番号」はセルの位置(ポジション)を表します。位置の数え方は、基点となる"左上セル"を1として、右方向に1・2・3…と進み、指定したRangeオブジェクト(ここではRange("A1:C4"))の右端までいくと、次行の左端に戻って4・5・6…と進みます。このとき、基点となるセルは、指定したRangeオブジェクト(ここではRange("A1:C4"))の"左上セル"つまり、今回でしたらRange("A1")です。ここが基点です。そういう考えで、あらためてMacro5のコードと、その実行結果をご覧ください。

次に、Macro6です。こちらは、Itemプロパティの引数「列番号」を省略しないで指定したケースです。引数「列番号」が指定されると、その前の引数「行番号」は、単なる"左上からの位置"ではなく"何行目"を表します。そして、引数「列番号」が"何列目"です。ここ、ややこしいから整理します。「Range("A1:C4").Item(2)」は、左上から右方向に向かって2つめのセル、つまりセルB1を表します。対して「Range("A1:C4").Item(2, 1)」は、基点となる左上のセル(ここではセルA1)を(1, 1)として「2行目で1列目」のセル、つまりセルA2の意味です。整理したんですけど、よけいに混乱させてしまったかもしれませんね。要するにこれって、みなさんがよく使うCells(行, 列)と同じです。せっかくですから、Cellsについても少しだけ解説しましょうか。まず、Cellsプロパティは、

  • WorksheetオブジェクトのCellsプロパティ
  • RangeオブジェクトのCellsプロパティ
の2種類があり、それぞれ意味が異なります。

WorksheetオブジェクトのCellsプロパティは、そのWorksheetオブジェクトの「全セル」を返します。全セルですから、当然"基点"となる(1, 1)は左上のセルA1です。さて、全セルというのは、要するにRangeオブジェクトです。Rangeオブジェクトの既定プロパティはItemプロパティでしたね。したがって、一般的によく使われる「Cells(3, 2)」のような書き方って、本当は「Cells.Item(3, 2)」という意味です。このItemプロパティを省略して「Cells(3, 2)」みたく表しているだけです。次に、RangeオブジェクトのCellsプロパティです。たとえば「Range("B3:D6").Cells(3, 2) = 100」みたいな使い方です。RangeオブジェクトのCellsプロパティは、指定したRangeオブジェクトの中で左上のセルを(1, 1)とした、相対的な位置を返します。

もちろん「Range("B3:D6").Cells(3, 2)」というのは、省略しないで書くと「Range("B3:D6").Cells.Item(3, 2)」です。さらにややこしい話をすれば、先ほどから「"基点"は左上のセル」と言ってきました。つまり、RangeオブジェクトのCellsプロパティでは、何も複数のセル(セル範囲)に対して指定しなくてもよく、次のような書き方もできるということです。

Sub test1()
    Range("B3").Cells(3, 2) = 100
    Range("B3").Cells(2, 0) = 200
    Range("B3").Cells(-1, 1) = 300
End Sub

上のコードと結果を見て、一発で「ああ、なるほどね～」みたいに理解できる人って、まずいないと思います。一応、解説しましょう。まず、コードから明かなように、Range("B3")が"基点"のセルですから、ここが"1行目で1列目"のセル、つまり(1, 1)となります。

このセルB3が(1, 1)なのですから、(3, 2)はセルC5になります。これは普通に、Cellsをイメージすればいいです。

セルB3が(1, 1)ですから、1行下のセルB4は(2, 1)ですね。問題は次です。では、その左隣にあるセルA4はどうでしょう。(2, -1)ではありません。1よりひとつ小さい数ですから、0です。

相対的な位置を表すOffsetとは違います。一般的に使われる、WorksheetオブジェクトのCellsは、基点の(1, 1)がセルA1です。セルA1から左や上にはセルがありません。なので、0行目とか0列目などはワークシート上では存在しません。しかし今回は、基点の(1, 1)がセルB3です。であれば、(0, 0)も存在します。

ということは、セルA4が(2, 0)になります。こんな書き方は初めてでしょうけど、理屈ではこうなります。もちろん、0が最小ではありません。マイナスの行番号や列番号も存在します。

昔からよく私は「Rangeオブジェクトって、実はものすごく難しいです」って教えてきました。その理由のひとつは、こういうことです。もちろん、この使い方を見て「おお！知らなかった！これって、もしかして便利に使えるかも！」なんて考えてはいけません。これは、Rangeオブジェクトの仕組みを解説しているに過ぎません。実務でこんなことは、やっちゃダメですよ。理由は簡単です。そのマクロを引き継いだ人が、絶対に混乱するからです。そして、ネットを検索しても、詳しく解説している情報は皆無だからです。そんなマクロを後任者に渡すなんて、それはもう"イジメ"か"嫌がらせ"に他なりません。だから、絶対に実務で使っちゃダメですよ。これ、熱湯風呂の"お約束"で言ってるんじゃないですから。決して良い子は真似しないようにね。

さてさて、このように「Range("B3:D6").Cells(行番号, 列番号)」という使い方は、Range("B3:D6")の中だけを操作できるのではなく、ただ単に「左上のセルB3を基点とする相対的な位置」を表しているのに過ぎません。そのへんを利用したのが最後のMacro7です。もう一度、コードと実行結果をお見せしましょう。

Sub Macro7()
    Dim A As Range
    Set A = Range("A1:C4")
    A(13) = "2022/4/5"
    A(5, 2) = "水瀬"
    A.Range("C5") = 50
End Sub

「A(13)」というのは、要するに「Range("A1:C4")(13)」つまり「Range("A1:C4").Item(13)」です。Range("A1:C4")内にセルは12個しかありませんけど、これはRange("A1")を"基点"とした位置を表しますから、13番目のセルはRange("A5")です。同様に「A(5, 2)」は「Range("A1:C4").Cells(5, 2)」ですから、Range("A1")を(1, 1)とした「5行目で2列目」のセル、つまりRange("B5")です。最後の「A.Range("C5")」はオマケです。Rangeオブジェクトを返すRangeプロパティも、Cellsプロパティと同じように

  • WorksheetオブジェクトのRangeプロパティ
  • RangeオブジェクトのRangeプロパティ

があります。本当は、Application.Rangeもありますが、ここでは割愛します。Rangeプロパティも、Cellsプロパティと同じように、Worksheetオブジェクトに対して指定したときは、常に左上のセルA1を基点とした位置を表しますが、RangeオブジェクトのRangeプロパティは、指定したRangeオブジェクト(の左上セル)を基点とした相対的な位置を表します。

Sub test2()
    Range("B3").Range("C1") = 100
    Range("B3").Range("A2") = 200
    Range("B3").Range("B3") = 300
End Sub

ああ、ちなみにですけど、上記の書き方で、調子に乗って「Range("B0")」とか「Range("C-1")」みたいに書くとエラーになりますからね。"A1"や"C2"というのはセルのアドレスです。セルのアドレスとは、各セルに対して、Excelが標準で定義している"名前"つまり文字列です。"C2"というのは「C列の2列目」ではなく、単純な文字列"C2"です。このへんの話は、さらにややこしくなるので、やめておきます。

はい、以上です。お疲れ様。ここまでの話は「理解して活用してください」という主旨ではなく「ややこしくて混乱してもらう」のが目的です。よく、ワークシート上のセル範囲を、オブジェクト変数に入れると言いますが、実際はセル範囲にニックネームをつけているようなものですから、そこで行うことは"セル(Rangeオブジェクト)の操作"です。となれば、たとえば「A(2)」のような書き方も、実際はRangeやCellsを使った記述と同等です。そして、そこには、いつもより難しく複雑な概念やイメージが必要になるという、そういう覚悟だけは持っていてください。

バリアント型変数に入れる

冒頭にも書きましたが、セル範囲を変数に入れる(格納する)には、2つの方法があります。

  1. オブジェクト変数に入れる
  2. バリアント型変数に入れる

オブジェクト変数に入れるときの注意などは、上記のとおりです。さて次に「バリアント型変数に入れる」ときの話をします。たとえば、次のようなコードです。

Sub Macro8()
    Dim A As Variant
    A = Range("A1:C4")
End Sub

セル範囲を変数に格納するとき、Setを使いません。また、格納する変数の型はバリアント型です。動的配列を指定することもできますが、意味がないので誰もやりません。普通のバリアント型です。さて、この書き方に対して、上記で動作確認した「Macro5」「Macro6」「Macro7」と同じようなことをしてみます。

Sub Macro9()
    Dim A As Variant
    A = Range("A1:C4")
    MsgBox "A(1)：" & A(1) & vbCrLf & _
           "A(5)：" & A(5) & vbCrLf & _
           "A(9)：" & A(9)
End Sub

エラーです。では「Macro6」と同様のコードではどうでしょう。

Sub Macro10()
    Dim A As Variant
    A = Range("A1:C4")
    MsgBox "A(1,3)：" & A(1, 3) & vbCrLf & _
           "A(2,1)：" & A(2, 1) & vbCrLf & _
           "A(3,2)：" & A(3, 2)
End Sub

こちらは成功します。同じ結果ですね。最後に、先の「Macro7」と同様のコードを実行してみます。

Sub Macro11()
    Dim A As Variant
    A = Range("A1:C4")
    A(13) = "2022/4/5"
    A(5, 2) = "水瀬"
    A.Range("C5") = 50
End Sub

こちらはエラーです。さあ、いかがでしょう。なぜ「Macro9」と「Macro11」はエラーになって、なぜ「Macro10」は成功するのでしょうか。ちゃんと説明できますか？これ、理解している者からすると「はぁ？そんなのあったり前でしょ」ってレベルなんですけど、VBAの基礎を学習しないで、ネットのコードを訳も分からずコピペしている方々には難しいかもしれませんね。詳しく解説します。

まず、次のコードに注目してください。

【オブジェクト変数に入れるとき】
Set A = Range("A1:C4")

【バリアント型変数に入れるとき】
A = Range("A1:C4")

似ていますよね。違うところといえば、左端にSetがあるかないか…なんて考えていませんか？とんでもない、両者は根本的に異なります。違いは「=」の右側に書かれている「Range("A1:C4")」です。これの意味が違うんです。

オブジェクト変数というのは、その名のとおり「オブジェクトを入れる」変数です。難しいことを言えば、オブジェクト変数とは"参照型変数"で、C言語の"ポインタ"みたいなものです。まぁ、簡単に言えば、オブジェクト(ここではセル)に別名(ニックネーム)をつける仕組みみたいなものです。あるいは「オブジェクトそのものを入れる変数」みたいな感じです。上記のコードで"入れている"オブジェクトは「セル(Rangeオブジェクト)」です。つまり「セルそのものを入れて」います。よろしいですか？ここ難しいですから、落ち着いて考えてください。入れているのは「セルの横幅」とか「セルの値」みたいな「セルの××」ではなく「セルそのもの」です。オブジェクト式「対象.様子」「対象.命令」の「対象」だけを指定しています。

対して、後者では「バリアント型」変数に入れています。バリアント型というのは、"変数の型"のひとつです。ほかにも「文字列型(String)」とか「長整数型(Long)」などがあります。"変数の型"というのは、いわゆる「○○専用」みたいなイメージです。「長整数型(Long)」は整数専用ですから、"田中"とか"東京"のような文字列を入れることはできません。さて、そうした"変数の型"のひとつである「バリアント型」というのは、オールマイティな万能タイプです。バリアント型の変数にはどんな値でも入れられます。今回は、その「バリアント型」変数が、イコール記号の左側(受取側)に書かれています。

しかし、よく見ると左端にSetがありません。上記のように、オブジェクト変数というのは「オブジェクト(そのもの)を入れる」変数です。あるいは、そのオブジェクトにニックネームをつけるようなイメージです。そして、オブジェクト変数にオブジェクトを入れる(ニックネームをつける)ときには、必ずSetを使わなければなりません。これはルールです。したがって、後者

【バリアント型変数に入れるとき】
A = Range("A1:C4")

は、Range("A1:C4")にニックネームをつけているのではなく、何らかの値を変数Aに格納しています。あくまで値です。では、その値は何でしょう。一般的にオブジェクト式は

  • 対象.様子
  • 対象.命令

のように、最低2つの単語で構成されます。ただし、対象がセル(Rangeオブジェクト)だったときは、「対象.様子」の"様子"を省略してもいいという特例があります。そのように、セル(Rangeオブジェクト)の"様子"を省略すると、Valueという様子(プロパティ)が指定されたものとみなされます。つまり、後者のコードは

【バリアント型変数に入れるとき】
A = Range("A1:C4").Value

という意味です。ちなみにValueプロパティは、セルの中に入っている値を表します。

ここまでを理解したうえで、では、Range("A1:C4")に入っている値とは何でしょう。もちろん、ひとつだけではありません。全部で12個の値です。

このような"複数の値"を配列と呼びます。今回の場合は、二次元配列です。VBAにおいて、一般的な配列のインデックス(添え字)は0から始まりますが、今回のように、セル範囲を代入された配列は、例外的にインデックスが1から始まります。

さあ、では、先にご紹介した2つのマクロが、なぜエラーになったのか考えてみましょう。まず、Macro9です。

Sub Macro9()
    Dim A As Variant
    A = Range("A1:C4")
    MsgBox "A(1)：" & A(1) & vbCrLf & _
           "A(5)：" & A(5) & vbCrLf & _
           "A(9)：" & A(9)
End Sub

これは論外です。Range("A1:C4").Valueの結果は二次元配列です。A(1)ではインデックスを1つしか指定していません。二次元配列なのですから、たとえばA(1, 3)などのように2つ指定しなければ配列の部屋(要素)を特定できません。

Sub Macro11()
    Dim A As Variant
    A = Range("A1:C4")
    A(13) = "2022/4/5"
    A(5, 2) = "水瀬"
    A.Range("C5") = 50
End Sub

こちらもエラーです。まず「A(13) = "2022/4/5"」は、先のようにインデックスが足りません。勘違いする方が多いのは、次の「A(5, 2) = "水瀬"」です。こちらはちゃんとインデックスを2つ指定しています。しかし、変数Aに格納された配列に「(5, 2)」は存在しません。

最後の「A.Range("C5") = 50」も論外です。これが、ある意味で本稿のテーマなのですが、オブジェクト変数にセル(Rangeオブジェクト)を入れたときと、バリアント型変数に配列を入れたときは、根本的に意味が異なります。

【オブジェクト変数に入れるとき】
Set A = Range("A1:C4")

【バリアント型変数に入れるとき】
A = Range("A1:C4")

オブジェクト変数は、オブジェクトを入れる変数、あるいは、オブジェクトに別名をつけるようなイメージです。つまり、オブジェクト変数は「セルそのもの(Rangeオブジェクト)」です。Rangeオブジェクトであれば、VBAの仕組みによって「A.Item(1)」だとか「A.Cells(2, 3)」のような使い方が可能です。対してバリアント型に入れる「A = Range("A1:C4")」とは、実際には「A = Range("A1:C4").Value」です。Valueプロパティは「セルに入っている値(数値とか文字列)」です。変数に入っているのは、単なる"100"とか"田中"などですから、それらの値をセル(Rangeオブジェクト)のように操作することはできません。値は値です。それ以上でもそれ以下でもありません。複数のセルをバリアント型に入れると、そのバリアント型変数は二次元配列になります。二次元配列は「A(2, 3)」のような指定しかできませんし、言うまでもなく、配列の大きさを超えた要素は存在しません。

まとめ

今回は、かなり"ややこしい話"で、途中でクラクラしてきた方もいるでしょう。要するにどういうことなのか、以下にポイントをまとめます。

1.セル範囲を変数に入れる方法は2つある

まずは、これです。

【オブジェクト変数に入れるとき】
Set A = Range("A1:C4")

【バリアント型変数に入れるとき】
A = Range("A1:C4")

両者の違いは、Setを使っているかどうかで判断してください。Setを使っているときは「オブジェクト変数」に"セルそのもの"を入れていて、Setを使っていないときは「バリアント型変数」に"セルの値"を入れています。そして、その「バリアント型変数」は二次元配列になります。

2.オブジェクト変数に入れたとき

セル範囲を、Setを使って、オブジェクト変数に入れたときは、そのオブジェクト変数をセル(Rangeオブジェクト)として操作します。セルを特定するには、次のように指定できます。

  • A(2) ← 括弧内に1つの数値を指定する ← 左上からの位置(ポジション)
  • A(3, 1) ← 括弧内に2つの数値を指定する ← Cellsのような行と列

オブジェクト変数はセル(Rangeオブジェクト)ですから、RangeやCellsを使ったときと同等に、セルに対する操作ができます。

Sub Macro()
    Dim A As Range
    Set A = Range("A1:C4")
    
    A(3).Copy Range("C5")           ''セルのコピー
    A(2, 2).Font.Bold = True        ''文字の書式
    A(4, 1).NumberFormat = "m月d日" ''セルの表示形式
End Sub

2.バリアント型変数に入れたとき

セル範囲を、Setを使わないでバリアント型変数に入れたとき、そのバリアント型変数は二次元配列になります。二次元配列ですから、要素を指定するときは「A(2, 3)」のように、2つのインデックス(添え字)を指定します。なお、一般的な配列では、インデックス(添え字)は0から始まりますが、バリアント型変数にセル範囲を入れたときのインデックス(添え字)は、1から始まります。

二次元配列の各要素は、セル(Rangeオブジェクト)ではなく単なる値(数値や文字列)です。セルではありませんので「A(2, 3).××」のような、プロパティやメソッドはありません。各要素に対して行えることは、値を取得することと、値を代入することだけです。もちろん、配列内の要素を変更したところで、代入の元となったセル範囲は影響を受けません。