title: ReDim Preserve raise error Subscript out of range
body: <p>Getting a </p>
  
  <blockquote>
    <p>Run-time : Error No. 9<br>
    Subscript out of range</p>
  </blockquote>
  
  <p>at the line:</p>
  
  <pre><code>ReDim Preserve aryFileNames(UBound(aryFileNames) - 1) 
  </code></pre>
  
  <p>In the code below which is meant to convert text files to Excel files in a particular folder.</p>
  
  <pre><code>Sub ConvertTextFiles()
      Dim fso As Object '&lt;---FileSystemObject
      Dim fol As Object '&lt;---Folder
      Dim fil As Object '&lt;---File
      Dim strPath As String
      Dim aryFileNames As Variant
      Dim i As Long
      Dim wbText As Workbook
  
      Application.ScreenUpdating = False
      '// I am assuming the textfiles are in the same folder as the workbook with //
      '// the code are. //
      strPath = ThisWorkbook.Path &amp; Application.PathSeparator
  
      '// Set a reference to the folder using FSO, so we can use the Files collection.//
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set fol = fso.GetFolder(strPath)
  
      '// Using FSO's Files collection, we'll run through and build an array of //
      '// textfile names that exist in the folder. //
      ReDim aryFileNames(0)
      For Each fil In fol.Files
          If fil.Type = "Text Document" Then
              '// If correct Type (a text file), we'll assign the name of the found //
              '// textfile to the last element in the array - then add an empty //
              '// element to the array for next loop around... //
              aryFileNames(UBound(aryFileNames)) = fil.Name
              ReDim Preserve aryFileNames(UBound(aryFileNames) + 1)
          End If
      Next
      '// ... now since we were adding an empty element to the array, that means we'll//
      '// have an emmpty ending element after the above loop -  get rid of it here. //
      ReDim Preserve aryFileNames(UBound(aryFileNames) - 1)
  
      '// Basically, For Each element in the array... //
      For i = LBound(aryFileNames) To UBound(aryFileNames)
          '// ...open the textfile, set a reference to it, SaveAs and Close. //
          Workbooks.OpenText Filename:=strPath &amp; aryFileNames(i), _
          Origin:=xlWindows, _
          StartRow:=1, _
          DataType:=xlFixedWidth, _
          FieldInfo:=Array(Array(0, 1), _
          Array(7, 1), _
          Array(55, 1), _
          Array(68, 1))
          Set wbText = ActiveWorkbook
          wbText.Worksheets(1).Columns("A:D").EntireColumn.AutoFit
          wbText.SaveAs Filename:=strPath &amp; Left(aryFileNames(i), Len(aryFileNames(i)) - 4), _
          FileFormat:=xlWorkbookNormal
  
          wbText.Close
      Next
      Application.ScreenUpdating = True
  End Sub 
  </code></pre>