DoEvents in VBA: Asynchronously load a list of files from a folder into a userform

Spoiler alert: asynchronous behaviour isn't supported in VBA. You can only mimic the behaviour of an asynchronous application in VBA thanks to the DoEvents instruction. The biggest benefit of doing it is for the user experience, but you can hardly rely too much on this technique and you have to be aware of its shortcomings and the risks it presents. Please refer to the previous article I wrote on DoEvents in VBA, which will give you some sort of introduction.

Spoiler alert: asynchronous behaviour isn’t supported in VBA. You can only mimic the behaviour of an asynchronous application in VBA thanks to the DoEvents instruction. The biggest benefit of doing it is for the user experience, but you can hardly rely too much on this technique and you have to be aware of its shortcomings and the risks it presents. Please refer to the previous article I wrote on DoEvents in VBA, which will give you some sort of introduction.

Going forward, I will assume you know how to use the DoEvents instruction. I will only focus on the use of DoEvents in a specific situation where you need to populate an area or a userform with a list of files in a set of folders, and don’t want to freeze the user’s screen while doing so.

Get the list of files from a folder

First let’s ask the user to select a folder using a folder dialog box:

Private Sub ChooseFolder_Click()
    Dim fd As FileDialog
    Dim initialFolder As String
 Dim SelectedFolders As String
    initialFolder = "C:\...\Pre-selected Folder"
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   
    With fd
        .Title = "Pick one or more folders:"
        .InitialFileName = initialFolder
        If .Show = -1 Then
            Set SelectedFolder = .SelectedItems.Item(1)
            Call ListFiles(SelectedFolders)
      Else
            Err.Raise 666, , "Error in the folder selection"
        End If
    End With
End Sub

So far so, good, note that there could be many different files to display. Now we want to add each file in this folder to a form’s list box.

Display asynchronously a list of files into a userform

Interacting with external files and folders from VBA can be slow, and the operations could have to repeat a large number of times. In this case, the use of DoEvents to show the progress of the action is totally valid and justified.

Sub ListFiles(Folders)
    On Error Resume Next
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(Folder)
    Set oFiles = oFolder.Files
   
    ' Clean the userform previous content
    Sheets("Sheet1").FilesList2.Clear
   
    ' Add file names to the userform list box
    For Each oFile In oFiles
        ' Note that we are using a activeX userform object
        Sheets("Sheet1").FilesList2.AddItem oFile.Name
        DoEvents  ' display the updated list box and continue
    Next
End Sub

 

We could totally drop the doEvents line, the effect would be to freeze the window as long as the list of files hasn’t been fully added to the user form list box.

This macro doesn't do anything with the list of files that we have just displayed, as it is not the object of this article. We could totally imagine a good deal or use cases:

  • Open a list of files selected from this list,
  • Perform a specific action on these files like move them to a new folder, get some data from them, delete them,
  • Print the selected files

Actually there are as many possible uses as you see fit as userforms are simply a user interface tool.

 

Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances
Close

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :
1
2
3
4
5

Up next

Articles similaires