0

I have always run this code to retrieve the file path of the folder I select. But for some reason now I am getting a subscript out of range error now.

Public Function GetFolder()

Dim objFileDialog As Office.FileDialog
    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)
    
    With objFileDialog
        .InitialFileName = "G:\"
        .AllowMultiSelect = True
        .ButtonName = "Folder Picker"
        .Title = "Folder Picker"
        If (.Show > 0) Then
        End If
        If (.SelectedItems.Count > 0) Then
            GetFolder = .SelectedItems(1)
        End If
    End With
End Function

Running the code and expecting to retrieve a file path string.

June7
  • 19,874
  • 8
  • 24
  • 34
David
  • 1
  • Works fine here, so run a _Compact & Repair_. – Gustav Jun 29 '23 at 17:06
  • Hi, I Compact & Repair and still get a subscript out of range error still. – David Jun 29 '23 at 17:44
  • Can you check your VBA project has a reference to 'Microsoft Office NN.N Object Library' (where NN.N is likely 16.0) – JohnM Jun 29 '23 at 18:11
  • Hi John, the reference library is exactly what you described. – David Jun 29 '23 at 19:13
  • Rewrite the function. No copy-paste. – Gustav Jun 29 '23 at 19:45
  • 1
    Which line triggers the error? -- Also, try a full [Decompile](https://stackoverflow.com/a/3268188/3820271). – Andre Jun 29 '23 at 20:28
  • I wrote the code out again without copy and paste and recompiled the code. The line If (.Show > 0) will open the window to selected the folder and after selected the folder it will show the subscript error message. Which is weird because This code been working for months and now has this issue? – David Jun 30 '23 at 13:03
  • @Andre asked you yesterday, Which line triggers the error? I don't expect that to be the line with .SelectedItems(1). Do you store the result of GetFolder() in an array, and did you change "Option Base" recently? – hennep Jul 01 '23 at 09:19
  • The `If (.Show > 0) Then End If` part doesn't do anything, did you omit something there? Put a breakpoint there and then step debug, to see which line causes the error. See [Debugging VBA Code](http://www.cpearson.com/excel/DebuggingVBA.aspx) – Andre Jul 01 '23 at 14:26
  • Trying to print `.SelectedItems(0)` or `.SelectedItems(2)` triggers an `Invalid procedure call or argument` error, so a wrong index here doesn't seem to be the issue. – Andre Jul 01 '23 at 14:28
  • Could you try this - create a new Database, add a (standard) Module and add your `GetFolder()` function with no other code ... run the function (ie F5) and see if it works okay? – JohnM Jul 01 '23 at 16:55

0 Answers0