title: Make VBA project run more efficient
body: <p>I am a very new coder in VBA (and in general). I have managed to write some code to check a coordinate file with value in an Excel sheet, and use the coordinate to verify the value in the relevant file coordinate location (the range specified by the coordinate file). My problem is that there are more than 10000 lines to check that involves more than 100 Excel files.</p>
  <p>The run time of the macro is more than 2 hours.</p>
  <p>I have tried to set screen update off, etc., to make it faster but it seems not to be helping much.</p>
  <p>Could anyone suggest how I can make it run more efficiently?</p>
  <h2>Main code</h2>
  <pre><code>Sub Output_varification()
   
  'input folder path for the Axiom output
   
  Dim Folderpath As String
   
      With Application.FileDialog(msoFileDialogFolderPicker)
          .Title = &quot;Select A Target Folder&quot;
          .AllowMultiSelect = False
          If .Show = -1 Then
              Folderpath = .SelectedItems(1) &amp; &quot;\&quot;
          End If
      End With
         
      
      If Folderpath &lt;&gt; &quot;&quot; Then
      'MsgBox Folderpath
     
      Else
      MsgBox &quot;no folder chosen&quot;
     
      End If
   
  'find the starting point of the output file
   
  Dim Start As Range
   
  Dim choice As Variant
   
  choice = MsgBox(&quot;Do you want to start from scratch?&quot;, vbYesNo, &quot;Starting point&quot;)
   
  If choice = 7 Then
   
      Set Start = Application.InputBox(&quot;choose the template line on template number cell (should be column B)&quot;, &quot;Input&quot;, Type:=8)
  'MsgBox Start.Value
   
  Else
  Set Start = ActiveSheet.Range(&quot;A:F&quot;).Find(what:=&quot;Template&quot;, LookIn:=xlValues)
   
  End If
   
  Application.ScreenUpdating = False
   
  While Start.Offset(1, 0).Value &lt;&gt; &quot;&quot;
     
      Call Reading_Coordinates(Start.Offset(1, 0), Folderpath)
     
      Set Start = Start.Offset(1, 0)
   
  Wend
   
  Application.ScreenUpdating = True
   
  MsgBox &quot;Task complete!&quot;
   
  End Sub
  </code></pre>
  <h2>Sub Function 1</h2>
  <pre><code>Private Sub Reading_Coordinates(Line As Range, File As String)
   
  'Line As Range, File As String
   
  Dim Filepath As String
  Dim ctemplate As Workbook
   
  Dim Temp_number As String
  Dim Sheet_number As String
  Dim Row_number As String
  Dim Column_number As String
  Dim Amount As Variant
   
  'test
  'Set Line = Range(&quot;b2&quot;)
  'File = &quot;\\fwlnp006vf.ln.fw.gs.com\zjunli\home\Desktop\Template Hub project\Axiom output\GSI output\&quot;
  'MsgBox File
   
  'Read data in one line in Axiom output
   
  If Temp_number &lt;&gt; Line.Value Then
   
      Temp_number = Line.Value
      Sheet_number = Line.Offset(0, 1).Value
      Row_number = Line.Offset(0, 2).Value
      Column_number = Line.Offset(0, 3).Value
      Amount = Line.Offset(0, 4).Value
     
      'MsgBox Temp_number &amp; Sheet_number &amp; Row_number &amp; Column_number &amp; Amount
     
      'open the file with the template number
      'Note wonn't work for template more than 1 part
     
      If Temp_number &lt;&gt; &quot;F_40_0*&quot; Then
     
          Filepath = Dir(File &amp; &quot;*&quot; &amp; Temp_number &amp; &quot;*.xlsx&quot;)
         
          'MsgBox Filepath
          On Error Resume Next
          Set ctemplate = Workbooks.Open(File &amp; Filepath)
         
              Call Get_Cell(Row_number, Column_number, Filepath)
              If Get_Cell(Row_number, Column_number, Filepath).Value &lt;&gt; Amount Then
                  Line.Offset(0, 4).Interior.Color = 65535
                  Line.Offset(0, 5).Value = Get_Cell(Row_number, Column_number, Filepath).Value
              End If
             
              Workbooks(Filepath).Close (False)
     
      End If
    
  Else
      Sheet_number = Line.Offset(0, 1).Value
      Row_number = Line.Offset(0, 2).Value
      Column_number = Line.Offset(0, 3).Value
      Amount = Line.Offset(0, 4).Value
         
            Call Get_Cell(Row_number, Column_number, Filepath)
              If Get_Cell(Row_number, Column_number, Filepath).Value &lt;&gt; Amount Then
                  Line.Offset(0, 4).Interior.Color = 65535
                  Line.Offset(0, 5).Value = Get_Cell(Row_number, Column_number, Filepath).Value
              End If
     
  End If
   
  End Sub
  </code></pre>
  <h2>Sub function 2</h2>
  <pre><code>Private Function Get_Cell(xrow As String, xcolumn As String, ftemplate As String) As Range
   
  Dim findrow As Range
  Dim findcolumn As Range
   
  Set findrow = Workbooks(ftemplate).Worksheets(1).Range(&quot;C:C&quot;).Find(what:=xrow, LookIn:=xlValues)
  Set findcolumn = Workbooks(ftemplate).Worksheets(1).Range(&quot;D:AZ&quot;).Find(what:=xcolumn, LookIn:=xlValues)
   
  Set Get_Cell = Cells(findrow.row, findcolumn.column)
   
  End Function
  </code></pre>