How do I call the FileDialog with a function and pass the results back-CodePudding

I am trying to call the FileDialog in a Function and pass the results back to work with it. I tried the following but is giving me an error (Object variable or With block variable not set):

Function SelectFilesDialog() As Object
    Dim fd As FileDialog
    Dim filePath As String
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .Title = "Select HTML file"
        .Filters.clear
        .Filters.Add "Text Files", "*.html"
    End With
    If fd.Show <> -1 Then
    Exit Function
    End If
    
    SelectFilesDialog = fd
End Function

The function is called as follows:

Sub test()    
    DialogResults = SelectFilesDialog()
    filePath = filePath = fd.SelectedItems(1)    
End Sub

MY QUESTION:
How do I call the FileDialog with a function and pass the results back

CodePudding user response：

Objects in VBA MUST be assigned using the set nomenclature. So your problem will be addressed by

Set SelectFilesDialog = fd

and then

set DialogResults = SelectFilesDialog()

CodePudding user response：

There are a few things to change in the shown code above.

The return-type of the function SelectFilesDialog is Object. An Object-Type is defined by the SET keyword.

So this line SelectFilesDialog = fd should be set SelectFilesDialog = fd. Now this function should be syntactally correct returns an Object.

Same thing here:

Sub test()    
    DialogResults = SelectFilesDialog()
    filePath = filePath = fd.SelectedItems(1)    
End Sub

First of all, the variable DialogResults should be declared as an Object. (By the way - if there is no Option Explicit-Statement in your first line, add it; this will ensure every variable has to be declared) As we know, Object variables will be defined by the set-keyword.

SET DialogResults = SelectFilesDialog()

The following line filePath = filePath = fd.SelectedItems(1) is not an allocation. Its an comparsion of filePath = fd.SelectedItems(1). The result would be allocated to filePath (true|false).

You have to change this line as followed, if you want to allocate the FileDialog-Property "SelectedItems(1)" to your string variable filepath.

filepath = DialogResults.SelectedItems(1)

Be aware that the user could choose the cancel-option in the filedialogs to avoid further runtime errors.