皆さんこんにちは。Excel開発15年、セルネッツの竹本です。
今回は「時間の計測2つの方法」について解説をしていきます。
まず実際に2つの方法を順番にやっていきます。
「11、会員データ5000件」というシートに5000人分のダミーデータが入っています。最終行は5001行目になります。
このデータを使って秒数の計測をしていきます。
行う処理としては、ヘッダーをここにコピーして、単純にすべてのセルの値をここに転記します。5000件分を転記していく処理になります。
Aのボタンは細かい時間計測、1000分の1秒まで表示するような細かな表示の仕方です。
Bはざっくり何秒と表示します。通常はBの計測で十分です。
すでに5回実行しているので、この下にどんどん追加されるような動きになります。
まずA、細かいほうからやります。確認画面がここに表示されます。
最終行は5001行目、ステータスバーに表示されて、終わりました。4.109秒です。かなり細かな秒数を確認できました。
もう1回やります。今度は3.8秒になりました。最初は6秒かかったりしていました。
ここに関数が入ってアベレージを取っていますが、平均は4.431秒になります。
今度はざっくりのほうです。秒数的にはあまり変わりませんが、同じようにここのメッセージを確認します。
これは整数です。ざっくり3秒になりました。
もう1回やります。5秒かからないぐらいですよね。
今回は毎回クリアすることを省略しています。下の5001行目までちゃんと書かれていることを確認いただきました。
実際には大差ないということです。
Aで見なければならないときは、私たち開発会社が同じ件数を同じ条件で実行したときにどのぐらい差があるかを見るときです。そのときは細かい秒速までやりますが、そうではないときはBで良いと思います。
プログラムソースの解説をしていきます。
Aの細かいほうとBのざっくりで2つに分けました。
ブックの構成を先にご説明をしておくと、4つのモジュールを作っています。
「O_11、会員データ5000件」という長い名前を「O_11」という4文字でコントロールできるようにしています。
そしてThis workbook、これはなにもないですね。
別の動画で紹介をしますが、ステータスバーはユーザーフォームを使います。「UF01」という短い名前にしています。
使用する変数は11シートの最終行を確認する変数です。「P_11Fin行」というベタな名前をつけています。
あとは時間計測用の変数、タイマー、始まり、Wで取っています。戻り値はlongにしています。
今回、細かく取るほうはAPIを使うので、そのままDeclare PtrSafe Function、64bit対応にしてあげてこのまま使ってください。
そして時間計測。詳しく説明をしていく細かいほうになります。
まずは流れから。Aをクリックするとモジュール時間計測で「細かい」に入ってきます。
今はここが選択されていますが、このモジュールに入ってきたところです。
ここで使っている変数はFin行というlong型の変数と読み込み行、読み込み率です。
ベタにPublicではありません。セルネッツ流では、必ずPublicかそうでないかがわかることをすごく重視しているので、その他みたいな感じで「S」つきにしています。
モジュールもモジュールであることがわかるように「M_」で始まるようにしています。
プロシージャーですが、プロシージャーの前後がはっきりわかるように境界線を入れています。
境界線の文字数も、A4縦で印刷したときに変な改行をしないような、文字溢れしないような目安にしています。
実際にやっていたことですが、まず対象シートの最終行を求めています。
これは別の最終行のところで解説をしていますのでそちらをご覧ください。
11シートの最終行を1列目で検出しています。5001行分ありますよね。
次に確認メッセージです。
これから実行しようとすること、時間計測、細いほうでやるということと、最終データ行を目視チェックできるようにしています。
「はい」を押すとこの下に入ってきて、戻り値は6で返ってきますので、6以外にだったらendで終わってしまって良いです。
エグジットサブで戻す必要がある場合もありますが、今回はやらないので終わりで良いです。
そしてこれが時間計測。
ここでまずタイマーという関数で、今現在の時刻をdouble型のパブリックタイム、始まりに入れてしまいます。
「はい」を押した瞬間の何時何分何秒の時間をここに入れてしまいます。
次に今回はここがポイントです。時間の計測。
開始の時間はここでも取っていますが、細かく取るほうなので、もう1回ここで時間計測というモジュールを呼びます。
このモジュールは時間計測なのでここに入っていきます。
「開始」という文字を渡して、Public型ですね、スタートとエンドが欲しいので、「開始」という言葉を渡してスタートとエンド。
ここで時間計測のモジュールに入ってきました。
ここでSステータスについては「開始」という言葉で入ってくるので、ケース分、ステータスは「開始」になるので、やっていることはこれだけです。
Get Tick Countをタイマースタートのほうに持ってきます。
これが開始時間の取得です。
のちほど最後に取るのは開始ではなく終了したときです。
終了したときも同じようにやりますが、先ほど開始のときに使ったスタート、Get Tick Countからスタートのときの秒数を引けば何秒かかったかがわかりますが、それを1000で丸めることで秒数がわかるようになります。
このモジュールはやっていることはそれだけです。
時間計測というモジュールにこれを渡すことで、タイマーのスタート・エンドが取得されて戻ってきます。
これはパブリック変数のバリアント型、なんでも入るやつにしています。元に戻ります。
戻ってきました。まずここで「はい」を押したときの時間をここで取得します。
そしてAの更新です。画面の更新をfalseということで止めます。
そして出力するエリア。HからMに対してクリア。
先ほどクリアして実行していたんですが、速度が速かったのでそこがわからなかったのです。
クリアを押します。クリアした直後にA1からF1をHに、A1からF1をコピーしてペーストして、Escキーで下のほうに出てくるこれですね、今はクリップボード、メモリの中に入っているので、エスケープをしてなくしました。その処理がここの処理です。
コピーが終わりました。
コピペが終わったあとにはクリップボードに入っているので、カットコピーモード、エスケープを押したときのメモリを解放しています。
そしてここからUF01、ユーザーフォームになります。VBモードレスで表示をします。
UF01というのはこれです。ここでそれが表示されます。
入ってやってからここまで、コンマ何秒の世界になります。
ループ処理、わかりやすく星印で飾ってみましたが、最終行までループします。
今回は解説用で各ステートメントに補足をちょっとずつ入れています。
今回はここの読み込み行からto Fin行というのが5001行目になっていたので、そこまでループします。一括りがここまでです。
その中で1行ずつ読んでいくと、今度は列数、1列目から6列目はベタで6列しかないのでそれを転記をしています。
転記の使用については単純に秒数のカウントの仕方の説明になるので、単純に入れているだけです。
処理ステータスバーについてもユーザーフォームに関する説明になってしまうので、ざっくりここに書かれている通りのことをやっています。
これに関しては別動画で解説をします。
その処理が終わりました。
5001件、5000件の処理が終わったときにステータスバーをhide、非表示にしたあとで、unloadしてメモリを開放したら終わります。
そして画面更新をtrueで元に戻します。
先ほどと同じですね。開始という言葉をここに乗せて渡していたんですが、今度は終了なので、終了だったら下のほうに入ってきます。
そして終了した時刻がここで決まります。
それをセルに出力をしておきたいので、15列目の最終行、この15列目と言っているのはO列のことです。
15列目の最終行を求めて、その最終行に1足したところに今もらってきたタイマー、タイムエンド、そこに書き出します。
次はなくても良いんですが、こういう配慮は習慣づけたほうが良いので、そこにマウスカーソルが止まった状態で完了メッセージを表示します。何秒かかりました。終わったところにカーソルが止まっているということになります。
もう1回やってみましょう。5001件目、変なボタンが右側についてしまっていますね。4.07秒。
今度はBのざっくりバージョンです。
Aの細かいほうで解説し終えた感があります。やっていることはほとんど同じです。ざっくりというタイトルを変えただけです。
現在の時刻を取っています。
先ほど細かいほうではこれを使っていましたが、今回はコメントアウトして、これは使いません。あとは全部一緒です。
ここもこのままですね、先ほどのやつと一緒ですね。
フォーマットで4桁表示で表示するかしないかだけなので、ここは先ほどと同じです。
ここは先ほどの細かいバージョンだったんですが、それを今回使っていないので、それをコメントアウトした感じです。
今度は列がずれただけですね。15列目のこうだったのが右側の16列目のP列になって、ターゲットの主力列が1列右にずれただけです。そこしか変わっていないですね。
最後にその場所にカーソルが止まって完了メッセージが出るということで、違いはそれしかありません。
もう1回Bのざっくりをやってみます。
4秒ということで今ここにカーソルが止まりました。
ということで、この2つの方法があることを覚えていただきたいのですが、基本はBのざっくりバージョンで全然問題はありません。
ポイント、最後になりますが、通常は秒単位で大丈夫です。
ExcelVBAの場合、完了時に秒速を表示するというのは、パソコンによって処理の早いパソコンと遅いパソコンが顕著に出てきます。
遅い早いを感覚的なものではなくて、数値として検証するためにも必ず完了時に何秒かかったのかを表示するようにしてください。
最後、Excelのメリットということで、ExcelVBAの開発のメリットですが、書式が変更できます。
たとえば当初、下3桁を表示していました。もう少し細かくしたいのであれば、このサンプルのところが5桁になりますし、下1桁で良いのであればこういうような表示になります。
これに関して、たとえばAccessのレポート機能でいうと、文字列としてこれが印刷されてきてしまうので、ユーザー側で変更することがなかなかできません。
プログラムをいじらないといけないので、影響調査、改修となってきます。
出力に対する書式の変更ができるというのはExcelVBAの非常に大きなメリットになるので、それを最後に3番目として補足をさせていただきました。
ということで、2つの時間計測の方法を紹介をしました。
今回の解説は以上となります。少しでも参考になったかたはぜひチャンネル登録・高評価をお願いします。毎週金曜日の夜9時に投稿しています。ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
