UnsplashTwitterFacebookはてブLINE2018.10.03この記事は約7分で読めます。Unsplashどうもマサヤです!VBAを触って最初に動いたときは感動しましたよね!でも、触り慣れてくるとある問題にぶつかります。それは・・・・・・VBAの実行速度が遅い!重い!・・・挙句の果てには応答なしのまま動かない。データが多くなればなるほど遅く、重くなってしまいます。セルを参照・計算しているVBAは顕著に遅くなります。VBAが遅い状況でExcelや他のアプリを触るとExcelがほぼ固まります。そのため、実行中はPCをほぼ操作できません。このVBAが遅い問題を解決するには配列が有効です。今日は配列による高速化の方法を紹介しますよ!また、コピペで使えるコードも用意してます!説明不要!という場合は目次からコードの箇所まで飛んでください。自分好みにカスマイズしてお使いください。配列化の前に、お手軽に出来る高速化も試してみませんか?配列は本当に高速なのか?動画で確認なぜ配列を使うと高速なのか?コピペできる!配列を使った高速化コード関数も作りました!まとめ:より高速化の高みへスポンサーリンク配列化の前に、お手軽に出来る高速化も試してみませんか?配列以外にも簡単にできる高速化があります。マクロを簡単に高速化する4つの方法!簡単に高速化出来るコードあったよな~。コード忘れちゃったからコードだけ知りたい!そんなアナタと私のための高速化できるコードを紹介していきます。忘れた時にどうぞ!コピペしてお使いください。mmm-program.com2018.09.19配列は本当に高速なのか?動画で確認では、本題の配列による高速化に触れていきましょう!まずはどれほど速度差があるのか動画で確認してください。↑の動画で行っている処理は至ってシンプルです。行1000×列1000に対して数字を書き出しているだけです。これを配列化したものと、そうでないもの差を動画で確認できます。最初のVBAは配列を使わず、セルを参照しているコードになります。2つ目が配列を使ったコードになります。速度差はご覧いただいたとおりです。最初のVBA(セル参照)は18.07秒、2つ目のVBA(配列化)は1.33秒です。実に、約18倍の速度差があります!なぜ配列を使うと高速なのか?ただ、数字を書き出すだけでなぜこれほど速度に差があるのか?VBAが遅くなる要因としてセル参照の回数の多さがあります。何を隠そうExcelにおいてセルの参照は動作として重い部類に入るんです。今回、紹介した遅いコードは行1000×列1000に対して数値を書き出してます。実にセル参照を100万回行ってます。これに比べて速いコードは、セル参照を2回しかしていません。配列化が速くなることは必然ですね!ちなみに今回使ったコードは下記になります。'配列を使った速いコードです。Subスポンサーリンクコピペできる!配列を使った高速化コードでは、コピペできるコードを紹介します!コードの内容はセル範囲を一括して配列に格納します。Subただし、データの途中で空白行・列があったりすると上手く取り込めません。ひと固まりのデータにしてお使いください。関数も作りました!コードが汚れるから別関数にしたい・・・と私が思ったので作りました!こちらのほうがスッキリしますね。あと、シート名も選べるように変更してます。開始列)コピペしてアナタ好みにカスマイズしてご利用くださいね!まとめ:より高速化の高みへ今回の高速化方法をざっくりまとめると・・・セル参照を極力減らすために配列に格納しましょう!ということになります。逆にセル参照が1回・2回程度であれば意味がない高速化方法ですね。あと、気を付けたいのはセル範囲から配列へは高速化できたものの、配列からセルへ書き出す際に、一つ一つのセルに対して書き出すコードを書くと遅くなるので注意が必要です。この場合は動画で使った下記のサンプルコードのように、配列からセル範囲へ一括で書き出すコードにすれば速く処理できます。my_arrayと、言ったものの一括のセル範囲に書き出すシーンはあまりないかもしれません)笑ただ、セル範囲で書き出す方が間違いなく速いので意識してみましょう。ツールのレイアウトを変えれるなら是非検討してみてくださいね。簡単にExcelへの起動回数(アクセス数)をカウント!そのままコピペで使えます。活用できるとすばらしいですが、まだまだ理解できてません。次のケースだと、どのように配列で書き換え出来ますか?宜しくお願いします。AMかしゅさんブログを見ていただきありがとうございます!配列を理解するのは、なかなか難しいですよね。質問の内容から察するに、データが5行ごとで区切られていると思われます。コピーして別シートへの貼り付ける処理をしたいと見受けられます。コピぺの回数が20万回という尋常でない数字なので、データ構造をきれいにしたほうがいいと思います。具体的には1行開けずにひと塊のデータとして扱うことを考える。今のままで配列を使っても、20万回の参照が発生するのでそこまで高速になりません。配列の置き換えといった意味で回答できずにすいません。PMmMmさまご返事ありがとうございます。回数に関しては改善効果をベンチマークするための一例です。1000回でも、200回でも構いません。実際のデータは、行方向に5フィールドで1組のかたちで並んでます。データ数は数百組とお考えください。これらのデータに対して、任意の計算式を追加して6フィールド目を追加したいのです。編集する方法として、例えば「行の挿入」ではあまりに重た過ぎます。別のシートを追加し、そちらに上の行から順番にコピーして行った方が断然速いと考えて、今はそのような処理を行っています。(まぁ実際断然速いです。)しかし、この処理を稼働しているOSがWin7ということも影響して速度が安定しません。一瞬で処理が終われる方法はないかと模索していました。(Win10へ移行、という選択は今の段階では無しでお願いします)配列を応用した圧倒的な速度改善、という術(すべ)で解消できるなら是非ご教授してほしいです。PMかしゅさん結論から言うと、このデータ構造やしたいことを考えた場合、配列で一瞬で処理させるのはかなり難しいと思います。実際どれ程のデータがあるかわかりませんが、配列化有効なのはセルへの参照回数が少ないときです。5行で一組のデータで、6行目の計算式を入れるといったシーンでは、あまり効果は見込めないでしょう。5行ではなく、全てのデータを一組として扱えるようにデータ構造を変えて、出力(ペースト)も少なくできれば、配列は効果はあります。今のままでも調子が良いときは、10秒以内で処理が完了するとのことなので、本記事の冒頭に紹介している簡単に高速化をまだお試ししていないなら使って見てください。PMmMmさまご返事ありがとうございます。ありがとうございます。コメントをキャンセルメールアドレスが公開されることはありません。時短!コピペで使える複数行を高速に削除するコード2018.09.092018.11.20プロフィールどうも、マサヤ@プログラム勉強中です!メイン記事はプログラムですが、楽しく生きていくための学びや料理など、色々好きなこと書いてます。場所・時間に縛られず、好きに生たい。それを実現するためエンジニアを目指すブログです。私に変えねばならないものを変える勇気を、どうしようもないものを受け入れる静穏を、そして、それらを見分ける洞察力を与えて下さい。時短!コピペで使える複数行を高速に削除するコード2018.09.092018.11.20プロフィールどうも、マサヤ@プログラム勉強中です!メイン記事はプログラムですが、楽しく生きていくための学びや料理など、色々好きなこと書いてます。場所・時間に縛られず、好きに生たい。それを実現するためエンジニアを目指すブログです。私に変えねばならないものを変える勇気を、どうしようもないものを受け入れる静穏を、そして、それらを見分ける洞察力を与えて下さい。