変数を使って、計算式を動的に編集

「固定編集の文字列」と「変数」を組み合わせて計算式をマクロで動的に作成することができます。
この「固定編集の文字列」と「変数」は「&」演算子で接続します。 ここの章を最初から読み進めてきた方なら、「今さらこんな簡単なこと」と思われるでしょうが、ダブルクォーテーションで囲うと固定文字列になるということを取り違えて、囲った中に「変数」名を書いて「うまく行かない」と思ってしまう方は結構多いようです。
もし、そのような間違いをしていても、自分でデバッグをできるようになっていれば、自分の記述に誤りがあることは見つけられるのですが．．．



FormulaまたはFormulaR1C1プロパティでマクロからセルに計算式をセットすることができます。
例えば、A11セルにA1セルからA10セルの合計を算出する計算式をセットするものとします。

    Range("A11").Formula = "=SUM(A1:A10)"
このあたりは、マクロの記録で取ったコードを参考にすれば、初心者でもすぐに書くことができる範囲だと思います。
でも、これをマクロにしたところでマクロとして用いるにはあまり意味がありません。マクロにするなら例えば、データの行数が変わっても最終行を判断して、その下に合計式を作成してくれるなどの機能がないと、毎回行数に応じてコードを書き換えなければならなくなってしまいますね。

そこで、考えて作ったコードの例はこのようなものでした。
このコードは間違っています。

    Dim lngRow As Long                                              ' 行INDEX
    lngRow = 11
    Range("A & lngRow").Formula = "=SUM(A1:A & lngRow - 1 & )"
このサンプルを動かすと、当然ですが、
「Rangeメソッドは失敗しました。Globalオブジェクト」
とエラーメッセージが表示されます。これは左辺側のエラーですがこれを解決させたとしても、
「アプリケーション定義またはオブジェクト定義のエラーです。」
とエラーメッセージが表示されます。
でも、このコードを作った方は、これが間違っていることを自分でデバッグできません。1行に記述してしまっているためにFormulaプロパティにセットしている計算式の文字列がどのように構成されているか確認できないのです。
ここではセットされる式が「"=SUM(A1:A10)"」となっているものとばかり考えているわけですから、なぜエラーになるのか解らないわけです。
では、このような時はどのように確認すれば良いのでしょうか。

このような場合は、編集している文字列を一度、変数に格納してみます。
上記のエラーメッセージでデバッグをクリックすると、 

    Range("A & lngRow").Formula = "=SUM(A1:A & lngRow - 1 & )"
このようにエラー発生行が黄色に反転表示されます。
でもこの状態では、「正しく書いたはず」と思っている方には間違っている状態が確認できません。
こういう場合は、いきなり1行で仕上げるのではなく、編集文字列を一旦変数に格納してみて下さい。 

    Dim lngRow As Long                                              ' 行INDEX
    Dim strRange As String                                          ' セル範囲表意
    Dim strFormula As String                                        ' 計算式
    lngRow = 11
    strRange = "A & lngRow"
    strFormula = "=SUM(A1:A & lngRow - 1 & )"
    Range(strRange).Formula = strFormula
このように変更しても、やはり計算式をセットするFormulaプロパティの行でエラーになります。 でも、エラー行が黄色に反転したことろで、「strRange」「strFormula」の変数名のところにマウスポインタを載せてみると、格納された値がセルのコメントのように表示されます。 また、マクロを起動させる時に、「実行」ではなく「ステップイン」をクリックして下さい。マクロはソースコードの1行目が黄色で反転した状態で止まりますから、 そこからF8キーを押していくことで1行ずつ進められます。変数に値が格納された以降の行まで進んだら、「イミディエイトウィンドウ」を開いて、 

    ?strFormula
と入力してみると、格納された値が直下行に表示されます。これはエラーで黄色く反転している状態でも同様に行なうことができます。 どうでしょう。目論んだ通りの計算式が表示されるでしょうか。 

    ?strFormula
    =SUM(A1:A & lngRow - 1 & )
こんなふうに表示されませんか。
ダブルクォーテーションに囲われた間は固定編集された文字列として扱われます。
もう、結論(正解)はお解りですね。 

    Dim lngRow As Long                                              ' 行INDEX
    Dim strRange As String                                          ' セル範囲表意
    Dim strFormula As String                                        ' 計算式
    lngRow = 11
    strRange = "A" & lngRow
    strFormula = "=SUM(A1:A" & lngRow - 1 & ")"
    Range(strRange).Formula = strFormula
「&」演算子と変数はダブルクォーテーションで囲われた外側に置かなければならないのです。
でも、コード作成でミスをすることはありますから、自分でデバッグしてそのミスを見つけることがここで身につけていただきたいポイントです。