FirstValidPath()

When working with VBA, it's common to encounter scenarios where you need to determine the first valid path among a list of paths.

This is a relatively easy problem to code a one-off solution for. So, for years, that's what I did. Until one day the pattern became too much for me to ignore, and I decided to write a generic function so I could stop repeating myself. The code is simple enough that I would classify this as a convenience function, even though it's much longer than the typical convenience function (many are a single line of code).

I called the function FirstValidPath() and you can use it to check the existence of files or folders.

The Problem

Since I wrote the function, I've used it to solve a variety of problems.

Find the Path to an Executable

I often use this function as a quick and dirty way to look for an executable that could be in a handful of different locations depending on the configuration of the computer or the installed version of the application.

For example, an installed copy of IrfanView is typically found in one of the following paths:

C:\Program Files\IrfanView\i_view32.exe
C:\Program Files (x86)\IrfanView\i_view32.exe
C:\Program Files\IrfanView\i_view64.exe

While a registry lookup is often a more reliable way to look for a path to an executable, hardcoding a limited number of possible paths has the benefit of generally being easier to debug–especially in a controlled environment such as a corporate network.

Enforce a Priority Sort

In certain scenarios, you may need to enforce a priority order when determining the first valid path among multiple options. For example, you might have a list of fallback paths that you want to check in a specific order until you find a valid one. This function makes that task easy and results in readable code.

For example:

Dim ValidPath As Variant
ValidPath = FirstValidPath("C:\Important\Path", _
                           "D:\Fallback\Path", _
                           "E:\Alternative\Path")

Large Data File-Naming-Convention Migrations

I also use this function when migrating to a different file naming convention for large binary objects, such as images. For more details on that scenario, see my article on large data file migrations.

The fact is, once you have this function in your toolbox, you'll find all sorts of uses for it. It's one of those tools you didn't know you needed until you have it.

The Approach

To determine the first valid path, we use the FileSystemObject from the Microsoft Scripting Runtime library.

This object provides a range of useful methods for working with files and folders. By leveraging the FileSystemObject, we can easily check the existence of paths and handle various scenarios, such as wildcard paths, folder paths, and regular file paths.

The Algorithm

Here is the algorithm employed by the FirstValidPath function:

Initialize the result as Null.
Iterate through the array of paths.
For each path:
• Check if it is not null.
• Determine the type of path (wildcard, folder, or file) using string comparisons.
• Use the appropriate method from the FileSystemObject or my custom FileExists function to check the existence of the path.
• If the path exists, assign it to the result and exit the loop.
Return the first valid path found, or Null if no valid paths are present.

The Function

Here's the FirstValidPath() function on its own without required dependencies:

Function FirstValidPath(ParamArray Paths() As Variant) As Variant
    FirstValidPath = Null
    If UBound(Paths) - LBound(Paths) >= 0 Then
        Dim i As Integer
        For i = LBound(Paths) To UBound(Paths)
            If Not IsNull(Paths(i)) Then
                Dim ThisPath As String, PathExists As Boolean
                ThisPath = Paths(i)
                
                If InStr(ThisPath, "*") > 0 Or InStr(ThisPath, "?") Then  'Wildcard
                    PathExists = Len(Dir(ThisPath)) > 0
                ElseIf Right(ThisPath, 1) = "\" Then  'Folder
                    Dim oFSO As New FileSystemObject
                    PathExists = oFSO.FolderExists(ThisPath)
                Else   'File
                    PathExists = FileExists(ThisPath)
                End If
                
                If PathExists Then
                    FirstValidPath = ThisPath
                    Exit For
                End If
            End If
        Next i
    End If
End Function

The Full Code

The code below includes required helper functions that I've written about in the past:

FileExists(): The Subtle Dangers of Dir()

You'll also need to add the following references via Tools > References...

Microsoft Scripting Runtime

Yes, you could use late binding to avoid adding the Microsoft Scripting Runtime library, but I've come to favor early binding unless there is a compelling reason to choose late binding.

The code below can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

Option Compare Database
Option Explicit

' ----------------------------------------------------------------
' Procedure : FirstValidPath
' Date      : 5/23/2008
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/firstvalidpath/
' Purpose   : Find the first valid path among a set of paths.
' Returns   : The first valid path as a Variant value, or Null if no valid path is found.
' Usage     : Useful for locating files or folders that may be in different locations
'               on different users' computers.
' Notes     - Directories must be passed with a trailing "\" otherwise the function
'               will assume it is looking for a file with no extension.
'           - Requires a reference to the Microsoft Scripting Runtime library.
'           - Accepts Null parameters; if all parameters are Null, Null is returned.
' ----------------------------------------------------------------
Function FirstValidPath(ParamArray Paths() As Variant) As Variant
    FirstValidPath = Null
    If UBound(Paths) - LBound(Paths) >= 0 Then
        Dim i As Integer
        For i = LBound(Paths) To UBound(Paths)
            If Not IsNull(Paths(i)) Then
                Dim ThisPath As String, PathExists As Boolean
                ThisPath = Paths(i)
                
                If InStr(ThisPath, "*") > 0 Or InStr(ThisPath, "?") Then  'Wildcard
                    PathExists = Len(Dir(ThisPath)) > 0
                ElseIf Right(ThisPath, 1) = "\" Then  'Folder
                    Dim oFSO As New FileSystemObject
                    PathExists = oFSO.FolderExists(ThisPath)
                Else   'File
                    PathExists = FileExists(ThisPath)
                End If
                
                If PathExists Then
                    FirstValidPath = ThisPath
                    Exit For
                End If
            End If
        Next i
    End If
End Function

'source: https://nolongerset.com/the-subtle-dangers-of-dir/#fileexists-
Private Function FileExists(FullPathOfFile As String)
    Dim HasWildcards As Boolean
    HasWildcards = (InStr(FullPathOfFile, "*") > 0) Or (InStr(FullPathOfFile, "?") > 0)
    
    If HasWildcards Then
        FileExists = (Len(Dir(FullPathOfFile)) > 0)
    Else
        Dim oFSO As New Scripting.FileSystemObject
        FileExists = oFSO.FileExists(FullPathOfFile)
    End If
End Function


Sub TestFirstValidPath()
    Dim fpIrfanExe As String
    fpIrfanExe = FirstValidPath("C:\Program Files\IrfanView\i_view32.exe", _
                                "C:\Program Files (x86)\IrfanView\i_view32.exe", _
                                "C:\Program Files\IrfanView\i_view64.exe")
    Debug.Print fpIrfanExe
End Sub

Sample Usage

Acknowledgements

Article excerpt generated with the help of ChatGPT
Portions of this article's body generated with the help of ChatGPT

Photo by Amy Baugess on Unsplash