風柳メモ

ソフトウェア・プログラミング関連の覚書が中心。

エクセルのVBAで正規表現のメソッド実行に時間がかかる現象と対策(仮)

エクセルの VBA で、正規表現(VBScript.RegExp)の .Excute メソッドを使っているプロジェクトにて、不自然に動作が重くなってしまう現象に遭遇しました。
Windows 10 Pro バージョン 2004 (OS ビルド 19041.630) 上で、Microsoft® Excel® for Microsoft 365 MSO (16.0.13328.20262) 32 ビットにて発生。

実は、以前にも同じ現象が発生していたのですが、そのときには Windows の再起動で発生しなくなったので放置していました。
しかし、今回の場合は Excel や Windows を再起動しても改善が見られなかったため、少し調べてみることにしました。




発生した状況(再現方法)

  1. 「Microsoft Excel マクロ有効ワークシート」を新規作成
  2. 標準モジュールを追加し、VBSCript.RegExp の Excute メソッドを使用しているこちらのプロジェクトのソースコードを貼付け
  3. サンプルツイートのテキスト(【】部分に隠し文あり)を選択してコピーし、A1セルに貼り付けたあと、B1セルに「=ZenDecode(A1)」のような数式を入力(画像1)
    なお、この段階では特に遅くは感じない
  4. 一度保存して閉じてから、再度開き、「セキュリティの警告」に対して [コンテンツの有効化] ボタンを押す(画像2)
f:id:furyu-tei:20201115142102p:plain
1. サンプル
f:id:furyu-tei:20201115142146p:plain
2.「セキュリティの警告 マクロが無効にされました。」表示
[コンテンツの有効化]を押した直後や、B1セルを再入力したり、A1&B1を別セルにコピーしたりすると、体感できるほどに不自然に動作が重くなってしまいました。

対策

ワークシートを作成直後には特に重くなかったのに、保存して閉じる→再度開く→[コンテンツの有効化] を実施後に重くなってしまう(その後はずっと重いままとなる)ことに気づいたので、セキュリティ関係の機能の問題ではないかとあたりをつけ、トラスト センターの「信頼できる場所」に、当該マクロ有効ワークシートの場所を追加してみました。
  1. リボンの「ファイル」メニューから、「オプション」→「トラスト センター」で [トラスト センターの設定 (T) ...] をクリック
  2. 「信頼できる場所」の [新しい場所の追加 (A) ...] で、当該ワークシートを置いてあるフォルダを指定する(必要に応じて、「□ この場所のサブフォルダーも信頼する (S)」にもチェックを入れておく)
その後、該当ワークシートで試してみると、軽快に動作するようになりました。

注意事項

  • 同様の環境であっても、現象が再現しない場合もあります
    自分のところでも、同じファイルを、別の PC 上で試しても(「信頼できる場所」に置いていないにも関わらず)特に重くなりませんでした。また、同一 PC 上であっても、ファイルの内容によっては(?)再現しない場合もあるようです。
  • 上記の対策は、マクロのセキュリティ関連の設定になるため、「信頼できる場所」に追加するフォルダについては慎重に検討するようにしてください。試して見る場合には自己責任でお願いします。
  • あくまで当方個人の環境での対策のため、同様の現象が発生していて対策通りの手順を踏んでも改善されない場合もあるかと思いますが、ご容赦願います。

エクセルで揮発性の数式を含むセルを抽出する試み

はじめに

エクセルのワークシートで使える関数の中には揮発性関数というものがあり、これらは再計算の度に再評価(実行)されてしまいます。

揮発性関数の中には OFFSETINDIRECT といった便利な関数も含まれているためについ使ってしまいがちですが、かといってこれらを多用していると動作が重くなる要因のひとつとなります。
場合によっては、他の関数に置き換える等の対処を考えたほうが良いかも知れません。
自動再計算を行わないという対処法もありますが、これはこれで何かと不便です。

そこで、調査用として、シート中の揮発性の数式を含む(再計算時に常に再評価されてしまう)セルを抽出する方法について考えてみました。

f:id:furyu-tei:20201105095343p:plain
サンプルマクロの実行結果例

なお、他の方々から知見を得るために、クイズ形式でツイートもしています。

もっとうまい方法をご存じの方、コメントやこのツイートへのリプライででも教えて下さいませ。

試し方

標準モジュールに下記のサンプルコードを貼り付けて、目的のシートをアクティブにした状態でマクロ(MarkVolatileCells)を実行すると、揮発性の数式を持つセルにメモが添付されます。

サンプルコード


解説

引数の値をそのまま返すような単純なユーザー定義関数(UDF)を作成した場合、その引数にセル参照を指定しておくと、再計算時には参照先が揮発性の場合にのみ当該 UDF が呼び出されることになります。

サンプルソースコードの MarkVolatileCells() では、この性質を利用して、

  1. 作業用に、対象シート(target_sheet)を複製
  2. 複製した作業用のシート(temp_sheet)中で数式を持つセル(=target_sheetでも同じアドレスのセルは数式を持つ)に対し、数式を target_sheet の同じアドレスのセルへの参照を引数としたUDF(ReferCellValue)に置換置換を行う前には自動計算状態(Application.Calculation = xlCalculationAutomatic)にして、全ての ReferCellValue が一度呼び出される(実行される)ようにしておく
  3. 手動計算状態に切り替え(Application.Calculation = xlCalculationManual)た後、コレクション(VolatileAddressCollection)を初期化する
  4. 強制的に再計算(temp_sheet.Calculate)を行うと、参照先が揮発性の場合にのみ ReferCellValue が呼び出されるため、このアドレスをコレクション(VolatileAddressCollection)に記録
  5. 記録されたアドレスに対応する target_sheet 中のセルにメモを添付

という処理を行っています。

注意事項

上記の手法は当方が思いつきで試してみたものであり、正しく揮発性の数式を持つセルが検出できるのかについての保証はありません。あくまで参考として考えて下さい。

また、数式が指定されているセルに対してしか使えないため、

  • 条件付き書式内で揮発性数式が使われている場合
  • リンクした図の場合

等のケースについては検出できません。

ちょっとだけカユイところに手が届くブラウザ拡張/スクリプト

覚え書きを兼ねて、今後も更新される可能性のある自作のブラウザ拡張機能/スクリプト類をまとめてみました。

Amazon.co.jp のカユイところに手が届くブラウザ拡張/スクリプト

覚え書きを兼ねて、今後も更新される可能性のある自作の Amazon.co.jp 用ブラウザ拡張機能/スクリプト類をまとめてみました。

船を出すのなら九月…その船は今どこにふらふらと浮かんでいるのか?

九月というと反射的に、中島みゆきさんの「船を出すのなら九月」とか
アルバム「生きていてもいいですか」に収録に収録されています

生きていてもいいですか【リマスター(HQCD)】

生きていてもいいですか【リマスター(HQCD)】

シギサワカヤさんの「九月病」
九月病 上巻 (ジェッツコミックス)

九月病 上巻 (ジェッツコミックス)

九月病 下巻 (ジェッツコミックス)

九月病 下巻 (ジェッツコミックス)

とかを連想してしまう、業の深い(?)自分がいます。
いずれも名作ですので、体験したことのない方はぜひご体感ください。
なお、心身ともに良好なときに視聴することをおすすめします


船を出すのなら九月 [作詞・作曲:中島みゆき]

船を出すのなら九月 誰も見ていない星の九月
人を捨てるなら九月 人は皆 冬の仕度で夢中だ

あなたがいなくても 愛は愛は愛は
愛は まるで星のようにある

船を出すのなら九月 誰も皆 海を見飽きた頃の九月

夢をとばすなら九月 たくさんの愛がやせる九月
海へ逃げるなら九月 知らぬまに夜が誘いをのばしてる

あなたがいなくても 愛は愛は愛は
愛は どうせ砂のようにある

人を捨てるなら九月 誰も皆 冬を見ている夜の九月
船を出すのなら九月 誰も皆 海を見飽きた頃の九月

宙船(そらふね) [作詞・作曲:中島みゆき]

その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

その船は今どこに ふらふらと浮かんでいるのか
その船は今どこで ボロボロで進んでいるのか
流されまいと逆らいながら
船は挑み 船は傷み
すべての水夫が恐れをなして逃げ去っても
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

その船は自らを宙船(そらふね)と 忘れているのか
その船は舞い上がるその時を 忘れているのか
地平の果て 水平の果て
そこが船の離陸地点
すべての港が灯りを消して黙り込んでも
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

何の試験の時間なんだ 何を裁く秤(はかり)なんだ
何を狙って付き合うんだ 何が船を動かすんだ
何の試験の時間なんだ 何を裁く秤なんだ
何を狙って付き合うんだ 何が船を動かすんだ
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

こちらはアルバム「ララバイSINGER」に収録されています

ララバイSINGER

ララバイSINGER

このブログにしては異色の記事ですが、せっかくはてなブログでJASRAC管理の歌詞を掲載できるようになったのに、そのことに気がついてからもそういえばまだ一度も利用したことなかったなとちょっと思ったので……。

関連