Check Folder Existence Using VBA in Excel (Free UDF)

Before a macro creates, deletes or saves into a folder, you should check folder existence using VBA. If the folder is missing, the macro stops with a run-time error. If it already exists, creating it again also fails. In this tutorial you build a small user defined function (UDF), Folder_Existence, that returns TRUE when a folder exists and FALSE when it doesn’t. You can use it directly in a worksheet cell, like any Excel formula, to check a whole list of folders at once. The example checks one folder per employee (EMP-1 to EMP-32) inside a parent folder.

⬇️  Download the Folder Existence Workbook
.zip with one macro-enabled workbook (.xlsm) · Excel 2010 and later for Windows · enable macros after opening

Watch the Video Tutorial

In the video, PK checks 32 employee folders, creates a missing one, recalculates to see FALSE turn into TRUE, and then writes the function from scratch in a new module.

What You Will Build

• A user defined function that takes a folder path and returns TRUE or FALSE.
• A parent path cell (C1) that holds the main folder.
• A Folder Exist column that checks a subfolder for every name in the list.
• Automatic recalculation with Application.Volatile, so a press of F9 refreshes every result.
• A building block you can reuse in macros that create, move or delete folders.

How the Workbook Is Laid Out

• B1: the label Parent Path; C1: the full path of the parent folder, for example C:\Users\YourName\Desktop\MyFolder.
• A3: the header EMP Name; A4:A35: EMP-1 to EMP-32, which are the subfolder names to check.
• B3: the header Folder Exist; B4:B35: the formula that calls the function.

In the video, the parent folder holds EMP-1 to EMP-15 only, so the formula returns TRUE down to EMP-15 and FALSE from EMP-16 onwards.

Part 1: Write the Check Folder Existence Function

Step 1: Insert a module

Go to Developer > Visual Basic (or press Alt + F11), then choose Insert > Module. A function must sit in a standard module, not in a sheet module, to be usable as a worksheet formula.

Step 2: Paste the function

Option Explicit

Function Folder_Existence(MyPath As String) As Boolean

Application.Volatile

If VBA.Dir(MyPath, vbDirectory) = "" Then
    Folder_Existence = False
Else
    Folder_Existence = True
End If

End Function

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm) so the function is kept.

How the function works

• Function Folder_Existence(MyPath As String) As Boolean creates a function with one input, the path to check, and a TRUE or FALSE result.
• Application.Volatile marks the function as volatile, so Excel recalculates it whenever the sheet recalculates, not only when its input changes. That is what lets F9 pick up a folder you just created.
• Dir(MyPath, vbDirectory) asks Windows for the name at that path. The vbDirectory attribute tells the Dir function to include folders as well as files.
• If Dir returns an empty string (""), nothing was found, so the function returns FALSE. Otherwise it returns TRUE.
• The VBA. prefix simply makes it clear that this is VBA’s own Dir function. The video version writes plain Dir, which works the same way.

In the video, PK builds the same logic again under the name Check_Folder_Is_Avaialble. Both functions are in the download, in Module1 and Module2.

Part 2: Use the Function in the Worksheet

Step 3: Enter the formula

In B4, join the parent path, a backslash and the employee name, and pass the result to the function:

=Folder_Existence($C$1&"\"&A4)

Press F4 after selecting C1 to lock it as $C$1, so the parent path stays fixed when you copy the formula. A4 stays relative and moves down the list. For EMP-1 the function receives C:\...\MyFolder\EMP-1.

Step 4: Fill it down

Copy B4 down to B35. Each row now shows TRUE when the employee’s folder exists and FALSE when it is missing. Your own function also appears in the formula AutoComplete list as you type =Folder.

Step 5: Recalculate after creating a folder

Create a missing folder in File Explorer, for example EMP-16. Excel does not watch your folders, so go back to the sheet and press F9 or click Formulas > Calculate Now. Because the function is volatile, every row is checked again and EMP-16 changes from FALSE to TRUE.

Using It Before Creating Folders (Optional Addition)

This macro is an addition, not part of the original file or video. PK’s next step in the series is to create multiple folders from an Excel range. The check is what keeps that safe, because MkDir fails with an error when the folder already exists. This sketch creates only the missing employee folders:

Sub Create_Missing_Folders()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim r As Long
Dim p As String

For r = 4 To sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    If sh.Range("A" & r).Value <> "" Then
        p = sh.Range("C1").Value & "\" & sh.Range("A" & r).Value
        If Dir(p, vbDirectory) = "" Then MkDir p
    End If
Next r

Calculate
MsgBox "Done"

End Sub

Test it on a copy of your folder first. Calculate at the end refreshes the TRUE and FALSE column.

A Stricter Alternative with FolderExists (Optional Addition)

Also an addition. Dir with vbDirectory returns files as well as folders. So if a file called EMP-20 sits in the parent folder, the function above returns TRUE even though no folder exists. If that matters, use the File System Object, which checks folders only:

Function Folder_Exists_FSO(MyPath As String) As Boolean

Application.Volatile
Folder_Exists_FSO = CreateObject("Scripting.FileSystemObject").FolderExists(MyPath)

End Function

CreateObject loads the File System Object at run time, so you don’t need to add a reference. Use it exactly like the original: =Folder_Exists_FSO($C$1&"\"&A4).

Common Errors and Fixes

#NAME? in the Folder Exist column

Excel cannot find the function. Check that macros are enabled, that the code is in a standard module of this workbook, and that the name in the formula matches the function name exactly.

Every row shows FALSE

The parent path in C1 is wrong or has a trailing space. Copy the path from the File Explorer address bar and paste it into C1 without a backslash at the end, because the formula adds one.

Blank rows show TRUE

When the name cell is empty, the formula passes the parent path ending in a backslash, and that folder does exist. Leave the formula out of empty rows, or wrap it: =IF(A4="","",Folder_Existence($C$1&"\"&A4)).

The result does not change after I create a folder

Press F9 or use Formulas > Calculate Now. Creating a folder in Windows does not make Excel recalculate on its own.

Tips

• Check that the Folder Exist cells hold the formula and not a typed TRUE or FALSE. A typed value never updates.
• Add conditional formatting to colour FALSE rows, so missing folders stand out in a long list.
• The same function works with network paths such as \\Server\Share\Reports, as long as you have access to the share.
• Keep reusable functions like this in your Personal Macro Workbook or an add-in, and call them from any macro.
• Microsoft Learn explains Application.Volatile and when a function recalculates.

Want It Ready-Made?

If you work with folders every day, the Folder Automation Tool V1.0 in Excel on NextGenTemplates.com is a finished VBA tool for folder tasks, and you can browse all Excel VBA tools for more ready-made automation.

Related Tutorials

• Create Multiple Folders from an Excel Range
• Rename Multiple Files in Excel VBA with One Click
• File System Object: Get Files Information in Excel
• VBA User Defined Functions: Create a Custom UDF
• Folder Automation Tool V1.0 in Excel VBA

Frequently Asked Questions

How do I check if a folder exists in Excel VBA?

Use the Dir function with the vbDirectory attribute. If it returns an empty string, the folder does not exist. The Folder_Existence function in this tutorial wraps that test and returns TRUE or FALSE.

Can I use the function in a worksheet cell?

Yes. Because it is a public function in a standard module, you can call it like any Excel formula and pass it a full folder path, for example the parent path joined with a subfolder name.

Why does the function use Application.Volatile?

Volatile functions recalculate every time Excel recalculates. That way, pressing F9 after you create or delete a folder updates every TRUE and FALSE result.

Does the function also detect files?

Yes. Dir with vbDirectory returns files and folders, so a file with the same name also gives TRUE. Use the FolderExists version if you need to test for folders only.

Do I need a reference to Microsoft Scripting Runtime?

No. The Dir version is built into VBA, and the FolderExists version uses CreateObject, which needs no reference.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To check folder existence using VBA, you need only a few lines: Dir with vbDirectory, a test for an empty result, and Application.Volatile so the results refresh. Turn it into a user defined function and you can check a whole list of folders from the worksheet, or call it before your macros create or delete anything. Download the workbook, point C1 at your own parent folder and see which subfolders are missing.