ＥＸＣＥＬ ＶＢＡの書き方

質問

ＥＸＣＥＬ ＶＢＡの初心者です。
ＥＸＣＥＬ２００３を使っています。
数式の中のパラメータの書き方が分からず困っています。
具体的には、単純な計算をR1C1形式の数式を使い、FOR文でループを回そうとしました。
この際、例えば以下のようなＶＢＡを、試行錯誤の結果やっと動くようにはなりましたが、とくに「"」（ダブルクォーテーション）の使い方が理解できていません。
ＮＧだった理由、ＯＫとなった理由を、すみませんがお分かりの方教えていただけないでしょうか。
（ヘルプを読んでも残念ながら理解できる程のレベルを持っていません。）

やりたいこと：例えば単純な引き算を複数のセルに、ループをまわして設定したい。
---------------
Sub 質問()
Dim i As Integer
' "A1"との差を求めるVBA→ "B2"から"E2"に数式入力
For i = 2 To 5
Cells(2, i).Select
' (1)問題の行→これはＯＫの例
ActiveCell.FormulaR1C1 = "=r[-1]c-r[-1]c[" & 1 - i & "]"
Next i
End Sub
---------------
' (2)問題の行→実行時エラー'1004'
ActiveCell.FormulaR1C1 = "=r[-1]c-r[-1]c[1 - i]"
---------------
' (3)問題の行→コンパイルエラー（構文エラー）
ActiveCell.FormulaR1C1 = "=r[-1]c-r[-1]c["1 - i"]"
---------------
' (4)問題の行→コンパイルエラー（構文エラー）
ActiveCell.FormulaR1C1 = "=r[-1]c-r[-1]c["&1 - i&"]"
---------------

回答No.3

okormazd · ベストアンサー

(1)
入力されたセルの数式を見ればわかるが、"　　"で囲まれた部分はそのまま文字列として入ります。「&」は文字列接続の演算子です。" "で囲まれていない部分は、計算されて接続されます。
したがって、

i=2で、　　　　　=r[-1]c-r[-1]c[　 と、(1-i=1-2=)　-1　と、　]　がつながって、
=r[-1]c-r[-1]c[-1]になります。これはワークシートの数式として有効なのでOKです。

i=3なら、=r[-1]c-r[-1]c[-2]　になります。

(2)
"　　　"で囲まれた文字列がそのまま入るのですが、=r[-1]c-r[-1]c[1 - i]　という式では、i　が文字列の中なので計算されません。ワークシートの数式では計算できない数式ですが、VBAで定義されていないエラーなので、実行時エラーになると思います。（3)
。" "で囲まれた部分は文字列としても、それで囲まれていない　1 - i　は何のことかわかりません。仮に(1)のように計算されたとしても、それぞれがバラバラにあるだけで、数式としての構文にはなりません。コンパイラはどうしていいかわからないので
す。

(4)
文字列接続の演算子　&　は、ワークシートでは半角空白はなくてもエラーにならないが、VBAでは、半角空白を前後に入れないとエラーになります。

お礼

お礼が遅くなり申し訳ありませんでした。
""で囲まれていない部分のご説明も有り、また全体的に詳細なご説明をしていただき、良く理解できました。
ご回答をして頂いた皆さんに感謝申し上げます。これですっきりしました。

回答No.2

watabe007

「"」（ダブルクォーテーション）の使い方はANo.1さんがされていますので
R1C1の絶対参照について
この場合A1セルを絶対参照することで
Sub 質問()
　　Dim i As Integer

　　For i = 2 To 5
　　　　Cells(2, i).FormulaR1C1 = "=R[-1]C-R1C1"　　'R1C1：R(行)が1 C(列)が1 つまりA1を指しています。
　　Next i
End Sub

A1形式で表すと
Sub 質問()
　　Range("B2:E2").Formula = "=B1-$A$1"
End Sub

と、書くことができます。参考までに

お礼

お礼が遅くなり申し訳ありませんでした。
１．絶対座標を使って計算さるせるというのがまさに私がやりたかった方法で、方法がわからず苦肉の策としてｉを使ってセルを相対参照していました。
２．A1形式ですが、
Sub 質問()
　Range("B2:E2").Formula = "=B1-$A$1"
End Sub
は、B1がセルに応じてC1、D1・・・となっていくのですね。
Range指定の最初のセルの数式を書くということでしょうか。
自分の持っている解説書に見当たらず、勉強になりました。
本当にありがとうございました。

回答No.1

readme_eml

""で囲むのはのは文字列です。

(2)は変数iまでが""で囲まれていて i が 変数としてでなくて iという文字としてセルに入るからです。
(3)は"=r[-1]c-r[-1]c["1 - i"]" は ("=r[-1]c-r[-1]c[") の文字列と次の数値1とを&でつないでいないからです。その後のi と("]")も同じです。

(4)は内容的には(1)と同じですが、 & と 1、 i と & の間にスペースが入っていないのが原因です。VBAはスペースを勝手に入れてくれるときとそうでないときがあります。

お礼

早々にご回答をいただきながらお礼が遅くなり申し訳ありませんでした。
１．数式全体を"で囲むのは、FormulaR1C1（など）を使ってセルに数式を入力する場合の新たな取り決めかと、先入観で思っていましたが、あくまで「文字列を囲む」ということから来ていることを認識しました。そうすると式の見方が変わり、理解できるようになりました。
２．項番(4)は確かにスペースを入れないでそのまま実行してしまう時と、自動的にスペースを入れてうまくいくときとがありました。
有り難うございました。