よく使うコーディング　VBA編

Option Explicit

Sub my_startup()

   Dim wb As Workbook, wb_setting As Workbook
   Dim arr_data As Variant, arr_setting As Variant
   Dim iii As Long, jjj As Long, nnn As Long, flag As Boolean
   Dim StartTime As Date, StopTime As Date
   Dim f_path As String, f_name As String, f_ext As String
   Dim setting_path As String, setting_name As String, setting_ext As String
   Dim last_row As Long, last_col As Long

   ' 処理時間計測
   Application.StatusBar = “処理開始”
   Application.ScreenUpdating = False
   StartTime = Time

   ' ☆ーーーーユーザ入力ーーーーー☆ 
   f_path = “file_path” ' ファイルの格納先パス　最後の￥マークは含めない
   f_name = “path_name” ' ファイル名　拡張子は含めない
   f_ext = “.csv”
   setting_path = “setting_path” ' ファイルの格納先パス　最後の￥マークは含めない
   setting_name = “setting_name” ' ファイル名　拡張子は含めない
   setting_ext = “.csv”
   ' ☆ーーーーーーーーーーーーーー☆

   ' 工程A : 様々な読み込み
   ' データの読み込み
   DoEvents
   Set wb = Workbooks.Open(Filename:=f_path & “\” & f_name & f_ext, _
                                 ReadOnly:=True)

   With wb.Worksheets(1)
      last_row = .Cells(Rows.Count, 1).End(xlUp).Row
      last_col = .Cells(1, Columns.Count).End(xlToLeft).Column
      arr_data = .Range(.Cells(1, 1), .Cells(last_row, last_col))
   End With
   wb.Close
   Set wb = Nothing

   ' セッティングの読み込み
   DoEvents
   Set wb_setting = Workbooks.Open(Filename:=setting_path & “\” & setting_name & setting_ext, _
                                 ReadOnly:=True)

   With wb_setting.Worksheets(1)
      last_row = .Cells(Rows.Count, 1).End(xlUp).Row
      last_col = .Cells(1, Columns.Count).End(xlToLeft).Column
      arr_setting = .Range(.Cells(1, 1), .Cells(last_row, last_col))
   End With
   wb_setting.Close
   Set wb_setting = Nothing

   ' 工程B: 処理をLoop
   nnn = 0
   For iii = LBound(arr_data, 1) + 1 To UBound(arr_data, 1) ' 最初の行を見送る　全部がいいなら+1を消して。

      For jjj = LBound(arr_setting, 1) + 1 To UBound(arr_setting, 1) ' 最初の行を見送る　全部がいいなら+1を消して。

         flag = True ' flagリセット　あくまで位置は参考。都度変わると思っていい。

         ' 工程C: 状態の判定
         If IsEmpty(arr_setting(jjj, 1)) Then ' 判定　あくまで例。ココの選定に合格した者が処理される。
            flag = True
         Else
            flag = False
         End If

         ' 工程D: 処理の実行
         If flag = True Then

            ' 何かの処理

            nnn = nnn + 1 ' このnnnは処理に通過した回数

         End If

         ' DoEvents
         Application.StatusBar = “処理中… data:” & CStr(iii) & “/” & CStr(UBound(arr_data, 1)) _
         & ” setting:” & CStr(jjj) & “/” & CStr(UBound(arr_setting, 1)) _

      Next jjj

   Next iii

   'ステータスバー表示
   Application.ScreenUpdating = True
   ' 時間計測
   StopTime = Time - StartTime
   Application.StatusBar = “処理すべて完了:[” & _
                           Format(Hour(StopTime) * 60 + _
                           Minute(StopTime), “00”) & “分” & _
                           Second(StopTime) & “秒]”

End Sub