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.
CodePudding user response:
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