Excel 2013で画面更新を抑止したときの効果

背景

Application.ScreenUpdating = False とすることによって画面更新を抑止すれば，処理速度が向上することはよく知られている。

一方，Excel 2013でSDIになったためか，この方法が悪さをするケースも時々あり，Application.Visible = False とすることで解決できたケースもあった。

そこで，画面更新を抑止して処理速度向上を狙う場合の，各種方法の効果を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. VBAで，20000個のセルを1個ずつ選択するプログラム（下記）を作成する。
   
       For I = 1 To 20000 Step 1
           Cells(I, 1).Select
       Next
    
2. このプログラムを実行する際に，下記の各方法で画面更新を抑止し，処理時間をtimeGetTime関数で計測する。

• 対策なし
• Application.Top = 2000（ExcelウィンドウをPC画面の表示可能領域の外側に移動）
• Application.Visible = False（Excelウィンドウを非表示化）
• Windows(ThisWorkbook.Name).Visible = False（ウィンドウを非表示化）
• ThisWorkbook.IsAddin = True（ブックを一時的にアドイン状態にしてウィンドウを非表示化）
• Application.WindowState = xlMinimized（Excelウィンドウを最小化）
• Application.ScreenUpdating = False

3. なお，キャッシュなど様々な影響があると思われるが，それらの影響が対等になるようにいろいろとりまぜて，計10回ずつ測定する。

結果

結果は下図のとおり。エラーバーは十分に小さいので，省略した。

• 定番の Application.ScreenUpdating = False が最も高速であった。
• Application.Visible = False は，なにも対策しないときの2倍高速だが Application.ScreenUpdating = Falseよりは圧倒的に遅い。

Excel 2013のApplication.Waitの分解能

背景

Application.Waitは1秒未満も指定できる。

そこで，どこまで短い時間を指定できるのか，分解能を調べてみた。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. For~NextループでApplication.Wait○○を指定回数だけ回した。
2. この処理にかかる時間をtimeGetTime関数で計測した。

結果

分解能は0.01秒（=10ms）のようである。

Excel 2013 DoEventsの実行時間

背景

Excel 2013になって，DoEventsを挟まないとうまく動作してくれないケースが増えた。
今さらながら，1回あたりの実行時間を調べてみた。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 下記のコードの実行時間をtimeGetTime関数を利用して計測する。
2.  この時間を100000で割って，1回あたりの実行時間を算出する。

For I = 1 To 10 ^ 5 Step 1
      DoEvents
Next

結果

CPU: Core i7 2.4GHzで10万回のループで，約5秒であるから， 1回あたり， 0.05ms（=50ns）のオーダーであった。

Excel 2013でのファイルの読み書きに与えるアロケーションユニットサイズの影響

背景

ハードディスクをフォーマットするときに，アロケーションユニットサイズの設定が可能であるが，Excelファイルの読み書きでのの影響がどのくらいあるかを調べた。

検証環境

Excel 2013（64bit） + Windows 10 Pro（64bit）
CPU: Core i7 2.4GHz
RAM: 16GB
仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. セルに「あ」だけを入力して，約20MBのExcelファイル（xlsb形式）を作成する。
2. 空きパーティション（約97GB）を4kBまたは64kBのアロケーションサイズでフォーマットし，Excelファイルを開くのにかかる時間，上書き保存するのにかかる時間をtimeGetTimeを利用して計測する。
3. キャッシュなど様々な影響があると思われるが，それらの影響が対等になるようにいろいろとりまぜて，計20回ずつ測定する。

結果

以下のとおりで，アロケーションユニットサイズによる性能差はみられない。

Excelファイルを開くのに
アロケーションユニットサイズ4kB:     12.51±0.14秒
アロケーションユニットサイズ64kB:   12.50±0.16秒

Excelファイルを上書き保存するのに
アロケーションユニットサイズ4kB:     16.23±0.23秒
アロケーションユニットサイズ64kB:   16.15±0.23秒

Excel 2013の各種統計関数の処理時間

背景

大量のセルの統計量を算出する場合，関数によって処理時間が大きく異なる場合がある。

そこで，各種統計関数の処理時間を調べてみた。

検証環境

Excel 2013（64bit） + Windows 10 Pro（64bit）
CPU: Core i7 2.4GHz
RAM: 16GB
仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 約100万行×50列（=約5000万個）の乱数列を作成する。ただし，再計算されないように，値は固定する。
2.  5000万個のセルに対して，統計関数によって統計量を算出する。
3. Application.CalculateFull を実行するのに要する時間を，timeGetTime関数を利用して計測する。
4. 2.～3.を，各種関数ごとに実行，比較する。
5. ただし，キャッシュの効果などもあるので，どの関数をテストするかの順序については無作為実験を行う。また，Excel再起動ごとに2セットずつ測定し，計10回ずつの実験を行う。

結果

結果は下図のとおり。誤差棒は10回の標準偏差。

DEVSQ関数を除いては，計算内容から定性的に予想される結果とほぼ同じになった。
また，PERCENTILE.INC関数を使うと遅くなると感じていたのだが，それを裏付ける結果となった。
SUMSQとVAR.Sとの違いはあまりない。これに対し，DEVSQはVAR.Sと比較して大幅に遅い。理由は不明。定義上は，VAR.SとDEVSQの計算量は大して違わないはずであるが・・・。

Excel 2013のVAR.S関数とSTDEV.S関数の速度差

背景

STDEV.関数は，平方根をとるぶんだけ，VAR.S関数よりも処理に時間がかかることも考えられる。

そこで，速度を比較した。

検証環境

Excel 2013（64bit） + Windows 10 Pro（64bit）
CPU: Core i7 2.4GHz
RAM: 16GB
仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 2個の数値をセルに入力する。平方根の処理の差を際立たせるため，個数は少ないほうがよい。
2. VAR.S関数で2個の数値の分散をApplication.Calculatefullメソッドで10000回再計算する。
3. 同様に，STDEV.S関数で標準偏差を計算する。
4. 2，3の処理時間を比較する。

結果

有意な違いはなさそう。
処理時間（=速度）を気にすることなく，便利なほうを使えばよい。

Excel 2013で取り扱えるデータの大きさ（RAND関数の結果を固定した場合）

RAM 16GBを実装し，仮想メモリOFFのコンピュータで，
OS起動直後にExcelだけ起動した状態で，RAMが1.9GB消費されている。

この状態で新規ブックを開き，

1. 列Aの1048576個のセルに RAND関数を入力する。
2. 列全体の結果を値として固定する。
3. これをDo～Loopで繰り返して，何列まで入力できるか?

を試した。

その結果，411列まで実行して，RAMが約8GB消費されていた。この時点で約2時間経過したので，マクロの実行を中止し，保存したところ，ファイル容量は約4.4GBであった。

作成したファイルをいったん閉じて，再度開いて操作可能状態になるのに，2分50秒。

列A全体を選択し，コピーしようとしたところで，Excelが動作停止してしまった。

OSを再起動して，再度試みるも，やはり列全体をコピーしようとしたところで，動作停止した。

RAMの空きは約7GBあるにもかかわらず・・・。

100万個×400列のデータを格納できることは確認できたが，それ以上は実用にならないのだろう。




確認環境

Excel 2013（64bit） + Windows 10 Pro（64bit）

Excel 2013で取り扱えるデータの大きさ（RAND関数）

RAM 16GBを実装し，仮想メモリOFFのコンピュータで，
OS起動直後にExcelだけ起動した状態で，RAMが1.9GB消費されている。

この状態で新規ブックを開き，

1. 列Aの1048576個のセルに RAND関数を入力する。
2. 列Bの1048576個のセルににRAND関数を入力する。
3. これをDo～Loopで繰り返して，何列まで入力できるか?

を試した。

その結果，172列か173列のあたりで，RAMが上限まで消費され，動作不安定となった。

仮想メモリをON（システム管理サイズ）にしてみると，184列までは入力できたが，それ以上は時間がかかりそう（一見，無反応だが，処理はやっている模様）なので中止した。本件に関して，仮想メモリをONにすることの実用上のありがたみはほとんどないということであろう。


なお，仮想メモリをOFFに戻して再度実験し，170列まで入力した状態で，

• ファイル容量は約2.7GB（xlsb形式） 
• 作成したファイルをいったん閉じて，再度開いて操作可能状態になるのに，5分20秒
• 約1億7800万個のセルの平均，標準偏差，最小，最大は計算可能。再計算に25秒～45秒かかる（Core i7，2.4GHz）。

確認環境

Excel 2013（64bit） + Windows 10 Pro（64bit）

ExcelのApplication.Runメソッドの速度

例えば，Book1というブックに，Testというプロシージャがある場合，このプロシージャを呼び出すには，

    Call Test

または

    Application.Run "Test"

とやればよい。

では，どちらの方法が速いか?


    Sub Test()
        '    中味はなし
    End Sub

というテスト用コードを作成し，別のプロシージャから Test を呼び出してみたところ，
Call Test のほうが，Application.Run "Test" よりも約200倍速かった。

・・・とはいうものの，

1回あたりの呼び出し時間は Application.Run "Test" でも10ns（ナノ秒）オーダなので，
ループで何万回も使用するのでなければ，状況に応じて便利なほうを使えばよい。


Application.Runは，ほかのブックのプロシージャを参照設定なしで呼び出せるので便利。
しかも，Privateプロシージャさえも呼び出すことができる。

ただし，ほかのブックのプロシージャを参照設定してCallで呼び出す場合と，参照設定せずにApplication.Runで呼び出す場合とでは，速度差はさらに大きく，前者のほうが800倍速かった。

よく知られているように，あらかじめ参照設定（事前バインディング）したほうがよいということであろう。

確認環境

Excel 2013（64bit） + Windows 7 Professional（64bit）