Rangeの変態的な使い方

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

これは皆さんも同じだと思いますが、さて寝ようかな～ってベッドに入り、電気を消して目をつぶり、とりあえずボォ～ッとExcelのこととかVBAのことを考えますよね。(こんなデータにあの機能使ったらどうなるのかな…)(あのプロパティって配列に入れずに直でイケんじゃね…)で、気になって試してみたくなって、やおらベッドから出て、PCの電源入れて、Excelを起動して、実際に試してみて「ああ、やっぱりな～よし！寝よう」ってベッドに戻ります。(あ、でも、確かLong型とDate型って微妙に動作違ったよな…なんだっけな…どんなケースだったかな…)やおらベッドから出て、PCの電源入れて、Excelを起動して、実際に試してみて「ああ、そうだ、これだ。よし！寝よう」って。(うん？待てよ？つーことは、先に処理しとけば…)やおらベッドから出て(略)、そして、気がつけば朝になっていると。きっと皆さんも同じですよね。

そのときは、Rangeについて考えていました。
Range("A1")の引数"A1"って、要するに何なのかな～
ヘルプでは「A1参照形式のアドレスを指定する」って書いてあったけど、セルに名前を付けておけばRange("Data")とかもイケるしな～
まぁ、セルのアドレスって最初から設定されている名前だし、同じことか～
でも、テーブルの構造化参照だったらRange("Data[[#All],[地域]]")とかできるしな～これって、名前の域を超えてるよな～
「アドレス」っていうより「セル参照」を指定するってことなんじゃね～
セル参照だったら、A1とかだけじゃなくて関数でもイケるよな～たとえばOFFSET関数とか…
うむ！やってみよう！やおらベッドから出て(略)

やってみました。

Sub Macro1()
    Range("OFFSET(A1,1,0)") = 100
End Sub

一発で成功したときは、さすがに驚きました。Rangeの引数って、要するに「A1参照形式の"セル参照"」を指定すればいいんです。そして、セル参照を返すワークシート関数も、引数に指定できると。一瞬、これは大発見だぁと喜んだのですが、セル参照を返すワークシート関数って、本稿執筆時点(2023年10月)で7個しかありません。OFFSET関数は、そのひとつです。ワークシート関数は大別すると「値型関数」と「参照型関数」に分類できるのですが、「参照型関数」も厳密には「セル参照を返す関数」と「セルの値を返す関数」の2種類があります。参照型関数の中で、最もよく使われるVLOOKUP関数は「セルの値を返す関数」なので、今回のケースには使えません。

だがしかし、落胆するのは早計です。INDEX関数はセル参照を返します。

Sub Macro3()
    Range("INDEX(B1:B5,3)") = "佐倉"
End Sub

ほらね。さらに、INDEX関数がイケるんなら、INDEX+MATCHだって動くはずです。

Sub Macro4()
    Range("INDEX(A1:A5,MATCH(""瀬戸"",B1:B5,0))") = 999
End Sub

これと同じことをVBAでやるなら、次のようになります。

Sub Macro5()
    Dim FoundCell As Range
    Set FoundCell = Range("B1:B5").Find(What:="瀬戸")
    If Not FoundCell Is Nothing Then
        FoundCell.Offset(0, -1) = 999
    End If
End Sub

上記のコードには「オブジェクト変数」「Findメソッド」「Not演算子」「Offsetプロパティ」と、ビギナーには難関な要素が数多く含まれています。可読性のことはともかく、これが1行で書けるというのは驚きです。

とはいえ「セル参照を返す関数」は、いかんせん7個しかありません。OFFSET関数を使うくらいならVBAのOffsetプロパティの方が簡単だし、INDIRECT関数を使うのは意味がありません。そう考えると、この「Rangeの引数にワークシート関数を指定する変態的な使い方」って、あまり使い道がないような気がしてきました。

なんか、他に実用的な使い方ってないかな？って考えましたが、それほど良いアイデアは出ませんでした。次のケースくらいでしょうか。下図のような表で、表の右端列(ここではE2:E6)にSUM関数を代入します。

Sub Macro6()
    Range("DROP(TAKE(" & Range("A1").CurrentRegion.Address & ",,-1),1)") = "=SUM(B2:D2)"
End Sub

まぁ、ここまでくると、VBAのスキルとワークシート関数のスキルが両方必要になってきますので、マクロを作る人でも簡単には書けないでしょうし、もし、後任者としてビギナーがこのマクロを引き継いだら、完璧にチンプンカンプンでしょう。

まとめ

われながら、面白い書き方を見つけたものだと感じています。ただし、決してこの書き方を推奨するつもりはありません。あくまで変態的な使い方の例としてご紹介しました。いわば"宴会芸"です。くれぐれも、第三者が閲覧・編集するかもしれない実務レベルのマクロでは、使用しないようにしてください。

以下は雑感です。何なら読み飛ばしてください。
インターネットの、特にQ&A系の掲示板などは、承認欲求を満たす場としての側面が強いと感じています。ほとんどの場合は、金銭的な報酬などが存在しないにもかかわらず、自分の時間や労力を費やして回答を書き込む動機は何でしょう。もちろん「自ら学習するため」という理由もあります。他人の質問や疑問に答えるのって、何よりも自分のスキルを向上させる近道です。私も若い頃、パソコン通信の掲示板などで、そうした修行をしてきました。しかし、それだけでなく「自分のExcelスキルを高く評価してもらいたい」などの承認欲求もありました。それは当然のことですし、決して悪いことではありません。では、自分が書いた回答の価値を高めるには、どうしたらいいでしょう。シャノンの情報理論によれば、情報の価値は「起こりづらさ」によって決まります。有名な例としてあげられるのは「犬が人間を噛んだ」という情報よりも「人間が犬を噛んだ」という情報の方が、発生頻度としては起こりづらいでしょう。したがって、情報の価値は「犬が～」＜「人間が～」であると。もちろん、ここでいう"価値"とは"情報量"のことであり、金銭的な云々という意味ではありません。さて、掲示板の情報(回答)における「起こりづらさ」とは、閲覧者が同等の内容を「知る機会が少ない」かどうかに相当するでしょう。たとえば「知っている」「見慣れている」と感じる内容よりも「知らなかった」「見たことがない」ような内容の方が、一般的に"情報(回答)の価値が高い"と評価される傾向にあると思います。たとえば「Range("A1").Offset(1, 0)」は普通です。多くの人が知っています。でも「Range("OFFSET(A1,1,0)")」を知っている人は少ないでしょう。であれば、後者の書き方をした方が、情報(回答)の価値は高くなるはずです。情報の価値としては、そのとおりだと思います。しかし、シャノンの情報理論は、あくまで"情報の価値"を定量化するものであり、"情報が人にもたらす価値"という主観的な意味は含まれていません。その掲示板が、純粋な"学術系掲示板"ならいいです。価値の高い情報(回答)を寄稿することで、閲覧している研究者たちの知識が深まり高まるのですから。しかし、一般的な掲示板は違います。一般的な掲示板で質問したり閲覧する人は、その情報(回答)を「実務で使う」目的で利用します。どれほど価値の高い情報であっても、その情報を使うことによって「実務面での価値」が下がっては本末転倒です。目的は実務なのですから。回答者と閲覧者は、求めている価値が異なります。パソコン通信時代から現在に至るまで、掲示板などに抱いていた違和感がこれです。とはいえ、回答者は間違っていません。回答者は、自分の回答の価値を高めるのも目的のひとつです。問題は閲覧者です。閲覧者は、さまざまな情報に触れた際、その情報を使うことによって生じる「実務面での価値」を、十分に吟味してください。その意識が希薄すぎます。「ネットに書いてある価値の高い情報≠実務面で価値の高い情報」なんてことは、現在の情報社会において、言わずもがなでしょう。それなのに「ネットに書いてあったから」という安易な理由だけで採用してしまうから、その結果「作成者がいなくなると誰も触れない属人化ブック」が生まれてしまうんです。もちろん「実務面での価値」を判定するのは、一筋縄ではいきません。多くの状況や要因が関係してくるので難しいです。難しいですけど、その気持ちを忘れないでいただきたいです。