エクセルマクロ・VBA達人養成塾 小川です。
昨日の続きです。
.Valueで値を設定したあと、参照元セルの値を変更すると？ – Excelマクロ・VBA
今日は、.Valueの親戚をご紹介。以下の2つです。
.Formula
.FormulaR1C1
ちなみに、今日のブログ記事のネタは、あなたがマクロを書く際にはほとんど役に立ちません。
なぜなら、先にオチを書いておくと、 .Formula とか .FormulaR1C1 とかを使ってセルの値を設定することなんて、まあまずないから。
自動記録でマクロを作っているときに、ちょっとうれしくなるくらいです。
でも、エクセルの挙動についての知識を確固たるものにするとか、ITリテラシーを高めるとか、そういうことを望む人には、なんか深く入るかな、と思って書いてみました。
それでは、行ってみましょう。
.FormulaR1C1 は、自動記録をするときなど、よく登場するものです。
以下、自動記録をしながら、セルB2に「3」という値を書き込んでみました。
「FormulaR1C1」とは何ぞや？という話をしたいのですが。
そのために、もっと簡単な、 .Formula のお話をしておきます。
.Formula は、そのセルに入っている「式」を取ってきます。
例えば、セルA1に、「=5+3」という式が入っていたとしましょう。
昨日までに解説しているとおり、この式の「戻り値」は「8」です。従って、このセルの「値」は「8」。
で、このとき、以下のマクロを実行してみます。
結果は、以下のようになったはずです。
8
=5+3
ということ。
この性質は、あるセルから、そのセルの「値」を取ってきたいのか、「式」を取ってきたいのか、というような使い分けをしたいとき有効です。
このことについて学ぶため、以下の2つのマクロを実行して、その結果を比べてみてください。
上記のマクロを実行すると、セル「C1」には、「8」という式が入り、その結果、「8」という値が表示されます。
セル「C1」には、「=5+3」という式が入り、その結果、「8」という値が表示されます。
.Value を使ってセルに何かを書き込むときは、「.Value」を使っても、「.Formula」を使っても、違いは生じません。
が、セルに入っているものを取ってくるときには、違いが生じるんですね。
以下、左辺も.Formulaで書いてみました。ただし、起こる出来事は、上のサンプルと同じです。
次に、 .Formula と .FormulaR1C1 の違いについて。
いろいろマクロを実行して、確認してみましょう。
まずは、簡単にこんなところから↓
どちらも、 =5+3 という式を返します。
では、ちょっとひねって。
セル「A5」に、「=A1+5」という式を入れます。
その状態で、以下のマクロを実行してみましょう。
すると、以下のように出力されます。
=A1+5
=R[-4]C+5
「=A1+5」については解説不要でしょう。
「=R[-4]C+5」について言うと。
これは、「そのセルの4つ上、0つ右のセルの値 + 5」という意味の式になります。
要は、.Formulaは、式を「絶対参照」で取ってきて、
.FormulaR1C1は、式を「相対参照」で取ってくる、というところが違いになります。
なんのこっちゃ、と思うあなた。
以下に示すちょっと準備をしてから、マクロを実行してみてください。
その準備とは…。以下の2つです。
セル「A7」に「1」と記入する。
セル「B9」に「=A1+5」と記入する。
そして、以下のマクロを実行してみてください。
すると、以下のようになったはずです。
|A列 |B列 |C列 |D列 |E列 |
--------------------------------------
7 行目 |1 | | | | |
--------------------------------------
8 行目 | | | | | |
--------------------------------------
9 行目 | |6 | | | |
--------------------------------------
10行目 | | | | | |
--------------------------------------
11行目 | | | |6 |5 |
--------------------------------------
12行目 | | | |6 |5 |
--------------------------------------
13行目 | | | |6 |11 |
--------------------------------------
14行目 | | | |6 |11 |
--------------------------------------
15行目 | | | |6 |11 |
--------------------------------------
16行目 | | | |6 |11 |
--------------------------------------
17行目 | | | |6 |11 |
--------------------------------------
18行目 | | | |6 |11 |
--------------------------------------
19行目 | | | |6 |11 |
--------------------------------------
20行目 | | | |6 |11 |
--------------------------------------
「値」を見ただけでは何が何だか分からないので、
B列、D列、E列のそれぞれに、どういう「式」が入っているか、確認してみましょう。
こうなっています↓。
|A列 |B列 |C列 |D列 |E列 |
----------------------------------------
7 行目 |1 | | | | |
----------------------------------------
8 行目 | | | | | |
----------------------------------------
9 行目 | |=A7+5| | | |
----------------------------------------
10行目 | | | | | |
----------------------------------------
11行目 | | | |=A7+5|=D9+5 |
- ---------------------------------------
12行目 | | | |=A7+5|=D10+5|
----------------------------------------
13行目 | | | |=A7+5|=D11+5|
----------------------------------------
14行目 | | | |=A7+5|=D12+5|
----------------------------------------
15行目 | | | |=A7+5|=D13+5|
----------------------------------------
16行目 | | | |=A7+5|=D14+5|
----------------------------------------
17行目 | | | |=A7+5|=D15+5|
----------------------------------------
18行目 | | | |=A7+5|=D16+5|
----------------------------------------
19行目 | | | |=A7+5|=D17+5|
----------------------------------------
20行目 | | | |=A7+5|=D18+5|
----------------------------------------
ここから分かるとおり、 .Formula は、式を何の工夫もなく持ってくるのに対し、
.FormulaR1C1 は、式を「基準となるセルから、左右にいくつ、上下にいくつ離れたところにあるセルの値を利用する」という感じで式を持ってきます。
いわゆる、「絶対参照・相対参照」というヤツですな ヾ(´ー｀)ﾉ
..ということで。
.FormulaR1C1 の挙動について説明せんがために、 .Formula から説明してきましたが。
ようやく、お話は一段落。
さて、で、この知識。
どう役に立つのかというと…。
冒頭に書いたとおり、実は、ほとんど役に立ちません。
.Formula とか .FormulaR1C1 とかを使ってセルの値を設定することなんて、まあまずないから。
そんなわけで、達人養成塾の講座でも、あとで興味を持ったとき用にテキストには詳しく書いていますが、本編ではまず扱いません。
でも、エクセルの挙動についての知識を確固たるものにするとか、ITリテラシーを高めるとか、そういうことを望む人には、なんか深く入るかな、と思って書いてみました。
次回も、ちょっとマニアックなプロパティを扱います。
小川先生、こんにちは。
今の職場で、自動記録でマクロを作ってほしいと言われてます･･･汗
いろいろな工程のうち、途中までは自動マクロができているうえ、
細かいキー操作まで指示が書いてあるので（自動記録用ですね；）
いう通りにする方がいいかなと思い、めったにしなくなった
自動記録マクロを作っています。
そうすると、でてきたんですよ、.FormulaR1C1が。
そしてネット検索したら、ありがたいことに、
先生のブログが上位に出てきました。
ちなみに、私がやろうとしているのは、
別ファイル参照のvookup式を、シートコピーしたため
ファイルの位置の記述が不要になった部分を置換で削除する部分です。
.FormulaR1C1は、
自動記録でしかお目にかからないワードだったんですね。
こうして、人の作ったマクロや、先方の要望どおりに作成しようとすると
遭遇することがあるので、やはりリテラシーとして、学習しておくべきであると感じました。
ありがとうございました！ gooska
コメントありがとうございます。
また、お返事遅れました。
自動記録よりも、基本的にスクラッチ（自分でゴリゴリコードを書くこと）のほうが、高い生産性のマクロを簡単につくれますよ。
手段ではなく、目的にフォーカスしましょう。
相手が最終的に欲しいものは？相手は、そのやり方（手段）しか知らないから、それしか提示できなかったのかもしれません。