数式マクロを復活させるアイデア
セルに数式マクロは、ユーザーが間違って別の値を書き込んでしまうと使えなくなってしまします。
たくさんの数式マクロがある場合、これを元に戻すのは結構な労力となります。
そこで、VBAを使って数式マクロが設定しているセルの位置とマクロの式を保存しておく事が考えられます。
更にこの情報をプログラムコードにしてプロシージャにすれば、誤って書き込んだセルの数式を復活させることができます。
よくよく考えると、プログラムコードというのは文字列ですので、上の例を文字列で書き出す事ができれば、自動でプログラムコードを書き出せる事になります。
そこで、今回はDebug.Print を使い、イミディエイトウィンドウにプロシージャの形で書き出します。これを標準モジュールにコピーすれば実際にプロシージャとして使うことができ、手間をかけずに数式マクロの復元が可能になります。
数式マクロを書き出す際の注意点
VBAで数式マクロをセルに書き込むには、エスケープシーケンスに注意する必要があります。
文字列を囲む ” (ダブルクォーテーション)です。
例えば、ある範囲の○を数える数式では数式の中に “ を含みますので、エスケープシーケンスを使って “” としなければいけません。
セル内の記述 =COUNTIF(C2:C6,”○”)
VBAでの記述 Range（”C8″） = “=COUNTIF(C2:C6,““○““)”
今回は、このような処理も自動で行いたいので、専用のプロシージャを作ります。
'エスケープシーケンスを考慮した文字列を得る
Function getEscString(ByVal s As String)
Dim escStr As String
escStr = Replace(s, """", """""")
escStr = """" & escStr & """"
getEscString = escStr
End Function このプロシージャは、引数の文字列を “” で囲み、中に “ があれば “” に変換してくれます。
引数 =COUNTIF(C2:C6,”○”)
戻り値 “=COUNTIF(C2:C6,””○””)”
全体のプログラム
では上のプロシージャを使って全体のプログラムを作っていきましょう。
まずはシート内のどこに数式マクロが設定されているかを探し、その場所と数式を取得していけば良いでしょう。
探索するセルの範囲（Range）に関しては、全部のセルを探すのは効率が悪いので、とりあえず大きめの範囲を指定することにします。
Sub buildMyCode()
Dim r As Range
Dim myRange As Range
Dim MySheetName As String
Set myRange = Range("A1:ZZ200") '適当に大きめの範囲を指定
MySheetName = ActiveSheet.Name
Debug.Print "Sub FormulasOn" & MySheetName & "()"
Debug.Print " With Worksheets(""" & MySheetName & """)"
For Each r In myRange
If r.HasFormula Then
Debug.Print " .Cells(" & r.Row & "," & r.Column & ")=" & getEscString(r.Formula)
End If
Next r
Debug.Print " End With"
Debug.Print "End Sub"
End Sub実行すると、イミディエイトウィンドウに以下のように出力されますので、これを標準モジュールにコピーすればOKです。
実行すると、イミディエイトウィンドウに以下のように出力されますので、これを標準モジュールにコピーすればOKです。今回作成されたコードは以下のようになりました。
今回は1行のみですが、シートの各所に数式マクロが記述されていたり、複雑な数式マクロが記述されていても自動で以下のようなプロシージャを作ってくれます。
Sub writeMyFormula()
Cells(8,3)="=COUNTIF(C2:C6,""○"")"
End Sub上記のプロシージャを実行する事で、数式マクロが書き換えられても元の状態に戻してくれます。
【2024.4.28 加筆】
本プログラムは、特定のシート上の数式マクロを復活させるためのものなので、With Worksheets でシート名を固定するように変更しました。
尚、セルの入力範囲を ActiveSheet.UsedRange で、使用しているセルの範囲を取得するプログラムは、以下にありますので、参考にしてみてください。
まとめ
今回は少々トリッキーな方法を紹介しました。
気を付けなければいけないのは、文字列の中に ” がある場合はエスケープシーケンスの事を考慮しなければならないという点でした。
また、今回のようにプログラムコードを出力するという方法を応用すればHTMLファイルを作る事もできます。フォルダ内の画像を参照するようなHTMLファイルを自動で作ることができれば、ブラウザでアルバムのように画像のリストを参照できるようになりますね。
（このような事をしてくれるのがワードプレスのようなアプリです。）
また、プログラムコードを自動生成するという行為は、ruby on railsが有名で、アプリの骨格となるコードを生成してしまう機能を持っています。
考えてみれば、我がExcelVBAもボタンを設置すれば、新規にコードを作成してくれますね。
さて、今回のプログラムですが実際にユーザーに勝手に書き変えられてしまうという事例の改善策として実装した経験があります。実際に組み込む場合には、どのタイミングでこのプログラムを実行するかという点に悩む事になると思います。
私の場合は「更新」というボタンを付けて、手動で実行してもらうようにしました。ボタン処理したくない場合は、シートチェンジなどのタイミングが良いかもしれません。
参考にして頂ければ幸いです。
