Excelマクロの処理を安定させる方法3選

Excel(VBA)マクロ技術者として未熟な方向けに、マクロの処理を安定させる方法を紹介します。

マクロの処理を安定化させる事で、処理速度や信頼性を向上させる事ができ、あなたのプログラミング技術もググっと向上します。

それでは、1つずつ見ていきましょう!

Excelマクロの処理を安定させる方法3選

次の3つを意識するだけで、あなたのプログラミングスキルが飛躍します。

ファイルオープン・クローズ時にDoEventsを使う

ワークブックを開いたり、閉じたりを繰り返すと、Exceclマクロが落ちる事があります。

このエラーが発生する時、デバッグで確認することもできず、Excel自体が落ちるので、原因究明にとても時間がかかってしまいます。

発生してしまうと、ドツボにハマってしまうので今のうちから対策しておきましょう。

Sub Test
  DoEvents 'ココが大切!
  WorkBooks.Open("ファイル名”)
End Sub

こんな感じで、DoEventsをファイルオープンの前に挿入しましょう。

クローズする際も同じです。

Sub Test
  DoEvents 'ココが大切!
  WorkBooks.Close("ファイル名”)
End Sub

とても簡単ですね!

是非、DoEventsを書いておいてくださいね!

セルへの書き込み回数を減らす

続いて、セルへの書き込み回数を減らしましょう。

Excelのセル参照が増えると、マクロの処理スピードが遅くなる、という話をよく聞きますが、参照自体は大きな問題ありません。

一番負荷がかかるのはExcelのセルへの書き込み処理です。

書き込み回数が増えれば増えるほど、処理が遅くなるので可能な限り書き込み回数を減らしましょう。

Sub Test
  Dim i as Long

  '▼遅い処理
  For i = 1 to 100
    WorkSheets(1).Cells(i,1).value = i
  next

End Sub

A列の1行目〜100行目まで繰り返し処理するので100回書き込み処理をすることになります。

次のようにしましょう。

Sub Test
  Dim i as Long
  Dim Temp(100,1) as Long

  '▼配列を作る
  For i = 1 to 100
    temp(i,1) = i
  next

  '▼配列ごとセルに代入する
  WorkSheets.Rnage("A1:A100").Value = temp

End Sub

まず、配列を作ってそれをそのまま、WorkSheetにぶち込みます。

これでスピードUPです。

配列を作るのがめんどくさいと感じるかもしれませんが、データ数が1万を超えてくると、パワーを発揮するので是非覚えておきましょう。

Dictionaryを使い検索スピードをあげる

Dictionaryは辞書ですが、こちらを上手く使うことでデータ参照速度を飛躍的にUPする事ができます。

次のコードは、For文が入れ子になっており、10,000×10,000回処理が走りますね。これは遅くて当たり前ですよね。

さらに、セルの値が等しい時のみ、Debug.printで値を出力します。

Excel上のテーブルのマッチング処理なんかで書いてしまいがちなプログラムですね。

ダメなソースコードはこんな感じです。

Sub test
  Dim i as Long
  Dim j as Long
  
  Dim tWS1 as worksheets
  Dim tWS2 as worksheets
  
  Set tWS1 = WorkBooks.WorkSheets(1)
  Set tWS2 = WorkBooks.WorkSheets(2)

  for i = 1 to 100000
    for j = 1 to 100000

      if tWS1(i,1).Value = tWS(j,1).Value then
        Debug.Pring "ヒットしました"
      End if

    next
  next
End Sub

for文を入れ子にしているので処理に時間がかかることは、理解できますよね、これを高速化するには次のように記載します。

Sub test
  Dim i as Long
  Dim dict as Dictionary
  
  Dim tWS1 as worksheets
  Dim tWS2 as worksheets
  
  Set tWS1 = WorkBooks.WorkSheets(1)
  Set tWS2 = WorkBooks.WorkSheets(2)
  Set dict = New Dictionary

  for i = 1 to 100000
    if dict.Exist(tWS1.Cells(i,1).Value) = False then
      dict.Add Key:=tWS1.Cells(i,1).Value , Item:=i
    End if
  next

  for i = 1 to 100000
      if dict.Exist(tWS2.Cells(j,1).Value) = True then
        Debug.Pring "ヒットしました"
      End if
  next

End Sub

これだとループの回数が単純に20,000回で済むので一つ前の処理に比べ、かなり短縮されますね。

まとめ

Excelマクロの処理を安定させる方法について記載しましたが如何でしたでしょうか?

どれも知っておくと、ExcelVBAの処理が安定しますので、是非使ってくださいね!

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です