【事務職が頑張るExcel・VBA】セルの関数を消されたくないときは...

 セルに入っている関数を消しちゃって焦ったことがあるひと...消されちゃって組み直したことがある人って結構いるのかな(;´･ω･)

　今日はいじられたくない関数の変更防止策を考えていくよ♪

　　　　　　　セルの関数は消させない！！

ただし、デメリットとして気軽に関数の式を変更できなくなるので中身を変更しないようなテンプレ処理のところに使おう

目標

　指定したセルに変更があった場合、決められた値で上書きして変更されるのを阻止しよう。

　下図のB5,B6は変更しても元の式で上書きされます。

使用するいろいろなもの

　・Worksheet_Change
　・Application.EnableEvents
　・Intersect
　・Formula

Change関数を準備しよう

　Excelの開発タブからVisual Basicを起動してコード上のタブから「Workheet」タブを選択、その右のタブから「Change」を選択するとコードに「Worksheet_Change」関数が自動生成されるのでやってみよう！

　この関数はExcelのセルが変更されるとそのセルのオブジェクトを引数のTargetにのせて実行される関数だよ(/・ω・)/
セルの変更がされるたびに実行されるので重い処理や処理条件を間違えると反応が鈍くなったりするので注意Σ(ﾟдﾟlll)

変更箇所がどこか調べる

　見出しの目標の図にあるように、B5のSUM関数を変更できないようにしたいので「Intersect」を使って下記のように記述したらいいのだ

   'sum関数のいじられ防止
   If Not Intersect(Range("B5"), Target) Is Nothing Then
       'B5が変更された場合の処理いろいろ
   End If

　Intersectは引数の範囲が重なっているとその範囲を返してくれる。重なっていないとNothingになってしまうのでこれで判断材料にしよう

セルの値を上書きしてあげよ

　セルのB5に消されたくない関数を代入し直してあげればいいよね。

Range("B5").Formula = "=SUM(B1:B4)"

　数式をいれたいときは「Formula」を使って代入してあげよう。これでいけるは...いけないだとΣ(ﾟдﾟlll)ｶﾞｰﾝ

　実はエラーになる。原因は無限ループが起こってしまっている...
セルB5に変更があった場合、セルB5を元の数式で書き換えるという作業をしているのだが、書き換えた結果またWorksheet_Change関数が実行されB5が変更されたのでまた書き換えられ...というループ((´∀｀*))ヶﾗヶﾗ

　一時的にWorksheet_Changeを止めておかないといけないので「EnableEvents」をFalseにしたあとで対象セルを変更。そのあと元に戻すコードにしてあげよう。

       Application.EnableEvents = False
       Range("B5").Formula = "=SUM(B1:B4)"
       Application.EnableEvents = True

式に「”」が入ってるときの注意

文字列として「”」で囲って代入しているので文字列の中に「”」が入っている場合は注意が必要。

Range("B6").Formula = "=TEXT(B5,"000000")"  'これだとエラー

これだとエラーになるのでTEXT関数内の「”」を「””」に書き換えよう

Range("B6").Formula = "=TEXT(B5,""000000"")"  '"を二つつけよう

完成(/・ω・)/

これでセルの関数を簡単に変更することができなくなった。

式を書き換えたい場合はコードをいじらないといけなくはなるけど
(ﾉ∀`)ｲｲｶ

このコードが書けるならそもそもセルに関数置いておく必要なくね？という疑問はそばに避けておこう...


今回も無料部分で完結してるので有料部分はExcelファイルを置いているだけです。