数式を配置するプログラム（演習）
上記のような集計表に数式マクロを配置していくVBAプログラムを演習形式で解説していきます。一見、簡単そうな処理に見えますが、休日をまたいで差分を計算しなければいけない点が少々厄介です。
プログラミング内容
ある施設で電気・水道・ガスの使用量を集計する表があったとします。
施設が休みの時は、測定しないため、セルが塗りつぶされています。
いつも月曜日が休み。というわけでもなくイレギュラーで連休などが入ります。このため数式マクロは規則的とはならず、仕方なく手作業で直していたというのが現状でした。
VBAでは、数式マクロをセットする事もできますので、今回は休み（塗りつぶし）をセットしたらプログラムを実行して解決したいと思います。
数式マクロの式を直接指定
まず、マクロですが、前日の指針値と当日の指針値との差を計算しています。
電気に関しては係数の2400を乗じる事になっているため *2400 が付いていますが、上水・ガスに関してはただの引き算になります。
前日が休みでない時は、以下のような式になります。
=IF(D6=””,””,(D6-D5)*2400)
当日が空白の時は、空白を返してマイナスの値を出さないようにしています。
もし、E6のセルのみであれば、文字列として上記の式を渡してもOKですが、”（ダブルクォーテーション）に関しては、エスケープシーケンスしなければいけないため、””と重ねる必要があります。例を挙げます。
Range（”E6″）=”=IF(D6=““““,““““,(D6-D5)*2400)”
やたらと冗長になりますが、このようになります。
青色の部分がエスケープシーケンス部分です。
Formula・FormulaR1C1
上記の方法では、繰り返しのループに入れて使う事ができませんので、相対的に指定する必要があります。そこで登場するのがFormula・FormulaR1C1です。両者の違いは、
Formula はA1,B2などで絶対的指定。
FormulaR1C1 はRCで相対的に指定する事ができる点です。
今回は相対的に指定できるFormulaR1C1を使います。
FormulaR1C1は [ ] を付けると相対的な位置、何もつけないと絶対的な位置を指定できます。
Rは行を指し、Cは列を指します。
Range("C3").FormulaR1C1 = "=RC1" '=$A3この例では、R（行）はそのまま、C1で一番目の列（A列）を指定しているのでA3の値を参照します。
Range("C5").FormulaR1C1 = "=RC[-1]" '=B5この例では、R（行）はそのまま、C[-1]で一つ手前の列（B列）を指定しているのでB5の値を参照します。
プログラムに組み込む
では先の例をFormulaR1C1 で表現してみましょう。
Range（”E6″）=”=IF(D6=””””,””””,(D6-D5)*2400)”
これをFormulaR1C1 で表現すると、
Range（”E6″）.FormulaR1C1 = “=IF(RC[-1]=””””,””””,(RC[-1]–R[-1]C[-1])*2400)”
上記のようになりますね。これは、一つ前の指針値と引き算している例です。
今回のプログラムでは連休に対応したいので、赤色の部分を変数に置き換えます。
また、Range（”E6″）部分も、Cells（6,”E”）に置換えます。
ソースコード１
プログラムを組むときは最初から全てを追わずに段階的に仕上げていく方が上手くいきます。
まずは、「使用量」に前日の値と当日の値を引き算した結果を表示するように書いてみます。
Sub setFormulaTest1()
Dim i As Long
For i = 6 To 36
Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]="""","""",(RC[-1]-R[-1]C[-1])*2400)"
Cells(i, 7).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-1]C[-1])"
Cells(i, 9).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-1]C[-1])"
Next i
End Subソースコード2
次の条件を追加します。網目のある行に関しては値が入りませんので、数式は不要ですので書き込まないようにします。
条件式は、
If Cells(i, “D”).Interior.Pattern <> xlPatternSolid Then
となります。
Interior.Patternが網目のプロパティ設定で、
xlPatternSolidが「網目なし」の値となります。
網目なし以外（何らかの網目が設定されている時）は、For文の最後に飛ぶようにします。
ただ、これだけだと不十分です。B列の日付に注目しましょう。
小の月の場合は、空白が来る事があるので、これを捕まえる事にしましょう。
If Cells(i, “D”).Interior.Pattern <> xlPatternSolid Or Cells(i, “B”) = “” Then GoTo LABEL
これで、数式を設定する箇所を特定できました。
Sub setFormulaTest2()
Dim i As Long
For i = 6 To 36
'D列（値部分）のセルの網掛けが「無し」以外か、B列（日付）が空白でない場合
If Cells(i, "D").Interior.Pattern <> xlPatternSolid Or Cells(i, "B") = "" Then GoTo LABEL
Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]="""","""",(RC[-1]-R[-1]C[-1])*2400)"
Cells(i, 7).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-1]C[-1])"
Cells(i, 9).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-1]C[-1])"
LABEL:
Next i
End Subソースコード3
最後に、休日のずれを考慮するようにしましょう。
Cells(i, 5).FormulaR1C1 = “=IF(RC[-1]=””””,””””,(RC[-1]-R[-1]C[-1])*2400)”
連休が重なるにつれて赤色の付いた１を増やさなければなりません。
連休は何日続くか不定ですが、今回はFor文で適当な最大値をセットする方法にします。
今回は月ごとの表なので、連休の想定される最大値は、月の日付の最大値31でOKでしょう。
二重のループになりますので、カウンタ変数の ｊ を使います。
j は休日が何日続くかの値となります。
For文で、当日のセルから上に遡っていき、網目であれば上、また上と進み、IF文で網目の無い日を捕えます。
If Cells(i – j, “D”).Interior.Pattern = xlPatternSolid Then
最終的に網目なしのセルに行き当たると、その値と当日の値との差を求める数式が記録されます。
Sub setFormula()
Dim i As Long, j As Long
For i = 6 To 36
'D列（値部分）のセルの網掛けが「無し」以外か、B列（日付）が空白でない場合
If Cells(i, "D").Interior.Pattern <> xlPatternSolid Or Cells(i, "B") = "" Then GoTo LABEL
For j = 1 To 31 '連休間隔(勝手に抜けるので最大値は適当に大きな値)
If Cells(i - j, "D").Interior.Pattern = xlPatternSolid Then
Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]="""","""",(RC[-1]-R[-" & j & "]C[-1])*2400)"
Cells(i, 7).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-" & j & "]C[-1])"
Cells(i, 9).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-R[-" & j & "]C[-1])"
Exit For
End If
Next j
LABEL:
Next i
End Sub
上手くいかない時は、、、
これを書いている時、D５のセル部分で ｊ のループで処理が止まらずにエラーとなるケースがありました。この状況を明示的に回避するためには冒頭で、
Range(“D5”).Interior.Pattern = xlPatternSolid
としてやればOKです。D5セルは、前月の最終日の検針値を記載しているセルになります。
デバッガで探ったところ、網目が無い状態にもかかわらず、xlPatternSolid の値ではない状態でした。 おかしいと思った時はデバッガで辿ることが重要です。
コメント