365日EXCELの強制終了に苦しむエンジニア（旋盤工）が、テクニックを詰め込んでみる。

はじめに

本屋に行くと、数学ガール（ポアンカレ予想）『初版』　が売っていたので、ポエムのネタにすべく早速購入しました。
ネタといえば、この記事の元ネタはこれです。
EXCELでNC管理システムを構築して、関数型プログラミングを楽しんでいたのですが、
ある日を境に突然強制終了を繰り返すようになりました。
セルに埋め込む関数が多くなれば、それだけ処理時間が掛かるし、
EXCEL自体は負荷を掛けると簡単に強制終了します。
そこで強制終了しないためにやったテクニックを晒してみたいと思います。

最初から繰越前提でシステムを設計する。

本業の合間にEXCELを弄っているので、前年のデータがそのまま残っている状態でした。
そのままでは、破綻するのは目にみえているので、データに棚卸項目を作って年末の棚卸に備えます。
業務アプリを作る場合は、常識なのだけれど、作り始めた時は、あまり考えていませんでした。

残しておく必要のない埋め込み関数は、マクロで数値化する。

どうやら日報作成システムを作り始めてから、強制終了が日常化したようです。
日報は、メールで報告したら、データだけ履歴として必要なので、割と数値化は簡単なのですが、
手動で数値化する範囲をコピーして値をぺったんでは、面倒ではあります。
また、数値化すると、テーブルに行を追加する場合に、埋め込み関数を勝手にコピーしないので、
行追加マクロで、関数を埋め込む仕組みにする必要があります。
今、メール送信時に、マクロで数値化できるように改造中です。

INDIRECTではなくRANGEを使用する。

割と最近発見したのですが、条件により参照テーブルを変えるのは、INDIRECTではなくRANGEを使用します。
危険な事をすると直に強制終了になりますので、RANGEのほうが安全ですしね。
引数にテーブル名を組み込むよりも、関数内で自由に使えた方が便利です。

テーブル+INDEX+MATCH+OFFSET+RESIZE でテクニックを駆使する。

　あの記事で説明しています。
関数を簡単にして負荷を掛けるのを防ぎます。

関数をリリースする前に、別のEXCELでテストしてからコピペする。

コーディングを少しでも間違えると、エラー処理が走って重すぎて、強制終了になるようです。
しかし、当人は怠惰なので実践していません。

絶対、無限ループにならないようにコーディングする。

以下のように、必ず有限回で終わるように、FOR NEXT ループを回します。

DIM I:FOR I = 1 TO R.ROWS.COUNT
   IF DI(R,I) = "" THEN EXIT FOR
   ...
NEXT I

以上ですが、追加するかも。