VBA中Sub检查文件存在正常，Function调用却返回0的问题

检查文件存在性时出现异常：

• 运行Sub宏指定路径I:\SomeFolder\a.txt，调用Dir函数后弹出消息框显示a.txt，结果符合预期；
• 编写FileExists函数后，在Excel单元格中使用=FileExists(A1)（A1单元格值为该文件路径），函数返回0，相同逻辑输入却得到不同结果。

提供的代码 #

Sub宏代码 #

Sub CheckFile()
  f = "I:\SomeFolder\a.txt"
  returnvalue = Dir(f)
  MsgBox (returnvalue)
End Sub

FileExists函数代码 #

Function FileExists(f)
    returnvalue = Dir(f)
    FileExists = returnvalue
End Function

原因分析 #

Excel单元格公式会自动对函数返回值做类型转换：

• Dir函数返回的是字符串类型的文件名，Sub宏里的MsgBox直接输出字符串，不会做类型转换，所以能正常显示文件名；
• 当函数直接返回字符串文件名时，Excel会尝试将其转为数值类型。如果文件名不是纯数字，转换失败就会返回0。

解决方案 #

方案1：返回布尔值（推荐） #

修改函数返回存在与否的布尔值，Excel能正确识别：

Function FileExists(f As String) As Boolean
    ' 文件存在返回True，不存在返回False
    FileExists = Dir(f) <> ""
End Function

方案2：返回字符串并确保Excel识别为文本 #

如果需要返回文件名，可明确返回文本类型，避免被转换为数值：

Function FileExists(f As String) As String
    Dim returnvalue As String
    returnvalue = Dir(f)
    
    If returnvalue <> "" Then
        FileExists = returnvalue
    Else
        FileExists = "文件不存在"
    End If
End Function

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