VBA运行时错误'52'（错误文件名或编号）Dir函数行报错咨询

问题场景 #

执行文件夹存在性校验逻辑时触发报错，调试器高亮指向Dir函数所在行，原实现代码如下：

Dim ExcelPath As String, PDFPath As String

'Check if folders exist

ExcelPath = ThisWorkbook.Path & "\" & Range("Excel_Folder").Value
PDFPath = ThisWorkbook.Path & "\" & Range("PDF_Folder").Value

If Dir(ExcelPath, vbDirectory) = VBA.Constants.vbNullString Or Dir(PDFPath, vbDirectory) = VBA.Constants.vbNullString Then
    MsgBox "The Excel & PDF folders do not exist. Please create these in the same folder as this workbook first.", vbInformation, "Folder does not exist"
    Exit Sub
End If

代码设计目标为校验当前工作簿同级目录下的Excel、PDF存储文件夹是否存在。

核心诱因 #

• 传入Dir的路径包含非法内容：Range("Excel_Folder")/Range("PDF_Folder")单元格值为空、存在首尾不可见空格、包含Windows系统禁止的文件名字符（/ : * ? " < > |），会直接导致Dir无法解析路径抛出52错误。
• 工作簿未保存触发路径异常：如果当前运行代码的工作簿是新建未保存状态，ThisWorkbook.Path返回空值，拼接后路径为\文件夹名格式的非法相对路径，无法被Dir识别。
• 逻辑写法隐患：VBA中Or运算符不支持短路求值，且连续带参数调用Dir时，若第一次调用已触发路径解析错误，程序会直接抛错中断，不会进入后续判断分支。
• 路径长度超限：拼接后的完整路径长度超过Windows系统默认260字符的路径长度限制时，Dir也会返回该错误。

修复方案 #

替换原有校验逻辑为以下实现，从源头阻断非法输入，同时增加异常捕获：

Dim ExcelPath As String, PDFPath As String
Dim excelFolderExist As Boolean, pdfFolderExist As Boolean
Dim folderName1 As String, folderName2 As String
Const illegalChars As String = "/:*?""<>|"
Dim i As Integer

' 前置校验：工作簿必须已保存才能获取正确的同级目录路径
If ThisWorkbook.Path = vbNullString Then
    MsgBox "请先保存当前工作簿后再执行操作", vbExclamation, "工作簿未保存"
    Exit Sub
End If

' 读取配置并做格式校验
folderName1 = Trim(Range("Excel_Folder").Value)
folderName2 = Trim(Range("PDF_Folder").Value)
' 校验空值
If folderName1 = vbNullString Or folderName2 = vbNullString Then
    MsgBox "Excel_Folder或PDF_Folder单元格配置不能为空", vbExclamation, "配置缺失"
    Exit Sub
End If
' 校验非法字符
For i = 1 To Len(illegalChars)
    If InStr(folderName1, Mid(illegalChars, i, 1)) > 0 Or InStr(folderName2, Mid(illegalChars, i, 1)) > 0 Then
        MsgBox "文件夹配置中包含非法字符 \ / : * ? "" < > |，请检查单元格内容", vbExclamation, "配置错误"
        Exit Sub
    End If
Next i

' 拼接完整路径
ExcelPath = ThisWorkbook.Path & "\" & folderName1
PDFPath = ThisWorkbook.Path & "\" & folderName2

' 分开校验两个路径，增加错误捕获
On Error Resume Next
excelFolderExist = (Dir(ExcelPath, vbDirectory) <> vbNullString)
If Err.Number <> 0 Then
    MsgBox "Excel存储路径非法：" & ExcelPath & vbCrLf & "错误：" & Err.Description, vbExclamation, "路径校验失败"
    Exit Sub
End If
pdfFolderExist = (Dir(PDFPath, vbDirectory) <> vbNullString)
If Err.Number <> 0 Then
    MsgBox "PDF存储路径非法：" & PDFPath & vbCrLf & "错误：" & Err.Description, vbExclamation, "路径校验失败"
    Exit Sub
End If
On Error GoTo 0

' 最终判断文件夹是否存在
If Not excelFolderExist Or Not pdfFolderExist Then
    MsgBox "The Excel & PDF folders do not exist. Please create these in the same folder as this workbook first.", vbInformation, "Folder does not exist"
    Exit Sub
End If

优化点说明 #

• 新增工作簿保存状态校验，避免根路径为空导致的拼接错误
• 提前对单元格读取的文件夹名做去空、空值、非法字符校验，从源头阻断非法路径传入Dir
• 拆分两次Dir调用，避免Or连写导致的无短路、连续调用逻辑冲突问题
• 增加路径校验的错误捕获，出现异常时直接给出明确的错误路径和原因，不会直接抛出运行时错误中断程序

内容的提问来源于stack exchange，提问作者Ro SG