ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化　について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

トップ > VBAの勉強 > VBA　セルに値ではなく計算式を入力する方法と2つの注意点

VBA　セルに値ではなく計算式を入力する方法と2つの注意点

VBAの勉強

このブログの記事一覧にもどる

VBAで値の計算結果ではなく、計算式をセルの中に作成する方法を解説します。

この記事は中級です。
レベルについてはExcel VBAの実力（レベル）を定義してみる　初心者～三段をご参照ください。

目次

セルに計算式を入力する方法と2つの注意点

今回やりたいこと

今回の題材は下図の用なシートです。

今回の題材となるデータ
今回の題材となる単価と数量と金額のデータ

C列にA列とB列のかけ算した結果を表示したいのですが、900という値を書き込みたいのではなく、その後手作業で、A列の単価を修正するので、A列とB列の値をかけ算した結果をC列に表示させるという計算式を入れたい、と言うのが今回のサンプルです。

サンプルコード

早速サンプルコードです。

Sub Formulaプロパティを使う()

    Cells(2, 3).Formula = "=RC[-2]* RC[-1]"

End Sub

これ実行すると、C2セルに式が入力されました。

実行結果
C2セルに値ではなく、式が入力された

これは、式を入れる意味で分かりやすく.Formulaとフォーミュラプロパティを指定することで明示しましたが、エクセルさんが意図をくんでくれるので単に式を入れても同じ結果となります。

Sub Formulaプロパティを使う()

    Cells(2, 3) = "=RC[-2]* RC[-1]" '.Formulaを省略した書き方

End Sub

文法の使い方

今回はR1C1という表記方法で、式を入力しました。値ではなく、式をセルの中に書き込みたいときのVBAの書き方について説明します。

例えばCells(2,3) = 5 とすればC2セルに5が入力されますよね。数式バーを見ると5が入ります。式が入っている時は、今回の例のように=A2*B2と言ったような式が表示されています。つまり、ここに式を意味する文字列を入れてあげれば、あとはエクセルさんが「あ、これは式だな」と認識して、式を意味する文字列が入力される一方、セルの値は計算した結果が表示されるというわけです。

R1C1形式の書き方

R1C1形式の書き方はシンプルです。Rは行を意味するRow,Cは列を意味するColumnの頭文字です。今回の例では、"=RC[-2]* RC[-1]"と言う書き方をしています。A2セルはC2セルから見て行番号は同じ、列番号は2つ小さいのでRの後は省略して、RC[-2]となります。相対位置という考え方ですね。じぶんからみてRとCの方向にどれだけずれているかを表現しているわけです。

たとえばB2セルからみてC4セルはR[2]C[1]となります。行・列の順番に記述するのもポイントです。

普通のセルの番地の指定方法でも可能

上の絵で示したように、数式バーに=A2*B2と表示されていますので、これを入れることもできます。

Sub Formulaプロパティを使う()

    Cells(2, 3).Value = "=A2*B2"

End Sub

これでも、同じ実行結果が得られます。しかし、繰り返し構文を使いたいときなど、A1セルといったセルの番地の指定方法では列方向へのカウントアップができないため、R1C1形式での書式をオススメしたいと思います。

注意点（実はエクセルの中に式を残しておくのは絶対にオススメしない）

注意点があります。それはそもそもエクセルのシートの中に式を残すのはオススメでは無い事です。
そしてエクセルはプログラムで自動化するのがオススメであり、手作業を前提に式を残すのは絶対にオススメしません。

そもそも手作業の為に式を入力する必要があるのであれば、式を書いている時点でそれは自動化のプログラムで解決できるはずです。エクセルの中に式を残しておくのに反対な2つの理由を簡単に解説しておきます。

理由1：エクセルが重くなる

エクセルのファイルのサイズが大きくなります。さらに、動作も遅くなります。実は、エクセルの中に書いてある計算式は変更があった部分だけ再計算されるのではなく、どこか変更があると、全てのブックの全ての計算式が再計算されます。

VLOOKUPなどの重たい関数で一カ所数字を手で修正しただけで、やたらと時間が掛かり、ステータスバーに「実行中・・・○○%」と言ったような表示が出たことがありませんか？

あれは、関係ないところまで全て計算しているためなのです。

理由2：エラーの原因となる

計算式が入っていると言うことは、どこかのセルの値を参照しているはずです。手作業で計算するとその参照先の列ごと削除したりすると参照先が見当たらなくなって式が壊れます。するとそこには、エラーを表す特殊な値が入ります。予期せぬエラーはこうして人間の手によって原因が作り出されていきます。他のブックを参照しているVLOOKUPなどは最悪です。そんな事をする人の後工程の仕事を請け負ったり、自動化したりするのは極力やめておきましょう。

それでもエクセルを手作業で取り扱い人のために

エクセルを手作業で表計算ソフトとして使っている人は、シートの中に式を残しておくのが便利だと感じていると思います。しかし、シートの中に式を残して保存するのはは上述の2つの理由からNGです。

そこで、今回解説したVBAで式を入力するプログラムが役に立ちます。エクセルシートの中には計算式を残さずに、式を使う時だけ、式をセルに入力するプログラム（マクロ）を実行する。

作業が終わったら、シート全体をコピー、値貼り付けして式を含まないエクセルブックとして保存すれば良いのです。

エクセルシートの中に式は残さない。使う時に、必要な場所に式を入力するプログラムを使う。これを頭に入れて式をセルに書き込むプログラムを作るようにしましょう。

セルに値ではなく計算式を入力する方法と2つの注意点まとめ

簡単にまとめます。

  • セルの中に式を残すには、「＝」付きの式ごとダブルクォーテーションで囲む
  • エクセルの中には基本的に式は残さない
  • 手作業のために式をセルの中に入れたい場合は、式をセルの中に入れるためのプログラムを組む

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドを書きました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp