Excel File Dialog extremely slow

Question

Good morning. I have the following code in an Excel worksheet. It's an ActiveX control button which runs the following when pressed..

Private Sub importSheetPathbtn_Click()
    Dim fd As Office.FileDialog 'folder dialog box
    Dim strPath As String 'holds the path/filename
    Dim objFso 'create object to set as filesystem object in order to use folderexists... dumb
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker) 'sets the filedialog variable as folder picker object
    Set objFso = CreateObject("Scripting.FileSystemObject") 'creates the object
    
    'set parameters
    With fd
        'checks to see if master path has content
        If masterPathlbl.Caption <> "" Then
            'if master path has content checks to see if the content points to an existing directory
            If objFso.FolderExists(masterPathlbl.Caption) Then
                .InitialFileName = masterPathlbl.Caption 'if master path points to an existing directory, opens folder dialog box in that directory
            Else
                .InitialFileName = "C:\" 'if master path content is not an existing directory then opens to C drive
            End If
        Else 'if master path has no content, opens to C drive
            .InitialFileName = "C:\"
        End If
        
        
        If .Show = -1 Then 'if ok is pressed
            strPath = .SelectedItems(1)
        End If
    End With
    
    'if a valid path was seletced then places it into the master path
    If objFso.FolderExists(strPath) Then
        masterPathlbl.Caption = strPath 'places the selected folder into the master path label
    End If
    
    'clears objects
    'fd = Nothing
    'objFso = Nothing
End Sub

All it is doing is taking the folder the user selects and placing its path into a label as a caption. It works, but takes a good 10-15 seconds.

On a side note, I am also wondering how to properly close the objects that are created?

Any help would be appreciated, thanks for your time.

I can't see why that should take that long, but wouldn't `Dir` be less overhead than FSO? — Rory, Feb 24 '22 at 15:18
I am not too familiar with VBA, can you elaborate how you'd use Dir? — BGoetz, Feb 24 '22 at 15:22
`If Dir(masterPathlbl.Caption, vbdirectory) <> vbnullstring then` rather than all the `objFso.FolderExists` calls — Rory, Feb 24 '22 at 15:24
Awesome, thank you. I couldn't find a better way to do that online. I also changed "fd = nothing" to "Set fd = nothing", not sure why I didn't try that before. But back to the main issue, the lag is still there. — BGoetz, Feb 24 '22 at 15:35
Local, but the finished sheet will use network locations. I should probably mention I'm running excel in Windows 10 on a dual boot MacBook Pro. — BGoetz, Feb 24 '22 at 15:40

simple-solution · Accepted Answer · 2022-02-25T13:22:00.620

I did use TimerBenchmark (How do you test running time of VBA code?, How do you test running time of VBA code? and VBA-Benchmark) and entered three bm.TrackByName "label" lines:

Sub importSheetPathbtn_Click()
    ...
    ...
    bm.TrackByName "setup till .show"                  'bm ... added
    If .Show = -1 Then 'if ok is pressed
        bm.TrackByName "immediately after .show"       'bm ... added
    ...
    ...
    bm.TrackByName "finish"                            'bm ... added
End Sub

As visible in the TimerBenchmark your code needs minimal time:

IDnr  Name                     Count  Sum of tics  Percentage  Time sum
0     setup till .show             1           92       0,00%   9200 ns  <--- YOUR CODE
1     immediately after .show      1   38.232.918      99,99%    3,82 s
2     finish                       1        2.677       0,01%    268 us  <--- YOUR CODE
      TOTAL                        3   38.235.687     100,00%    3,82 s

Total time recorded:             3,82 s

You have to review your operating system setup to search for the reasons why your windows explorer reacts slowly. There are 100's of possible reasons!

'REMARK:
'when using VBA-Benchmark = cBenchmark.bas
'you need to change "QuickSort" with "QuickSortArray"
'in line 720 + 721

If (inLow < tmpHi) Then QuickSortArray vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSortArray vArray, tmpLow, inHi

instead of
If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

Interesting, I've never heard of TimerBenchmark. I'm going to mark this as answered since it seems as though it's not the code but my setup. Thanks for your time. — BGoetz, Feb 25 '22 at 15:51

Excel File Dialog extremely slow

1 Answers1