CODEWITHSUNDEEP
excelvba
Amatya
Amatya

Reputation: 1243

Excel VBA Dim declaration conundrum

I want the user to open a file of their choice to do stuff on. I assign the variable File_path as a Variant since I didn't know what its type was. I include Debug.Print TypeName(File_path) in the code to see what it gives me. My code is this:

 Dim File_path As Variant

 FilterType = "Text Files (*.txt),*.txt," & "Comma Separated Files (*.csv),*.csv," & "ASCII Files (*.asc),*.asc," & "All Files (*.*),*.*"

                FilterIndex = 4

            Title = "File to be Selected"

            File_path = Application.GetOpenFilename(FileFilter:=FilterType, FilterIndex:=FilterIndex, Title:=Title)

                If File_path = False Then

                    MsgBox "No file was selected."

                Exit Sub

                End If

           Debug.Print TypeName(File_path)

Debug.Print gives me String.

But when I rerun the code with

 Dim File_path As String

I get a run-time error '13': type mismatch on the line

 If File_path = False Then

What would be the right declaration in this case and what would be the general process of discovering that? Thanks.

Upvotes: 2

Views: 176

Answers (3)

Sam
Sam

Reputation: 7303

You should actually declare the return value as a variant

Dim File_path As Variant

The reason that you are getting the error is because Application.GetOpenFileName returns a Variant. When that value can be converted to a string that is ok, but when the user clicks Cancel or the X button a Boolean is returned and that can't be directly compared to a String type.

To demonstrate this you can run the below bit of code. If you click cancel then you won't get an error. If you select a file, you get the same Type Mismatch error that you received but for the opposite reason. Trying to assign a String to a Boolean type.

Dim ans As Boolean
ans = Application.GetOpenFilename()

If you check out the Microsoft Documentation it explains usage quite well in there.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub dural()
    Dim s As String
    s = CStr(Application.GetOpenFilename())
    If s = "False" Then
        MsgBox "look like you cancelled"
    End If
End Sub

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

if you want to check if there is any string assigned to your variable you need to do it in one of the following ways:

'1st option
if File_path = "" then

'2nd option- recommended
if Len(File_path) = 0 then

In both situation keep your variable File_path as String.

Upvotes: 2

Related Questions