.Value, .Formula, .FormulaR1C1 – Excelマクロ・VBA

  • このエントリーをはてなブックマークに追加
  • follow us in feedly

エクセルマクロ・VBA達人養成塾 小川です。

昨日の続きです。

「式」と「戻り値」、「値」 – Excelマクロ・VBA

.Valueで値を設定したあと、参照元セルの値を変更すると? – Excelマクロ・VBA

今日は、.Valueの親戚をご紹介。以下の2つです。

.Formula
.FormulaR1C1

ちなみに、今日のブログ記事のネタは、あなたがマクロを書く際にはほとんど役に立ちません。
なぜなら、先にオチを書いておくと、 .Formula とか .FormulaR1C1 とかを使ってセルの値を設定することなんて、まあまずないから。
自動記録でマクロを作っているときに、ちょっとうれしくなるくらいです。

でも、エクセルの挙動についての知識を確固たるものにするとか、ITリテラシーを高めるとか、そういうことを望む人には、なんか深く入るかな、と思って書いてみました。

それでは、行ってみましょう。

.FormulaR1C1 は、自動記録をするときなど、よく登場するものです。

以下、自動記録をしながら、セルB2に「3」という値を書き込んでみました。

Sub Macro3()
Range(“B2”).Select
ActiveCell.FormulaR1C1 = “3”
Range(“B3”).Select
End Sub

「FormulaR1C1」とは何ぞや?という話をしたいのですが。

そのために、もっと簡単な、 .Formula のお話をしておきます。

.Formula は、そのセルに入っている「式」を取ってきます。

例えば、セルA1に、「=5+3」という式が入っていたとしましょう。

昨日までに解説しているとおり、この式の「戻り値」は「8」です。従って、このセルの「値」は「8」。

で、このとき、以下のマクロを実行してみます。

Sub uni()
Debug.Print Range(“A1”).Value
Debug.Print Range(“A1”).Formula
End Sub

結果は、以下のようになったはずです。

8
=5+3

ということ。

この性質は、あるセルから、そのセルの「値」を取ってきたいのか、「式」を取ってきたいのか、というような使い分けをしたいとき有効です。

このことについて学ぶため、以下の2つのマクロを実行して、その結果を比べてみてください。

Sub uni2()
Range(“C1”).Value = Range(“A1”).Value
Range(“C2”).Value = Range(“A1”).Formula
End Sub

上記のマクロを実行すると、セル「C1」には、「8」という式が入り、その結果、「8」という値が表示されます。
セル「C1」には、「=5+3」という式が入り、その結果、「8」という値が表示されます。

.Value を使ってセルに何かを書き込むときは、「.Value」を使っても、「.Formula」を使っても、違いは生じません。
が、セルに入っているものを取ってくるときには、違いが生じるんですね。

以下、左辺も.Formulaで書いてみました。ただし、起こる出来事は、上のサンプルと同じです。

Sub uni3()
Range(“D1”).Formula = Range(“A1”).Value
Range(“D2”).Formula = Range(“A1”).Formula
End Sub

次に、 .Formula と .FormulaR1C1 の違いについて。

いろいろマクロを実行して、確認してみましょう。

まずは、簡単にこんなところから↓

Sub uniuni1()
Debug.Print Range(“A1”).Formula
Debug.Print Range(“A1”).FormulaR1C1
End Sub

どちらも、 =5+3 という式を返します。

では、ちょっとひねって。

セル「A5」に、「=A1+5」という式を入れます。
その状態で、以下のマクロを実行してみましょう。

Sub uniuni2()
Debug.Print Range(“A5”).Formula
Debug.Print Range(“A5”).FormulaR1C1
End Sub

すると、以下のように出力されます。

=A1+5
=R[-4]C+5

「=A1+5」については解説不要でしょう。
「=R[-4]C+5」について言うと。

これは、「そのセルの4つ上、0つ右のセルの値 + 5」という意味の式になります。

要は、.Formulaは、式を「絶対参照」で取ってきて、
.FormulaR1C1は、式を「相対参照」で取ってくる、というところが違いになります。

なんのこっちゃ、と思うあなた。

以下に示すちょっと準備をしてから、マクロを実行してみてください。

その準備とは…。以下の2つです。

セル「A7」に「1」と記入する。
セル「B9」に「=A1+5」と記入する。

そして、以下のマクロを実行してみてください。

Sub uniuni3()
Dim c As Long
For c = 7 To 16
Range(“D” & c).Formula = Range(“B9”).Formula
Range(“E” & c).FormulaR1C1 = Range(“B9”).FormulaR1C1
Next
End Sub

すると、以下のようになったはずです。


|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リテラシーを高めるとか、そういうことを望む人には、なんか深く入るかな、と思って書いてみました。

次回も、ちょっとマニアックなプロパティを扱います。

今なら先着30名限定で無料!定価4,800円の、初心者のためのエクセルマクロ動画講座。
  1. Excel 97~Excel 2016まですべて対応。動画本数20本、総再生時間2時間44分53秒
  2. PC, Mac, iPhone, iPad, Androidのお好みの環境で、いつでも好きなときに学べます。
2004年から10年間述べ3,000名以上に実施した研修の経験と実績を集約した講座です。
いますぐ無料で試してください。

「無料」と言っておきながら「あとから請求がくる」といったことは一切ありませんのでご安心ください。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です