速度対比実験 セル直接アクセスと変数仲介

15,000件ほどのデータ処理の依頼があった。

あるシートのデータを、一部データ変換を行いつつ、別のシートにレイアウト（項目の並び順）に貼りかえるという単純なもの。

１セルから１セルへ直接転記するよう実装していたが、セル直接の読み書きは遅い。

シート上のデータを変数に突っ込んで処理し、処理結果を変数から別のシートへ流し込むようにすると、一体どのくらい速くなるのか興味があったので書き換えてみた。

前者　ほぼ60秒に対し、後者　ほぼ 2秒であった。なんと30倍、仮に3秒であったとしても20倍、高速になった。

もしこういうテクニックを知らないなら「Excelの処理は遅い」という判断が下されてしまう。

上記以外にも、例えばデータの値によってセルに着色する、フォントを変える、などという処理はかなり時間を食ってしまうのだ。まとめて処理することで高速にはなるが、ウルトラＣ的に速くなる手法がないかな、と考えている。技術的には難しいのかもしれないけれど、セルオブジェクトに対してのアクセス速度が速くなれば解決してしまう問題なのかもしれない。

無視される書式設定

ExcelVBAでシステムを作成する時の鬼門は、何と言っても「Excel自身」だと思う。便利である反面、「コレ何？」ということがある。

インターフェースをExcel、データは外部データベースとしていると、外部データベースの内容をレコードセットからセルに一気に貼り付けることは当たり前のように行うが、そのとき設定しておいたはずのセルの書式を無視されることがある。「あれ？カンマ編集してるはずなのに」とセルを選択して抜けるとカンマ編集されたり・・。

回避方法は見つけた。が、動作保証されているのかどうかは謎だ。なので別の機会に紹介することにしよう。

もしかすると他所では発生していないのか？とたまに不安になるが、開発環境と客先では発生する。

Excel自体もそこまでテストして出荷していないだろうから、仕方の無いところかもしれない。

入力規則のリスト

入力規則のリストは従来「自シート内」でないとＮＧであった。

2007からは別シートもＯＫである。記録すると単にシートの修飾が付いただけである。

Sub Macro1()

    With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=Sheet2!H3:H5"

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .IMEMode = xlIMEModeNoControl

        .ShowInput = True

        .ShowError = True

    End With

End Sub

もしかすると別ブックからも？と試してみたが、それはだめそうであった。