数式の入力

  1. Home
  2. Excel
  3. VBA
  4. セルの操作

セルに数式を入力するときは、Rangeオブジェクトに数式を文字列として代入しちゃいましょう。先頭が「=」で始まる文字列は、Excelが自動的に数式と認識してくれます。次のコードは、セルA1とセルA2に数値を入力し、セルA3で合計を求めます。

Sub Sample1()
    Range("A1") = 10
    Range("A2") = 20
    Range("A3") = "=A1+A2"
End Sub

もちろん、ワークシート関数も同じ要領で入力できます。

Sub Sample2()
    Range("A1") = 10
    Range("A2") = 20
    Range("A3") = "=SUM(A1:A2)"
End Sub

もちろん、これで数式を入力することができるのですが、Excelの内部で何が行われているかも理解しておきましょう。

セルのデータはValueプロパティに格納されます。ところが、数式が格納されるのはValueプロパティではなくFormulaプロパティです。セルに数式を入力すると計算結果が表示されます。この計算結果がValueプロパティ、実際に入力されている数式がFormulaプロパティです。

「Range("A3") = "=SUM(A1:A2)"」では、Rangeオブジェクトのプロパティが指定されていません。そこで、ExcelはValueプロパティに「"=SUM(A1:A2)"」という文字列を代入しようとします。しかし、文字列の先頭を見ると「=」で始まっています。Excelでは「=」から始まるデータは「数式である」というルールがあります。そこでExcelは、「お、これは数式じゃん、それならFormulaプロパティに入れとかなくちゃ」と気を利かせてくれるのです。

このように、セルに数式を入力するときは、Valueプロパティに対して代入しても、Excelがうまく処理してくれます。ただし、セルに入力されている数式を取得するには、Formulaプロパティを使わなければなりません。下図は、セルA3に「=SUM(A1:A2」という数式が入力されています。ValueプロパティとFormulaプロパティは、次のように異なります。

Sub Sample3()
    MsgBox Range("A3").Value & vbCrLf & _
           Range("A3").Formula
End Sub

セルに入力されている数式を取得したり編集するときは、Formulaプロパティを使います。次の例は、セルA3に入力されている数式「=SUM(A1:A2)」を「=SUM(B1:B2)」に変更します。

Sub Sample4()
    Range("A3") = Replace(Range("A3").Formula, "A1:A2", "B1:B2")
End Sub

数式内の""(ダブルコーテーション)

ワークシート関数のLEFT関数は、指定した文字列の左側を抜き出します。たとえば、「=LEFT("tanaka",2)」は「ta」を返します。では、マクロでこの関数をセルに入力してみましょう。

Sub Sample5()
    Range("A1") = "=LEFT("tanaka",2)"
End Sub

すると、上のコードを入力したとたん「コンパイルエラー」というメッセージが表示されます。コードの段階でエラーなのですから、実行することもできません。

なぜエラーになるのでしょう。今までと同じように、ちゃんと入力したい関数を""(ダブルコーテーション)で囲っています。まったく同じように指定したのに、なんで？・・・と悩む前に、""(ダブルコーテーション)の役目を思い出しましょう。

VBAでは基本的に、VBAのプログラミング言語と一部の記号や数値しか入力できません。「Worksheets」とか「Select」などはプログラミング言語としてVBAにあらかじめ登録されているコマンド名です。したがって、VBE上でこれらの単語を普通に入力すると命令語として認識してくれるのです。

Sub Sample6()
    Worksheets(1).Select
End Sub

「Worksheets」「Select」はコマンド名、「1」は数値、「().」は記号ですね。これらはVBAのコードとして普通に入力できます。では、[合計]という名前のワークシートを開く場合はどうでしょう。

Sub Sample7()
    Worksheets(合計).Select
End Sub

上のように書くとエラーになります。「合計」はVBAのコマンド名でも、数値でも、記号でもないですから、VBAには理解できないのです。そこで「合計はコマンド名ではなく、合計という文字列と認識してください」ということを表すために""(ダブルコーテーション)で囲みます。

Sub Sample8()
    Worksheets("合計").Select
End Sub

これでVBAは「合計という名前のワークシート」と理解できます。マクロのコード中で、文字列を""(ダブルコーテーション)で囲むとは、こういうことです。

では、話を戻して

Sub Sample9()
    Range("A1") = "=LEFT("tanaka",2)"
End Sub

の、どこが悪いのか考えてみましょう。

文字列は""(ダブルコーテーション)で囲みます。逆に言えば、""(ダブルコーテーション)で囲まれているのは文字列と認識されます。すると、数式として入力しようとしたデータは、「=LEFT(」という文字列＋「tanaka」というコマンド名＋「,2)」という文字列という訳の分からないデータになってしまいます。「tanaka」を囲む""(ダブルコーテーション)を、文字列の区切りである""(ダブルコーテーション)ではなく、単なるデータとしての""(ダブルコーテーション)とするには、""(ダブルコーテーション)を2つ重ねて記述します。

Sub Sample10()
    Range("A1") = "=LEFT(""tanaka"",2)"
End Sub

これで正しく「=LEFT("tanaka",2)」という数式を入力できます。

ワークシート関数には""(ダブルコーテーション)がひんぱんに使われます。マクロでセルに入力するときには、十分注意して下さい。VBAにおける""(ダブルコーテーション)の取り扱いに関しては、下記のページで図解していますので、ぜひご覧ください。

ダブルコーテーションの表示