第26回目 計算式関係

○第26回目　計算式関係

　セルに入っているデータの種類として、計算式というものがあります。
　これを作成したりすることを考えて見ます。
　
　計算式もデータとなりますので、
Cells(12,2) = "=sum(b3:b10)"
　ということで計算式が入ってしまいます。
Cells(12,3).Formula = "=sum(c3:c10)"
　というほうが正式なようです。
　
　計算式は座標を書きこまなければならないので、このままでは汎用性に欠けます。
　つまり、上記の式では、左辺は変数で記述できますが、右辺は出来ないということです。
　これを何とかしないとマクロの意味合いがありません。
　問題を明確化しておきます。
　
　問題；b列からl列までの12行目に、それぞれ3行目から10行目までの合計の計算式を作ってください、ということになります。
　
　こんなのは通常のエクセルでやればいいのではないか、ということになりますが、応用が利きます(？)ので取り上げてみます。
　列記号と列数値の対応が問題になります。
　このことは第16回で問題として掲げておいたところです。
　
'列名を配置変数に格納して、列番号との対応をとる。
Dim r_no(312)
aa="abcdefghijklmnopqrstuvwxyz"
for　i＝1 to 26
r_no(i) = mid(aa,i,1)
next i

For i = 1 To 11
For j = 1 To 26
r_no(i * 26 + j) = r_no(i) & r_no(j)
Next j
Next i

　このように事前にセットしておくと、マクロは次のようになります。
　for i=2 to 12
Cells(12,i).Formula = "=sum(" & r_no(i) & "3:" & r_no(i) & "10)"
　next i
　
　ということで、多少めんどくさいですが、あっという間に終わってしまいます。
　
　このマクロの問題点は、
　1.通常のエクセル操作で簡単に出来るため意味がない
　2.プログラムをそのつど修正していくので汎用性がない
　ということになります。
　
　1は、その人にニーズがあるかないか、直面しているかしていないということに依存してしまい、一概に問題点と言えませんが、
　2は、工夫の余地がおおいにありですね。
　工夫については、エクセルのセルに変数を入れておき、それを用いて計算式を作るということが考えられます。
　例の場合の変数とは、"=sum("、r_no(i)、"3:"、r_no(i)、"10)"の5項目となります。そして、その内二つが動く変数となります。
　
　工夫の内容を明確化しておきましょう。
　動かない変数(最大6個)、動く変数(最大5個)をセルにセットしておき、それを用いて計算式を作るというマクロ、ということになります。
　当然ながら、計算式では、動かない変数と動く変数が必ず交互に現れ、最後は動かない変数となります。
　計算式を入れる場所も与える必要がありますので、その点も考慮する必要があります。
　
　デバックも入れるとそう簡単には出来そうもありませんね。
　
　
　ということで、別件。
　
　セルに計算式が入っているかどうかの判断です。
　計算式は言ってみればデータなので、どうやって区分するかです。
計算式が入っている時　… cells(2,12).HasFormula = True
計算式が入っていない時… cells(2,12).HasFormula = False
　となります。
　これを使った例を一つ。
　
Sub a50Table_Clear01()
'範囲を指定して実行する。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.ClearContents 'クリア
End If
Next
End Sub

　これは指定した範囲内で計算式のセルを残して、データをクリアするというものです。
　マクロを知らない時、このようなニーズがありましたが、そのときはその作業を人にやらせてしまいました。
　その後、マクロではこんな簡単にできるんだと、びっくりした一例です。
　
　また、上の応用として、計算式の入っているセルのみ保護するということも簡単に出来ます。