Why do Word VBA Collection.Add convert File objects to Strings?


  • I'm trying to use a VBA Collection to store files for later calculations on the file objects. However, the objects stored in the Collection isn't Files; instead they are Variant/Strings. I have written a short demonstration of whats happening:

    Sub StartSub()
      Dim fso As New FileSystemObject
      Dim d As Folder
      Dim f As file
      Dim files As New collection
      Dim fileNames As collection
      Dim fd As FileDialog
      Set fd = Application.FileDialog(msoFileDialogFolderPicker)
      fd.Title = "Select dir"
      Set d = fso.GetFolder(fd.SelectedItems(1))
      For Each f In d.files
        files.Add (f)
      Next f
      Set filesNames = GetFileNames(files)
    End Sub
    Function GetFileNames(files As collection) As collection
      Dim fileNames As collection
      Dim file As file
      For Each file In files
        fileNames.Add (file.Name)
      Set GetFileNames = fileNames
    End Function
    When I run this, the files Collection in StartSub contains Variant/Strings, and when trying to loop over these in GetFileNames it fails with a runtime error 424.

    I now have three questions:

    1. Why isn't  a File object stored in the Collection?

    2. Can a File object be made to be stored in the Collection through some magic?

    Best regards,

    ///Jens Carlberg

    Wednesday, July 24, 2013 2:09 PM