Excel VBA Formula, FormulaR1C1 プロパティについて

こんばんは

Excel VBAで 「数式」の貼り付け相当のことを行おうとした際、
pastespecial ではなく代入で実施しようとしましたが思った通りになりませんでした。
修正した結果、とりあえずの目的は達成できたのですが、
・下記コード「A」のFormulaR1C1プロパティで代入される結果がなぜそうなるのかよく分からない
　⇒　F5=RC[-5] ならわかる。なぜ=R[1]C[-5]になるのか？
・代入の構文を使用し、一文でPastespecial相当のことをする方法はないか

ご教示願います。

'実験コード-----------------------------------
’A1～A10 に1~10の数字
’B1～G1にはすべて "=A1"
'12～20行目のデータは参考として2~10に実際に入っている数式を
'Formulatext関数を利用して、A1形式、R1C1形式で記載しています。

Sub test()
Range("B2").Formula = Range("B1").Formula
Range("C2:D2").Formula = Range("C1").Formula

Range("B4:B6").Formula = Range("B1").Formula
Range("C4:D6").Formula = Range("C1:D1").Formula

Range("B8:D10").Formula = Range("B1").Formula

'------------------------------------------------------------
Range("E2").FormulaR1C1 = Range("E1").FormulaR1C1
Range("F2:G2").FormulaR1C1 = Range("F1").FormulaR1C1

Range("E4:E6").FormulaR1C1 = Range("E1").FormulaR1C1
Range("F4:G6").FormulaR1C1 = Range("F1:G1").FormulaR1C1 ’---A

Range("E8:G10").FormulaR1C1 = Range("E1").FormulaR1C1
End Sub

No.1 ベストアンサー 回答者： WindFaller 回答日時： 2016/09/17 00:27

こんばんは。

>⇒　F5=RC[-5] ならわかる。なぜ=R[1]C[-5]になるのか？

>Range("F4:G6").FormulaR1C1 = Range("F1:G1").FormulaR1C1 ’---A
たぶん、Range型で、相対参照になっているから、F1 の位置を平行移動して、F4の位置に見立てて、それぞれのセルの位置関係から、組み直されるというぐらいしか説明できません。

私自身、長く、R1C1型は使ってきたけれども、少なくとも、VBAとしては、一番、敬遠する使い方かもしれません。理屈抜きで、そのような数式の代入は絶対にしないということですね。

VBAは、VBAの一般的な方法で処理していきます。

どういう数式を入れるかにもよりますが、

例えば、
[F4]に、A4のセルの値を出し、[G4]に、B4のセルの値を出す
[F5]に、A5のセルの値を出し、[G5]に、B5のセルの値を出す
ならば、
まず、R1C1方式で考えて、
行は同じだから、R （同じ行という意味)
F4は、 =RC[-5], G4 は、同じ =RC[-5]

だから、数式代入の右辺は、必ずテキスト型の数式になります。

Sub FormulaEnter()
　 Range("F4:G6").FormulaLocal = "=RC[-5]"
End Sub

となるわけです。なぜ、FormulaLocal を使うかというと、日本固有の表記の混ざる関数などがある可能性があるからです。例えば、元号の年とか。

この回答へのお礼

回答ありがとうございます。

一意の値が代入される場合は問題なかったです。
（質問文 E8:G10 がそれに当たります）

今回は代入元が2種類になる（"=RC[-5]", "=RC[-6]"）
書き換えてみると
Range("F4:G6").Formula = Array("=RC[-5]", "=RC[-6]")
で、やはり結果は同じで
F5＝R[1]C[-5], G5=R[1]C[-6]
でした。（FormulaR1C1でなくても同じ動作なんですね）

持たれているイメージとしては数式=数式の代入は避けたほうが賢明といったところでしょうか。

"Local" についてありがとうございます。
ほかのページ参考にしてもよくわかっていませんでしたが
"元号とか"でなんとなく腑に落ちました。

お礼日時：2016/09/18 00:28

No.2 回答者： WindFaller 回答日時： 2016/09/18 10:37

どうもお返事ありがとうございます。

>持たれているイメージとしては数式=数式の代入は避けたほうが賢明といったところでしょうか。

そうですね。Range... = Range... の数式は、コントロールが効きません。つまり、Range 型に、相対参照式を貼り付けると、貼り付けた場所を起点としてしまうので、ずれてしまいます。例えば、こんなこともありえます。

Sub testRetardCell()
Range("A1").FormulaR1C1 = "=R[-1]C"
End Sub

R1C1型だからありえるものです。中身は、=A1048576　ということです。

その前に、マクロを良く書く人は、このようなオプションをつけます。ショートカットか、クイックアクセスツールバー(QAT)に、以下のようなコードを、個人用マクロブックに置きます。

こうすれば、ボタンはトグルになってくれます。
R1C1<->A1 切り替えボタン

Public Sub RCA1Alternative()
　With Excel.Application
　　If .ReferenceStyle = xlR1C1 Then
　　　.ReferenceStyle = xlA1
　　Else
　　　.ReferenceStyle = xlR1C1
　　End If
　End With
End Sub

これで、A1 からR1C1方式、R1C1からA1に切り替えながら、数式を作るわけです。

例えば、10万行を、1から順番に番号をつける場合は、

Sub TestRow()
　Range("A1:A100000").Formula = "=ROW(RC[+1])"
　Range("A1:A100000").Value = Range("A1:A100000").Value
End Sub

>今回は代入元が2種類になる（"=RC[-5]", "=RC[-6]"）
>書き換えてみると
>Range("F4:G6").Formula = Array("=RC[-5]", "=RC[-6]")

知っている人はやっている可能性あるのですが、アポストロフィ(')をつける方法で、以下のように使います。

Sub FormulaEnter()
　Range("F4:G6").Value = Array("'=RC[-5]", "'=RC[-6]")
　Range("F4:G6").Value = Range("F4:G6").Value
End Sub

これは、なぜか画面がA1方式でもエラーを出さないようです。
まだまだ、いろんな手法が残されているのかもしれません。

もう一つありますね。
正確なVBAコードは覚えていないけれども、こんな方法です。
VBAでは、Selection を多用するなとはいいますが、唯一の方法です。
数式を串刺しする方法です。選択したシートに全部、同じ数式が入っています。シートをループするのとどちらが便利かは分かりませんが。

Sub ThreeDFormulaEnter()
Worksheets.Select
Worksheets("Sheet1").Activate
Range("A5").Formula = "=A1+5"
Range("A5").Select
Selection = Selection.Formula
Worksheets("Sheet1").Select
End Sub