How to exit Sub() early?

Question

I'm completely new to VBA for applications and I have some pretty basic questions. I'm creating a userform and I need to be able to exit one of the subs early. I simply don't want to get a debug error when I exit GetOpenFilename window without selecting any workbooks and just end the sub. Secondly, I can't get GetOpenFilename dialogue box to show all the other available filenames (xls, xlm, xlsx, xlsm) when "All Files" filter is selected. What should I change for this to work properly?

I've tried to exit sub when TempArray IsEmpty, but for some reason it doesn't work.

   Private Sub cmdAddWorkbooks_Click()

'Variables declarations
Dim FileFilter As String
Dim FilterIndex As Long
Dim Title As String
Dim MultiSelect As Boolean
Dim WorkbookCounter As Long
Dim TempArray() As Variant
Dim SelectedWorkbooksTemp() As Variant

ReDim SelectedWorkbooksTemp(0 To 1, 0 To 1)

'Setting up GetOpenFilename parameteres
FileFilter = "Microsoft Excel Worksheet (*.xlsx),*xlsx," & _
             "Microsoft Excel Worksheet with Macros (*.xlsm),*xlsm," & _
             "Microsoft Excel 97-2003 Worksheet (*.xls),*xls," & _
             "Microsoft Excel 97-2003 Worksheet with Macros (*.xlm),*xlm," & _
             "All Files (.),."
FilterIndex = 5
Title = "Select Workbooks"
MultiSelect = True

'Using GetOpenFilename
On Error Resume Next
TempArray = Application.GetOpenFilename _
    (FileFilter:=FileFilter, _
    FilterIndex:=FilterIndex, _
    Title:=Title, _
    MultiSelect:=MultiSelect)
'exiting doesnt work
On Error GoTo 0
If IsEmpty(TempArray) Then End Sub

WorkbookCounter = UBound(TempArray)
ReDim SelectedWorkbooksTemp(1 To WorkbookCounter, 1 To WorkbookCounter)
For i = 1 To WorkbookCounter
    SelectedWorkbooksTemp(i, 1) = TempArray(i)
    SelectedWorkbooksTemp(i, 2) = Dir(TempArray(i))
Next i

End Sub

`End Sub` is only used as a closing for a `Sub`. What you need is `Exit Sub` instead. — 41686d6564 stands w. Palestine, Sep 03 '19 at 17:54
For more information, read the second half of the accepted answer in the linked question. — 41686d6564 stands w. Palestine, Sep 03 '19 at 17:57
*I've tried to exit sub when TempArray IsEmpty, but for some reason it doesn't work.* - `IsEmpty` returns `True` when a `Variant` subtype is `Variant/Empty`: it will always be `False` for anything that isn't a `Variant/Empty`. Don't use `IsEmpty` to verify whether an array contains elements. Use `UBound` for that, and in the case of `GetOpenFilename`, that `On Error Resume Next` is swallowing a *type mismatch* error when the dialog is cancelled. Remove it, and do `If VarType(tempArray) = vbBoolean Then Exit Sub` - the function returns `False` if the dialog is cancelled. — Mathieu Guindon, Sep 03 '19 at 18:03

Mathieu Guindon · Accepted Answer · 2019-09-03T18:15:06.640

The problem is with IsEmpty, not Exit Sub. End Sub can't be used like this.

IsEmpty will only ever be True when it's given a Variant/Empty. Given anything else, it returns False.

Application.GetOpenFilename does not return a variant array. It returns a Variant that may be an array that contains one or more filenames, or Boolean value False to indicate cancellation.

So, capturing its value into a Variant() will throw a type mismatch error on cancellation - error that you are swallowing with On Error Resume Next... remove that first, along with On Error GoTo 0.

Re-declare your result As Variant, and while you're at it, give it a meaningful name:

Dim selectedFiles As Variant
selectedFiles = Application.GetOpenFilename(...)

Now to exit the procedure scope if the dialog was cancelled, verify what Variant subtype selectedFiles has:

If VarType(selectedFiles) = vbBoolean Then Exit Sub

If it's a Boolean, the dialog was cancelled. Otherwise, you're looking at an array of file names... for which you don't know the boundaries - so don't assume it's 1-based:

For i = LBound(selectedFiles) To UBound(selectedFiles)
    ...
Next

I can't get GetOpenFilename dialogue box to show all the other available filenames (xls, xlm, xlsx, xlsm) when "All Files" filter is selected

AFAIK you need to specify explicitly what you want the "all files" filter to read like, just like every other file filter. Use wildcards as appropriate, and the dot for the file extensions:

FileFilter = "Microsoft Excel Worksheet (*.xlsx),*.xlsx," & _
             "Microsoft Excel Worksheet with Macros (*.xlsm),*.xlsm," & _
             "Microsoft Excel 97-2003 Worksheet (*.xls),*.xls," & _
             "Microsoft Excel 97-2003 Worksheet with Macros (*.xlm),*.xlm," & _
             "All Files (*.*),*.*"

Thank you for help, I understand. Do you happen to know how I could modify my FileFilter in a way which allows "All Files" option to work properly? — Piotrekdoro, Sep 03 '19 at 18:26

How to exit Sub() early?

1 Answers1